Course Icon Excel Training

Excel PivotTables

Designed for Excel versions 2016,2013,2010,2007

0800 1337 401

Course Objectives

Welcome to Seven Institute’s course for Microsoft Office Excel Pivot Tables. This course Excel Pivot Tables is intended to help all Excel users get up to speed quickly with the Excel Pivot Table feature. This course will also help more experienced users who have little to no experience with Excel 2007 and the ribbon interface.

This course will cover different features of Pivot Tables and includes modules on Pivot Charts and Powerpivot.

Please note public schedule courses are run using Microsoft Excel 2010 and this is very similar to 2007 and 2013. Some tools will not work in 2007.

Who Should Attend

Pivot Tables are simply one of the best features available in Microsoft Excel. This course is a must for anyone who is a serious about using Excel for data analysis and reporting.

Course Syllabus

GETTING STARTED WITH PIVOT TABLES

  • Describe and create PivotTables
  • Use the contextual PivotTable Tools tabs
  • Add and remove PivotTable information using the Field List
  • Change the Field List layout
  • Pivot data

WORKING WITH PIVOT TABLE DATA

  • Expand and collapse data
  • Filter, sort, group, and refresh data
  • Edit a data source

FORMATTING PIVOT TABLES

  • Modify PivotTable row and column labels and values
  • Use the Layout group on the PivotTable Tools – Design tab
  • Apply a style to a PivotTable
  • Change PivotTable style options
  • Manually format a PivotTable
  • Use the PivotTable Options dialog

USING THE CLASSIC PIVOT TABLE LAYOUT

  • Create and use a classic PivotTable frame
  • Switch to a classic PivotTable frame
  • Add data to a classic PivotTable
  • Pivot data in a classic PivotTable

ADVANCED PIVOT TABLE TASKS

  • Create a PivotTable based on external data
  • Refresh external data
  • Create a Slicer and use the Slicer Tools tab
  • Running Pivot Table Reports
  • Filtering Pivot Table Data
  • Grouping Dates and Numbers
  • Sorting Data
  • Updating the Data Source
  • Consolidating Multiple Pivot Talbes
  • Creating Pivot Tables from External Data

USING CALCULATIONS WITH PIVOT TABLES

  • Summerising values in different ways
  • SUM, COUNT, MAX, MIN, AVERAGE
  • Presenting data is differnet ways
  • Running total, % Parent row or column, % difference
  • Creating calculated fields
  • Creating calculated items

USING POWERPIVOT

  • Download, install, and use PowerPivot
  • Integrate Access and Excel data using PowerPivot
  • Create a PivotTable from PowerPivot data

GETTING STARTED WITH PIVOT CHARTS

  • Create a PivotChart from scratch or from an existing PivotTable
  • Add data to a PivotChart
  • Pivot chart data

USING THE PIVOT CHART TOOL TABS

  • Use the PivotChart Tools contextual tabs (Design, Layout, Format, and Analyze)

FORMATING A PIVOT CHART

  • Rename fields
  • Change the chart type
  • Apply a style to a chart
  • Manually format chart elements
  • Change the layout of chart elements

ADVANCED PIVOT CHART OPTIONS

  • Create a PivotChart based on external data
  • Create a slicer for a PivotChart
  • Create a scenario-based PivotTable and PivotChart
  • Create a PivotChart with PowerPivot data

USING MACROS WITH PIVOT TALBES

  • Introducing macros
  • Recording macros in the Pivot Table
  • Updating existing macros
See all of the available Seven Institute Excel Courses