This course will give you the skills to use a Pivot Table to produce meaningful information from a table of information in Excel.
You will learn that with persistence and the ability to manipulate Pivot Tables via 'Trial-and-Error' you will immediately get results.
To get the most from this course
You should be a competent Microsoft Excel user.
What this course will do for you
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 you will learn
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
- 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
- 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.