Excel at Scale: INDEX-MATCH, Power Query and Dashboards
Two-condition lookups, automated data pipelines, and interactive dashboards that do not require touching a formula.
INDEX-MATCH: the last lookup formula you will ever need
INDEX-MATCH separates the lookup logic from the return logic, which makes it the only option for two-directional or multi-condition lookups.
Use it when XLOOKUP cannot do the job — looking left, or matching on two columns at once.
- INDEX returns a value from a list at a specific position
- MATCH finds the position of a value inside a list
- Combined: find the price where product matches AND region matches
Power Query: cleanup that repeats itself
Power Query records every data-cleaning step you perform (remove duplicates, split columns, fix data types, etc). The next time source data arrives, one click of Refresh reruns every step automatically.
This is the single biggest time-saver for any analyst handling recurring reports — thirty minutes of manual cleanup becomes a five-second refresh.
- Data > Get & Transform > From Table opens the Power Query editor
- Every click is recorded as a step
- Close & Load writes the clean data to a new sheet
- When source data updates, click Refresh to rerun all steps
Building interactive dashboards
Base every chart on a PivotTable, never on raw data. Add Slicers (Insert > Slicer) so non-technical viewers can filter by region or month without editing formulas.
Connect slicers to multiple PivotTables via Report Connections so filtering one chart updates all charts at once.
- PivotTable as chart source — updates when data refreshes
- Slicer on region — filters the PivotTable
- Report Connections — one slicer, multiple charts
- Format with consistent colours and no gridlines for polish
When to introduce macros
A recorded macro is a sequence of clicks you teach Excel to repeat. For genuinely repetitive steps — reformatting twenty identical sheets every week, for instance — a simple macro removes the tedium.
Recording is safer than writing code from scratch; Lovable edits are enough for most analyst needs.
- Developer > Record Macro to capture clicks
- Run the recorded macro to replay every step at once
- Light editing of recorded code is usually safe; avoid complex logic unless you know VBA
Frequently asked
Is VBA knowledge required to use macros?
No — record a macro first. Light editing of recorded code is usually safe. Deep VBA requires separate study.
Can Power Query handle live data sources, not just static files?
Yes — Power Query can connect to databases, web APIs, and cloud services. Set refresh to automatic or on-demand.
Related topics
Discussion
- No comments yet — start the thread.