Advanced Excel · Interactive lesson 12 of 12
Macro recorder & automation awareness
A macro is just a recording of clicks, replayed on demand — no VBA-writing required to get started. The skill worth having isn't writing macros from scratch; it's knowing when one's worth recording, and being able to read what it generated.
Screens shown: Excel for Microsoft 365 for Windows. Practice works in any browser.
Written tutorialHow to Record a Macro in ExcelSkill check
Try these first:
- Decide whether a repeated task is actually worth automating.
- Record a simple formatting macro and read back what it generated.
- Explain the difference between relative and absolute references in a recorded macro.
- Name the file format required to save a workbook containing macros.
Comfortable already? You've completed the Advanced track — see the capstone exercise below.
Concept teach
What you're learning
- When a macro is appropriate
- A multi-click task you repeat often, on data shaped the same way each time — not a one-off, and not something a formula already handles better.
- Recording a repeatable step
- Developer → Record Macro, perform the clicks once, Stop Recording — Excel writes the VBA for you.
- Relative vs. absolute references
- By default a recorded macro replays on the exact same cells every time. Turn on "Use Relative References" before recording if it should instead act on wherever the selection happens to be.
- Reading generated VBA
- You don't need to write VBA to read it — each recorded line maps directly to one click you made, in order.
- Saving macro-enabled files
- A workbook containing macros must be saved as
.xlsm(or.xlsb) — a plain.xlsxsilently strips them out. - Macro security
- Macros from an untrusted source can run arbitrary code — Excel disables them by default in downloaded files for exactly that reason. Enable them only when you trust where the file came from.
Where to find it in Excel
Developer›Code›Record Macro
| A | B | C | D | E | F | G | H | ||
|---|---|---|---|---|---|---|---|---|---|
| 1 | Region | Sales | |||||||
| 2 | North | 6,900 | |||||||
| 3 | South | 5,400 | |||||||
| 4 | |||||||||
| 5 | |||||||||
| 6 | |||||||||
| 7 | |||||||||
| 8 | |||||||||
| 9 |
Swipe sideways to see the whole window.
- 1Developer tab. Turn it on in File › Options › Customize Ribbon. (View › Macros › Record Macro also works.)
- 2Record Macro; the same button turns into Stop Recording.
- 3Use Relative References before recording if the steps should work from any cell.
- 4Visual Basic (Alt+F11) shows the code Excel wrote.
- 5Save as .xlsm (macro-enabled) or the macro is lost.
- 6Macro Security controls whether macros can run.
Rendered view of Excel for Microsoft 365 (Windows). Ribbon layout can differ slightly on Mac, Excel for the web or a narrow window.
Guided build
Record a macro that formats a raw export: bold the header row, then fill it with color. Toggle relative references and see how the generated code changes.
Real-world exercise — capstone
Advanced Excel readiness
This exercise touches nearly everything in the track — using the same six-transaction dataset from A2, A4, A5, and A10:
- Turn the source data into a clean Table and add calculated fields for Gross Margin and Margin Percent (A1).
- Use a dynamic array to create a sorted, deduplicated list of regions or products (A2).
- Build a PivotTable summarizing Revenue and Gross Margin by month and region (A5).
- Add a slicer and a PivotChart for the main trend (A5, A9).
- Add an assumptions area and use it in at least one formula (A1).
- Add a reconciliation check comparing source Revenue to PivotTable Revenue (A10).
- Document refresh steps on a Notes sheet (A1).
- Optional: load the data through Power Query and make the report refreshable (A6).
Work through it independently, start to finish — a workbook another person could refresh and trust is the real bar at this level, not speed.
Spot a mistake or have an idea for this lesson? Send feedback