Microsoft Excel Pivot Table Reports and Charts – Level 2

Versions: All versions: 2007 to Office 365

Description

This is an advanced course in pivot tables. It shows you how to use the powerful analysis features, how to create calculated fields and items and use conditional formatting within your pivot table reports. It also covers pivot charts and dashboards.

Duration

1 day

Prerequisites

This is the second level Pivot Table course for people who are proficient at advanced level. You need to work on Excel daily with large amounts of data and you need to have completed our Pivot Table course – Level 1.

Module 1: Create a Consolidated PivotTable Report

  • Setting Up the Source Data
  • Page fields in data consolidations
  • Using Named Ranges
  • Consolidate Data without Using Page Fields
  • Consolidate Data by Using a Single Page Field
  • Consolidate data by using multiple page fields

Module 2: Analyse Data in Pivot Tables

  • Top 10 Analysis
  • Percentage contributions Analysis
    • % of Grand Total
    • % of Column Total
    • % of Row Total
    • % of Parent Row Total
    • % of Calculation
  • Running Totals
  • Ranking data
  • Variance Analysis
    • Difference From
    • % Difference From
  • The Index calculation
  • Frequency Analysis

Module 3: Create Formulas in PivotTable Reports

  • PivotTable Formula Elements
  • Field and Item Names
  • Formulas Operate on Sum Totals, Not Individual Records
  • Spaces, Numbers and Symbols IN Names
  • Field Names in Item References
  • Referring to Items by Position
  • Using formulas in PivotChart reports
  • Calculated fields and items
  • Create a Calculated Field
  • Use other functions with calculated fields
  • Add a Calculated Item to a Field
  • Modify and delete a calculated item
  • View Formulas
  • Edit a PivotTable Formula
  • Edit a Single Formula for a Calculated Item
  • Edit Individual Formulas for Specific Cells of a Calculated Item
  • Delete a PivotTable Formula
  • Delete a Calculated Field
  • Delete a Calculated Item

Module 4: Advanced Formatting of a PivotTable Report

  • Apply Styles
  • Apply Banding
  • Change the Number Format for a Field
  • Preserve or Discard Formatting
  • Remove All Formatting from a Report
  • Delete a PivotTable Report
  • Change the Layout of Columns, Rows, Subtotals and Grand Totals
  • Change the Order of Row or Column Items

Module 5: Conditional Formatting in PivotTables

  • The benefits of conditional formatting
  • Apply conditional formatting
  • Conditional formatting rules
  • Customised conditional formatting
  • Manage conditional formatting
  • Conditional formatting with a formula
  • Clear conditional formats
  • Highlight cells with rules
  • Use Top/Bottom rules
  • Use Data Bars and Colour Scales
  • Use Icon Sets
  • Create your own rules

Module 6: Pivot Charts

  • Filtering the PivotChart
  • Other ways to create a PivotChart
  • Create a PivotChart for an existing PivotTable
  • Connect to External Data to Create a PivotChart
  • Create Bar, Column, Pie and Trendline Charts and Combination Charts
  • Creating Markers
  • Using / Creating Secondary Axis - Values and Percentages
  • Secondary Axis - Values and Running Sums
  • Trending – Understanding trend lines

Module 7: Dashboard reporting

  • Overview of a dashboard
  • Create a data model in Excel
  • Use a data model in another PivotTable, PivotChart or PowerView report
  • Refine and extend the data model in the Power Pivot Add-in
  • Differences between a table in Power Pivot and a table in a sheet
  • Use the data model in Power View
  • Select a table and apply optimisations
  • Differences between PivotChart reports and standard charts

Request a quotation

Please type your full name.

Invalid email address.

Invalid Input

Individual or company?

How many people require training?