Home / Tech
Tech

What Is a Macro in Excel? How to Record and Use One

Illustration for What Is a Macro in Excel? How to Record and Use One

If you have ever repeated the same formatting, copying or tidying steps every week, you have probably wondered what a macro in Excel is and whether it could do the boring part for you. In short, a macro is a saved sequence of actions that Excel can replay with one click or one key combination. This guide explains how macros work, how to record your first one on Windows or Mac, how to run it, and how to stay safe when you open files that contain them.

Illustration showing the main idea of What Is a Macro in Excel? How to Record and Use One

What is a macro in Excel?

Microsoft Support describes creating a macro as recording your mouse clicks and keystrokes, which Excel then plays back whenever you ask. Behind the scenes Excel writes those actions as code in a language called Visual Basic for Applications, or VBA. You do not need to understand that code to record a simple macro, but you can open it, edit it or write your own later if you want more control.

Typical uses include:

  • Formatting a monthly export with the same column widths, headings and number formats.
  • Cleaning data you import regularly.
  • Applying the same print settings to several sheets.
  • Combining a series of steps you always perform in the same order.

Macro, formula or built-in tool: which do you need?

Macros are not always the right answer. Many jobs people try to automate are already handled by a formula or a built-in feature.

Task Best tool Why
Calculate values that update automatically Formula Recalculates by itself when the data changes
Repeat the same sequence of clicks every week Macro Replays recorded steps in one go
Remove repeated rows Remove Duplicates button No code needed, see our guide to removing duplicates in Excel
Clean and combine data from several files Power Query (Data tab) Designed for repeatable data imports

How to show the Developer tab

Macro tools live on the Developer tab, which Excel hides by default.

  • Windows: go to File > Options > Customize Ribbon, tick Developer in the Main Tabs list and select OK.
  • Mac: go to Excel > Preferences > Ribbon & Toolbar, tick Developer under Main Tabs and select Save.

How to record a macro in Excel

  1. On the Developer tab, in the Code group, select Record Macro.
  2. Type a name in the Macro name box. Start with a letter and avoid spaces; an underscore works well.
  3. Optionally assign a shortcut key and add a description so you remember what the macro does. Avoid keys that Excel already uses, such as Ctrl+C.
  4. Leave Store macro in set to This Workbook unless you want the macro available in every file, then select OK.
  5. Carry out the actions you want to automate, slowly and without extra clicks, because Excel records everything.
  6. Return to the Developer tab and select Stop Recording.

Before you start, you can switch on Use Relative References on the Developer tab. Excel then records cell movements relative to the active cell instead of fixed addresses, which is what you want if the macro should work on different rows each time.

How to run a macro

  • Go to Developer > Macros, choose the macro and select Run.
  • Press the shortcut key you assigned when recording.
  • On Windows, press Alt+F8 to open the Macro dialogue box.
  • Add a button: Developer > Insert > Button, then assign the macro to it.

Running a macro usually clears Excel’s undo history, so you generally cannot press Ctrl+Z to reverse it. Save a copy of your file before testing a new macro.

Save the workbook as a macro-enabled file

The standard .xlsx format cannot store macros. When you save, choose Excel Macro-Enabled Workbook (.xlsm) in the Save as type list, otherwise Excel warns you that the macros will be lost.

Look at the code

To see what you recorded, open the Visual Basic Editor from the Developer tab or press Alt+F11 on Windows. Microsoft notes that you can edit a recorded macro to make minor changes, such as altering a range or removing a step you did not need. Treat anything bigger as a job for someone comfortable with VBA.

Checklist-style illustration for What Is a Macro in Excel? How to Record and Use One

Are Excel macros safe?

Macros can contain harmful code, which is why criminals sometimes hide it in email attachments. Only enable macros in files from sources you trust. In the Trust Center, which you can open quickly from the Developer tab with Macro Security, the option to disable all macros with notification lets you decide case by case. Options that enable every macro are widely described as not recommended because of the risk.

Recent versions of Microsoft 365 also block macros in files downloaded from the internet by default, so you may see a security warning on documents you receive. If a file you did not expect, even one that looks like an invoice or a payslip, asks you to enable macros, do not.

Limits to know about

  • Desktop only. Excel for the web does not run VBA macros. Microsoft offers a separate automation feature called Office Scripts on some plans.
  • Recorded macros are literal. If your layout changes, a macro that clicks cell D5 will still click D5.
  • Windows and Mac can differ. A macro built on one system may not behave identically on the other.

Macros are most useful for repeatable admin, such as tidying the data behind your small business bookkeeping or formatting a list before you build an invoice following our UK invoice checklist. Learning a few Excel keyboard shortcuts will make recording and running them even smoother.

Frequently asked questions

What is a macro in Excel in simple terms?

It is a recorded set of steps that Excel can repeat on demand, saving you from doing the same task by hand every time.

Do I need to know coding to use macros?

No. You can record a macro without writing code. VBA only becomes necessary when you want to edit a macro or build something more advanced.

Are macros in Excel safe?

They are safe when they come from you or a source you trust. Treat unexpected files that ask you to enable macros as suspicious.

Which file type saves macros?

Use the Excel Macro-Enabled Workbook format, which has the .xlsm extension. A standard .xlsx file cannot store macros.

Why can I not see the Developer tab?

Excel hides it by default. Switch it on through Customize Ribbon on Windows or Ribbon & Toolbar in Preferences on Mac, as shown above.

Can I undo a macro?

Usually not, because running a macro clears the undo list. Save a copy of your workbook before you test one.