Microsoft Excel - Pivot Tables

You will learn that with persistence and the ability to manipulate Pivot Tables via 'Trial-and-Error' you will immediately get results. 

You should be a competent Microsoft Excel user.

When you first meet an Excel Pivot Table, you will probably ask yourself "could I do that?”.  The answer of course is yes! Unfortunately, most people tend to shy away from Pivot Tables, as they see them as too complex. This course will teach you how top make the best use of Pivot Tables in your Excel work  

What is a Pivot Table?

• Find data that can be analysed by a Pivot Table
• Uses and examples of PivotTables

Construct a PivotTable

• How to Construct a Pivot Table
• Add/Remove Pivot Table Fields
• Reorganise the PivotTable
• Work with multiple Values Areas
• Sorting Data
• Drill down to reveal source data

Analysing Data using Pivots

• Summarising values by using different functions
• Showing values as Percentages
• Comparing totals against a base value
• Showing Running Totals
• Ranking the values areas to show best to worst
• Changing the analysis function in sub-totals

Filtering and Slicers

• How to filter data using the Report Filter (Page Field)
• Filtering data using the Pivot Table filters
• Using Slicers to filter and analyse data

Designing the Pivot Table

• Using different Pivot Table styles
• Showing Sub-totals in different positions
• Removing Grand Totals
• Using Tabular and Outline views
• Creating “Flat” Pivot tables
• Apply numeric formats to the Data Area

Grouping Data

• Creating “Ad Hoc” Groups for analysis
• Grouping Data by Date e.g. by Year
• Grouping on numeric fields to analyse data by range

Multiple Consolidation Ranges

• Consolidate Data use a PivotTable
• Work with Consolidated Data

Calculated Items and Fields

• Create Calculated Items
• Create Calculated Fields
• Work the Calculated Items and Fields

Using an Excel Table

• Use Table Styles to format large amounts of data for readability.
• Use Table Styles to analyse data.
• Creating Pivot Tables from Excel Tables

PivotCharts

• Create a PivotChart report
• Format a PivotChart report
• Lay out a PivotChart report
• Work with data in a PivotChart report
• Creating a “Dashboard”

 Introduction to Power Query

• What is Power Query
• Cleansing and Transforming data
• Loading to Excel Tables.