Macro Recorder Setup in Excel: A Step-by-Step Guide

To set up the Macro Recorder in Excel for Microsoft 365 on Windows, show the Developer tab, choose Developer > Code > Record Macro, name the macro, choose where to store it, and select OK. Perform the actions you want Excel to repeat, then select Developer > Code > Stop Recording. Save the file as an Excel Macro-Enabled Workbook (.xlsm) if the macro should stay in that workbook.

What the Macro Recorder Does

Excel’s Macro Recorder observes supported actions in the workbook and turns them into Visual Basic for Applications (VBA) instructions. You can then run those instructions again rather than repeating the same sequence by hand. It is useful for routine tasks such as applying a standard format, preparing a report, or entering the same set of commands across similar sheets.

A recording captures actions, not the reason behind them. For example, if you select cell B2 and apply a border, Excel can record that specific selection and formatting. It does not necessarily understand that you meant “find the first cell in the current report and format it.” That distinction matters: a macro that works on one fixed worksheet may need editing before it can safely handle changing data or different workbook layouts.

The recorder is a practical way to create an initial macro without writing VBA from scratch. It does not record every possible Excel feature or action outside Excel, and the resulting code can include extra selections or movements that a programmer might simplify. Treat a recording as a starting point and check what it does before using it on important workbooks.

Show the Developer Tab

The Developer tab contains the recording controls and other macro tools. It may be hidden by default. In Excel for Microsoft 365 on Windows, enable it as follows:

Select File > Options.
Choose Customize Ribbon.
Under Main Tabs, select the Developer check box.
Select OK. The Developer tab should appear on the ribbon.

In Excel for Microsoft 365 on Mac, open Excel > Settings > Ribbon & Toolbar (the wording may appear as Preferences in some releases), select Developer in the Main Tabs list, and save the change. Excel for the web does not provide the desktop VBA Macro Recorder. A workbook containing VBA can be opened in the web app, but VBA macros cannot be created, edited, or run there; use the desktop app for those tasks.

Record a Macro Step by Step

Before recording, open the workbook and move to the sheet and starting cell the macro is meant to use. If the task depends on specific cells, decide whether it should always use those exact locations or work relative to the starting point. That choice affects how you record it.

Select Developer > Code > Record Macro.
Enter a short name beginning with a letter. Use letters, numbers, or underscores, and avoid spaces; for example, FormatReport.
Optionally enter a shortcut key and a description. On Windows, the shortcut field uses Ctrl plus a letter. Avoid replacing a shortcut you rely on, especially common combinations such as Ctrl+C.
Under Store macro in, choose the appropriate location. This Workbook keeps it with the current file. New Workbook stores it in a new workbook. Personal Macro Workbook makes it available in Excel on that computer, rather than embedding it in each work file.
Select OK, then carry out only the actions you want repeated. Avoid unrelated clicks, corrections, and exploratory steps.
When finished, select Developer > Code > Stop Recording. Do not close Excel or the workbook before stopping if you want to keep the recording.

The recording begins as soon as you confirm the dialog. A brief plan helps: list the steps, prepare the workbook, and then record a clean run from the intended starting point. The dialog’s description field is worth using when a macro will be shared or revisited later; note what it changes and any assumptions, such as the expected sheet or data layout.

Choose Absolute or Relative References

The Use Relative References control on the Developer tab determines how Excel records many cell movements. With relative references off, a recorded action generally points to the same worksheet locations each time. This suits a fixed template, such as applying formatting to a heading that is always in A1. With relative references on, movements are recorded in relation to the active cell when recording begins. This can help repeat a sequence on different rows or columns.

To use relative references, select Developer > Code > Use Relative References before starting the recording. The button is a toggle, so check whether it is on or off rather than assuming it resets automatically. For example, if you want to format each row’s cell three columns to the right of the selected starting cell, begin on the intended row and record the movement with relative references enabled. If you instead want every run to format cell D5, leave the control off and record that fixed location.

Neither mode makes a macro automatically understand a changing table. Relative recording can still depend on where you start, and absolute recording can become wrong if someone inserts or rearranges rows. Try the macro from more than one representative starting point when its purpose is to handle varying data.

Plan a Useful First Recording

A good first macro is short, predictable, and easy to verify. For example, suppose a weekly report always needs its heading bolded, its date column formatted, and a total row given a top border. Prepare a copy of the report, record those formatting actions, stop, and run the macro on another copy. Compare the result with the intended layout before using it on the live report.

Keep these practical points in mind while recording:

Start from a consistent sheet and cell, especially if the macro uses relative references.
Use clear worksheet names and a stable layout; avoid recording unnecessary navigation through unrelated sheets.
Prefer selecting a range deliberately over dragging unpredictably with the mouse.
Do not include sample values or temporary changes unless the macro should enter or change those values every time.
Test with a small, disposable copy before running a macro on a large or important workbook.

Typing a formula into a cell is a recordable action, but the recorder may preserve the exact cell or range used. If you want a repeatable process for changing data sizes, the recording may need VBA edits or a different approach. The key question is whether the same sequence of actions remains valid whenever you run it.

Store, Save, and Run the Macro

Where you store the macro determines where it is available. A macro saved in This Workbook travels with that workbook, provided the file is saved in a macro-enabled format. Choose Excel Macro-Enabled Workbook (*.xlsm) in the Save As file type list. Saving as a standard .xlsx workbook does not preserve VBA macros; Excel warns that macro content will be removed if you continue in that format.

The Personal Macro Workbook is useful for personal tools you want available whenever Excel starts on that computer. It is a hidden workbook that Excel loads for the user. It is not a way to share a macro with colleagues: they will not automatically receive your personal workbook. For a shared process, save the macro in a shared macro-enabled workbook or distribute it through an approved organizational method.

To run a saved macro in desktop Excel, choose Developer > Code > Macros, select its name, and choose Run. If you assigned a shortcut during recording, you can use that shortcut instead. On a Mac, shortcut behavior and available key combinations can differ from Windows, and some combinations may be reserved by macOS or Excel. A button or shape assigned to the macro can be easier for people using different computers.

Edit and Reuse a Recording

Excel stores recorded macros as VBA procedures. In the desktop app, select Developer > Code > Macros, select the macro, and choose Edit to open its code in the Visual Basic Editor. The recorded code can reveal why a macro behaves as it does: look for worksheet names, cell addresses, selections, and formatting commands. If the recorded sequence is more complicated than expected, test it in a copy rather than editing the only version of an important file.

You can also make a macro easier to run by adding a control. On Windows, insert a shape from Insert > Shapes, place it on the sheet, right-click it, and choose Assign Macro. Select the macro and confirm. A labeled shape such as “Format report” can be more discoverable than a keyboard shortcut. Excel also offers form controls through the Developer tab, but a simple shape is often enough for a basic workbook tool.

Recorded macros are usually tied to the workbook structure and actions used during recording. If users rename a sheet, insert rows, or change a report layout, test whether the macro still targets the right places. For macros intended for broad reuse, review the code and consider whether a more deliberate VBA procedure or an Excel feature designed for the task would be more reliable.

Common Setup Problems

Developer is missing: Enable it through Excel Options on Windows or Ribbon & Toolbar settings on Mac. Restarting is usually not necessary.
The macro is missing after reopening: Check whether the workbook was saved as .xlsm. A macro stored in the Personal Macro Workbook will not appear as a procedure inside the current workbook.
The macro runs on the wrong cells: Check the starting cell and whether Use Relative References was on during recording. Review the recorded code for fixed cell addresses or sheet names.
The shortcut does not work: Confirm the macro is available in the open workbook or Personal Macro Workbook, and check for a conflicting shortcut. Try running it from the Macros dialog to separate a shortcut issue from a macro issue.
Excel blocks the macro: Files from the internet or untrusted locations may be blocked by security settings. Do not enable content just to make an unknown file work; verify its source and follow your organization’s policy.

Another frequent issue is recording too much. If the macro unexpectedly scrolls, selects a different sheet, or changes an unintended cell, repeat the recording with a clean setup and fewer actions. Also remember that Excel’s Undo history does not reverse a macro run. If a macro changes or deletes data, restore from a backup or use a copy to test it rather than relying on Ctrl+Z.

Macro Security and Safe Testing

Macros can change workbook contents and may run code beyond simple formatting, so treat them as executable code. Run macros only in files from sources you trust. On Windows, macro controls are available under File > Options > Trust Center > Trust Center Settings > Macro Settings. The safest choice depends on your organization’s rules; avoid enabling all macros globally just to run one recording. Excel may also block macros from files marked as coming from the internet.

Before testing a recording, save a separate copy of the workbook. A macro can overwrite cells, delete sheets, or make many changes at once, and macro actions cannot be undone with Ctrl+Z. Check that the macro name and storage location are correct, run it on sample data, and inspect the result. If sharing a workbook, tell recipients what the macro does and what they should expect when they choose to enable it.

When to Use Another Tool

The recorder is a strong fit when the task is a repeated sequence of ordinary Excel actions and the layout is stable. It is less suitable when the job requires decisions, loops through unknown numbers of rows, handles exceptions, or needs to adapt to changing input. Those tasks may call for reviewed VBA code, particularly if the procedure must be robust across varying workbooks.

For repeatable data-import and transformation steps, Power Query may be a better fit because it records a sequence of data-preparation steps that can be refreshed. For simple formatting or analysis, built-in features such as cell styles, tables, and conditional formatting may be easier to maintain than a macro. If the goal is to automate work across Microsoft 365 apps, Power Automate may apply, depending on the task and available licensing. Choose based on the work to be repeated, not simply on which tool can record clicks.

Frequently Asked Questions

Can I record a macro without the Developer tab?
Yes, if the Record Macro command is available through Excel’s interface, but enabling the Developer tab is the clearest way to access the recorder and other macro controls. On Windows, turn it on through File > Options > Customize Ribbon.

Does the Macro Recorder work in Excel for the web?
No. Excel for the web does not record, edit, or run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use VBA macros.

Why did my macro disappear when I saved the workbook?
The workbook may have been saved as .xlsx, which cannot store VBA macros. Save a macro-containing workbook as .xlsm, or check whether you stored the macro in the Personal Macro Workbook instead.

Can I undo a macro with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal Undo history, so Ctrl+Z cannot reverse its changes. Save a backup copy before running a macro that edits or deletes data.

Can I share a recorded macro with someone else?
Yes. Save it in an .xlsm workbook and share that file through a trusted channel. The recipient needs desktop Excel with macros permitted by their security settings; a macro in your Personal Macro Workbook is not automatically shared.

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.