Microsoft Excel - Pivot Tables Masterclass Duration: 1 Day
Course Overview
This course has been designed for candidates who want the benefit of using the advanced formulas and functions of Excel Pivot Tables.
Prerequisites: A working knowledge of Excel L3 Advanced or attendance on our Excel L3 advanced training.
Benefits: Learn how to create and use pivot tables to build a list of unique values. Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies.
Preparing Your Data Ready for Pivot Tables
- Best Practise
- Data Range Vs Table
Creating Pivot Tables
- Pivot Table 1 – Standard Pivot
- Pivot Table 2 – Formatting a Pivot Table
- Pivot Table 3 – Summary Pivot
- Pivot Table 4 – Show Values As
Grouping Excel Pivot Table Data
- Pivot Table 5 – Group by Numbers
- Pivot Table 6 – Group by Dates
- Pivot Table 7 – Group by Text
Slicers
- How to add slicer for Pivot tables in Excel
- Changing the appearance of the slicer
- Adding a timeline slicer (Excel 2013 Onwards)
Pivot Charts
- What is a Pivot Chart
- Create a Pivot Chart
- Using Slicers with Charts
- How to drill down into your data via a Pivot Chart (Excel 2016 only)
Dashboard
- Creating a simply non VBA dashboard
- Adding slicers and charts
- Linking slicers to multiple charts
Tips and Tricks to Improve the Appearance of your Pivot Table
- Handling how empty cells are displayed
- Stop Auto-Width of columns when you refresh the data
- How to auto refresh your Pivot Table
- Create a Pipeline Pivot Table
- How to Handle error messages in your Pivot Table
Top 10
- Using Pivot Table Top 10 Filters
Report Filter to Worksheets
- Create Multiple Pivot Table Reports with Show Report Filter Pages
GetPivotData Function
- Use the Excel GETPIVOTDATA function to query a pivot table. Retrieve data from a pivot table in a formula.
Calculated Fields
- Create calculated fields, using formulas that work with the sum of other pivot fields.
Calculated Items
- Learn the difference between Excel pivot table calculated fields and calculated items. When and how to use them, examples and warnings.
Microsoft Excel - Pivot Tables Masterclass Duration: 1 Day
Course Overview
This course has been designed for candidates who want the benefit of using the advanced formulas and functions of Excel Pivot Tables.
Prerequisites: A working knowledge of Excel L3 Advanced or attendance on our Excel L3 advanced training.
Benefits: Learn how to create and use pivot tables to build a list of unique values. Because pivot tables summarize data, they can be used to find unique values in a field. This is a good way to quickly see all the values that appear in a field and also find typos, and other inconsistencies.
Preparing Your Data Ready for Pivot Tables
- Best Practise
- Data Range Vs Table
Creating Pivot Tables
- Pivot Table 1 – Standard Pivot
- Pivot Table 2 – Formatting a Pivot Table
- Pivot Table 3 – Summary Pivot
- Pivot Table 4 – Show Values As
Grouping Excel Pivot Table Data
- Pivot Table 5 – Group by Numbers
- Pivot Table 6 – Group by Dates
- Pivot Table 7 – Group by Text
Slicers
- How to add slicer for Pivot tables in Excel
- Changing the appearance of the slicer
- Adding a timeline slicer (Excel 2013 Onwards)
Pivot Charts
- What is a Pivot Chart
- Create a Pivot Chart
- Using Slicers with Charts
- How to drill down into your data via a Pivot Chart (Excel 2016 only)
Dashboard
- Creating a simply non VBA dashboard
- Adding slicers and charts
- Linking slicers to multiple charts
Tips and Tricks to Improve the Appearance of your Pivot Table
- Handling how empty cells are displayed
- Stop Auto-Width of columns when you refresh the data
- How to auto refresh your Pivot Table
- Create a Pipeline Pivot Table
- How to Handle error messages in your Pivot Table
Top 10
- Using Pivot Table Top 10 Filters
Report Filter to Worksheets
- Create Multiple Pivot Table Reports with Show Report Filter Pages
GetPivotData Function
- Use the Excel GETPIVOTDATA function to query a pivot table. Retrieve data from a pivot table in a formula.
Calculated Fields
- Create calculated fields, using formulas that work with the sum of other pivot fields.
Calculated Items
- Learn the difference between Excel pivot table calculated fields and calculated items. When and how to use them, examples and warnings.
