Master Excel

Dynamic arrays, Power Query, macros & dashboards.

A free expert Excel guide to the power features: dynamic array formulas (UNIQUE, FILTER, SORT, SEQUENCE, LET), advanced lookups, Power Query to clean and combine data, macros and VBA to automate the boring parts, building dashboards with slicers, what-if analysis (Goal Seek, Scenario Manager) and protecting your work — with examples and performance tips. Runs fully in your browser, no sign-up.

Frequently asked questions

What are dynamic array formulas in Excel?

Dynamic arrays (Excel 365/2021) are formulas that spill results across many cells from a single entry — UNIQUE returns a de-duplicated list, FILTER returns only matching rows, and SORT/SORTBY reorder a range. They update live as the source data changes, with no dragging. You can even nest them, e.g. =SORT(UNIQUE(FILTER(...))).

What is Power Query used for?

Power Query (Data ▸ Get & Transform) cleans and combines data and records every step, so when the source changes you just click Refresh and all the steps replay. It's ideal for repetitive monthly imports, merging tables and unpivoting messy data.

How do I create a macro in Excel?

Enable the Developer tab (File ▸ Options ▸ Customize Ribbon), then Developer ▸ Record Macro, perform the steps once, and Stop Recording. Replay it from Developer ▸ Macros or a button. Edit the generated VBA in the editor (Alt + F11), and save as a macro-enabled .xlsm file.

What is Power Pivot and DAX?

Power Pivot loads many tables into Excel's Data Model and links them with relationships, so a single PivotTable can pull from several tables and handle millions of rows. DAX is the formula language for writing measures like Total Sales := SUMX(Sales, Sales[Qty]*Sales[Price]) and year-over-year comparisons.

How do I protect cells and sheets in Excel?

By default all cells are locked, but locking only takes effect once the sheet is protected. Unlock your input cells (Format Cells ▸ Protection ▸ untick Locked), then Review ▸ Protect Sheet with a password — now only the inputs are editable and formulas can't be overwritten.

Related tools — Guides & Tutorials