In Excel for Microsoft 365 on Windows, set a macro by opening a workbook, choosing Developer > Record Macro, performing the actions you want to automate, and selecting Developer > Stop Recording. Save the file as an Excel Macro-Enabled Workbook (.xlsm), then run the macro from Developer > Macros or assign it to a shortcut or button. A macro records actions so you can repeat a routine without doing each step by hand.
What You’ll Need
- Microsoft Excel for Microsoft 365 desktop on Windows, or the desktop Mac app. Excel for the web cannot create or run VBA macros.
- A sample workbook, preferably a copy of the file you intend to use. Recording actions in a test copy makes it easier to check the results safely.
- A clear, repeatable task, such as applying the same formatting to a report or setting up a standard worksheet layout.
- Permission to save a macro-enabled workbook and, if you plan to share it, a way for recipients to access and trust the file.
Step-by-Step
- Prepare the workbook and plan the sequence. Open the workbook where you want the macro to be available and save a working copy first. Decide exactly which actions the macro should repeat, and arrange the sheet so you can perform them consistently. A recorded macro follows the actions you take; it does not automatically understand which data is important or adapt intelligently to every future layout. For example, if you want to format a weekly report, decide where the headings and data will be before recording. Avoid recording extra clicks, corrections, or navigation that are not part of the routine.
- Show the Developer tab if it is missing. On Windows, select File > Options > Customize Ribbon, check Developer in the Main Tabs list, and select OK. On a Mac, open Excel > Settings > Ribbon & Toolbar, enable Developer in the tab list, and save the change. The Developer tab contains the recording controls and macro tools. In Excel for the web, these VBA controls are not available; open the workbook in the desktop app instead.
- Choose whether cell references should be fixed or relative. On the Developer tab, look in the Code group for Use Relative References. If it is off, recorded cell selections and references are generally tied to the exact locations you used. If you turn it on, Excel records some movements relative to the starting cell, which can help repeat a sequence on different rows or columns. For instance, selecting a cell and moving one cell down can be more reusable than always selecting a particular address. Choose the setting before you start recording, and test the result with the active cell in a different location if you expect to use the macro that way.
- Start recording and name the macro. Select Developer > Record Macro. In the dialog box, enter a short name that starts with a letter and contains no spaces; for example, use FormatWeeklyReport rather than Format Weekly Report. You may add a description to remind yourself what the macro does. Under Store macro in, choose This Workbook if the macro is meant for this file. Choose Personal Macro Workbook only if you want the macro available in Excel whenever you use your computer; that option stores it separately and may not travel with the workbook.
- Set an optional keyboard shortcut. The Record Macro dialog lets you assign a shortcut using Ctrl and a letter on Windows. Pick a combination that you do not already rely on, because a macro shortcut can take precedence over an Excel shortcut while the workbook is open. Do not assign a shortcut if you are unsure; you can run the macro from the Macros list or add a button later. Mac keyboards and existing shortcuts differ, so check the dialog and test any shortcut on the Mac you will use rather than assuming a Windows combination behaves identically.
- Perform only the actions you want repeated. Select OK to begin recording, then carry out the routine at a steady pace. Use the ribbon commands, selections, entries, and formatting that belong in the final process. If the task includes typing a value, remember that the recording may replay that same value each time; it will not automatically ask for a new one. Be careful not to click unrelated cells or open dialogs that should not be part of the routine. If you make a mistake during recording, stop, discard or remove that recording, and start again instead of assuming Excel will exclude the mistake.
- Stop recording and save as a macro-enabled file. When the routine is complete, select Developer > Stop Recording. Then save the workbook using File > Save As, choose a location, and select Excel Macro-Enabled Workbook (*.xlsm) in the file type list. A standard .xlsx file cannot store VBA macros; if you save in that format, Excel warns that the macro will be removed. Choose the macro-enabled format and confirm any prompt. Keep a separate original or backup copy, especially before first testing the macro.
- Run the macro and check what it changes. Select Developer > Macros, choose the macro name, and select Run. On Windows, Alt+F8 opens the Macro dialog; on a Mac, Option+F8 commonly opens it, though you may need the Fn key depending on your keyboard settings. Check the affected cells, sheets, and formatting. To try a macro on different data, use a fresh copy of the workbook and verify that the results are correct before relying on it for routine work.
- Optionally add a button for easier access. On Windows, choose Developer > Insert and select Button under Form Controls. Drag on the worksheet to draw the button, then select the macro in the Assign Macro dialog and choose OK. Edit the button text to describe its action. You can also assign a macro to a shape: insert or select a shape, right-click it, choose Assign Macro, and select the macro. On Mac, use the available Form Controls or a shape and its Assign Macro command if shown in your Excel version. ActiveX controls are not supported on Mac, so use a Form Control or shape for a cross-platform workbook.
Common Mistakes
- Saving as .xlsx: This format does not retain VBA macros. Use .xlsm when the workbook needs to contain the macro, and check Excel’s warning before confirming a file type change.
- Recording the wrong selection behavior: With relative references off, a macro can keep returning to the same cells even when you intended it to work from the current row. With relative references on, it may move from the starting cell in ways you did not expect. Test both the starting position and the resulting selections.
- Recording unnecessary steps: A macro can repeat accidental clicks, temporary formatting, or the wrong cell entry. Plan the sequence first and record only the final process. If a recording is cluttered, it is often simplest to make a fresh one.
- Expecting the macro to make decisions: A basic recording repeats actions; it does not necessarily detect changing data sizes, choose between conditions, or ask for new input. Tasks that need branching or flexible logic may require editing VBA or using another Excel feature.
- Overwriting a useful shortcut: A chosen key combination can conflict with a shortcut you use regularly. Leave the shortcut blank unless it is genuinely convenient and test it after recording.
Tips
- Use names that explain the task, such as CleanImport or ApplyReportLayout. Meaningful names are easier to identify later in the Macros dialog.
- Write down the assumptions of the recording: which sheet should be active, where the data begins, and whether the macro expects a particular selection. A short description in the Record Macro dialog can serve as a reminder.
- Test the macro on a duplicate workbook with representative data, including a case with more or fewer rows if that is likely. A macro that works on one small sample may not behave as intended when the worksheet changes.
- Remember that Ctrl+Z cannot undo a macro’s changes. Save a backup before running a macro that changes or deletes data, and close without saving if an unexpected result occurs and you have not made other changes you need to keep.
- Share the .xlsm file with its purpose and source clearly explained. Recipients should only enable macros in files they trust; a macro can change workbook contents or perform other actions permitted by its code.
Troubleshooting
The Developer tab or Record Macro command is missing: Enable the Developer tab using the Windows or Mac settings described above. If you are using Excel in a browser, select the option to open the file in the desktop app. Also check whether your organization has restricted macro features.
The macro disappears after saving: Check the filename extension. If the workbook is .xlsx, save a copy as .xlsm and record or restore the macro in that macro-enabled copy. If you selected Personal Macro Workbook, the macro is stored separately rather than inside the current workbook, so it will not automatically be included when you send the file to someone else.
Excel blocks the macro when you open the file: Excel may block macros for security, particularly in files downloaded from the internet or received by email. Do not enable a macro merely to make a warning go away. Verify the file and its source first, then follow your organization’s approved process. On Windows, macro options are under File > Options > Trust Center > Trust Center Settings > Macro Settings. Avoid enabling all macros globally; use a trusted source or approved trusted location where appropriate.
The macro runs but changes the wrong cells: Check whether Use Relative References was enabled when you recorded it, and note which cell was active when the macro began. Re-record with the intended reference behavior, then test from the same and a different starting cell. Also confirm that the expected worksheet is active and that the workbook layout has not changed.
The shortcut or button does nothing: Open Developer > Macros and try running the macro directly. If it is not listed, check that it was stored in the current workbook or Personal Macro Workbook and that macros are permitted. For a button or shape, right-click it, choose Assign Macro, and confirm that the correct macro is selected. A shortcut conflict may also prevent the expected action, so try another unused key combination or use the Macro dialog.
Frequently Asked Questions
What does setting a macro do?
A macro saves a sequence of actions so Excel can repeat them. It is useful for consistent, repetitive work such as formatting a report, but a recorded macro may repeat fixed selections and values rather than adapting to every workbook.
Can I create or run a macro in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. Use Excel desktop for those tasks. The macro itself is not run simply because the workbook is open in a browser.
Do I need to know VBA to set a macro?
No. The Record Macro feature can capture a straightforward sequence without requiring you to write code. More complex tasks, such as adding conditional decisions or handling changing data ranges reliably, may need VBA knowledge or another automation method.
Can I use the macro in another workbook?
Yes, but how depends on where it was stored. A macro saved in This Workbook stays in that .xlsm file. A macro stored in Personal Macro Workbook is available in your Excel installation, but is not automatically packaged with a workbook you share. For another user’s file, use a suitable macro-enabled workbook and make sure the recipient trusts its source.
Can I undo a macro with Ctrl+Z?
No. Excel clears the normal undo history when a macro runs, so Ctrl+Z will not reverse its changes. Save a backup before running it, especially if it edits, moves, or deletes data. If the outcome is wrong, close the test copy without saving or restore from your backup.
Related
- Chemistry Balance Equation Worksheet: Practice and Answers
- Is the Balance of Payments Always Zero? Explained
- Split String Into an Array in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- TMG Macro Recorder? Excel’s Built-In Option Explained“TMG macro recorder” is not the name of a standard feature in Microsoft Excel. If you mean recording…
- Excel VBA: How to Include Double Quotes in a StringIn Excel VBA, write two double-quote characters inside a string literal wherever you want one actual double quote.…
- Excel VBA Date String Formatting ExplainedIn Excel VBA, use Format$ to turn a date value into a formatted text string, such as Format$(Date,…
- VBA InputBox Integer: Get and Validate Whole NumbersVBA’s standard InputBox returns text, not an Integer. In Excel VBA, use Application.InputBox with Type:=1 to request a…
- Excel Macro Disabled? How to Enable Macros SafelyIf an Excel macro is disabled, first confirm the workbook is from a source you trust, then check…
- Excel VBA: Import CSV Data the Right WayTo import a CSV file with Excel VBA, use Workbooks.OpenText to parse it, then copy its values into…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.