Microsoft Excel Pivot Table Reports and Charts – Level 1

Versions: All versions: 2007 to Office 365

Description

Pivot Tables are one of the most under-utilised tools in Excel. They are extremely powerful for reporting on large and complex data. The level 1 course is for people who have not had much experience creating pivot tables and covers the fundamentals of creating and using pivot tables.

Duration

1 day

Prerequisites

This is a course for people who are proficient at advanced level. You need to work on Excel daily with large amounts of data.

Module 1: Creating a PivotTable

  • When to Use a PivotTable Report
  • Proper list data
  • How PivotTable Reports Organise Data
  • Compare a PivotTable Report and a PivotChart Report
  • Create a PivotTable Report
  • Create a PivotChart from an Existing PivotTable Report
  • Create a PivotTable from an External Data Source

Module 2: Setting up the PivotTable report

  • PivotTable Field List
  • How the PivotTable Field List Works:
  • Guidelines for Moving Fields to the Four Report Areas:
  • What should go where?
  • Add Fields to the PivotTable Report
  • Copy Fields
  • Drag the field to the Values area in the layout section
  • Rearrange Fields
  • Remove Fields
  • Change the PivotTable Field List View
  • Sorting the Field List
  • Use a number to hide zero values in selected cells
  • Switch between Automatic or Manual Updating of the Report Layout

Module 3: Selecting data

  • Select Data in a PivotTable Report
  • Select Individual Cells
  • Select an Entire Report
  • Select All Instances of a Single Item
  • Select One Instance of an Item
  • Select Multiple Items
  • Select Subtotals and Totals

Module 4: PivotTable Report Options

  • Rename a PivotTable Field
  • Refresh
  • Changing the Data Source
  • Clear
  • Move the PivotTable
  • Change the Report Layout and Field Arrangement
  • Compact, Outline, Or Tabular Report Form
  • Repeat Item Labels
  • Change the Layout of Columns, Rows, Subtotals and Grand Totals
  • Change the Order of Row or Column Items
  • Adjust Column Widths on Refresh
  • Move a Column to the Row Labels Area or a Row to the Column Labels Area
  • Merge or Unmerge Cells for Outer Row and Column Items
  • Change how Blank Cells, Blank Lines and Errors are displayed
  • Change how Items and Labels with No Data are shown

Module 5: Grouping & Filtering Items in a PivotTable

  • Grouping by Numeric Items
  • Grouping by Date or Time Items
  • Grouping by Selected Items
  • Rename a Group
  • Ungroup Items
  • Collapse or Expand Details
  • The Collapse or Expand Buttons
  • Expand or Collapse Levels of Detail
  • Expand or Collapse Details for a Value Field
  • Filter a PivotTable or PivotChart Report
  • Display a Different Set of Values in a Report Based on a Report Filter Item
  • Show Report Filter Pages
  • Multiple Report Filters

Module 6: Slicers & Sparklines

  • Slicer elements
  • Create a slicer in an existing PivotTable
  • Format slicers
  • Slicer settings
  • Delete a slicer
  • Share slicers between PivotTables
  • Using the Timeline Slicer
  • Analyse trends with Sparklines
  • Module 7: Sub-totals and Totals
  • Subtotal and Total Fields in A PivotTable Report
  • Subtotal Row and Column Fields
  • Subtotal an Outer Row or Column Label
  • Subtotal an Inner Row or Column Label
  • Display or Hide Grand Totals for the Entire Report
  • Add fields to the PivotTable Report
  • Copy fields
  • Select data in a PivotTable report

Module 8: Printing PivotTables

  • Print a PivotTable Report
  • Page Break within an Item Group

Request a quotation

Please type your full name.

Invalid email address.

Invalid Input

Individual or company?

How many people require training?