Excel Macro Recorder: How It Works and When to Use It

Excel’s Macro Recorder captures many worksheet actions and turns them into VBA code that you can run again to repeat those actions. In Microsoft Excel for Microsoft 365 on Windows, open the Developer tab and select Record Macro; when you finish, select Stop Recording.

What the Excel Macro Recorder does

A macro is a stored set of instructions for Excel. The recorder creates those instructions while you work: for example, you might select a range, apply a number format, add a heading style, and set column widths. Excel translates supported actions into Visual Basic for Applications (VBA), its built-in programming language for desktop Office apps.

The recorder is useful because you do not have to write VBA from scratch to automate a predictable sequence. You can also inspect and edit the generated code later, so recording can be a practical way to learn what VBA commands correspond to familiar Excel actions.

It is not a general-purpose observer of everything you do. It records supported commands and actions in Excel, not your intent, mouse movements, or decision-making. A recording usually repeats the same addresses and sequence of steps. It will not automatically understand that next month’s data has more rows, decide which records meet a condition, or recover gracefully if a sheet has changed.

How to record a macro in Excel for Windows

If Developer is not visible on the ribbon, enable it first: select File > Options > Customize Ribbon, check Developer under Main Tabs, and select OK. Then use this workflow:

Open the workbook and, if the task depends on a starting cell, select that cell before recording.
Select Developer > Record Macro.
Enter a descriptive name. Macro names cannot contain spaces; use a name such as FormatMonthlyReport. Optionally add a shortcut and description.
Choose where to store the macro: This Workbook, New Workbook, or Personal Macro Workbook.
Select OK, perform the Excel actions you want repeated, then select Developer > Stop Recording.
Save the workbook in a macro-capable format if the macro is stored in that workbook.

Choose a shortcut cautiously: assigning one can replace a commonly used Excel shortcut while that macro workbook is open. A description helps later if you accumulate several macros. Keep the recorded actions focused; unrelated selections and clicks can become unnecessary code and make playback less reliable.

Absolute and relative references

One important setting is Developer > Use Relative References. When relative references are off, recorded actions commonly refer to specific worksheet locations. If you record formatting for A2, for instance, the macro may target A2 again when run. That is suitable when the task always belongs in a fixed location.

When relative references are on, Excel records actions in relation to the active cell. If you start in A2 and move one cell down, the recorded movement is relative; replaying from A10 moves to A11. This is useful when applying the same operation to different starting rows. Set the option before recording, and test the macro from a deliberate starting cell. Relative recording does not, by itself, make a macro smart enough to find the end of a changing data set.

A practical formatting example

Suppose a weekly report always has column headings in A1:D1. A recorder-based macro could bold those headings, apply a fill color, and autofit columns A through D. If the report layout never changes, a fixed-range recording may be perfectly adequate. If columns are inserted or the heading row moves, the recorded targets may no longer be right.

After recording, you can inspect the code in the Visual Basic Editor. The following short example illustrates the same kind of formatting operation in a more readable form; it is an example, not a claim that the recorder will produce precisely this code. It assumes the active worksheet has headings in A1:D1.

Save a copy of the workbook before running a macro: VBA actions cannot be reversed with Ctrl+Z, so a mistaken run can require closing without saving or restoring a backup.

Sub FormatReportHeadings()
    ' Format the four heading cells on the active sheet.
    Dim ws As Worksheet
    Set ws = ActiveSheet

    With ws.Range(A1:D1)
        .Font.Bold = True
        .Interior.Color = RGB(217, 225, 242)
    End With

    ' Fit the report columns to their contents.
    ws.Columns(A:D).AutoFit
End Sub

In actual VBA, worksheet addresses and column ranges need quotation marks inside the code: use ws.Range("A1:D1") and ws.Columns("A:D"). The code block above is intended to show the structure, but those quotation marks are required for executable VBA. Use the corrected lines when running it.

What the recorder captures—and what it does not

The recorder is best for repetitive, rule-based operations whose steps are already clear. It can capture many common tasks, including formatting cells, entering formulas, sorting or filtering data, and making some chart or worksheet changes. The exact code depends on the commands and selections involved.

Recorded code often relies on the active workbook, active sheet, selected cell, or a fixed range. That dependence explains why a macro can work once and then behave unexpectedly in another workbook. It also explains why recorded code may contain repeated Select and Activate commands. A person editing VBA can often simplify these, but a beginner should first understand what the macro targets rather than deleting lines indiscriminately.

Recording alone is a poor fit when the job needs conditions, loops over a variable number of rows, error handling, prompts, or decisions based on cell values. For example, a recording might apply a highlight to rows 2 through 50, but it will not automatically keep finding new rows added below row 50. VBA can add that logic, but it requires editing or writing code.

Run, inspect, and edit a recorded macro

To run a macro in the desktop app, select Developer > Macros, choose its name, and select Run. On Windows, Alt+F8 opens the Macro dialog. To inspect or edit code, select Developer > Visual Basic; Alt+F11 opens the Visual Basic Editor on Windows. Review the workbook and active-sheet references before running a macro on different data. A macro that clears, overwrites, or reformats cells can affect more than intended if its target range is wrong.

Testing on a copy is a sound habit, especially when a macro changes many cells. Start with a small, representative workbook, check the result, and confirm that the macro still works if the active cell or sheet differs from your original recording setup. Ctrl+Z does not undo VBA macro actions.

Saving and macro security

An ordinary .xlsx workbook cannot store VBA macros. If the macro belongs with a particular workbook, save it as an Excel Macro-Enabled Workbook (.xlsm). Excel Binary Workbook (.xlsb) can also store macros. Saving a macro-enabled file as .xlsx removes its VBA project, so keep a macro-capable copy if you need the code.

Personal Macro Workbook stores macros in a hidden workbook that opens with Excel on that computer, making them available across workbooks in that Excel installation. It is convenient for personal routines, but the macros are not automatically packaged with a workbook you send to someone else. Choose New Workbook when you want the recorded macro kept with a separate workbook instead.

Macros can make extensive changes, and VBA can run code beyond simple formatting. Only enable macros from sources you trust. On Windows, macro controls are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid enabling all macros just to run one file; follow your organization’s security policy and use the source and file details to assess whether a workbook is trustworthy.

Mac and Excel for the web differences

Excel for Mac supports VBA macros and macro recording, though ribbon and settings labels can vary slightly by release. If Developer is hidden, enable it through Excel > Settings (called Preferences in some releases) > Ribbon & Toolbar, then select Developer under the main tabs. Use the Developer tab to record and run macros; do not assume Windows keyboard shortcuts work identically on a Mac.

Excel for the web does not create, run, or edit VBA macros. It can open macro-enabled workbooks for supported spreadsheet work, but use the desktop Excel app to work with the VBA project. Office Scripts are a separate automation option in supported web and Microsoft 365 environments; they use TypeScript rather than VBA and are not simply another name for the Macro Recorder.

When to choose another automation method

Use the recorder when the task is a repeatable sequence of supported Excel actions and the layout is stable. Edit the generated VBA when you need to make that sequence more robust—for instance, to identify the last used row or handle different sheets. If you need a one-off transformation, built-in features such as filters, tables, and Power Query may be easier to maintain than a macro. For browser-based automation, consider Office Scripts where available, while accounting for its different language and capabilities.

A useful decision test is whether the task can be described as the same steps every time. If yes, record a small macro and test it. If the steps depend on changing data or exceptions, plan to add logic or choose a tool designed for that work. Recording is a starting point, not a guarantee that a macro will adapt to every workbook.

Frequently Asked Questions

Is the Excel Macro Recorder the same as VBA?
The recorder is a feature that generates VBA from supported actions. VBA is the programming language and environment where you can inspect, edit, and write macros. A recorded macro is VBA code, but it may need adjustment to handle changing layouts or data.

Why is Record Macro missing or unavailable?
On Windows, the Developer tab may be hidden; enable it through File > Options > Customize Ribbon. Excel for the web does not support recording VBA macros, so open the file in desktop Excel. In a managed workplace, security or administrative settings may also restrict macro features.

Can a recorded macro automatically handle new rows?
Not reliably just because it was recorded. A recording that targets a fixed range will generally keep targeting that range. To adapt to changing row counts, edit the VBA to identify the data range or use a different tool suited to the task.

Will saving as .xlsx keep my macro?
No. The .xlsx format does not store VBA macros; saving in that format can remove the workbook’s VBA project. Use .xlsm or .xlsb when the workbook needs to retain its macro.

Can I undo a macro with Ctrl+Z?
No. Excel clears the normal undo history when a VBA macro runs, so Ctrl+Z cannot reverse its changes. Save a separate copy before testing a macro that edits or deletes data.

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.