Auto Macro Recorder in Excel: How to Record and Use It

Excel’s macro recorder captures supported actions as VBA code, letting you replay a routine instead of repeating it by hand. In Microsoft Excel for Microsoft 365 on Windows, start it from Developer > Code > Record Macro, perform the task, and choose Developer > Code > Stop Recording when you are done.

What an auto macro recorder means

Auto macro recorder is a general phrase for a tool that records actions and turns them into a repeatable automation. Excel’s built-in feature is called Record Macro. It is not a background recorder that watches everything you do: recording begins when you start it and ends when you stop it. The resulting macro can be run again when you choose.

While recording, Excel translates many interactions into Visual Basic for Applications (VBA) instructions. For example, selecting a range, applying a number format, and adding a border may become VBA statements that select cells and change their formatting. You do not need to write that code to make a simple recording, but the code is available to inspect and edit later.

A macro is not a video or a literal replay of mouse movements. Excel records actions it can express as VBA, and the generated code often refers to workbook objects such as worksheets, ranges, and cells. That makes it useful for routine workbook operations, but it also means a recorded macro may depend on the workbook’s structure and the active selection.

How recording works

When you begin a recording, Excel opens a dialog where you can name the macro, assign an optional shortcut, choose where to store it, and add a description. As you work, Excel builds VBA statements corresponding to supported actions. Stopping the recording closes the capture process; it does not automatically run the macro or schedule it to run later.

By default, the recorder commonly captures references to the cells you used. If you selected B2 and formatted it, for example, the resulting code may target B2 again each time it runs. Excel’s Use Relative References option changes how recorded cell references behave. With relative recording enabled, actions can apply in relation to the current active cell, which is helpful for repeating the same operation across different rows or columns.

Relative references do not make every macro universally reusable. A recording can still rely on a particular sheet name, selection, or workbook layout. A macro that formats the current cell may be more flexible than one that explicitly targets a fixed range, but it still needs a sensible starting point and the expected data.

Record a macro in Excel for Windows

These steps apply to Excel for Microsoft 365 on Windows. Save a copy of important work before recording or running automation, especially if the macro will change or remove data.

If the Developer tab is not visible, choose File > Options > Customize Ribbon, select the Developer check box in the Main Tabs list, and choose OK.
Open the workbook and, if you want actions to work relative to the active cell, select Developer > Code > Use Relative References before recording.
Choose Developer > Code > Record Macro. Enter a clear name that starts with a letter and contains no spaces. Optionally set a shortcut and description.
Choose where to store the macro. This Workbook keeps it with the open workbook; Personal Macro Workbook makes it available in Excel on that computer; New Workbook puts it in a separate workbook.
Choose OK, then perform only the actions you want to automate. Avoid unrelated clicks or edits, because they may also be recorded.
Choose Developer > Code > Stop Recording. You can then run the macro or inspect its generated code.

You can also start and stop a recording from View > Macros. To inspect the code in Windows, choose Developer > Code > Macros, select the macro, and choose Edit; this opens the Visual Basic Editor. The Alt+F11 shortcut opens the editor on many Windows keyboards, but keyboard settings can affect shortcuts.

Choose the right recording settings

The settings you choose determine where the macro lives and how easy it is to reuse. Consider the task before selecting a storage location:

This Workbook: Use this when the macro is intended for the current file and should travel with it. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) to retain VBA macros.
Personal Macro Workbook: Use this for routines you want available across workbooks on the same computer. Excel loads this hidden workbook when it starts. It is not automatically shared with colleagues or made available on other devices.
New Workbook: Use this if you want the recorded macro stored separately from the workbook you are working in. Keep track of both files so you know where the macro is saved.
Shortcut: A keyboard shortcut can make a frequently used macro quicker to run, but it may override a built-in Excel shortcut while the workbook is open. Pick a combination carefully and document it.

Give the macro a descriptive name such as FormatMonthlyReport rather than Macro1. A useful description can explain the expected starting sheet, input range, or result. These details matter when you return to the file later or share it with someone who did not record it.

Practical examples and the recorder’s limits

A recorder is a good fit when a task consists of a predictable sequence of Excel operations. You might record formatting a recurring report, applying a consistent number format, setting print layout, or copying a prepared block into a known location. If a monthly report always has the same structure, a macro can save time on repetitive setup.

Suppose you routinely format a report heading, bold the first row, set column widths, and apply a date format. Recording those steps can produce a convenient one-click routine. If the next report has extra columns or a different sheet name, however, the recorded code may not adapt on its own. Test it on a copy and check the output before relying on it for a live report.

The recorder is less suitable when the job depends on decisions or changing data structures. It does not automatically infer that you want to find every new row, skip blank entries, handle errors, or choose different actions based on a cell’s value. A recorded sequence may also reproduce selections and scrolling that are unnecessary. Editing the VBA or using another automation approach may be needed for conditional logic, loops, robust error handling, or a reusable process that must adapt to changing ranges.

Not every Excel feature or action is recorded as a useful VBA instruction. Some actions may be unsupported, may produce code that assumes a particular interface state, or may require extra code to work reliably. A recording is a starting point, not proof that the result will work correctly in every workbook.

Run, inspect, and save the macro

To run a stored macro in Windows, choose Developer > Code > Macros, select its name, and choose Run. The View > Macros route also opens the macro list. If you assigned a keyboard shortcut, you can use it while the macro is available, taking care not to trigger an unintended command.

Macros generally cannot be undone with Ctrl+Z. A recorded action that changes cell values, clears a range, or alters formatting may leave no simple undo path. Save a separate copy before testing, and verify results on sample data. If the macro is part of an important process, keep an unchanged source file and a documented backup procedure.

To retain VBA, save the workbook in a macro-enabled format such as .xlsm. Saving a macro-containing workbook as a standard .xlsx file removes its VBA project; Excel warns about this when applicable. If a macro belongs in the Personal Macro Workbook, that workbook must also be saved when Excel prompts you at shutdown.

On Mac, Excel also supports VBA macros and macro recording, although ribbon arrangement, keyboard shortcuts, and some options can differ from Windows. In Excel for Mac, use Excel > Settings > Ribbon & Toolbar to make the Developer tab available when needed; wording can vary by release. Excel for the web does not create, run, or edit VBA macros. A workbook can be opened there, but VBA automation requires the desktop Excel app.

Security and common problems

Macros can make significant workbook changes, so treat macro-enabled files as executable content. Do not enable macros in a file from an unknown or untrusted source just because Excel displays a warning. Follow your organization’s security policy, and ask the sender or administrator if a workbook’s origin or purpose is unclear. A trusted location should be restricted to files you genuinely trust, not used as a shortcut to dismiss warnings.

If a macro is missing after you reopen a workbook, check whether it was saved in the correct file and whether that file is .xlsm rather than .xlsx. If Excel blocks it, review the security notification and your organization’s policy instead of trying to bypass the protection. If a macro runs on the wrong cells, check whether it was recorded with absolute or relative references and whether the active sheet and starting cell match its assumptions.

Another common surprise is that extra actions were captured. Stop recording promptly, then inspect and test the result. If the macro fails after a worksheet is renamed or a range moves, its code may refer to the old name or fixed address. Editing the macro to use a suitable range or worksheet reference can solve that problem, but changes should be tested on a copy.

Recorder versus other automation options

Use the recorder when you want to automate a short, repeatable set of actions and the workbook layout is stable. Use manual VBA when the process needs logic, error handling, loops, or more control over ranges and worksheets. VBA can run in desktop Excel, but it carries security considerations and generally needs an .xlsm or another macro-capable format.

For browser-based workflows, Office Scripts are a separate Microsoft automation technology in supported Microsoft 365 environments. They are not VBA macros, and availability depends on the account, platform, and organization settings. Power Automate can connect supported services and workflows, but it is not simply a replacement for recording every desktop click. Choose based on where the process runs, what it must connect to, and whether it needs to work in desktop or web Excel.

Frequently Asked Questions

Is there an auto macro recorder in Excel?
Yes. Excel calls its built-in feature Record Macro. It captures supported actions as VBA, but it does not continuously monitor activity or run tasks automatically on a schedule.

Can I record a macro without knowing VBA?
Yes. You can record and run many simple routines without writing code. Learning to inspect the generated VBA can help you troubleshoot or adapt a recording, but it is not required for every basic macro.

Can I use a recorded macro in Excel Online?
Excel for the web does not run or record VBA macros. Open the workbook in desktop Excel to use VBA automation. Office Scripts may be an alternative in supported environments, but they are a different feature.

Why does my recorded macro keep changing the same cells?
It may have been recorded using absolute references, which can target the same addresses each time. Turn on Use Relative References before recording if the actions should be relative to the active cell, then test the result with a copy of the workbook.

Will saving as .xlsx keep my macro?
No. The .xlsx format does not retain VBA macros. Save a workbook containing its macro as .xlsm, and confirm Excel’s warning if you are changing formats so you do not accidentally discard the code.

Can I undo a macro with Ctrl+Z?
Usually not. Macros clear Excel’s normal undo history, so save a backup before running one that changes data or formatting. Test unfamiliar macros on a copy first.

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.