FREE COURSE · MODULE 10 OF 12

Pivot tables turn thousands of rows into a one-page summary in seconds. This 20-chapter course takes you from inserting your first pivot table to slicers, timelines, pivot charts and calculated fields, using one practice workbook you can download.

📚 20 chapters⏱ About 2 hours📥 Practice file included🎓 Beginner to advanced

Start Chapter 1 →📥 Download practice file

Table of Contents

What you will learn

Build and shape

Insert a pivot table, place fields in rows, columns and values, and choose the right layout for a report.

Summarise correctly

Sum, count, average, percentage of total, running totals and differences with Value Field Settings.

Group and filter

Group dates by month and quarter, group numbers into bands, and filter with labels, values, slicers and timelines.

Make it interactive

Pivot charts, slicers connected to several pivots, refresh and change data source, and calculated fields and items.

Course curriculum

Insert a Pivot TablePrepare your data as a table and build your first pivot in four clicks.
Data Sorting in Pivot TableSort labels and values, ascending, descending and manual order.
Filters in Pivot TableReport filters, label filters, value filters and Top 10.
Value Field SettingsSum, count, average, min, max and “Show Values As” options such as % of total and running total.
Columns in Pivot TableCross-tab reports: putting a second dimension across the top.
Group and UngroupGroup dates into months, quarters and years, and numbers into ranges.
Report LayoutsCompact, outline and tabular forms, repeat labels and blank rows.
Subtotals and Grand TotalsTurn totals on and off and control where they appear.
Pivot ChartsCharts that stay linked to the pivot and respond to its filters.
SlicersOne-click filtering buttons, and connecting one slicer to several pivot tables.
TimelineA date slider for filtering by day, month, quarter or year.
Refresh and Change Data SourceKeep the pivot in sync when rows are added or the source moves.
Calculated Fields and ItemsAdd your own formulas, such as margin % or variance, inside the pivot.
Show DetailsDouble-click any number to see the rows behind it.
Pivot Table, Slicer and Timeline StylesBuilt-in and custom styles for report-ready formatting.
Distinct Count in a Pivot TableHow to get a distinct count in a pivot table with the Data Model, count unique customers or employees correctly, and see COUNTIFS and UNIQUE alternatives.
GETPIVOTDATA in ExcelHow to use GETPIVOTDATA in Excel to pull pivot table values into formulas: syntax, examples, dynamic cell references, dates and fixing #REF!
Pivot Table from Multiple SheetsBuild one pivot table from two or more Excel sheets without VLOOKUP: add tables to the Data Model, create relationships, fix errors and write a DAX measure.
PivotTable Options, Settings and Troubleshooting GuideEvery PivotTable Options setting explained: empty cells, autofit widths, retain items, refresh on open, plus fixes for the 10 most common pivot table errors.
Conditional Formatting and Number Formats in a Pivot TableApply conditional formatting to a pivot table that survives refresh: data bars, colour scales and icon sets scoped to the field, plus number format codes.

Before you start: this module assumes you can sort and filter a plain table. If not, take Sort and Filter first. After this module, put your pivots to work in the Excel Dashboard module.

Who this course is for

Analysts, accountants, HR and operations staff, students and anyone who receives raw data exports and has to report on them. No formulas are needed for the first twelve chapters. Calculated fields in chapter 13 use simple arithmetic only.

Skip the build: ready-made pivot-driven dashboards

NextGenTemplates has 7,000+ ready-to-use Excel, Power BI and Google Sheets templates, many built on the same pivot techniques taught here. Instant download, video tutorial with every file.

Browse Excel dashboards →