How to Create a Macro in Excel by Recording Your Steps

If you do the same fiddly formatting every week, the same bold headings, the same column widths, the same colour on overdue rows, a macro can do it for you in one keystroke. And you don't need to write a line of code. Excel's macro recorder watches what you click and type, then replays it whenever you ask.

Microsoft's own example is a monthly report where you colour certain customer names red and make them bold. Record it once, and from then on a macro does it in a second. I'll walk you through switching on the hidden tab you need, recording your first macro, running it, and saving it so it doesn't vanish.

Showing the Developer tab in Excel

Microsoft says the macro tools live on the Developer tab, which is hidden by default. You only need to switch it on once.

In Excel for Windows

1. Select File, then Options.
2. Select Customize Ribbon.
3. Under Main Tabs on the right, tick Developer.
4. Select OK.

In Excel for Mac

1. Go to Excel, then Preferences.
2. Select Ribbon & Toolbar.
3. In the Main Tabs list, tick Developer.
4. Select Save.

Microsoft notes that once it's showing, the Developer tab stays visible unless you untick it or reinstall.

Planning your Excel macro before you record

The recorder captures everything you do, including mistakes and stray clicks. A minute of planning saves a messy macro.

• Write down the exact steps, in order.
• Decide where you'll start, for example with the first cell of the data selected.
• Practise the steps once without recording.
• Keep it short. Several small macros are easier to fix than one long one.

Good first candidates are formatting jobs: switching a column to the date format you prefer, applying a heading style, or auto-fitting every column to its contents.

Recording a macro in Excel step by step

These are Microsoft's quick start steps:

1. On the Developer tab, in the Code group, select Record Macro.
2. Type a name in the Macro name box. Use letters and no spaces, such as FormatReport.
3. If you want, add a key in the Shortcut key box and a short note in the Description box.
4. Select OK. Recording starts.
5. Do the actions you want to automate, such as typing a standard heading or filling down a column.
6. On the Developer tab, select Stop Recording.

That's it. The macro now holds every click and keystroke you made between Record and Stop. Microsoft's Mac steps are the same, with Record Macro and Stop Recording on the Developer tab.

A tip on shortcut keys

Pick a shortcut that doesn't clash with ones you rely on. A Ctrl plus letter combination you already use for copy or save would be replaced while that workbook is open, which can be confusing.

Running your Excel macro

• Shortcut key: if you set one, press it.
• Macro dialog: Microsoft lists Alt + F8 to open the Macro box, where you can pick a macro and run, edit or delete it.
• Developer tab: select Macros, choose the name and select Run.

Select the cells you want to work on before running it, if your macro was recorded to act on a selection, like Microsoft's red-and-bold example.

Looking at your macro's code in Excel

Microsoft encourages a peek behind the curtain. On the Developer tab, select Macros, choose your macro and select Edit. The Visual Basic Editor opens and shows your recorded actions as code. Alt + F11 opens the same editor directly.

Some of the code will make sense straight away, like a line that sets bold. Microsoft suggests experimenting: change something small, close the editor and run the macro again to see what's different. Do this on a copy of your file.

Saving an Excel workbook that contains macros

This is where first macros often go missing. A standard workbook can't keep macros, so when you save, choose the macro-enabled workbook format if Excel prompts you. Give the file a clear name so you know it holds macros.

Anyone who opens the file later, including you, may see a security bar asking whether to enable content. Microsoft's guidance is to enable macros only when you're sure what they do, and the Macro Settings sit under File, Options, Trust Center, Trust Center Settings.

Testing a new Excel macro safely

Before you trust a macro with real work, test it on a copy of the sheet. Run it once and check every cell it touched. Microsoft notes that running a macro in Excel clears the undo history, so you can't simply press undo to reverse it, and a spare copy is your safety net. Once it behaves, run it on the real data and keep the copy for a week or so in case something looks off later.

Macro ideas for small business owners and creators

Repeated job	What the macro records
Weekly report tidy-up	Bold headings, column widths, number formats
Content calendar	Colour-coding each platform's rows
Order export	Deleting unused columns and sorting by date
Client tracker	Highlighting overdue follow-ups

If your Excel macro doesn't work as expected

• No Developer tab: switch it on in Options, Customize Ribbon, or on a Mac in Preferences, Ribbon & Toolbar.
• It changes the wrong cells: the macro replays exactly where you clicked. Start from the same cell each time, or select the cells first.
• Macro is gone after reopening: the file was saved in a format that can't hold macros.
• Macros won't run: your Trust Center settings or an IT policy may be blocking them.

Frequently asked questions

How do I create a macro in Excel without coding?

Use the macro recorder. On the Developer tab select Record Macro, name it, select OK, do the steps you want to repeat, then select Stop Recording.

Where is the Developer tab in Excel?

It's hidden by default. On Windows, go to File, Options, Customize Ribbon and tick Developer. On a Mac, go to Excel, Preferences, Ribbon & Toolbar.

How do I run a macro in Excel?

Press the shortcut key you set, or press Alt + F8 to open the Macro box, choose the macro and run it.

Can I edit a recorded macro?

Yes. On the Developer tab, select Macros, choose the macro and select Edit. Microsoft says this opens the Visual Basic Editor with your actions shown as code.

Why did my macro disappear after I saved?

The workbook was probably saved in a format that can't store macros. Save it in the macro-enabled format next time.