Microsoft Excel Macro Recorder: Record and Run Macros

Microsoft Excel’s macro recorder captures a sequence of actions in Excel and converts them into VBA code that you can run again. In Excel for Microsoft 365 on Windows, start it from Developer > Record Macro, perform the actions, and choose Developer > Stop Recording when finished.

The recorder is useful for automating repetitive, rule-based tasks without writing every line of a macro yourself. Its output is a starting point rather than a guarantee of a robust program: recorded macros can depend on the active sheet, selected cells, and the exact layout of a workbook.

What the Excel macro recorder does

A macro is a saved set of instructions for Excel. The recorder observes many actions you perform through Excel’s interface—such as formatting cells, entering values, or applying a sort—and writes corresponding Visual Basic for Applications (VBA) instructions. Later, running the macro repeats those instructions.

For example, if you regularly bold a report’s header, fill it with a color, and adjust its column widths, you can record those steps once. The resulting macro can repeat them rather than requiring the same clicks every time. The recorder does not understand the purpose of your work, however. It records actions, not intent: it cannot infer that a particular row is a header unless the recorded actions or code specify that.

Enable the Developer tab first

The Developer tab contains the recording controls and is commonly hidden by default. In Excel for Microsoft 365 on Windows, enable it with these steps:

Choose File > Options.
Select Customize Ribbon.
In the list of Main Tabs, check Developer, then select OK.

On Excel for Mac, open Excel > Preferences (called Settings in some newer builds), choose Ribbon & Toolbar, and enable Developer. Excel for the web does not provide the desktop VBA macro recorder; use the Windows or Mac desktop application to create or edit VBA macros.

Record a macro step by step

Before recording, decide what the macro should do and which workbook or worksheet it should affect. If practical, try the task on a copy of the data first. Then use the recording dialog:

Open Developer > Record Macro.
Enter a descriptive name. A macro name must begin with a letter and cannot contain spaces; underscores can separate words.
Optionally assign a shortcut key, choose where to store the macro, and add a description.
Select OK, then carry out the actions you want Excel to record.
When done, choose Developer > Stop Recording.

Keep the recording focused. Unnecessary clicks, selections, or corrections are also recorded and can make the macro harder to reuse. A name such as FormatMonthlyReport is more informative than Macro1, especially when a workbook contains several macros.

Absolute and relative references

One important choice is whether recorded cell references should be absolute or relative. With absolute references, a recorded instruction may always target the same address, such as A1. With relative references, actions are described in relation to the cell that was active when recording began. That can let a macro work on a different starting row or column.

Before recording, use Developer > Use Relative References to turn relative recording on or off. The setting matters: if you record formatting with relative references off, the macro may keep formatting the original cells even when you select another range before running it. If relative references are on, the same actions can shift with the starting selection. Test the macro from the position and on the data where you expect to use it.

Choose where the macro is stored

The Record Macro dialog’s Store macro in option determines where the saved instructions are available:

This Workbook: Stores the macro with the current file. This is usually the best choice when the automation belongs to one report or workbook.
Personal Macro Workbook: Stores it in Excel’s personal macro workbook, which is intended for macros you want available in your Excel installation across workbooks. It is normally hidden while Excel is running.
New Workbook: Creates the macro in a separate workbook.

For a workbook containing VBA, save in a macro-enabled format such as Excel Macro-Enabled Workbook (.xlsm). The standard .xlsx format does not store VBA macros; saving a macro-enabled file as .xlsx can remove its macros. If you need a macro to travel with a report, store it in that report’s workbook rather than relying on a Personal Macro Workbook that recipients do not have.

What a recorded macro can and cannot do

The recorder is effective when a task consists of repeatable Excel commands and predictable cell operations. It can capture many common formatting, editing, and data-management actions. Its generated VBA can also be opened in the Visual Basic Editor for review or improvement.

It is less suitable when the task depends on decisions or changing conditions. For instance, recording a sequence that selects rows 2 through 20 will not automatically know to include new records added next month. A human-written macro can be adapted to find the last used row, check values, or handle exceptions, but the recorder does not automatically add that logic just because the task appears repetitive.

Some interface actions may not be recorded as a useful or complete sequence, and actions outside Excel are not captured as Excel instructions. A recorded macro may also rely on a particular workbook structure, active sheet, selection, or dialog state. Inspect and test the result rather than assuming that every click translates into a dependable, portable procedure.

A simple formatting example

Suppose a report always has its headings in A1:D1. After recording the bold and fill-color actions, Excel may generate code with selections and commands. A simplified version of the same formatting operation could look like this:

Save a copy of the workbook before running or changing a macro. VBA macros cannot be undone with Ctrl+Z, so a mistaken change may require restoring the saved copy.

Sub FormatReportHeader()
    ' Format the first four cells on the active worksheet.
    Dim ws As Worksheet
    Set ws = ActiveSheet

    With ws.Range("A1:D1")
        .Font.Bold = True
        .Interior.Color = RGB(0, 112, 192)
    End With
End Sub

This example illustrates why editing recorded code can help: it works directly with a range instead of relying on a series of selections. It still assumes the intended header is in A1:D1 on the active worksheet. If the layout changes, the range or logic must change too.

Run, inspect, or assign a macro

To run a saved macro in the desktop app, choose Developer > Macros, select its name, and choose Run. You can also use a shortcut assigned in the Record Macro dialog. Take care when assigning shortcuts: a shortcut can conflict with an existing Excel shortcut, so a macro shortcut should be chosen deliberately.

To inspect or modify the VBA, open Developer > Visual Basic. The Visual Basic Editor shows the modules where recorded procedures are stored. If you want a clickable control in a worksheet, use Developer > Insert and choose a Button under Form Controls, then assign the macro when prompted. A button is convenient for workbook users, but it does not make the macro safe to run or remove the need to distribute a macro-enabled file.

Common recording problems

The macro changes the wrong cells: Check whether Use Relative References was set as intended, and confirm the active cell and worksheet before running it.
The macro works only in the original workbook: It may contain fixed sheet names, cell addresses, or selections. Review the procedure and test it on a copy of the other workbook.
The macro is missing after saving: Confirm that the file was saved as .xlsm or another VBA-capable format, not .xlsx. Also check whether the macro was stored in This Workbook or Personal Macro Workbook.
The macro does not appear or run: In the desktop app, check Developer > Macros and whether Excel’s security settings or an organization’s policy has blocked it.
The macro repeats unnecessary steps: Record again with a shorter, deliberate sequence, or edit the generated VBA to remove redundant selections.

Security and compatibility

A macro can change or delete workbook content and can run code, so treat it like a small program. Run macros only when you trust their source and understand why they are needed. Do not enable macros simply because a file asks you to; unexpected macro prompts in files from unknown senders are a reason to be cautious. Organizations may apply additional macro policies.

Excel’s Trust Center settings on Windows are available through File > Options > Trust Center > Trust Center Settings > Macro Settings. Security choices may be controlled by an administrator, and changing settings to allow all macros is not a safe general fix. Excel for the web can open and preserve workbooks that contain VBA, but it cannot run, create, or edit those VBA macros; use desktop Excel for those tasks.

When to use another tool

Use the macro recorder when you want to automate a repeatable sequence of desktop Excel actions and are willing to test the result. If the task requires branching logic, flexible ranges, error handling, or dependable use across changing workbook layouts, consider learning VBA or asking someone to write and review the code.

For supported Microsoft 365 workflows, Office Scripts are another automation option, particularly for Excel on the web and integrations with Power Automate. They use TypeScript rather than VBA and have different capabilities and availability. Power Query is often a better fit for repeatedly importing, cleaning, and reshaping data. These tools solve different problems: choose based on the task, the Excel environment, and whether the automation must run in desktop Excel or online.

Frequently Asked Questions

Is the Excel macro recorder the same as VBA?
The recorder creates VBA code from actions in Excel. VBA is the programming language and environment behind those recorded macros, and you can edit the generated code in the Visual Basic Editor.

Can I record a macro in Excel for the web?
No. Excel for the web does not create, edit, or run VBA macros. Open the workbook in the Excel desktop application to use the VBA recorder.

Why does a recorded macro use Select so often?
The recorder commonly reflects actions performed through the interface, including selecting cells before changing them. You can often simplify the resulting VBA to refer directly to a worksheet or range, but check the assumptions before editing.

Will a macro work in every workbook?
Not necessarily. It may depend on sheet names, cell addresses, selected cells, or a specific layout. Test it on a copy of each intended workbook and adjust the code if the structure differs.

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 running a macro that changes data or formatting.

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.