How to Record a Macro in Excel (No Coding Needed)

A macro is a recorded set of steps that Excel can replay on demand. To make one, turn on the macro recorder (View > Macros > Record Macro), do the steps you want to automate once, then stop the recording. From then on, running the macro repeats those steps in one click or one keyboard shortcut, with no coding required.

For months I spent the first twenty minutes of every Monday doing the same routine on a weekly export: delete three columns, trim the spaces, format the dates, apply a filter, resize the columns, and freeze the top row. Twelve or so steps, always in the same order, always slightly boring and occasionally done wrong because I was half asleep. A colleague saw me at it one morning and said, "You know you can just record that, right?" I recorded it that same afternoon. The Monday routine went from twenty minutes to about four seconds, and I've been a bit evangelical about macros ever since, though I'll admit I've also broken a spreadsheet or two with them, which I'll get to below.

How do I record a macro?

1. Go to View > Macros > Record Macro (or use the Developer tab if you have it switched on)
2. Give the macro a name with no spaces, such as FormatWeeklyExport
3. Optionally, assign a shortcut key. Pick a letter combination that isn't already in use, because yours will override Excel's built-in shortcut with the same keys
4. Choose where to store it: This Workbook keeps it with the current file, while Personal Macro Workbook makes it available in every file you open
5. Click OK, then carry out your steps exactly as you want them repeated
6. Go to View > Macros > Stop Recording when you're done

Everything you click and type between steps 5 and 6 is recorded, including mistakes. If you accidentally click the wrong thing, you can usually stop, delete the macro, and start again, which is faster than trying to fix it.

How do I run a macro once it's recorded?

Three common ways:

• Press the shortcut key you assigned
• Go to View > Macros > View Macros, select the macro, and click Run
• Add a button to the sheet (Developer > Insert > Button) and assign the macro to it

If you've read my post on "keyboard shortcuts", a custom macro shortcut works the same way as the built-ins, and is one of the most satisfying ways to use that knowledge.

Why did my macro work yesterday but not today?

The most common cause is relative versus absolute recording. By default, the recorder uses absolute cell references, which means if you selected cell A1 during recording, the macro will always go to A1, no matter which cell you started in. That's fine for a task that always happens in the same place, and a disaster if your data is in a different position next time.

To make the macro follow the cursor instead, click View > Macros > Use Relative References before you start recording. The macro then records movements relative to wherever you began, like "move down two rows" rather than "go to cell A3."

Other common causes:

1. The data has more or fewer rows than last time, and the macro was recorded with a fixed range
2. A sheet or column was renamed or moved, so a step now points at something that doesn't exist
3. The active sheet was different when you ran it, so the steps hit the wrong place

Can I undo a macro if it does something wrong?

This is the one that catches beginners out, and the part where I've personally been burned. Running a macro usually clears Excel's undo history, so Ctrl+Z will not reverse it. Once a macro has deleted columns or overwritten data, those changes are generally permanent unless you close the file without saving.

My habit now is to save a copy of the file before running any new or untested macro, and to test it on a copy of the data first. If you've read my post on "cleaning messy data" , a TRIM-and-Paste-Special routine is a perfect first macro to record, but test it on a copy, since Paste Special Values replaces your original data.

How do I save a workbook that contains a macro?

A standard .xlsx file can't store macros. You need to use File > Save As and choose Excel Macro-Enabled Workbook (.xlsm). If you save as .xlsx by mistake, Excel will warn you that the macros will be lost, so read that prompt carefully rather than clicking through it.

When you open a macro-enabled file, Excel may show a security warning and keep macros switched off until you click Enable Content. The exact behaviour depends on your Excel version and your organisation's security settings, and files downloaded from the internet or email may be blocked from running macros by default. Only enable macros in files you trust.

Is recording enough, or do I need to learn VBA?

Recording gets you a surprisingly long way for repetitive formatting and cleaning jobs. Behind the scenes, Excel writes VBA code for every recorded action, and you can see it by pressing Alt+F11 to open the Visual Basic Editor. You don't need to understand it at first, but reading a recorded macro is one of the best ways to start learning, because you can see exactly what code matches the steps you just took.

Where recording falls short is anything that needs a decision, such as "do this only if the cell is blank," or a loop that repeats until the data runs out. That's where a bit of hand-edited VBA comes in, and it's a natural next step once you've got a few recorded macros you actually use.

FAQ

Do macros work in Excel on the web or on Mac? Recording and editing macros is mainly a desktop Excel feature. Excel for the web has limited macro support, and Mac versions differ in some details, so check what your own version supports before building something other people will rely on.

Where does the Personal Macro Workbook live, and is it safe? It's a hidden workbook (PERSONAL.XLSB) that Excel opens in the background, so macros stored in it are available everywhere. It's saved on your own computer, so it won't travel with a file you email to someone else.

Can a macro damage my computer? A macro you record yourself only does what you did during recording. Macros in files from other people can contain harmful code, which is why Excel blocks them by default. Don't enable content in files from sources you don't trust.

Why is my shortcut key not working? Check whether the macro is stored in a workbook that's currently open, since a shortcut for a macro in a closed file won't work. Also check the key combination isn't being overridden by a different macro, or by a shortcut from another program.

What's the difference between a macro and a formula? A formula calculates a value and updates automatically when the data changes. A macro performs actions, like formatting or deleting, and only runs when you tell it to.