What a macro is and when you need one
A macro is a set of instructions that Excel records and plays back automatically. When you perform the same steps over and over — formatting cells, copying data between sheets, deleting rows that meet certain conditions — you can record those steps once and then run them with a single click or keyboard shortcut.
Macros save time on repetitive work. If you spend ten minutes every week reformatting a report the same way, a macro can do it in seconds. The trade-off is that macros only work in Excel files saved as .xlsm (macro-enabled) format, not the standard .xlsx format most people use.
You do not need to write code. Excel's macro recorder watches what you do and translates it into instructions automatically. You can also edit a macro later if you need to change a step or add a condition.
Key Takeaways
- Macros are recorded by turning on the recorder, performing your steps, then stopping the recorder — no coding required.
- You must save your file as .xlsm (Excel Macro-Enabled Workbook) for macros to work and persist when you close the file.
- Run a macro by pressing its keyboard shortcut, clicking a button you assign to it, or selecting it from the macro list and clicking Run.
- Macros record absolute cell references by default, so they repeat the exact same cells each time — use relative references if you want them to adapt to where your cursor is.
How to record a macro step by step
Open the file you want to add a macro to. Go to the View tab at the top of the ribbon. Click Macros (on the right side of the ribbon), then select Record Macro from the dropdown.
A dialog box appears asking for a macro name, a keyboard shortcut (optional), and where to store it. Give your macro a name with no spaces — something like FormatReport or DeleteBlanks. If you want to run it with a keyboard shortcut, type a single letter in the Shortcut Key field (Ctrl + that letter will trigger it). Leave the storage location as "This Workbook" unless you have a reason to store it in your Personal Macro Workbook. Click OK.
Excel is now recording. Perform the exact steps you want the macro to repeat. Click cells, type text, apply formatting, delete rows, copy and paste — whatever your task is. Work slowly and deliberately, because the macro will record every action, including mistakes. When you are finished, go back to the View tab, click Macros, and select Stop Recording.
Your macro is now saved in the file. Before you close, save the file and choose Excel Macro-Enabled Workbook (.xlsm) from the file type dropdown. If you save it as a regular .xlsx file, the macro will be deleted.
Running your macro
Once recorded, you can run a macro in three ways. The fastest is the keyboard shortcut you assigned during recording — just press Ctrl + the letter you chose. The second way is to create a button on your spreadsheet that runs the macro when clicked. The third way is to go to View > Macros > View Macros, select the macro name from the list, and click Run.
To create a button, go to the Developer tab. If you do not see it, right-click the ribbon, select Customize the Ribbon, check the Developer box, and click OK. On the Developer tab, click Insert, then choose Button (Form Control). Draw the button on your sheet by clicking and dragging. A dialog appears asking which macro to assign — select yours and click OK. You can now type a label on the button and click it to run the macro anytime.
Absolute versus relative references
By default, Excel records macros using absolute references. This means if you click cell A1 during recording, the macro will always click cell A1 when you run it, no matter where your cursor is. This works well for tasks like formatting a specific report template that never changes location.
If you want a macro to adapt to your current position — for example, to format whatever row you are currently in — you need relative references. Before you start recording, go to View > Macros > Use Relative References to toggle it on. The button will appear highlighted. Now record your macro. When you run it, it will perform the same steps relative to wherever your cursor is, rather than always going to the same cells.
Most people use absolute references for their first macros. Relative references are useful once you understand how they work, but they can produce unexpected results if you are not careful about where your cursor is when you run them.
Editing a macro after recording
If you made a mistake during recording or want to change what a macro does, you can edit it. Go to View > Macros > View Macros, select the macro name, and click Edit. The Visual Basic editor opens, showing the code Excel generated from your recorded steps.
You do not need to understand the code to make simple changes. Look for cell references (like A1, B5) or values (like text you typed) and change them directly. If you delete a line, that step will not run. If you add a line, be careful — the code must follow Excel's syntax or the macro will error when you run it. For complex edits, it is safer to record a new macro than to edit the code by hand.
After editing, close the Visual Basic editor by clicking the X button. Your changes are saved to the macro immediately.
Common problems and how to fix them
If your macro does not run, check that your file is saved as .xlsm format. Macros do not work in .xlsx files. You can convert a file by going to File > Save As, choosing the location, and selecting Excel Macro-Enabled Workbook (.xlsm) from the file type dropdown.
If a macro runs but does nothing, it may have recorded steps that do not apply to your current data. For example, if you recorded a macro that deletes row 5, and row 5 is now empty, nothing visible will happen. Re-record the macro with a fresh set of data, or edit it to reference the correct cells or use a condition that finds the data you want to change.
If you get an error message when running a macro, the code may have been corrupted or you may have edited it incorrectly. Try deleting the macro and recording it again. Go to View > Macros > View Macros, select the macro, and click Delete.
Security and macro warnings
When you open a .xlsm file, Excel may show a yellow bar asking whether to enable macros. This is a security feature — macros can potentially carry malware if they come from an untrusted source. If you created the macro yourself or trust the file source, click Enable Content. If you are unsure, click Disable and the file will open without running any macros.
If you share a macro-enabled file with others, they will see the same warning. This is normal and expected. There is no way to remove the warning without disabling the macro or changing the file format back to .xlsx.
Frequently Asked Questions
Can I undo a macro after it runs?
Yes. Press Ctrl + Z immediately after running the macro, just as you would undo any other action. Excel treats a macro as a single undo step, so one Ctrl + Z will reverse everything the macro did. If you wait too long or perform other actions first, you may not be able to undo it.
What if I want to run a macro on a different file?
Macros are stored inside the file they were created in. To use a macro in a different file, you can copy the macro to your Personal Macro Workbook, which loads automatically in every Excel session. Go to View > Macros > View Macros, select the macro, click Edit, select all the code (Ctrl + A), copy it (Ctrl + C), close the editor, then open your Personal Macro Workbook and paste the code into a new module.
Can I delete a macro?
Yes. Go to View > Macros > View Macros, select the macro name from the list, and click Delete. Confirm the deletion. The macro is removed from the file immediately. This does not affect your data — it only removes the recorded instructions.
Do I need to know programming to edit a macro?
No, but it helps. You can make simple changes like editing cell references or values without knowing code. For complex changes, it is safer to record a new macro or find someone familiar with Visual Basic. Excel's macro editor has a help menu if you want to learn the basics.