Course Objectives
Advanced Excel: Power Query, Power Pivots and Macros
Course Methodology
This is a hands-on course with practical applications and use of Microsoft Excel 2016 throughout the five days. All examples, exercises and cases are practiced in Excel.
Course Objectives
By the end of the course, participants will be able to:
Apply key Excel functions to prepare data for analysis using pivot tables
Create and customize pivot tables to reconcile and analyze accounts efficiently
Utilize Power Query to clean up and prepare data for reporting
Utilize pivot tables functions and calculations to generate a set of management and business analysis reports
Run macros to speed up work and utilize other advanced techniques in data analysis and reporting
Report and analyze big data using PowerPivots
Target Audience
Accountants, senior and junior accountants, business analysts, accounting and finance professionals, business analysts, research professionals and staff from any function who need to master and upgrade their skills in Excel pivot tables, Power Query and Power Pivots and work with big data analysis.
Target Competencies
Power Query
Practicing pivot tables
Working with PowerPivot
Reporting
Analyzing business data
Designing basic macros
Note
This is a hands-on training course using laptops, which will be made available by Meirc for the duration of the training.
Course Outline
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 un-grouping 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
Introduction to Macros: Let Excel do the work for you
Where you cannot use Power Query, use macros
Planning your macro
Creating and recording macros
Editing macros
Introduction to Visual Basic for Application (VBA)
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 un-grouping 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
Introduction to Macros: Let Excel do the work for you
Where you cannot use Power Query, use macros
Planning your macro
Creating and recording macros
Editing macros
Introduction to Visual Basic for Application (VBA)