Course Details

Tables and Pivot tables are two of Excel's most powerful features. Tables allow you to manage and analyse your data in Excel quickly and easily. Learn how to insert, sort and filter a table, and how to display a total row at the end of a table A pivot table allows you to extract the significance from a large, detailed data set. This course is designed to develop experience and confidence in using the Excel Tables and PivotTables to analyse data and produce sophisticated management reports. Discover how to perform a variety of calculations with Pivot Table Data. You will learn how to construct Pivot Tables and Charts to consolidate and summarise your data.

Duration:

1 Day

Audience:

Users needing to manipulate and analyse data stored in Excel lists using pivot tables.

Prerequisites:

Participants must have an intermediate level knowledge and experience with Microsoft Excel.

Excel 2016

PivotTable Fundamentals

  • What is a PivotTable?
  • Why should I use a Pivot Table?
  • What are the advantages?
  • PivotTable Terminology
  • Preparing your data for use in a PivotTable
  • Creating a PivotTable
  • Selecting the Data Source
  • PivotTable Fields List
  • Filterering data with Page Fields
  • Adding Fields
  • Removing Fields
  • Understanding the Field icons

Presenting Data in PivotTables

  • PivotTable Ribbon
  • Hiding and Unhiding Items
  • Sorting Data in a PivotTable
  • Using AutoSort
  • Advanced AutoSort
  • Refreshing a PivotTable Report
  • Inserting Data into the Data Source
  • Formatting Numerical Data
  • Selecting Parts of a PivotTable
  • PivotTable Formats
  • PivotTable Options
  • Change the Summary Function
  • Adding Multiple Data Fields
  • Changing Calculations
  • Hiding and Showing Row/Column Details
  • Displaying Data Details

Grouping Data

  • Grouping Data
  • Group by Dates
  • Group by Number
  • Ungrouping Data

Calculations in PivotTables

  • Changing custom calculations
  • Creating Calculated Fields
  • Calculated Items
  • GetPivotData() to extract information
  • Using Other Sources of Data
  • Selecting data from another Excel workbook
  • Connecting to an Database
  • Using a Saved Query as the data source
  • Using a csv text file as the source of a PivotTable

Using PivotCharts

  • PivotChart terms
  • Modifying the Chart
  • Caution - Loss of Formatting in PivotCharts

Publishing PivotTables to the Web

  • Saving a PivotTable as a web page
  • Creating Interactive PivotCharts - Web
  • Adding Fields to a PivotChart - Browser

Re-using PivotTables

  • Requerying data
  • Using Saved queries
  • Automating PivotTable Updates
  • Saving a PivotTable Template

Excel 2013

PivotTable Fundamentals

  • What is a PivotTable?
  • Why should I use a Pivot Table?
  • What are the advantages?
  • PivotTable Terminology
  • Preparing your data for use in a PivotTable
  • Creating a PivotTable
  • Selecting the Data Source
  • PivotTable Fields List
  • Filterering data with Page Fields
  • Adding Fields
  • Removing Fields
  • Understanding the Field icons

Presenting Data in PivotTables

  • PivotTable Ribbon
  • Hiding and Unhiding Items
  • Sorting Data in a PivotTable
  • Using AutoSort
  • Advanced AutoSort
  • Refreshing a PivotTable Report
  • Inserting Data into the Data Source
  • Formatting Numerical Data
  • Selecting Parts of a PivotTable
  • PivotTable Formats
  • PivotTable Options
  • Change the Summary Function
  • Adding Multiple Data Fields
  • Changing Calculations
  • Hiding and Showing Row/Column Details
  • Displaying Data Details

Grouping Data

  • Grouping Data
  • Group by Dates
  • Group by Number
  • Ungrouping Data

Calculations in PivotTables

  • Changing custom calculations
  • Creating Calculated Fields
  • Calculated Items
  • GetPivotData() to extract information
  • Using Other Sources of Data
  • Selecting data from another Excel workbook
  • Connecting to an Database
  • Using a Saved Query as the data source
  • Using a csv text file as the source of a PivotTable

Using PivotCharts

  • PivotChart terms
  • Modifying the Chart
  • Caution - Loss of Formatting in PivotCharts

Publishing PivotTables to the Web

  • Saving a PivotTable as a web page
  • Creating Interactive PivotCharts - Web
  • Adding Fields to a PivotChart - Browser

Re-using PivotTables

  • Requerying data
  • Using Saved queries
  • Automating PivotTable Updates
  • Saving a PivotTable Template

Excel 2010 / Excel 2007

PivotTable Fundamentals

  • What is a PivotTable?
  • Why should I use a Pivot Table?
  • What are the advantages?
  • PivotTable Terminology
  • Preparing your data for use in a PivotTable
  • Creating a PivotTable
  • Selecting the Data Source
  • PivotTable Fields List
  • Filterering data with Page Fields
  • Adding Fields
  • Removing Fields
  • Understanding the Field icons

Presenting Data in PivotTables

  • PivotTable Ribbon
  • Hiding and Unhiding Items
  • Sorting Data in a PivotTable
  • Using AutoSort
  • Advanced AutoSort
  • Refreshing a PivotTable Report
  • Inserting Data into the Data Source
  • Formatting Numerical Data
  • Selecting Parts of a PivotTable
  • PivotTable Formats
  • PivotTable Options
  • Change the Summary Function
  • Adding Multiple Data Fields
  • Changing Calculations
  • Hiding and Showing Row/Column Details
  • Displaying Data Details

Grouping Data

  • Grouping Data
  • Group by Dates
  • Group by Number
  • Ungrouping Data

Calculations in PivotTables

  • Changing custom calculations
  • Creating Calculated Fields
  • Calculated Items
  • GetPivotData() to extract information
  • Using Other Sources of Data
  • Selecting data from another Excel workbook
  • Connecting to an Database
  • Using a Saved Query as the data source
  • Using a csv text file as the source of a PivotTable

Using PivotCharts

  • PivotChart terms
  • Modifying the Chart
  • Caution - Loss of Formatting in PivotCharts

Publishing PivotTables to the Web

  • Saving a PivotTable as a web page
  • Creating Interactive PivotCharts - Web
  • Adding Fields to a PivotChart - Browser

Analysing Data from Multiple Ranges

  • Setting up the PivotTable using multiple ranges
  • Using Multiple Page Fields

Re-using PivotTables

  • Requerying data
  • Using Saved queries
  • Automating PivotTable Updates
  • Saving a PivotTable Template