"A Training Solution Provider delivering Learning Skills to keep forever"

Call the JCS training team free now 0800 5425 150 Or mail Email | training@jcstraining.com

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.