Excel Pivot Tables and Power Pivot

This course will cover the many features of Pivot Tables and then move onto the more powerful use of PowerPivot and the DAX functions that are used to manipulate the data.  Users will be shown how to create data models both within and outside of the PowerPivot environment to enable multiple data sources to be manipulated.

Using PowerPivot involves the use of DAX functions which are written differently from standard Excel Functions.  In addition to core Excel functions that users may be familiar with many new functions are available and introduced during this course in order that the enhanced power of PowerPivot can be fully utilised.

Course Topics

Pivot Table Essentials

Layout • Limitations • Data preparation • Slicers and Timelines • Refreshing Data • Pivot Table Cache

Customising Pivot Tables

Changing Report Layouts • Working with Sub Totals • Converting a Pivot Table to Values • Changing Function Type • Changing the Calculation Type

Grouping, Sorting and Filtering

Grouping Dates • Grouping Numeric Data • Creating Bespoke Groups • Sorting and Filtering • Sorting with a Custom Group • Manual Sorts • Special Filters such as Top Five 

Calculated Fields and Items

Calculated Fields • Calculated Items • Modifying Calculated Fields or Items • Deleting Calculated Fields or Items • Changing the Order of Calculation • Documenting Formulas • Calculation Constraints

Pivot Charts and Conditional

Creating Pivot Charts • Formatting Pivot Charts • Adding Slicers and Timelines • Creating a Dashboard • Using Conditional Formatting

Analysing Multiple Data Sources

Using Multiple Consolidation Ranges • Using the Internal Data Model • Creating a Relationship • Managing Relationships • Adding/Removing Tables from the Data Model • Creating Pivot Tables from an Existing Data Model

Using Power Pivot

Enabling PowerPivot • Benefits and Limitations of PowerPivot • Connecting to Data Sources • Defining Relationships • Building the Pivot Table

DAX Function

What are DAX Functions? • Getting Started with DAX • Avoiding Implicit Calculated Fields (Measures) • Creating and Amending Calculated Fields (Measures) • Basic DAX Functions • Other Functions – DISTINCTCOUNT & DIVIDE • Calculated Columns • Using CALCULATE, IF, SWITCH FILTER and FIND and other DAX Functions