What a macro does and when you need one

A macro in Excel is a recorded sequence of actions that you can play back with a single click or keyboard shortcut. When you record a macro, Excel watches what you do — clicking cells, typing text, explore formatting, running calculations — and stores those steps. Later, you run the macro and Excel repeats every step in the same order.

You need a macro when you do the same sequence of actions over and over. Examples: formatting a monthly sales report the same way each time, copying data from one sheet and pasting it into another with specific formatting, or running the same calculation on different sets of numbers. Without a macro, you repeat the steps manually. With one, you press a button.

Macros save time only if you repeat the task at least a few times. If you format a report once a month for a year, a macro saves you real hours. If you do it once, recording takes longer than just doing the work.

Key Takeaways

  • Recording a macro means turning on the recorder, doing your work normally, then stopping the recorder — Excel stores the steps automatically.
  • You must enable macros in the Trust Center before you can record them, and you must save the file as .xlsm (macro-enabled) not .xlsx.
  • Run a recorded macro from the Macros dialog by selecting it and clicking Run, or assign it to a button or keyboard shortcut for faster access.
  • Macros work best for tasks that follow the exact same pattern each time, like formatting, copying, or moving data between sheets.
  • If a macro does not work as expected, you can edit it in the Visual Basic editor or delete it and record a new version.

Enable macros in Excel before you start

Excel blocks macros by default for security reasons. Before you can record one, you must tell Excel to allow them. Open Excel and go to File > Options > Trust Center > Trust Center Settings > Macro Settings. Select "Enable All Macros" or "Disable All Macros Except Digitally Signed Macros" depending on your security preference. Most people choose the first option for their own files.

After you enable macros, save your file as an Excel Macro-Enabled Workbook (.xlsm) instead of the standard .xlsx format. When you save, Excel will ask you to choose the file type — select "Excel Macro-Enabled Workbook" from the dropdown. If you save as .xlsx, your macro will be deleted when you close the file.

Record a macro step by step

Go to the View tab and click "Macros" (or use Alt + F8 on Windows, or Option + F5 on Mac). A dialog box opens. Click "Record Macro". A second dialog appears asking for a name, description, and where to store it. Name it something you will recognize — "FormatSalesReport" or "CopyToArchive" work better than "Macro1". Leave the storage location as "This Workbook" unless you want to use the macro in multiple files.

Click OK. The recorder is now running. Do your work exactly as you normally would. Click cells, type, format, copy, paste, delete — whatever the task requires. Excel records every action. When you finish, go back to View > Macros and click "Stop Recording". Your macro is now saved.

The most common mistake is moving the mouse without clicking or typing. If you click a cell and then move to another without doing anything, the macro records that movement. Keep your actions intentional: click only the cells you need, type only what you need to type, and move only when necessary.

Run your macro from the Macros dialog

To run a macro you recorded, go to View > Macros (or Alt + F8 / Option + F5). The Macros dialog shows a list of all macros in your workbook. Click the one you want and click "Run". Excel plays back every step you recorded, in order, at normal speed.

If the macro does not work as expected, the most common reason is that it was recorded with specific cell references. For example, if you recorded a macro that copies from cell A1, the macro will always copy from A1 — even if you want to copy from A5 this time. You can re-record the macro or edit it in the Visual Basic editor (click "Edit" in the Macros dialog) to make it more flexible, but editing requires some knowledge of programming.

Assign a macro to a button or keyboard shortcut

Running a macro from the Macros dialog works, but a button or keyboard shortcut is faster. To assign a keyboard shortcut, open the Macros dialog, select your macro, and click "Options". Type a letter or number in the "Shortcut Key" field. From then on, you can run the macro by pressing Ctrl + that key (or Cmd + that key on Mac).

To add a button to your spreadsheet, go to Insert > Shapes and draw a rectangle where you want the button. Right-click it and select "Assign Macro". Choose your macro and click OK. Now you can click the button to run the macro. You can change the button text by double-clicking it and typing.

Edit or delete a macro

If a macro does not work the way you want, you can delete it and record a new one, or you can edit the code. To delete, open the Macros dialog, select the macro, and click "Delete". To edit, click "Edit" and the Visual Basic editor opens. This shows the actual code Excel recorded. If you know Visual Basic, you can change the code directly. If you do not, recording a new macro is usually faster.

Common edits include changing cell references to make the macro work on different rows, or adding pauses so the macro runs slower and you can see what it is doing. Most people find it easier to re-record than to edit, especially for straightforward tasks.

When macros are not the right tool

Macros work well for repetitive tasks that follow the same pattern. They do not work well for tasks that change each time. If you need to format a report differently depending on the data, or copy a different number of rows each month, a macro will not adapt — it will do exactly what you recorded, no more and no less.

For tasks that need to change based on conditions, consider using Excel formulas instead. For tasks that involve data from outside Excel, or that need to run automatically on a schedule, you may need a different tool like Power Automate or a programming language like Python.

Frequently Asked Questions

Can I share a macro with someone else?

Yes, but only if you send them the .xlsm file that contains the macro. The macro is stored inside the file. If you send a .xlsx file, the macro will not be included. The person who receives it must also have macros enabled in their Trust Center settings to run it.

What if my macro runs too fast and I cannot see what it is doing?

You can slow it down by editing the code in the Visual Basic editor and adding a delay command, but this requires programming knowledge. The simpler approach is to re-record the macro and pause between steps by clicking cells slowly or typing slowly — the macro will record the pauses.

Can I undo a macro after it runs?

Yes. Press Ctrl + Z (or Cmd + Z on Mac) when ready after the macro finishes. Excel will undo all the steps the macro performed, just as if you had done them manually and pressed undo.

Why does my macro not work when I open the file on a different computer?

The most common reason is that macros are disabled on that computer. Ask the person using it to enable macros in their Trust Center settings, or send them instructions on how to do it. Another reason could be that the file path or sheet name changed, which breaks macros that reference specific locations.

Can I record a macro that works on different numbers of rows each time?

Not easily with the standard recorder. The recorder captures exact cell references, so it will always work on the same rows. To make a macro flexible, you would need to edit the code in the Visual Basic editor or use a more advanced feature like a loop, which requires programming knowledge.