Microsoft Tools · MIC-79

Excel PivotTables and PivotCharts

Excel PivotTables and PivotCharts is a hands-on course in summarising and analysing data with PivotTables and PivotCharts, from data preparation, grouping, Show Values As and calculated fields to slicers, timelines, the Data Model and GETPIVOTDATA. It suits staff who build reports in Excel regularly, and you leave able to build a refreshable dashboard from PivotTables.

Updated 30 Oct 2026

From 5,310 THB / person 5,900 −10% excl. VAT 7% · group rates available

Corporate training quote Join the waiting list

PDFDownload the course outline

Duration12 hours · 2 days
FormatOnsite / live online
Next roundOn request
CertificateIncluded

Course overview

The PivotTable is one of the most powerful summarising tools in Excel, yet many users only drag fields into place and total them. When they need to compare with last year, show shares, rank items or combine several tables, they fall back on long formulas or copy figures by hand every month, which is slow and error-prone, even though PivotTables can do all of this built in and refresh with one click.

This course teaches learners to use PivotTables and PivotCharts to their full potential. It starts with preparing data as Excel tables, building and formatting readable PivotTables, grouping dates, numbers and text, analysing shares, growth, running totals and ranks with Show Values As, and creating calculated fields and items. It then covers slicers, timelines and PivotCharts, the Data Model for summarising several tables, PivotTables from Power Query, managing refresh and data sources, GETPIVOTDATA and dashboards, and closes with a workshop that builds a reporting system that updates every month. (2 days, 6 hours per day, 12 hours in total, Intermediate level.)

What you’ll gain

Prepare data as Excel tables that are ready for PivotTables and grow with new data
Build and format PivotTables that are easy to read and keep their format on refresh
Group dates, numbers and text the way the business views them
Analyse shares, growth, running totals and ranks with Show Values As
Create calculated fields and calculated items correctly
Build interactive reports with slicers, timelines and PivotCharts
Summarise several tables with the Data Model and Power Query
Build a dashboard from PivotTables that refreshes with new monthly data

Who this course is for

Staff and team leads who summarise reports in Excel regularly
Sales, marketing, accounting, purchasing and HR staff who produce monthly reports
Users who know basic PivotTables and want to go further
Anyone still writing formulas or copying figures by hand every month
Anyone building a foundation before moving on to Power Query and Power BI

Prerequisites

Everyday Excel use such as data entry, formatting, sorting and filtering
Basic formulas such as SUM, IF and VLOOKUP or XLOOKUP
Some experience creating PivotTables helps but is not required
A Windows laptop with Excel for Microsoft 365, Excel 2021 or Excel 2024

Curriculum

Course Details

This course builds on Advanced Microsoft Excel for Microsoft 365, which covers PivotTables in a single topic, and focuses on PivotTables and PivotCharts throughout. It runs for 2 days, 6 hours per day (12 hours in total, 09:00-16:00), as lectures with labs on a single fictional company's sales data. Intermediate level. Learners use their own or their organisation's Excel on a Windows laptop. The institute does not provide licences. The Data Model topics work only in Excel for Windows. Power Query is used only as far as PivotTables need it. To go deeper into Power Query, continue with Power Query in Microsoft Excel, and to move reports to Power BI, continue with From Excel to Power BI Desktop Reports.

Day 1 Summarising and Analysing with PivotTables

Section 1: Lab: Preparing Data for PivotTables

Tabular data: one row per record, with no total rows, merged cells or stacked headers
Convert ranges into Excel tables so the source grows automatically
Fix dates and numbers stored as text, extra spaces and duplicates
Turn cross-tab layouts into long data with Unpivot
Lab: clean up a sales export so it is ready to summarise

Section 2: Lab: Building and Formatting PivotTables Fluently

Place fields in Rows, Columns, Values and Filters to answer the question at hand
Summarize Values By: Sum, Count, Average, Max, Min and Distinct Count
Sorting, Top 10 filters and value filters
Compact, Outline and Tabular report layouts with Repeat Item Labels
Subtotals, grand totals, number formats and PivotTable styles that survive a refresh
Lab: a sales report by region, product and sales rep

Section 3: Lab: Grouping Data

Group dates into years, quarters and months, and the pitfalls of auto grouping
Group numbers into bands such as age bands or order value bands
Group text items manually into the groups the business uses
Fiscal years, weeks and Thai Buddhist years with helper columns in the source
Lab: quarterly sales split by order value band

Section 4: Lab: Show Values As

% of Grand Total, % of Column Total and % of Parent Row Total
Difference From and % Difference From against the previous month or year
Running Total In and % Running Total In
Rank Smallest to Largest and Rank Largest to Smallest
Lab: share, growth and rank from the same field on one page

Section 5: Lab: Calculated Fields and Calculated Items

Calculated fields for gross profit and margin percentage
The limits of calculated fields, which work on totals rather than rows
Calculated items and the risk of double counting
Solve Order and List Formulas to review every formula
Lab: calculate profit and compare custom product groups

Day 2 PivotCharts, the Data Model and Dashboards

Section 6: Lab: Slicers, Timelines and PivotCharts

Add slicers and timelines and style them to match the report
Report Connections: one slicer controlling several PivotTables
Create PivotCharts and choose the right chart for the question
Combo charts and secondary axes for sales against growth percentage
Hide field buttons, and know when a regular chart works better
Lab: trend, share and ranking charts that filter together across the page

Section 7: Lab: The Data Model and Multi-table PivotTables

Add tables to the Data Model and create relationships between them
The idea of fact tables and lookup tables in Excel
Summarise across tables without XLOOKUP, including Distinct Count
An introduction to Power Pivot and simple DAX measures
Lab: a PivotTable over sales, product, customer and branch tables

Section 8: Lab: PivotTables from Power Query and Managing Refresh

Combine monthly files with Power Query before summarising
Load To: Only Create Connection and Add to Data Model
Refresh All, refresh on open and the order of refreshes
Change Data Source, moved files and the pivot cache that bloats files
Lab: a monthly report where you drop in a new file and refresh once

Section 9: Lab: GETPIVOTDATA and Dashboards

How GETPIVOTDATA works, and referencing cells instead of fixed values
Pull figures from PivotTables into a report form with your own layout
Lay out a dashboard with KPIs, trends, shares and details, plus conditional formatting
Optional extras: the GROUPBY and PIVOTBY functions and Copilot in Excel for licensed users
Protect sheets and share files through OneDrive or SharePoint
Lab: a one-page sales dashboard driven by a single set of slicers

Section 10: Workshop: A PivotTable Reporting System

Brief: two years of multi-branch sales data with monthly targets
Prepare the data with Excel tables and Power Query
Build the Data Model, PivotTables, PivotCharts and a dashboard
Test the refresh with a new month of data
Present and review the reports together

Schedule & training options

For individuals — public rounds

No public rounds are open right now. Join the waiting list and we will contact you first when the next round opens, or ask us on LINE. Or call 02-570-8449 or 088-807-9770

Join the waiting list Ask about rounds

For organisations — in-house / private

Tailor the content to your team’s tools and projects
Your dates, at your office or live online
Quotation with tax ID for procurement

Corporate training quote

Instructors

Samit KoyomFounder & CEO, IT Genius Engineering

Frequently asked questions

How does this course differ from Advanced Microsoft Excel for Microsoft 365?

This course builds on Advanced Microsoft Excel for Microsoft 365, which covers many areas of Excel and PivotTables in a single topic. This course spends both days on PivotTables and PivotCharts, from data preparation, grouping, Show Values As and calculated fields to slicers, the Data Model, Power Query, GETPIVOTDATA and dashboards.

Can I join if I have never used a PivotTable?

Yes, as long as you use Excel day to day and can write basic formulas. The course starts from preparing data and creating a PivotTable, but moves faster than a beginner course. Those with some PivotTable experience get the most out of it.

Which Excel versions work, and can I use a Mac?

We recommend Excel for Microsoft 365 on Windows; Excel 2021 and 2024 on Windows cover almost every topic. The institute does not provide licences. Excel for Mac has no Data Model or Power Pivot, so Mac users cannot do those labs and should use a Windows machine to cover everything.

Does the course cover GROUPBY, PIVOTBY and Copilot?

Yes, as optional extras so you know when a formula or a PivotTable is the better choice. GROUPBY and PIVOTBY work in Excel for Microsoft 365, and Copilot in Excel needs a supporting licence. The core of the course remains PivotTables, which work in every listed Excel version.

Can I bring my own work files to class?

Yes, during the workshop and Q&A, but remove confidential and personal data first. All main labs use a fictional company's sales data so everyone can follow along together.

Which course should I take next?

To automate data preparation from many sources, continue with Power Query in Microsoft Excel. To move your reports into shareable online dashboards, continue with From Excel to Power BI Desktop Reports.

Articles are published in Thai.