Excel Pivot Tables

This one day course is designed for experienced Excel users who will be using pivot tables extensively in their work. Delegates will learn how to use this powerful tool to summarise and analyse data and then enhance it with formatting, custom calculations and filtering to produce unique and useful reports.

Course Outline

Introducing Pivot Tables

What Is a Pivot Table, Understanding the Benefits of Using Pivot Tables, Preparing to Create a Pivot Table, Exploring the Excel Table Features

Creating a Pivot Table

Exploring an Insurance Policy Example, Changing the Pivot Table Layout, Charting the Data in a Pivot Table

Modifying a Pivot Table

Changing a Pivot Table, Adding a Report Filter, Updating the Pivot Table, Changing the Summary Function, Applying a PivotTable Style, Deleting a Pivot Table

Summarizing Data

Exploring a Work Orders Example, Using the Summary Functions, Showing or Hiding Grand Totals, Creating Subtotals, Grouping Numbers and Dates

Formatting a Pivot Table

Controlling the Report Layout, Adding Blank Rows in the Layout, Using a Pivot Table Style, Adding Row and Column Shading, Formatting the Row and Column Headers, Removing a Pivot Table Style, Creating a Pivot Table Style, Applying a Custom Pivot Table Style, Modifying a Custom PivotTable Style, Duplicating a Pivot Table Style, Deleting a Custom Pivot Table Style, Using Themes, Applying a Theme, Saving the File

Sorting and Filtering in a Pivot Table

Adding Report Filters, Arranging Report Filters, Clearing All Filters, Moving Labels, Sorting Labels, Sorting Values, Sorting Automatically When the Pivot Table Changes, Sorting Labels in a Custom Order, Filtering Row and Column Labels, Viewing Filter and Sort Information, Removing Filters, Filtering Values, Filtering for a Date Range, Applying a Manual Filter, Including New Items in a Manual Filter, Filtering by Selection, Showing Top and Bottom Items

Creating a Pivot Table from External Data

Creating a Pivot Table from a Text File, Creating a Pivot Table from an Access Query, Creating a Pivot Table from an OLAP Cube

Updating a Pivot Table

Updating an Excel Table, Changing an Excel Data Source, Refreshing All Pivot Tables in a Workbook, Retaining Deleted Items, Changing an Access Data Source, Refreshing the Pivot Table Regularly, Saving Source Data with the File

Creating Calculations in a Pivot Table

Creating Custom Calculations, Creating Formulas

Enhancing Pivot Table Formatting

Applying Conditional Formatting, Setting Format Options

Creating a Pivot Chart

Creating a Default Pivot Chart, Creating a Line Pivot Chart

Printing and Extracting Data from a Pivot Table

Using the Show Details Feature, Using Show Report Filter Pages, Printing a Pivot Table, Using the GetPivotData Function