Live training with Eithne Dolan
Microsoft Excel Advanced Level 2
Dashboards, auditing and Power Query
I’ll help you build more sophisticated solutions, create interactive dashboards and take your first steps into automating data preparation with Power Query.
Course outline: PDF, 2 pages
- Group size
- Recommended maximum: 10
- For an interactive session
- Delivery
- Live virtual
- Demonstrations, guided practice and supplied exercise files
- Duration
- 6 hours
- One 6-hour session or two 3-hour sessions
Is this course for you?
Does any of this sound familiar?
You’re already comfortable with advanced formulas, XLOOKUP, dynamic arrays, Tables and PivotTables. Now you want to combine information, produce management reports and turn large amounts of data into something clear, visual and useful. Do you...
- Regularly combine information from different worksheets?
- Use FILTER, SORT and UNIQUE but want to combine them to solve more complex problems?
- Need to filter data using several conditions at the same time?
- Use XLOOKUP but want to handle multiple criteria, wildcards and more complex searches?
- Find that some of your formulas are becoming long and difficult to understand?
- Inherit spreadsheets and struggle to work out how the formulas have been constructed?
- Spend time troubleshooting formula errors?
- Create PivotTables and charts but want to bring them together into a professional interactive dashboard?
- Need to compare performance across products, categories, months or years?
- Want charts and reports that respond when a user makes a selection?
- Spend time repeatedly cleaning or preparing data before you can report on it?
- Keep hearing about Power Query but aren't sure whether it would help you?
If any of the above resonates, this course is for you.
What you’ll be able to do
- Stack and reshape data dynamically using VSTACK, HSTACK, TOCOL and TOROW.
- Build nested dynamic-array formulas using multiple conditions, dates and partial matches.
- Use advanced XLOOKUP techniques.
- Audit and troubleshoot formulas in complex workbooks.
- Prepare and create a dynamic (self-automating) Excel dashboard.
- Create PivotTables, Pivot Charts, slicers and year-on-year comparisons for an interactive dashboard.
- Build a Power Query process to import, transform, load and refresh data.
Your course outline
Seven modules. Real-world application.
Advanced formulas, analysis, reporting and automation techniques brought together, so you can build Excel solutions that are more powerful, more dynamic and easier to maintain.
01Stacking and Reshaping Dynamic Arrays
- VSTACK and HSTACK
- TOCOL and TOROW
- CHOOSECOLS and CHOOSEROWS
- Combining similarly structured worksheets
- Managing differently sized arrays and errors
02Advanced Nested Dynamic-Array Formulas
- Nest FILTER, UNIQUE and SORT
- Multiple AND/OR conditions
- Date-range filtering
- Partial-text and case-sensitive criteria
- Return selected columns
- Combine FILTER with TAKE and DROP
- SEQUENCE and RANDARRAY
03Advanced Lookups
- XLOOKUP with multiple criteria
- Return multiple columns
- Wildcard matching
- Exact and approximate matching
- Bottom-up searches
- XLOOKUP nested with text and array functions
- Approximate-match VLOOKUP
04Formula Auditing and Analytical Techniques
- Trace Precedents and Dependents
- Show Formulas
- Evaluate Formula
- Error Checking
- Watch Window
- Go To Special
- RANK, FREQUENCY and FORECAST
- VALUE and ABS
05Advanced Conditional Formatting
- Multiple-condition rules
- Dynamic date ranges
- Wildcards and partial text
- Exception highlighting
- PivotTable Conditional Formatting
- Rule priorities
06Build a Dynamic (self-automating) Excel Dashboard
- Create multiple PivotTables from the same data source
- Create summary reports
- Create % difference reports
- Create and connect slicers to multiple PivotTables
- Create KPIs
- Create a "Clear Selections" button using a macro
- Create a Timeline
- Create custom chart templates
07Introduction to Power Query
- What is Power Query?
- Extract, Transform and Load
- Power Query versus formulas and copy-and-paste
- Import data from an Excel spreadsheet
- Navigate the Power Query Editor
- Automated data cleaning
- Automated data transformation
- Close & Load
- Update the source and Refresh
- Create a PivotTable from prepared data
What you’ll learn to do
Rather than learning each function in isolation, you’ll see how several functions work together to solve real business problems.
Combine and reshape data
- VSTACK and HSTACK
- TOCOL and TOROW
- Nest FILTER, UNIQUE and SORT
- Multiple conditions, date ranges and partial text
Take XLOOKUP further
- Multiple criteria lookups
- Wildcard and approximate matches
- Search from the bottom upwards
- Combine with text and array functions
Audit and troubleshoot
- Trace Precedents and Dependents
- Evaluate a formula step by step
- Error Checking and the Watch Window
- Ranking, frequency and year-on-year analysis
Dashboards and Power Query
- An interactive dashboard from one data source
- KPIs, slicers and timelines
- Import, clean and transform data
- Refresh when the source changes
Build an interactive Excel dashboard
A major part of the course: you’ll start with a structured sales dataset and turn it into a visual, self-updating report with PivotTables, KPIs, slicers and timelines, so users can explore performance from different perspectives.
Before you join: you should be confident with Advanced Level 1 topics, including dynamic arrays, XLOOKUP, advanced logical formulas, formula-driven Conditional Formatting and PivotTables.
Let’s make Excel work for you
Book a free 20-minute consultation with me to discuss your team, your Excel experience and your training goals.
Book a Free Consultation