This is a basic/intermediate level course for those who are mainly self-taught users of Excel who would like to improve their skills and become more proficient users. The workshop focuses on those features of Excel , especially tables, that can be applied immediately to your data and make the biggest impact on your spreadsheet usage whatever type of data you work with.
Skills introduced:
Worksheets: navigating, auto-fill, moving & copying worksheets, worksheet tabs, number formatting.
Excel Tables: advantages, creating, sorting & filtering, adding up-date data, themes & styles, referenced formula.
Introduction to Charts: choosing the correct chart type, creating and editing basic charts.
Conditional Formatting: allows cells to be automatically formatted (with colour of text/cell etc.) if they meet certain criteria set by the user.
Formula introduction: basic arithmetical functions and order of preference, AutoSum, sum, counta, count, min, max, average.
Take your basic Excel skills up to a new level and work more efficiently and effectively with this workshop. Master some of the advanced uses of Excel Tables, format your data accurately and learn some of the some popular and most effective business formula.
Skills introduced:
General workbook/worksheets tips
Number formatting including custom formatting of dates and numbers
Advanced Excel Tables features including advanced filtering and sorting including Slicers, Forms for entering data, and structured referenced formula
Formula & Functions including:
Absolute & Relative references
Logical functions: IF, AND, OR, SUMIFS, COUNTIFS, AVERAGEIFS
Lookup functions: VLOOKUP, XLOOKUP
Dashboards
A Dashboard is a visual, dynamic, summary / overview of a business, set out on one spreadsheet using graphs and tables. Dashboards analyse and display an overview of your data quickly and easily in an attractive and dynamic way.
Advantages include:
· Direct focus on KPIs
· Summarise large quantities of data
· Contain a combination of graphs, tables and other visual images
· Dynamic, allowing the user to select which data to display at any time
· Easy to update when new data is added
· Assist in making quick and reliable business decisions
· Help keep track of business performance
Skills introduced:
Attendees will learn how to create:
· Excel Tables
· Pivot Tables
· Graphs
· Timelines
· Slicers
· Create and modify a Dashboard
· Dashboard design and elements of good layout
By the end of this workshop, you will have created a fully functional, professional looking dashboard. You will also have the confidence, and knowledge to create your own dashboards in the future.
Requirements:
This is an intermediate level workshop and you will need to be reasonably confident uses of spreadsheets. Special knowledge of any of the skills to be covered is not necessary.
Date and Time functions
This workshop covers some of the most important date and time functions used in business applications to help you create and maintain timesheets, work-logs, invoices and orders, stock control and project management activities.
Requirements:
This is an intermediate level workshop so you will be a reasonably confident user of Excel with a knowledge of how basic formula work.
Skills covered:
The workshop will cover the following:
formatting dates and times;
using arithmetical calculations and date and time functions to:
work out timesheets,
apply date and time stamps,
track hours worked,
calculate differences between dates,
calculate working days with or without weekends and holidays,
calculate differences between dates,
use conditional formatting to highlight dates
The functions covered will include:
TODAY(), NOW(), DATE(year, month, day), EDATE(), WEEKNUM(), WEEKDAY(), WORKDAY(), INTWORKDAY(), NETWORKDAYS(), DATEDIF(), TIME(hour, minute, second).
Excel Tables
Excel Tables form the basis for using data effectively and efficiently. A basic understanding of Tables is essential for any Excel user.
Excel Tables have many advantages:
Headers always remain visible as you scroll down: table headers replace column letters when scrolling.
Sorting & filtering data quickly and effectively including the use of Slicers and Advanced filtering.
Automatic updating of data into the table.
Using a Form to enter data.
Adding Themes and Styles.
Tables have their own contextual tab: Table Design which allows for several options e.g. Table Name; Remove Duplicates; Insert Slicer; Table Style Options; Quick Styles.
Formula entered in one cell is automatically entered in entire column and changes when one cell is changed.
Structured References (use of table and column header names): used to make formula more readable (e.g. =SUM( Sales2[Order Amount]).
The workshop also covers Conditional Formatting and Data Validation which complement Tables.
Introduction and skills introduced:
Power BI (Business Intelligence) is a stand-alone Microsoft business intelligence software that is free to download. There is also a mobile version for Apple and Android.
This workshop is an introduction to Power BI Desktop which is a very powerful data analysis tool that makes data analysis relatively straight forward. It can analyse vast amounts of data, from a variety of sources (e.g. internet, Excel, Access, CSV files, text files, external databases SQL etc.) and provide clear understandable visualisation of the data in the form of reports, charts, maps, and dashboards, which can be shared.
Power BI uses the same engine to “get and transform data” that Excel uses in the form of Power Query and Power Pivot and Calculated Fields.
Power Pivot allows for the data to be collected and “cleaned” (the data is cleaned by changing it, adding, deleting, or modifying columns, and inserting calculations). Where necessary various tables of data can be linked and relationships formed between them to enable more effective analysis.
Requirements:
You will need to have downloaded Power BI Desktop onto your laptop (Ctrl + Click: Downloads | Microsoft Power BI).
Any knowledge of Power Pivot and Power Query will be useful but is not essential. You are welcome to contact the trainer to discuss whether the workshop may be suitable for you.
Power Query is a business analytics tool that can transform the way you work with data by automating repetitive tasks and analyzing large (or small) amounts of data from a variety of sources without using Excel functions or formula.
Power Query (called: Get & Transform from 2016) gives added capability in that it allows data from multiple sources (e.g. internet, Excel, Access, CSV files, text files, external databases SQL etc.) to be downloaded and then transformed (by adding or deleting columns, cleaning data and adding calculations). The data can then be downloaded and analyzed in an Excel table, Pivot Table. All this analysis is done without using any Excel formula.
Power Query is also part of Power BI so this workshop will serve as an introduction to Power BI.
Content:
This workshop will cover many of Power Query's Transformation and Add Column capabilities using several examples, including:
A business wants to import several CSV files from an external database containing information about its clients, sales and products.
Power Query is used to import, select, and clean the data, and combine it into a single table which is then analyzed in a Pivot Table. The following month’s data is added and is automatically updated.
Requirements:
Power Query is available in Excel 2016, 2019 and Microsoft 365. (It is available as a downloadable add-in for 2010 and 2013 from the Microsoft site. If you decide to download the add-in please ensure that this is done before you come to the workshop). To check your version of Excel, open a spreadsheet, click on File, Accounts.
This is an Intermediate/Advanced level workshop and so you should be familiar with Excel tables have some basic knowledge of Pivot Tables. You are welcome to contact me to discuss whether the workshop may be suitable for you.
Pivot Tables are powerful Data Analytical tools for summarizing, sorting, re-organising and grouping data in a sophisticated and meaningful way and they can also form the basis for developing Dashboards.
The workshop will cover:
preparing the raw data,
creating and modifying Pivot Tables,
formatting Pivot Table fields,
inserting grand-totals, sub-totals & filters,
report layouts,
grouping data.
The workshop will serve as a foundation for the more advanced Power Pivot Power Query and the Data Model workshop.
Skills needed: This is an intermediate level workshop for reasonably confident users of Excel.
Interactive workshop: The workshop is interactive, and attendees will get hands-on experience creating and modifying a Pivot Table. Raw data to work with and notes will be provided.
Skills needed:
This is an intermediate level workshop and attendees should be reasonably confident users of Excel and be able to create a basic chart.
Requirements:
Attendees must bring their own laptop computer (not Tablet) with full Microsoft Excel, version 2007 or higher. (To check version, open a spreadsheet, click on File then Account).
Skills introduced: Charts allow a visual representation of data and are excellent for summing up large amounts of information in an easy to read and understand format. The workshop will cover: criteria for selecting the best chart for specific data and purposes; detailed formatting and customising of charts and data series; a variety of chart types including: bar, column, pie, line, combination, mirror, stacked and picture.
Interactive workshop: The workshop is interactive and attendees will get hands-on experiences creating charts, formatting and adapting them. Raw data to work with and useful summary notes will be provided.