Excel: Mastering PivotTables
Excel: PivotTables is an intermediate-level course of 5 lessons (about 1 h 25 min), with 16 exercises and 15 quiz questions. Prerequisites: Excel: Lookup and Logical Functions. The course needs a Windows virtual machine with Power BI, planned for a later phase of the platform.
- Excel
- Intermediate
- 5 lessons
- 1 h 25 min
- 16 exercises
- Windows virtual machine (phase 2)
The course in brief
The PivotTable (tableau croisé dynamique, TCD) is Excel's most widely used analysis tool. This course starts from a clean source (a structured table), then teaches you to build and organize a PivotTable, group dates and amounts, create calculated fields without falling into their trap, filter with slicers and timelines, and produce PivotCharts. You finish with a refreshable dashboard.
What you will be able to do
- Prepare a source in list format and turn it into a named structured table
- Build and rearrange a PivotTable: areas, summaries, percentages, sorting, refreshing
- Group dates by month and quarter and numbers by range
- Create a correct calculated field and know when to use a calculated column instead
- Filter several PivotTables with slicers, a timeline and report connections
- Produce a dashboard with PivotCharts
Syllabus: 5 lessons
- Prepare a data source that a PivotTable can use
- Turn a range into a structured table and name it
- Use calculated columns, the total row and structured references
- 02
Creating and Organizing a PivotTable
- Create a PivotTable from a structured table
- Place fields in the Rows, Columns, Values and Filters areas
- Change the summary and the display of values, sort and refresh
PivotTableRows, Columns, Values, Filters areasSummary and display of valuesRefreshing
- 03
Calculated Fields and Grouping
- Group dates by month and quarter and numbers by range
- Create manual groups of items
- Create a calculated field and understand that it applies to totals
- 04
Slicers and Timelines
- Filter a PivotTable with slicers and a timeline
- Connect a slicer to several PivotTables
- Build a small filterable dashboard
- 05
PivotCharts
- Create a PivotChart from a PivotTable
- Drive it with fields, slicers and the timeline
- Choose a suitable chart type and make it readable
Final project
First-Half Sales Dashboard
From the course's ventes_s1.csv file, build a refreshable Excel dashboard for the sales director: a named structured table as the source, PivotTables of revenue by month and quarter, by region and by category, each region's share, average price per category, PivotCharts, and Region and Commercial slicers with a timeline connected to all the PivotTables. Then add three July sales of your choice and show that everything updates with Refresh All.
Deliverables
- An .xlsx workbook with a Données sheet (Ventes table) and a Tableau de bord sheet
- At least three PivotTables: by month/quarter (Q1 12 040, Q2 10 780), by region (Nord 46,80 %) and by category with the PrixMoyen calculated field (total 158,47)
- Two titled PivotCharts, without field buttons
- A Region slicer, a Commercial slicer and a timeline linked to all the PivotTables
Assessment criteria
- Source in list format, named table, calculated Montant column
- Accuracy of the figures (total 22 820 before adding the July sales)
- Calculated field used only for a ratio of totals
- Consistent interactions: one slicer click updates all the blocks
- Dashboard readability: titles, alignment, visible filters
Frequently asked questions
Do I need to know formulas before learning PivotTables?
Yes, it is recommended to have taken the course on lookup and logical functions: SOMME.SI.ENS (SUMIFS) in particular lets you check a PivotTable's results.
Does a PivotTable change my data?
No. It reads the source data and presents a summary of it. You only need to refresh it when the data changes.
Are slicers and timelines available in all versions?
They are available in recent versions of Excel for Windows and Mac as well as in Microsoft 365. Some interface options may vary depending on the version.
What is the difference with Power BI?
PivotTables summarize a source within Excel. Power BI links several tables in a model and publishes shared reports. The logic of the areas (rows, columns, values, filters) is the same.
This course, told for your job
Say what you want to be able to do: “Excel: PivotTables” takes its place in your path, with examples from your field. The concepts stay those of the course.