Day 03 Part 1 — Advanced Excel Analytics¶
Excel is far more powerful than most analysts realise. Beyond formulas and pivot tables lies a full analytics stack: Power Query for ETL, Power Pivot for data modelling, DAX for measures, and dynamic array formulas. This session covers the advanced Excel that handles datasets and modelling tasks people assume require Power BI.
Learning Objectives¶
- Use Power Query in Excel for repeatable data transformation
- Build a data model with Power Pivot and relationships
- Write DAX measures inside Excel
- Use dynamic array formulas (Excel 365)
- Know when Excel is enough and when to graduate to a BI tool
Power Query in Excel¶
The same Power Query engine that's in Power BI is built into Excel (2016+). It's Excel's answer to repeatable data cleaning.
Use it to: - Combine all CSVs in a folder into one table - Clean messy data with recorded, repeatable steps - Merge (join) tables - Unpivot crosstab data into tidy format - Refresh with one click when source data changes
Power Query replaces manual copy-paste cleaning
If you clean the same report every month by hand, build it once in Power Query. Next month, drop in the new file and click Refresh. The steps re-run automatically — no errors, no wasted hours.
Power Pivot — Data Modelling in Excel¶
Power Pivot lets Excel hold millions of rows (far beyond the 1M row sheet limit) and build relationships between tables — a proper data model, in Excel.
- Load multiple tables into the Data Model
- Create relationships (drag key to key, like Power BI)
- Build a star schema: fact table + dimension tables
- Then a PivotTable can use fields from multiple related tables at once
DAX in Excel¶
Yes — the same DAX from Power BI works in Excel's Power Pivot. Create measures in the Data Model:
Total Revenue := SUM(Sales[Amount])
Total Orders := DISTINCTCOUNT(Sales[OrderID])
Revenue YTD := TOTALYTD([Total Revenue], 'Date'[Date])
Margin % := DIVIDE([Profit], [Total Revenue], 0)
These measures then appear in PivotTables and respond to slicers — giving you Power BI-like calculation power inside a workbook.
Dynamic Array Formulas (Excel 365)¶
Modern Excel has powerful array formulas that "spill" results across cells automatically:
=FILTER(data, condition) // return rows matching a condition
=SORT(data, sort_col, order) // sort an array
=UNIQUE(range) // distinct values
=SORTBY(data, by_array, order) // sort by another column
// Combine them: top 5 products by sales
=TAKE(SORT(FILTER(A2:C100, C2:C100>0), 3, -1), 5)
// XLOOKUP — the modern lookup
=XLOOKUP(lookup, lookup_array, return_array, "Not found")
// SUMIFS / COUNTIFS — conditional aggregation
=SUMIFS(revenue, region, "West", status, "completed")
=COUNTIFS(status, "cancelled", category, "Electronics")
When Excel Is Enough vs When to Graduate¶
| Use Excel when | Use Power BI/Tableau when |
|---|---|
| Data < ~1M rows (Power Pivot extends this) | Data is millions of rows from a database |
| One-off or ad-hoc analysis | Recurring dashboards for many users |
| Quick financial modelling, what-if | Real-time / scheduled refresh needed |
| Audience lives in Excel | Audience needs interactive web dashboards |
| Sharing a single file | Centralised, governed, secured reporting |
Excel + Power Query + Power Pivot is a legitimate analytics stack
Don't dismiss Excel. With Power Query and Power Pivot, it handles surprisingly large, modelled analyses — and it's where most businesses still live. Mastering advanced Excel is highly employable.
Practice Exercises¶
Warm-up
1. Use Power Query to import a CSV, change column types, and remove blank rows.
2. Write a SUMIFS to total revenue for completed orders in one category.
3. Use FILTER and SORT (Excel 365) to show the top 10 orders by value.
Main 4. Use Power Query to combine multiple monthly CSV files from a folder into one table. 5. Build a Power Pivot data model with a sales table and a date table, related. 6. Write DAX measures (Total Revenue, YTD, Margin %) in Power Pivot and use them in a PivotTable with slicers.
Stretch 7. Build a self-refreshing monthly report: Power Query loads → Power Pivot models → PivotChart dashboard with slicers. Add a new month's file and refresh.
Interview Questions¶
[Beginner] What is the difference between SUMIF and SUMIFS?
[Mid-level] What is Power Query and how does it differ from regular Excel formulas?
[Mid-level] What is Power Pivot and how does it let Excel handle more than 1 million rows?
[Senior] A stakeholder insists on doing everything in Excel, but the data is 5 million rows in a SQL database refreshed daily. How do you advise them?