Macro Recorder in Excel: What It Can and Cannot Do

Excel’s Macro Recorder turns many actions in the desktop app into VBA code that you can run again. It is useful for repetitive, straightforward tasks, but it is not a complete recorder of everything you do: some actions are not captured, and recorded macros usually need checking before they are dependable or reusable.

What the Macro Recorder Does

A macro is a set of instructions that automates work in Excel. When you record one, Excel observes supported commands—such as selecting cells, entering values, applying formatting, or using certain ribbon commands—and writes corresponding Visual Basic for Applications (VBA) instructions. You can replay the macro to repeat those actions.

The recording is not a video or a sequence of mouse clicks. Excel records operations as code, so the resulting macro can act on ranges, properties, and commands. That distinction matters: a macro might reproduce a formatting change without preserving the exact path of your pointer, but it may also refer to a particular cell address in a way that makes it behave differently when run elsewhere.

For example, recording a routine that applies a heading style and sets column widths can be a quick way to create starter code. Recording a process that makes decisions based on changing data, handles unexpected errors, or loops through a variable number of sheets is less likely to produce a robust finished solution without editing.

How to Record a Macro in Excel

In Excel for Microsoft 365 on Windows, use the Developer tab’s Code group to start and stop recording. If Developer is not visible, enable it through File > Options > Customize Ribbon, select Developer in the Main Tabs list, and choose OK. You can also access recording from View > Macros > Record Macro.

Save a copy of the workbook, especially if you are working with important data. A macro’s actions cannot be reversed with the normal Undo command.
Choose Developer > Record Macro. In the dialog, give the macro a name without spaces. An underscore can separate words, as in Format_Report.
Choose where to store it. This Workbook keeps it with the current file; Personal Macro Workbook makes it available in Excel on that computer; New Workbook stores it in a separate workbook.
Optionally assign a shortcut key or add a description. Avoid a shortcut that replaces a shortcut you rely on.
Carry out only the actions you want included. Excel records supported actions from this point onward.
Choose Developer > Stop Recording, or use the stop control shown in Excel’s status bar when available.

On Mac, Excel also has macro recording in the desktop app, though ribbon layout and keyboard shortcuts differ from Windows. Use the Developer tab’s recording controls, enabling the tab in Excel’s ribbon settings if needed. Excel for the web does not create or run VBA macros; open the workbook in desktop Excel for those tasks.

Relative and Absolute References

One of the most important choices is whether a recorded action refers to a fixed address or to a position relative to the active cell. With absolute references, a macro may select a specific range such as A1:B10 each time. This is suitable when the same cells always need attention, but it can be inconvenient when the data moves or grows.

With relative references, the recorded actions are based on the active cell’s position. For example, if you start in C2 and move one cell to the right, the macro can repeat that move from whichever cell is active when it runs. In Windows Excel, toggle Developer > Code > Use Relative References before recording; the control affects how subsequent actions are recorded. Check the active cell before starting, because it determines the starting point for relative actions.

Neither setting is universally better. Use fixed references for a known report layout; use relative references for a repeated operation that should follow the current selection. If the macro must find a changing last row, locate a value, or handle different sheet layouts, recording alone may not be enough. Inspect the generated VBA and consider replacing hard-coded addresses with logic that identifies the right range.

What It Can and Cannot Capture

The recorder is strongest when the task consists of visible, supported Excel commands performed in a predictable order. Depending on the feature and the way it is used, it can capture many common formatting and worksheet operations. But a recorded sequence is a literal recipe, not an intelligent description of your goal. It does not automatically know that you mean “format every new row” or “repeat until the data ends.”

Common limitations include:

Conditional decisions: The recorder does not infer rules such as “if the amount is overdue, highlight it.” You can add VBA conditions afterward or use Excel’s built-in conditional formatting.
Changing data size: A recorded selection may cover today’s rows but miss rows added later, unless the macro is revised to find the appropriate range.
Unsupported or complex interactions: Some features, dialogs, add-ins, and interactions with other applications may not record as expected. Test the particular workflow rather than assuming every command is captured.
Mouse movement: It records supported Excel operations, not a general-purpose replay of pointer movement and screen activity.
Errors and unusual cases: It does not automatically add error handling, validation, or recovery steps for missing sheets and unexpected values.

These limits do not make the tool useless. They explain why a macro that works once on the original sheet might fail on a different workbook. Treat the recording as a first draft, then examine it and test it on representative copies of the data.

Where Macros Are Stored and How to Save Them

A macro stored in the current workbook travels with that workbook, but the file must use a macro-enabled format. For a workbook containing VBA, save as Excel Macro-Enabled Workbook (.xlsm). Saving it as a standard .xlsx file removes the VBA project, and Excel warns before discarding it.

The Personal Macro Workbook is a hidden workbook that Excel can load when it starts. It is useful for personal utilities you want available across workbooks on the same computer. It is not a convenient way to distribute a macro to colleagues: they will not automatically have your Personal Macro Workbook. For a shared task, store the macro in the appropriate workbook or provide it through an organization-approved method.

Be careful when assigning shortcuts and deciding where to save. A shortcut assigned to a macro can conflict with a built-in Excel shortcut, and a macro in one workbook may not be available after that workbook is closed. If a macro should run for a team, document its purpose, dependencies, and required file format.

Reviewing, Editing, and Testing the Recording

To inspect a recorded macro, choose Developer > Code > Macros, select it, and choose Edit. Excel opens the Visual Basic Editor, where the recorded instructions appear as VBA. The code can look lengthy because the recorder may include selections and navigation steps that a human would not consider essential.

For example, a recording may select a cell, select a range, and then apply formatting. VBA can often perform the formatting directly without changing the user’s selection. Removing unnecessary selections can make a macro easier to read and less disruptive, but edits require care: a mistaken change can alter the result or cause an error.

Test with a copy of the workbook and compare the output with what you intended. Useful checks include:

Run it with the expected starting cell active, then try a different starting cell if relative references are involved.
Try a workbook with more or fewer rows than the original recording.
Confirm that it affects only the intended sheet and range.
Check the result after closing and reopening the saved file.
Read the macro’s actions before using it on sensitive or business-critical data.

Macros cannot be undone with Ctrl+Z on Windows or Command+Z on Mac. Those shortcuts undo certain ordinary edits; they do not roll back a macro as a single reversible action. A backup is therefore a practical safeguard, not an optional formality.

Security and Trust

VBA macros can change workbook contents and interact with files or other parts of the computer. That power makes macros useful, but it also means an unexpected macro can be harmful. Do not enable a macro simply because a workbook displays a prompt. Consider who supplied the file, whether you expected the macro, and whether your organization has a policy for reviewing or signing code.

Excel’s macro security settings are available through File > Options > Trust Center > Trust Center Settings > Macro Settings on Windows. Organizations may manage these settings centrally. Avoid lowering security globally just to make one file work. If a trusted work file is blocked, ask the file’s owner or your IT administrator for an approved solution rather than bypassing protections.

When to Use VBA or an Alternative

Use the recorder when you need a quick starting point for repeatable actions and the workflow is mostly fixed. It is especially helpful for learning the VBA statements associated with Excel commands. Use edited VBA when the task needs variables, loops, conditions, error handling, or logic that adapts to changing data.

For a task that is fundamentally a data transformation, built-in features may be simpler and safer. Tables, formulas, sorting and filtering, conditional formatting, and Power Query can often handle repeated work without a macro. For browser-based automation in Microsoft 365, Office Scripts may be an option where the feature is available to your account and organization. Office Scripts are separate from VBA and are not simply another name for the Macro Recorder.

Excel for the web cannot run VBA macros. If a workbook depends on VBA, use the desktop Excel app. If the requirement is to automate a web-based workflow, check whether Office Scripts or an approved workflow tool supports the needed actions; availability and capabilities vary by account and environment.

Frequently Asked Questions

Is the Excel Macro Recorder the same as VBA?
The recorder creates VBA code for supported actions. VBA is the programming language and environment behind those macros; you can edit recorded code or write VBA without recording.

Why did my macro record the wrong cells?
It may have captured fixed cell references, or the active cell may not have been where you expected when recording began. Check whether Use Relative References was enabled and inspect the macro in the Visual Basic Editor.

Can I use a recorded macro in Excel for the web?
No. Excel for the web does not run or create VBA macros. Open the file in desktop Excel, or investigate Office Scripts for a suitable browser-based task.

Can I undo a macro?
No. Ctrl+Z in Windows and Command+Z on Mac do not undo a macro’s actions. Save a separate copy before running a macro that changes data.

Why did Excel remove my macro when I saved?
The workbook may have been saved as .xlsx, a format that does not preserve VBA. Save a workbook containing macros as .xlsm and heed Excel’s warning if it detects that VBA would be removed.

Does recording make a macro safe?
No. Recording only captures actions; it does not certify that a macro is safe or correct. Review the source and test the result, particularly when the workbook came from someone else.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.