Home / Non-Accredited / Microsoft Office & Computer Skills / Excel Pivot Tables, Dashboards and Power Pivot (2 days)
Quick Look Course Summary:Excel Pivot Tables, Dashboards and Power Pivot (2 days)
-
Length: 2 day(s)
-
Price (at your venue): 1 Person R 10,326 EX VAT 3 Person R 7,594 EX VAT 10 Person R 5,537 EX VAT
-
Certification Type:Non-Accredited
-
Locations & Venues: Off-site or in-house. We train in all major city centres throughout South Africa.
Get Free & personalised
Training Advice
Excel Pivot Tables, Dashboards and Power Pivot (2 days)
Your team turns raw system exports into refreshable summaries and interactive dashboards – without long formulas or waiting for one Excel expert.
Finance, sales, HR and operations teams spend hours every month summarising exports from accounting, payroll, CRM and ERP systems by hand. A pivot table does the same work in seconds and refreshes when new data arrives. This two-day excel pivot table course takes delegates who already use Excel from their first pivot table to an interactive dashboard built on several related tables.
Day one is pivot tables from start to finish: preparing clean source data, building and adjusting reports, grouping dates and numbers, slicers and timelines, Show Values As, calculated fields and items, drilling down to the detail and using GETPIVOTDATA. Day two turns those skills into reporting: pivot charts and graphs, a dashboard with connected slicers and KPI cards, Power Query to combine and clean monthly files, and Power Pivot with the Data Model, relationships and DAX measures, including year-to-date and same-period-last-year comparisons.
Exercises use realistic finance, sales and HR data in Microsoft 365 or Excel 2021/2024, and delegates finish by building a complete dashboard from raw data. The course is also the natural stepping stone to Power BI, which uses the same Power Query and DAX skills.
Why this course
For your organisation
- Monthly reporting that refreshes instead of being rebuilt
- Faster answers to management questions, with fewer formula errors
- Interactive dashboards managers can filter themselves
- Less reliance on a single Excel expert
For your delegates
- Confidence building and adjusting pivot tables on any data set
- A repeatable way to clean and combine system exports
- Your own working dashboard, built during the course
- A first grounding in Power Pivot and DAX
What delegates will be able to do
- Prepare source data so that pivot tables summarise it correctly the first time
- Build, rearrange, filter and format pivot tables
- Group dates and numbers and control subtotals and grand totals
- Add slicers and timelines so colleagues can filter a report safely
- Use Show Values As, calculated fields and calculated items
- Create pivot charts and an interactive dashboard with connected slicers
- Combine and clean files with Power Query and refresh them each month
- Relate several tables in the Data Model and write core DAX measures
- Share, refresh and maintain pivot reports and dashboards
Who should attend
Finance and accounts teams, sales and operations analysts, HR and payroll administrators, and managers who already have a working knowledge of Excel and need to turn data exports into summaries, reports and dashboards without complex formulas. Delegates should be comfortable with tables, sorting and filtering and basic formulas; staff who first need formulas and lookups should start with our Excel Intermediate course.
Course outline
Day 1 – Pivot tables from start to finish
Making the data work for you
- What pivot tables do and the questions they answer
- Clean source data: one header row, no blank rows and no merged cells
- Converting ranges to Excel tables so reports grow with the data
- Fixing numbers stored as text and inconsistent dates
A tour of the pivot table
- Creating a pivot table and using Recommended PivotTables
- The Fields pane: Rows, Columns, Values and Filters
- Several views of the same data
- Compact, outline and tabular report layouts
Adjusting the pivot table
- Adding, removing and renaming fields
- Number formatting that survives a refresh
- Sorting, Top 10, label and value filters
- Refreshing and changing the data source
Grouping, subtotals and slicers
- Grouping dates by month, quarter and year
- Grouping numbers into bands and grouping items by hand
- Showing, hiding and positioning subtotals and grand totals
- Inserting and formatting slicers and timelines
Calculations in pivot tables
- Sum, Count, Average, Max, Min and Distinct Count
- Show Values As: percentage of total, difference from, running total and rank
- Adding a second value field
- Calculated fields and calculated items
Using pivot results elsewhere
- GETPIVOTDATA and how to switch it off
- Show Report Filter Pages
- Drilling down to the rows behind a number
- PivotTable options and styles, and keeping formatting on refresh
Day 2 – Dashboards, Power Query and Power Pivot
Pivot charts
- Creating a PivotChart
- Choosing column, line or combination charts, and when a pie works
- Filtering charts with field buttons and slicers
- Formatting charts for clear reporting
Building an interactive dashboard
- Planning a dashboard: audience, KPIs and layout
- Connecting one slicer to several pivot tables
- KPI cards linked to pivot results
- Conditional formatting, sparklines and data bars
- Tidying and protecting the dashboard sheet
Combining and cleaning data with Power Query
- Importing from workbooks, CSV files and whole folders
- Removing columns, splitting, changing types and unpivoting
- Appending and merging queries
- Refreshing a repeatable query each month
The Data Model and Power Pivot
- Using several tables as source data through the Data Model
- The Power Pivot window and Diagram view
- Creating relationships between tables
- One pivot table from several related tables
DAX measures
- Calculated columns compared with measures
- Core DAX: SUM, COUNTROWS, DIVIDE and CALCULATE
- A date table, year-to-date and same period last year
- Setting up KPIs in Power Pivot
Sharing and maintaining reports
- Refresh All and refresh on open
- Sharing through OneDrive, SharePoint and Teams
- What works in Excel for the web, and when to move to Power BI
- Final exercise: a complete dashboard from raw data
How it is delivered
Two days, 08:30-16:00. In person at your offices anywhere in South Africa, at a BOTI venue, or live online. One delegate or a group of up to 20, each on their own Windows computer with Microsoft 365 or Excel 2021/2024; laptops with the software can be supplied on request. Workbook and practice data included, and exercises can use your own exports. We deliver it at your offices in Johannesburg, Pretoria, Durban, Cape Town and anywhere else in South Africa, at a BOTI venue in Johannesburg, or live online for teams in different places.
What our clients say
“The facilitator effectively encouraged participation and used practical examples, discussions and interactive activities to ensure that the concepts were understood and could be applied in the workplace.”
“Business Optimization Training Institute facilitated Microsoft Excel Training for our company. The course was efficiently scheduled and planned around our timetable. We were impressed by the quality of training.”
“The training was very informative and practical, leaving our employees feeling confident in their ability to leverage Excel more effectively.”
Frequently asked questions
Can you run this course at our offices?
Yes. Most groups are trained on site anywhere in South Africa. We can also run it at a BOTI venue or live online.
Is there a one-day pivot table course?
Yes – Microsoft Excel Pivot Tables (1 day) concentrates on pivot tables and pivot charts. This two-day course adds dashboards, Power Query and Power Pivot.
Does Power Pivot work in every version of Excel?
Power Pivot needs Excel for Windows (Microsoft 365 or Excel 2016 and later). It is not available in Excel for Mac, so we recommend Windows laptops and can supply them.
Is it accredited?
No – it is a non-accredited short course and delegates receive a BOTI certificate of attendance.
Can we use our own data?
Yes. Send a sample export beforehand, with personal information removed, and the facilitator uses it in the dashboard exercise.
What comes next?
Microsoft Power BI Basics (3 days) takes the same Power Query and DAX skills into Power BI reports and dashboards.
Related courses
Get your price – the more delegates, the less each pays
Change the options to see your price per person, then email yourself the quote and course outline.
Contact us now – we reply within 15 minutes during office hours
Monday to Friday, 08:00–17:00. After hours, we reply on the next working day – or call 011 882 8853.