Advanced Excel: Functions, Power Query and Power Pivots Training Course

Key functions to prepare data for pivot table reporting

• Table format

• Lookup functions

• Text functions

• Naming cells

Advanced techniques in creating and customizing pivot tables

• Number and cell format

• Report layout

• Calculation in value field

• Grouping and ungrouping fields

Default and customized sorting and filtering

• Sorting using custom list

• Creating calculated field

• Filtering using slicers and timelines

• Connecting multiple pivot tables to one set of slicers

• Customizing reports using the GetPivotData option

Power Query: A must-have skill

• Introduction to this new feature

• Where does Power Query fit in the Power family!

• Get and transform: Link your Excel to external other data sources

• Excel files

• Text files

• Web

• SQL

• Creating and editing the Query

• Get data from: Tables, files and folders

• Power Query to clean up data

• Practical examples:

• UnPivotting data

• Working with nested column headers and merged cells

• Naming, merging, splitting and removing columns

• Filtering rows in different ways

• Transforming and formatting data

• Combining queries: Merge and Append

• The different types of joining data

Analyzing disparate data sources with pivot tables

• Utilizing pivot table wizard

• Using internal data model

• Building pivot tables using external data sources

 The new world of PowerPivot

• Benefits and drawbacks of PowerPivot

• Merging data from multiple tables without using Vlookup

• Creating better calculations using the DAX Formulas

• Using DAX to create calculated fields

• Calculate and Related functions