How to Record a Macro in Excel

If you spend part of every workday clicking through the same Excel steps (formatting the same columns, running the same report, cleaning up the same data), you’ve probably been hoping there’s a faster way. There is, and it’s built right into Excel.

Recording a macro lets Excel watch what you do and replay it on demand. You don’t write a single line of code, and it takes about two minutes to set up. You’ll learn how to record your first macro, run it, save it correctly (this part trips up a lot of people, fingers crossed you won’t make the same mistake), and fix the most common things that go wrong.

What Is an Excel Macro?

An Excel macro is a saved sequence of actions (clicks, formatting changes, formula entries, you name it) that Excel can replay automatically whenever you need it. Under the hood, Excel stores those actions as VBA (Visual Basic for Applications) code, but you never have to look at that code if you don’t want to. The Macro Recorder captures everything while you just work normally.

What you can automate with macros:

• Formatting cells, rows, or columns (fonts, colors, borders, number formats)
• Inserting formulas and functions
• Sorting and filtering data
• Deleting blank rows or columns
• Any multi-step task you repeat regularly

Macros are a solid fit for beginner Excel users and experienced folks alike, really anyone who does the same thing in a spreadsheet more than once a week. You do the task once while recording, and Excel handles it from then on.

Heads up: VBA macros work in Excel for Microsoft 365, Excel 2021, and Excel 2019 on Windows and macOS. If you use Excel in a web browser, the Macro Recorder isn’t available there. The alternatives section near the bottom covers your options.

Before You Begin

Make sure you have:

• Excel for Microsoft 365, Excel 2021, or Excel 2019 on Windows 10/11 or macOS
• A workbook open with some data to practice on

The Developer tab is where the Macro Recorder lives, and it’s hidden by default. If you don’t see it in your ribbon, start with the next section before anything else.

How to Enable the Developer Tab

This is the part most people get stuck on when they’re first trying to record a macro. The Developer tab isn’t visible until you turn it on manually.

On Windows

1. Click File in the top-left corner of Excel.
2. Click Options at the bottom of the left panel.
3. In the Excel Options window, click Customize Ribbon in the left pane.
4. On the right side, under Main Tabs, check the box next to Developer.
5. Click OK.

On macOS

1. Click Excel in the menu bar.
2. Select Preferences.
3. Click Ribbon & Toolbar.
4. Under Main Tabs, check Developer.
5. Close the window.

The Developer tab now appears in your ribbon. You’re ready to record.

How to Record a Macro in Excel

With the Developer tab visible, recording a macro is pretty straightforward. The core steps are the same on Windows and macOS. The only difference is the keyboard shortcut called out below, which is Windows-only.

1. Open the workbook where you want to store the macro, or a blank practice workbook.

2. Click the Developer tab, then click Record Macro in the Code group. On Windows, you can also press Alt + T + M + R to open the dialog directly.

3. In the Record Macro dialog, fill in the details before you click OK:

– Macro name: Give it a clear, descriptive name with no spaces. Use underscores or CamelCase instead, like FormatMonthlyReport or Clean_Data both work. Excel won’t accept spaces. – Shortcut key (optional): Type a letter to assign a Ctrl + [letter] shortcut on Windows. Avoid common combos like Ctrl + C or Ctrl + V. More on that in the troubleshooting section. – Store macro in: This choice matters. This Workbook keeps the macro in the current file only. Personal Macro Workbook makes it available in every Excel file you open, which is great for macros you use constantly. New Workbook saves it to a new file. – Description (optional): Worth a quick note, especially if others will use the file.

4. Click OK. Recording starts immediately. Every action you take from here (clicking cells, changing formatting, typing formulas, adjusting column widths) gets captured.

5. Perform the exact steps you want to automate. Work deliberately. The recorder captures every click, so wrong turns and corrections get recorded too. If you’re unsure what you’re doing, practice the steps manually a couple of times before hitting record.

6. When you’re done, click the Developer tab again and click Stop Recording in the Code group.

On Windows, Alt + T + M + R toggles recording off as well. The same shortcut starts and stops it. Your macro is now saved and ready to run.

How to Run a Recorded Macro

1. Click Developer > Macros in the Code group.

2. In the Macro dialog, select your macro from the list.

3. Click Run.

If you assigned a keyboard shortcut when you set the macro up, you can skip the menu entirely; just press the shortcut and it runs on the spot.

How to Edit or Delete a Macro

Editing a Macro

If the macro didn’t quite do what you expected, you’ve got two options:

• Open the VBA Editor: In the Macro dialog (Developer > Macros), select the macro and click Edit. The Visual Basic Editor opens and shows the exact code Excel recorded. Even if VBA looks like a foreign language, small tweaks, such as swapping a cell reference or changing a color value, are often readable enough to manage without programming experience. Another approach: record a short second macro with just the new steps, then copy its code into the first macro inside the editor.
• Delete and re-record: For most beginners, this is genuinely the faster option. Scrapping a flawed macro and recording a clean one takes less time than debugging VBA you didn’t write.

Deleting a Macro

1. Click Developer > Macros.
2. Select the macro you want to remove.
3. Click Delete.
4. Click Yes to confirm.

Saving Your File as a Macro-Enabled Workbook

This is the step that catches people off guard. If you save your workbook in the standard .xlsx format, Excel warns you that macros can’t be saved in that format, and if you click past that warning without reading it, your macros don’t survive. To keep them, you have to save as .xlsm, the macro-enabled workbook format.

1. Click File > Save As.

2. Choose your save location.

3. In the Save as type dropdown (Windows) or File Format menu (macOS), select *Excel Macro-Enabled Workbook (.xlsm)**.

4. Click Save.

When you or anyone else opens a .xlsm file, Excel shows a yellow security bar asking whether to enable the macros. Click Enable Content if the file came from you or someone you trust. Don’t click it for files from unknown sources.

Sharing Macro-Enabled Excel Files

Macros are useful, but they’re also a known security risk. Malware has been distributed through macro-enabled Office files. Because of that, most email providers (Gmail included) block .xlsm files as attachments, so your file may never reach the recipient’s inbox.

If you need to share a macro-enabled workbook:

• Use cloud storage: Upload to Google Drive or OneDrive and share the link. That sidesteps email attachment filters entirely.
• Use local file transfer: If you’re on the same network as the recipient, a direct share avoids the email problem altogether.

And if someone sends you an .xlsm file from a source you don’t recognize, don’t rush to click Enable Content. Scan the file before opening it, and only enable macros in files you’re confident about.

Optional: Add a Button to Run Your Macro

If you’d rather click a big visible button on the spreadsheet than remember a keyboard shortcut, you can do that too.

1. Click the Developer tab.
2. In the Controls group, click Insert.
3. Under Form Controls, select Button (the rectangle icon).
4. Click and drag on the spreadsheet to draw the button.
5. In the Assign Macro dialog that appears, select your macro and click OK.
6. Right-click the button and choose Edit Text to give it a descriptive label, like “Run Monthly Report”.

Office Scripts and Power Automate: Modern Alternatives

VBA macros recorded through the Macro Recorder are still the fastest way to automate repetitive in-spreadsheet work, and for most people doing that kind of task, the Macro Recorder is exactly the right tool. But if you’re on Microsoft 365 and your needs have grown beyond a single workbook, two newer options are worth knowing about.

Option	Best For	Requires Coding?	Works in Excel for the Web?
VBA Macro Recorder	Quick automation inside one workbook	No (to get started)	No
Office Scripts	Cloud-based automation, sharing across teams	Some (TypeScript/JavaScript)	Yes
Power Automate	Workflows across multiple apps (Excel + Outlook, SharePoint, etc.)	Minimal (low-code)	Yes

• VBA Macros: The classic option. Stores automation directly in your workbook, works offline, and you don’t need to write any code to get started. Editable later in the VBA editor if you want to go further.

• Office Scripts: Microsoft’s newer scripting system for Excel on the web and some Microsoft 365 desktop integrations. Scripts run in the cloud, can be shared across your organization, and use TypeScript/JavaScript instead of VBA. They’re not recorded the same way as classic macros.

• Power Automate: Best when you need Excel to talk to other apps. You can build a flow that pulls data from SharePoint, processes it in Excel, and sends a summary by email, all on a schedule. More setup than a macro, but powerful for cross-app workflows.

If you’re just trying to automate something you do inside a single spreadsheet, the Macro Recorder is still the right starting point.

Tips and Troubleshooting

Common Problems

Problem: I can’t find the Record Macro button

The Developer tab is hidden by default. Follow the steps in the “How to Enable the Developer Tab” section above to add it to your ribbon.

Problem: My macros disappeared after I saved the file

You saved as .xlsx instead of .xlsm

. Excel can’t store macros in the standard format. It warns you, but it’s easy to click past without noticing. Go to File > Save As and choose *Excel Macro-Enabled Workbook (.xlsm)** to fix it going forward.

Problem: The macro runs on the wrong cells

By default, the Macro Recorder uses absolute references. It records the exact cells you clicked, not your position relative to your cursor. If you recorded clicking on cell A1, it will always go to A1 no matter where you are when you run it.

To fix this, click Use Relative References in the Developer tab’s Code group before you start recording. That tells Excel to record movement relative to wherever you are, so the macro works from any starting position.

Problem: My keyboard shortcut is conflicting with an existing Excel shortcut

Assigning Ctrl + C to your macro would override copy, which is not what you want. If the Ctrl + [letter] combo is already in use, Excel remaps your macro to Ctrl + Shift + [letter] instead. Pick a less common letter, or check the shortcut field in the Record Macro dialog before clicking OK.

Problem: The macro works in this workbook but not in others

You stored it in This Workbook. To use a macro across all your Excel files, delete it and re-record it. This time choose Personal Macro Workbook in the Store macro in dropdown. Excel creates a hidden file called Personal.xlsb that loads every time Excel opens, making the macro available everywhere.

Problem: Excel shows a security warning every time I open the file

That’s expected for .xlsm files. If the file is one you trust, you can add its folder to Excel’s trusted locations list: go to File > Options > Trust Center > Trust Center Settings > Trusted Locations and add the folder path.

Problem: I want to tweak the macro without re-recording the whole thing

Go to Developer > Macros, select the macro, and click Edit. The VBA Editor opens and shows exactly what Excel recorded. Small changes, such as a different cell reference, a new color value, or an extra step, are usually readable even without VBA experience. Another option: record a short second macro with just the new steps, then copy its code into the first macro inside the editor.

Pro Tips

• Test on a copy first. Run any new macro on a duplicate of your data before relying on it for real work. It’s easy to record something that behaves perfectly on sample data but does something unexpected in the wild.
• Keep macros focused. One macro per task is much easier to manage than one giant macro that does everything. Shorter macros are also easier to fix when something goes wrong.
• Use the Description field. A quick note about what the macro does and when to use it will save you a lot of head-scratching six months from now.

Wrapping Up

Step	Action	Notes
1	Enable the Developer tab	Hidden by default, do this first
2	Click Record Macro and name it	Choose where to store it before clicking OK
3	Perform your actions	Every click is captured, so be deliberate
4	Click Stop Recording	Macro is saved and ready
5	Save as .xlsm	Macros don’t survive in standard .xlsx files

Once you get the hang of it, recording a macro takes about two minutes, and you’ll probably recoup that time the very first time you run it. The trickiest parts are finding the Developer tab (hidden until you enable it) and remembering to save as .xlsm. Get those two things right and everything else finally clicks into place.

If you eventually want to go further, functions like COUNTIFS, SUMIFS, and AVERAGEIFS are great for more advanced data work, and if your automation needs grow beyond a single workbook, Office Scripts and Power Automate are worth exploring. For more on Excel automation, consider checking out AutoHotkey for automating tasks outside of Excel, or learning about AI-enhanced investing tools for data analysis. Additionally, understanding how to share files between PCs, smartphones, and tablets can be useful for collaborating on Excel projects. For team management and collaboration, tools like team management apps can help streamline your workflow. Lastly, for those interested in integrating AI with everyday apps, there are resources available to explore that as well.