Data Analysis - Excel 365 – Power Query, Power Pivot, Dashboard Production and beyond

Course Description

Excel 365 has heralded an explosion in the tools available for reporting and analysis in accounting. This course will introduce two of the most powerful Excel tools - Power Query and Power Pivot and then use those techniques (with standard Excel skills) to produce an Excel Dashboard. We will examine Excel tools for ‘What if’ and newer techniques in the very important data analysis areas of LOOKUP (especially XLOOKUP) and FILTERING. The course will complete using the Excel Dashboard we created to communicate the results via exchanges between Excel and PowerPoint.
1 day
€375.00
 

Who should attend

This course is aimed at experienced Excel users who have to:
Analyse data using Excel
Use data which has been analysed in Excel
Present Excel-based data to an audience

What is covered and how?

This course has four main parts:
1. Using a worked example to introduce two of the most powerful Excel tools; Power Query and Power Pivot. The ultimate output here will be an Excel-based dashboard.
2. Examining some self-generated techniques and the Excel tools for ‘What if’ simulations - both input to output and output to input.
3. Comparing legacy Excel to newer techniques in the very important data analysis areas of LOOKUP and FILTERING.
4. Using the dashboard output of 1 above, communicating the results via exchanges between Excel and PowerPoint.

This course will operate with a mixture of instructor-delivery and practical exercises using a case-study approach. The emphasis will be on delivering practical techniques which can be used in everyday work, rather than on esoteric theory.

Benefits

Participants will benefit from:
An introduction to Power Query, giving an appreciation of its power and where you might use it in your organisation.
An introduction to Power Pivot to help participants see where they might enhance reporting by switching to from legacy pivot tables.
Seeing how Excel can assist in sanity checking and validating analyses, forecasts etc. through the various WHAT IF tools available.
Exploring the use of the new LOOKUP and FILTER tools for more succinct and surgical analysis of datasets.
Understanding better the relationship between Excel and PowerPoint and issues associated
Creating a dashboard in PowerPoint from linking to Excel and validating and repairing links between the two where required.

Course Content and approach

All of the techniques covered will be applied to the same supplied case study, the output of which will be a number of reports culminating in a dashboard.

Querying using Power Query
The Data Model concept
Power Pivot
DAX measures and Syntax

What If analysis: IF, LOOKUP, Data Tables, Scenario Manager, Goal Seeker, Solver and FORECAST.

XLOOKUP and XMATCH - superseding VLOOKUP AND HLOOKUP - with real-world examples and dynamic array formulas

Filtering - a comparison of the legacy desktop with the FILTER function making the old methods defunct

Excel and PowerPoint - linking vs embedding, using the CAMERA tool for live-linking and applying these techniques to our Case Study dashboard.

Advanced ExcelMicrosoft ExcelMS ExcelExcel 365Excel 2021Microsoft Excel 2021