To record a macro in Excel for Microsoft 365 on Windows, open the Developer tab, choose Record Macro, perform the actions you want to repeat, and select Stop Recording. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), then run the macro from Developer > Macros. A recorded macro saves a sequence of actions as VBA so you can replay routine work without repeating every click.
What You’ll Need
- Excel desktop for Windows or Mac. Excel for the web can open macro-enabled workbooks, but it cannot record, run, or edit VBA macros.
- A workbook with sample data or a copy of the workbook you intend to use. Practise on a copy first, especially if the macro will change or delete data.
- Permission to use macros on your computer. If Excel displays a security warning, run macros only in files from a source you trust.
- The Developer tab visible in the ribbon. If it is missing in Windows, enable it using File > Options > Customize Ribbon, select Developer under Main Tabs, and choose OK. On Mac, open Excel’s ribbon and toolbar settings and turn on the Developer tab; the exact settings layout can vary by Mac Excel release.
Step-by-Step
- Plan the task and prepare the workbook. Decide what repeatable sequence the macro should perform, such as applying a heading style, setting column widths, adding a total row, or formatting a report. Start with a duplicate workbook or a copy of the sheet, and make sure you know which cells the macro should affect. A recording captures actions, not your intention: an accidental click, an unwanted selection, or an incorrect value may be captured too. If the task depends on a particular range, select that range before you begin or make sure your planned actions select it explicitly.
- Choose whether the macro should use fixed or relative cell references. On the Developer tab, look for Use Relative References. By default, Excel usually records many cell selections as fixed locations: replaying the macro can return to the same cells regardless of where you start. If you want the same actions to happen relative to the active cell, turn on Use Relative References before recording. For example, a macro that formats the cell one column to the right of your starting cell should generally use relative references. The toggle affects how selections and movements are recorded; it does not make every recorded action automatically adapt to different table sizes.
- Start the recording. Select Developer > Record Macro. In the dialog box, enter a short name beginning with a letter. Macro names cannot contain spaces; use an underscore or joined words, such as
Format_Monthly_Report. You can add a description to explain the task. The shortcut field is optional. If you assign a shortcut, take care not to replace a shortcut you use regularly. Under Store macro in, choose This Workbook when the macro should travel with this file. Choose another location only if you understand where the macro will be saved and who will need it. Select OK to begin recording. - Perform only the actions the macro needs to repeat. Work through the task carefully and in the intended order. For a simple report-formatting macro, you might select the report title, apply bold formatting, set a fill colour, adjust the column widths, and apply a number format to a known range. Use ribbon commands and ordinary cell selections as needed. Avoid typing temporary notes, opening unrelated dialogs, or clicking around to inspect the sheet; those actions may be recorded and replayed. If you make a mistake during recording, stop and begin again rather than assuming Excel will omit the mistaken action.
- Stop recording as soon as the sequence is complete. Select Developer > Stop Recording. Depending on your Excel window and layout, a Stop Recording control may also appear in the status bar. Confirm the recording has stopped before doing other work. If Use Relative References was turned on for this recording, you can turn it off afterward if you want later recordings to use the default behaviour. The relative-reference setting is a toggle, so check its state before the next recording.
- Save the workbook in a macro-compatible format. Choose File > Save As, select a location, and set the file type to Excel Macro-Enabled Workbook (*.xlsm). Confirm the format warning if Excel asks whether to keep the VBA project. If you save as a standard
.xlsxfile, Excel cannot store the VBA macro in that format, and the macro will be removed if you choose to discard it. Keep a separate backup if the workbook contains important information. - Run the macro on a safe test case. Select Developer > Macros, click the macro name, and choose Run. On Windows, you can also open the Macro dialog with Alt+F8; in Excel for Mac, Option+F8 opens the macro list in supported configurations. If you assigned a shortcut, use it only after confirming what the shortcut does. Check the results cell by cell and compare them with the expected outcome. A macro’s actions cannot be reversed with Ctrl+Z, so use a disposable copy or save a backup before running it on valuable data.
- Check what the recording actually saved. If the result is wrong, do not keep running the macro on the original data. Restore the test copy, record the sequence again more carefully, or inspect the generated VBA using Developer > Visual Basic. You do not have to edit code to use a basic recording, but looking at the code can help reveal an unexpected fixed cell reference or an extra recorded action. If the task needs decisions, loops, or a range that changes size, a simple recording may need VBA editing or another approach.
Common Mistakes
Saving in the wrong file type: A macro saved in an .xlsx workbook will not be retained as VBA. Choose .xlsm when the macro belongs in that workbook, and verify the file name and type after saving.
Forgetting where the macro is stored: If the recording is stored in This Workbook, it is available in that workbook. If you store it in a different workbook or the Personal Macro Workbook, it may not appear when you open another file or send the workbook to someone else. Pick the storage location based on who needs to use the macro.
Recording with the wrong reference mode: A macro recorded with fixed references may keep formatting the original range when you expected it to work from the current cell. Conversely, relative references can make a macro act in an unintended location if you start from the wrong active cell. Test it from the same starting position and with the same kind of data you expect to use.
Capturing extra clicks or a mistaken selection: Excel replays recorded actions in sequence. An accidental selection or command can affect the wrong cells during later runs. Keep the recording focused, stop promptly, and test it on a copy.
Expecting a recording to understand changing data: A recorded series of actions usually repeats what you did; it does not automatically identify new rows, make choices based on cell values, or handle every layout. A report that grows each week may need a table-based approach or edited VBA rather than a fixed-range recording.
Tips
- Use a clear, descriptive macro name and write a short description in the Record Macro dialog. Names such as
Format_Invoiceare easier to identify thanMacro1. - Before recording, write down the intended start cell, target range, and final result. This reduces exploratory clicks that could become part of the macro.
- Prefer actions that are consistent between runs. If the task is meant to work on different rows, test with more than one starting row and check whether relative references behave as expected.
- Keep a clean original sheet or backup file. Recording over a formatted or altered sheet can make it difficult to tell whether the macro caused a problem.
- Use the optional description to note assumptions, such as the required active cell or the columns the macro formats. This matters when another person will run it later.
- Be cautious with macros received by email or downloaded from the internet. Do not enable content merely to view a workbook; only run VBA from a source you trust and when the task is understood.
- If you need a macro to run in another workbook, check the storage choice and test on the other workbook. A recording that refers to a specific sheet name or fixed cell locations may not transfer cleanly.
Troubleshooting
The Developer tab is missing. In Windows, go to File > Options > Customize Ribbon, select the Developer checkbox, and choose OK. In Mac Excel, open the ribbon and toolbar customization settings and enable Developer. If your computer is managed by an organization, an administrator may control which ribbon features are available.
Record Macro or Run is unavailable. Check that you are using the desktop Excel application, not Excel for the web. The web version does not record or execute VBA macros. Open the workbook in Excel for Windows or Mac, and check whether it is protected or opened in a restricted mode that prevents editing.
The macro does not appear in the list. Open the correct workbook and select Developer > Macros. In the dialog, check the macro list’s scope or the Macros in selection, because it may be showing only macros stored in a different workbook. Also verify that the recording was saved in the workbook you expect and that you reopened the macro-enabled .xlsm file rather than an .xlsx copy.
Excel warns that macros are disabled. Do not bypass the warning unless you trust the file and understand what the macro will do. If it is your own workbook, confirm that it was saved as .xlsm and review your organization’s security guidance. Windows settings are available under File > Options > Trust Center > Trust Center Settings > Macro Settings, but changing these settings can increase security risk. Avoid enabling all macros globally; follow your organization’s policy or ask its administrator.
The macro runs but changes the wrong cells. Check the active cell before running it and confirm whether the recording used relative or fixed references. Re-create the recording with the correct setting, or inspect the VBA to identify the recorded cell addresses. Test the revised macro on a copy; do not rely on Undo to restore a macro’s changes.
The macro works once but fails on a larger or differently arranged sheet. The recording may contain a fixed range, a sheet name, or a starting position that differs from the new data. Match the workbook layout to the conditions used when recording, or have the VBA adjusted to locate the correct range. Record Macro is best for repeatable, predictable actions; it is not a substitute for testing a process with varying data.
Frequently Asked Questions
Can I record a macro in Excel for the web?
No. Excel for the web can open and edit many workbook contents, but it cannot record, run, or edit VBA macros. Use the desktop Excel app for those tasks.
Does recording a macro require knowing VBA?
No. Excel creates the VBA code while you perform actions. You can run a straightforward recording without reading the code, although more flexible tasks may require VBA knowledge or help from someone who can edit it.
Why did my macro disappear after I saved?
The workbook may have been saved as .xlsx, which does not keep VBA macros, or the macro may have been stored somewhere other than the workbook you reopened. Save it as .xlsm and check the storage choice in the recording dialog.
Can I undo a macro with Ctrl+Z?
No. Excel clears the normal Undo history when a macro runs, so its actions cannot be undone with Ctrl+Z. Save a backup or run the macro on a copy before using it on important data.
Can one recorded macro work on any size of data?
Not necessarily. A recording often repeats specific cell selections and commands, so it may only work as intended with the same layout and range. Test it with realistic variations; a changing range may require edited VBA.
Can I record a macro on a Mac?
Yes, Excel desktop for Mac supports VBA macros and macro recording. The ribbon customization and some keyboard shortcuts differ from Windows, while Excel for the web does not support recording or running VBA.
Related
- Goal Seek in Excel Browser: Limits and Workarounds
- Goal Seek Button in Excel: Where It Is and How to Use It
- Goal Seek in Excel: How to Find the Input You Need
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Microsoft Excel Record Macro: Steps, Uses, and LimitsTo record a macro in Microsoft Excel for Microsoft 365 on Windows, select Developer > Code > Record…
- VBA Dictionary Item Count: Use the Count PropertyTo get the number of entries in a VBA Scripting.Dictionary, read its Count property: dict.Count. It returns the…
- Excel VBA AutoFilter: Exclude Multiple ValuesExcel VBA AutoFilter cannot use an array of “not equal to” criteria as a general exclusion list. An…
- How to Find VBA Code in Excel: Open and Search MacrosTo find VBA code in Excel for Microsoft 365 on Windows, open the workbook in the desktop app,…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
- VBA Range Is Nothing? Meaning, Checks, and Common FixesIn Excel VBA, a Range is Nothing when an object variable does not currently refer to a range…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.