How to Record a Macro in Excel

Recording a macro captures the actions you perform in Excel and saves them as reusable VBA code you can replay with one click. It is the simplest way to automate a repeatable task without writing code by hand. For analysts it removes the tedium of the same formatting or cleanup steps every period, as long as the steps stay consistent.

What recording does and when to use it

The macro recorder writes VBA that mirrors each action you take, from selecting cells to applying formats. Replaying the macro repeats those actions in order, which automates routine, well-defined tasks.

Use it when you do the same sequence often: stamping a standard format on a report, clearing and resetting an input block, or building a recurring summary layout. It suits tasks that are mechanical and repeatable rather than ones that branch depending on the data.

Step by step

First show the Developer tab through File > Options > Customize Ribbon and check Developer in the right-hand list.

1. Go to Developer > Record Macro.
2. Name the macro, optionally set a shortcut key, and choose where to store it.
3. Decide on Use Relative References before or during recording if the macro should work from wherever the cursor is.
4. Click OK and perform the exact steps you want captured.
5. Go to Developer > Stop Recording when finished.
6. Replay it from Developer > Macros, then Run, or with your shortcut key.

Toggle Use Relative References on the Developer tab when the macro should adapt to wherever you start.

A model use case

Record a macro that applies your house formatting to a raw export: set fonts, number formats, borders, and freeze the header row. Each period you paste the new export and run the macro to format it in one click instead of repeating a dozen manual steps.

Using relative references, record a macro that formats a single block, then run it on each block you click into. For a repetitive layout that never varies, a recording saves real time and removes the chance of skipping a step.

• Captures your exact actions as replayable VBA.
• Relative references let one macro run from any starting cell.
• Store it in the Personal Macro Workbook to use it across all files.

Pitfalls and limits

Recorded macros are brittle because they replay literal actions; if the data layout shifts, the macro can format the wrong cells or fail. The recorder cannot capture logic such as loops, conditions, or error handling, so anything that must react to the data needs hand-written VBA. Macro-enabled files must be saved as .xlsm, and macros may be blocked by security settings until enabled.

When you need the same reliable result across many different workbooks, a tested tool or add-in is usually more robust than a recording, because it handles varying layouts and edge cases that a literal replay does not. A recorded macro is best for stable, personal, repetitive tasks rather than logic that must adapt or be shared widely.