Recording Your First Macro

Excel Advanced

Chapter 6 · Recording Your First Macro: What It Actually Does

Every chapter so far — formulas, PivotTables, Power Query, DAX — asked you to interact with a feature Excel already built. A macro is different: it records a sequence of your own actions and lets you replay that exact sequence with one click or keyboard shortcut. This chapter covers the Macro Recorder specifically — the beginner-friendly way to create a macro without writing a line of code — and sets up Chapter 7's dive into reading and writing that code directly.

What a Macro Actually Is

A macro is a stored sequence of actions, written under the hood in VBA (Visual Basic for Applications) — Excel's own programming language. There are two ways to create one: recording your own clicks and keystrokes (this chapter), or writing VBA code directly by hand (Chapter 7). Recording is the on-ramp, in exactly the same relationship Chapter 4's Power Query Applied Steps panel has to hand-written M code — a beginner never needs to write the underlying language directly, but it's there, readable and editable, the moment anyone wants to go further.

Enabling the Developer Tab

The ribbon tab where every macro tool lives — Developer — isn't visible by default. Enable it once via File → Options → Customize Ribbon, then tick the Developer checkbox in the right-hand list. This single one-time setup step unlocks Record Macro, View Macros, and the Visual Basic editor button for good.

Recording Your First Macro

Developer → Record Macro opens a dialog before recording starts:

Macro name — no spaces allowed, must start with a letter (e.g. FormatNewData, not Format New Data).
Shortcut key — optional, e.g. Ctrl+Shift+F, to run the macro instantly later without opening any menu.
Store macro in — This Workbook keeps it available only in the file it was recorded in; Personal Macro Workbook stores it in a special hidden workbook that opens automatically every time Excel starts, making the macro available in every workbook you ever open, not just this one.

Click OK, perform the exact sequence of actions you want repeated — for example, bolding a header row, applying a number format, and freezing the top row — then click Developer → Stop Recording (or the small square icon that appears in the status bar while recording).

Running a Macro

A recorded macro can be run three ways: Developer → Macros, selecting it, and clicking Run; its assigned keyboard shortcut, if one was set; or by assigning it to a clickable shape or a Quick Access Toolbar button (right-click the macro in the Macros list → Assign Macro on any shape you've drawn on the sheet).

Relative vs. Absolute References While Recording

This is Chapter 3's relative-vs-absolute distinction, reappearing in a completely different context. By default, the Macro Recorder captures absolute cell addresses — a macro recorded starting at cell A1 always jumps back to exactly A1 every time it's run, no matter which cell was actually selected beforehand.

Toggling Developer → Use Relative References ON before recording changes this: the recorded macro instead replays its actions relative to wherever your cursor happens to be the moment you run it — select cell D8 and run it, and it behaves as though it started at D8, not wherever you originally recorded it from.

Toggling Use Relative References changes behaviour for every macro recorded afterward, not retroactively

This setting only affects macros recorded WHILE it's turned on — it does not change how an already-recorded macro behaves. If a macro needs to always return to one specific fixed cell (e.g. always going back to a fixed report header), record it with the toggle OFF; if it needs to act starting from wherever the user currently has selected, turn the toggle ON first.

Viewing the Recorded Code

Alt+F11 (or Developer → Visual Basic) opens the VBA Editor, where Developer → Macros → Edit jumps straight to your recorded macro's actual generated code:

Sub FormatNewData()
    Rows("1:1").Font.Bold = True
    Range("B2:B100").NumberFormat = "$#,##0.00"
    ActiveWindow.FreezePanes = False
    Rows("2:2").Select
    ActiveWindow.FreezePanes = True
End Sub

Nothing here requires understanding VBA yet — the point of this chapter is simply confirming that recording and hand-writing code produce the exact same underlying thing, previewing what Chapter 7 explains in full.

	Recorded macro	Hand-written VBA (Chapter 7)
Speed to create	Fast — just perform the actions once	Slower — requires knowing the syntax
Captures mistakes made while recording	Yes — every click, including unwanted ones	N/A — only what's deliberately typed
Loops, conditionals, user prompts	Cannot capture these at all	Fully supported
Readable, editable afterward	Yes, in the VBA Editor	Yes

Two different "recorder" features solving the same underlying problem

Chapter 4's Power Query Applied Steps panel and this chapter's Macro Recorder exist for the same reason: sparing a beginner from hand-writing the underlying language (M for Power Query, VBA for macros) for common, repetitive actions, while leaving that underlying code fully visible and editable the moment anyone wants to go further. Recognising this same pattern across two very different Excel features is worth more than memorising either one in isolation.

Saving as .xlsx silently discards every macro in the workbook

A workbook containing macros must be saved as a .xlsm file (Macro-Enabled Workbook) — saving it in the default .xlsx format triggers a warning dialog, but dismissing that dialog without reading it (or clicking the wrong button) permanently strips every macro from the saved file, with no way to recover them from that file afterward. Separately, opening a workbook containing macros that was downloaded or received from someone else shows Excel's own "Enable Content" security bar — macros are disabled by default in files from an untrusted source, requiring an explicit, deliberate trust decision before they'll run at all.

Hands-On Exercises

Exercise 1

Record a macro named FormatHeader that bolds row 1 and applies a light fill colour to it, assigning it the shortcut Ctrl+Shift+H, storing it in the Personal Macro Workbook. Explain what storing it there specifically enables that storing it in "This Workbook" would not.

📄 View solution

Exercise 2

You want a macro that bolds and colours whichever row is currently selected, not always row 1 specifically — usable on report data that starts on a different row each time. Explain which recorder setting must be turned on before recording, and describe what would go wrong if you recorded it with the default setting instead.

📄 View solution

Exercise 3

A colleague records a useful macro, then saves the workbook using the default Save (Ctrl+S) without changing the file type, dismissing a dialog box they didn't read. The next day the macro is gone. Explain exactly what happened and what they should have done differently when saving.

📄 View solution

Chapter 6 Quick Reference

Developer tab — enable once via File → Options → Customize Ribbon
Record Macro — name (no spaces), optional shortcut key, choose This Workbook vs. Personal Macro Workbook (available everywhere)
Use Relative References (toggle) — OFF (default) always returns to the exact recorded cells; ON replays relative to wherever the cursor currently is
Alt+F11 opens the VBA Editor; Developer → Macros → Edit jumps to a specific macro's generated code
Macros require saving as .xlsm — the default .xlsx format silently discards them
Macros from untrusted/downloaded files are disabled by default, requiring an explicit "Enable Content" decision