Advanced Excel – Formulas, Power Query & Dashboards by Ryan Vaz

Here is the 10-hour Advanced Excel course restructured into a 10-day schedule (1 hour per day).

This format allows you to learn a concept and then "sleep on it," which is often better for retention with complex topics like Power Query and DAX.

Week 1: Modern Formulas & Data Visualization

Day 1: The Modern Lookup Toolkit

• Goal: Stop using VLOOKUP and Index/Match.

• Topics (45 mins):

  • XLOOKUP: Basic lookups, 2-way lookups, and wildcard searches.

  • XMATCH: Finding positions dynamically.

  • Exercise (15 mins): Fix a broken report by replacing all VLOOKUPs with XLOOKUPs that handle errors gracefully (using the if_not_found argument).

Day 2: Dynamic Arrays (The "Spill" Era)

• Goal: manipulate lists of data without touching a Pivot Table.

• Topics (45 mins):

  • FILTER: Extracting rows based on criteria (e.g., "Show me all Sales > $500").

  • SORT & SORTBY: Auto-sorting lists.

  • UNIQUE: Creating distinct lists from duplicates.

  • SEQUENCE: Generating auto-numbers.

• Exercise (15 mins): Build a dynamic search box where typing a name instantly filters a data table below it.

Day 3: Advanced Logic & Logic Functions

• Goal: Write cleaner, shorter logical formulas.

• Topics (45 mins):

  • IFS: Replacing nested IF statements.

  • SWITCH: handling specific values (like turning "1" into "January").

  • TEXTJOIN & TEXTSPLIT: Managing text strings easily.

• Exercise (15 mins): Clean up a messy "Customer Feedback" dataset using text functions and categorize them using IFS.

Day 4: Visual Intelligence

• Goal: Make data easier to read at a glance.

• Topics (45 mins):

  • Advanced Conditional Formatting: Using formulas in rules (e.g., highlight the whole row if Cell A > 100).

  • Custom Charts: Building a "Bridge Chart" (Waterfall) to show P&L movement.

  • Sparklines: Mini charts inside cells.

• Exercise (15 mins): Create a "Heat Map" table that visually highlights high and low performance automatically.

Day 5: Advanced Pivot Tables

• Goal: Master the classic reporting tool.

• Topics (45 mins):

  • Slicers & Timelines (connecting one slicer to multiple tables).

  • Calculated Fields (doing math inside the pivot).

  • "Show Values As": % of Parent, Difference From, Running Totals.

• Exercise (15 mins): Build a report showing Month-over-Month growth % without writing a single formula.

Week 2: Automation & Data Modeling

Day 6: Power Query - The "Clean" Phase

• Goal: Never manually copy/paste or delete rows again.

• Topics (45 mins):

  • Importing data from folders (combining 10 separate monthly files into one).

  • Removing top rows, promoting headers.

  • Unpivoting: Turning "wide" data (months as columns) into "tall" data (database format).

• Exercise (15 mins): Connect to a folder of 3 CSV files, combine them, and load them to a table.

Day 7: Power Query - The "Transform" Phase

• Goal: Manipulate data before it hits the grid.

• Topics (45 mins):

  • Splitting Columns by Delimiter.

  • Conditional Columns (If/Then logic in Power Query).

  • Merging Queries: The "VLOOKUP" of Power Query (joining two tables permanently).

• Exercise (15 mins): Merge a "Sales" table with a "Product Master" table to bring in Product Names, then calculate Total Revenue inside Power Query.

Day 8: Data Modeling & Power Pivot

• Goal: Handle 1M+ rows and relate tables.

• Topics (45 mins):

  • The "Data Model" vs. Standard Excel Tables.

  • Creating Relationships (One-to-Many) between Fact and Dimension tables.

  • Intro to DAX: Calculated Columns vs. Measures.

• Exercise (15 mins): Link three tables (Sales, Customers, Products) in the diagram view and create a Pivot Table from the model.

Day 9: Analysis & Scenarios

• Goal: Forecasting and "What-If" decision making.

• Topics (45 mins):

  • FORECAST.ETS: Detecting seasonality and predicting future values.

  • Goal Seek: Finding the input needed to hit a target.

  • Solver: Optimizing for max profit given specific constraints (budget, inventory).

• Exercise (15 mins): Use Solver to determine the optimal product mix to maximize profit with limited raw materials.

Day 10: Capstone Dashboard

• Goal: Build a "Set and Forget" executive dashboard.

• Topics (45 mins):

  • Designing the layout (hiding gridlines, using shapes for backgrounds).

  • Assembling the pieces: Slicers on the left, KPIs on top (using Cube formulas or GetPivotData), Charts in the middle.

  • Protection: Locking the dashboard so users can only click Slicers.

• Exercise (15 mins): Finalize the "Executive View" dashboard using data prepared in Days 6-8.