Advanced Pivot Table Techniques (to achieve more in Excel): Excel learning resource
Advanced Pivot Table Techniques (to achieve more in Excel)
Positioned as the follow-up to Gharani's beginner pivot video, this one assumes you can already build a basic PivotTable and jumps straight into ten intermediate and advanced techniques.
Advanced Pivot Table Techniques (to achieve more in Excel)
Leila Gharani
Open on YouTube (opens in a new tab)What this video actually covers
Positioned as the follow-up to Gharani's beginner pivot video, this one assumes you can already build a basic PivotTable and jumps straight into ten intermediate and advanced techniques. The standout trick is "Show Report Filter Pages," which generates one separate PivotTable per item in a filter field with a single click — useful for splitting one summary into a per-region or per-rep report automatically instead of copy-pasting and re-filtering by hand. Other techniques covered: applying data-bar formatting directly inside a PivotTable's value cells, adding a calculated field that computes the difference between two existing fields, using custom number formats to display values as text or icons, building custom groupings for non-numeric row labels, and grouping a date field into quarters or months automatically.
Where to jump in
What to practice while watching
- Build one PivotTable from the practice sales dataset, then use Show Report Filter Pages to generate a separate PivotTable per region automatically.
- Add data bars to the Amount value field directly inside the PivotTable.
- Create a calculated field that subtracts one numeric field from another, for example amount minus a target.
- Group the date field into quarters or months instead of listing every individual date.
Exercise file
Practice the steps above on a real workbook instead of a blank sheet — no sign-up required.
Download PivotTable practice: sales transactions (.xlsx)Recommended learning path
Start by watching the lesson once without pausing, then reopen Excel and rebuild the example with your own small dataset. Save one clean practice workbook before moving to the next topic.
Source details
- Channel: Leila Gharani
- Format: video
- Level: Intermediate
- Topic: Pivot Tables
Go deeper with this skill
Use PivotTables to explore data quickly while keeping the source table clean and refreshable. For this article, the goal is to practice: Positioned as the follow-up to Gharani's beginner pivot video, this one assumes you can already build a basic PivotTable and jumps straight into ten intermediate and advanced techniques.
Practice workbook setup
Prepare a source table with headers, dates, categories, and numeric measures.
Practice workflow
- Convert the source data into an Excel Table before inserting the PivotTable.
- Build one simple summary first, then add filters, slicers, or grouping.
- Change value field settings and number formats so the result matches the question.
- Refresh after changing the source data and confirm new rows appear.
Quality checks
- The source table has no blank header names or merged cells.
- The PivotTable answers a specific question.
- Filters and slicers are visible enough for readers to know what they are seeing.
Common mistakes
- Building pivots from messy ranges instead of clean tables.
- Forgetting to refresh after source changes.
- Counting text fields when a numeric sum was expected.
Next actions
- Add one slicer and one PivotChart to turn the summary into a small report.
- Create a second PivotTable from the same source to answer a different question.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.
