Excel macros are one of the fastest ways to automate repetitive work, especially if you are cleaning the same report every week. In this guide, you will learn what macros are, how to enable the Developer tab, how to record simple macros, and how to run them from a clickable button.

What you will build in about 10 minutes:

  • A one-click macro button that sorts a table from largest to smallest.
  • A one-click macro button that applies consistent formatting.
  • A simple setup you can reuse whenever you receive new data.

Jump to:

What are Excel macros

Macros in Excel automate a sequence of actions. Instead of repeating the same clicks and menu steps, you record the process once, then run it again whenever you need it.

You can create a macro without writing code by using the macro recorder. Behind the scenes, Microsoft Excel stores that recording as VBA code, which is why macros live in macro-enabled files and can be edited in the Visual Basic Editor (VBE) if you ever need to refine them.

If you assign a macro to a button in your Excel workbook, it becomes a clean one-click workflow. That is often the best setup when you share the file with coworkers or want a repeatable process that is hard to mess up. And they’re simple to edit. Once you select them in VBE, a pane pops up that allows edits.

Before you start

Three quick notes that prevent the most common beginner frustrations:

  • You need the desktop version of Excel to create, run, or edit VBA macros. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros.
  • Save your file as a macro-enabled workbook, usually .xlsm, so your macro does not disappear when you close the file.
  • If macros are blocked by security settings, you may need to adjust Trust Center settings, or your organization may enforce policies that you cannot override.

Immediate feedback on your macros

If you have a specific macro question, you can use the spreadsheet assistant below.

Enable the Developer tab

The Developer tab is where you record macros, run macros, and open macro security settings.

Windows

  1. Go to File, then Options.
  2. Select Customize Ribbon.
  3. Under Main Tabs, check Developer, then select OK.

Mac

  1. Select Excel, then Preferences.
  2. Open Ribbon & Toolbar.
  3. Under Main Tabs, check Developer, then save your changes.

Create a simple sorting macro

This example uses a small sales dataset. The goal is simple. Sort the Amount column from largest to smallest with a single click. We’ll also talk about the importance of the name of the macro and the sequence of commands.

Excel worksheet with a Sort macro button that sorts a table with one click
Example, a Sort macro button that sorts your data with one click.

Step 1, prepare your data

Put your data into a clean table with headers, for example Date, Product, and Amount. If you get this report repeatedly, use the same layout each time so your macro stays consistent.

Step 2, record the macro

  1. Go to the Developer tab.
  2. Select Record Macro.
  3. In the Macro name box, use something clear, like SortByAmount, then select OK.
Excel Record Macro dialog where you name the macro before recording
Tip: Use descriptive macro names so you can find them later.

Once you start recording, Excel captures your actions until you stop recording. Note that the sequence you use matters for these.

Step 3, perform the sort

  1. Go to the Data tab.
  2. Select Sort.
  3. Choose the Amount column, then sort Largest to Smallest.
  4. Select OK.

Step 4, stop recording

  1. Return to the Developer tab.
  2. Select Stop Recording.

Run the macro

  1. Go to Developer, then Macros.
  2. Select your macro, for example SortByAmount.
  3. Select Run.

Assign the macro to a button

If you want true one-click automation, assign the macro to a shape button.

  1. Insert a shape, such as a rounded rectangle, and label it Sort.
  2. Right-click the shape, then select Assign Macro.
  3. Choose SortByAmount, then select OK.

Now the sheet sorts itself whenever you click the Sort button.

Create a formatting macro in Excel

Formatting macros are great for recurring reports. The idea is to apply the same look every time, consistent headers, borders, highlighting, and cleanup.

Step 1, record the macro

  1. Go to Developer, then select Record Macro.
  2. Name it something like FormatReport, then select OK.

Step 2, apply the formatting once

  1. Remove any columns or rows you do not need.
  2. Format the header row so it stands out.
  3. Add borders to your table.
  4. Apply any highlights you use consistently, for example top performers or outliers.

Step 3, stop recording

  1. Return to Developer.
  2. Select Stop Recording.
Excel worksheet with a Format macro button that applies formatting to a table
Example, a Format button that applies your saved styling in one click.

Assign the formatting macro to a button

  1. Insert another shape and label it Format.
  2. Right-click the shape, then select Assign Macro.
  3. Choose FormatReport, then select OK.

Now you can sort, then format, with two clicks.

Combining macros

Once you are comfortable, you can combine tasks into one macro so it runs end-to-end. For example, you can record a new macro called SortAndFormat that performs the sort steps, then performs the formatting steps, then stop recording. You end up with one button that produces the finished output.

If you need a more advanced cleanup task, like removing dashes across sheets, it often makes sense to build it as a macro so you can apply the change consistently. Here is a related formatting guide: how to remove dashes in Excel.

Macro best practices for recorded macros

Recorded macros are fast, but they can break if your data layout changes. These habits help your macros stay reliable:

  • Keep your dataset structure consistent, same headers, same column order.
  • Use Excel Tables when possible, tables expand automatically when new rows are added.
  • Avoid extra clicking during recording, record only the steps you actually need.
  • If the macro selects specific cells that shift around, consider re-recording with a cleaner starting point.
  • Name macros clearly, then keep a short note in a cell or hidden sheet describing what each one does.

Macros vs Office Scripts, which should you use in 2026

If you are automating inside desktop Excel, VBA macros are still the standard. If your workflow is cloud-first and starts in Excel for the web, Office Scripts is often the better fit.

Use VBA macros when Use Office Scripts when
You need desktop Excel automation and prefer the macro recorder workflow. You want cloud-based automation designed for cross-platform use.
Your workbook relies on macro-enabled files and existing VBA macros. Your team collaborates in Excel for the web and wants a scriptable workflow.
You want on-sheet buttons and a local, workbook-based automation setup. You want an automation approach built for modern cloud environments.

If you also use Google Sheets, the macro story is different there. Here is the related guide: adding macros in Google Sheets.

Macro security and sharing

Macros are powerful, and they are also a common security vector. If macros do not run, or you see warnings, check these first:

  • Trust Center settings, macro settings can block macros entirely, or allow them with a warning prompt.
  • File source, files downloaded from the internet may be blocked until trusted or unblocked.
  • Trusted locations, placing macro-enabled files in a trusted location can prevent repeated prompts.
  • Organization policies, a managed work device can enforce macro settings you cannot change.

If you share a macro workbook with coworkers and want to reduce accidental edits, treat it like a tool. Control who has edit access to the file, and consider protecting the VBA project if you distribute a macro-heavy workbook.

Video guide, making macros with Microsoft Excel

You can also watch this on my YouTube channel.

Video transcript

Introduction

Hi there. In todayโ€™s brief video, I will explain how to create macros in Excel, give a general overview of what they are, how to insert them, and then show some common macros you can use to save time.

Macros in Excel automate repetitive tasks. You can record a macro, then run it from the Developer tab, or trigger it from a button you add to your sheet.

The first thing to remember is that you need the Developer tab available. If you do not see it, go to File, Options, Customize Ribbon, then check Developer.

Sorting macros

Letโ€™s say you want a macro that sorts sales data by Amount, highest to lowest. Go to Developer, Record Macro, name it Sort, then select OK. Excel will record each action until you stop recording.

Go to Data, Sort, and sort by Amount from largest to smallest. Select OK, then go back to Developer and stop recording.

To run it, go to Developer, Macros, select Sort, then run it. If you want a one-click option, insert a shape, label it Sort, then assign the Sort macro to that shape.

Formatting macros

Now record another macro called Format. While it is recording, format the table, adjust header styling, add borders, and highlight key values. Then stop recording.

Create a new shape labeled Format, then assign the Format macro. Now you can sort with one click and format with another.

You can also combine these into one macro, but I wanted to show you how the pieces work first.

For more Excel tips and templates, check out SpreadsheetPoint Excel guides.

Frequently asked questions

Do I need to know VBA to make a macro in Excel?

No. You can record a macro with the macro recorder without writing code. Excel stores the recording as VBA behind the scenes, which is useful later if you want to edit or improve the macro.

Why canโ€™t I create or run macros in Excel for the web?

Excel for the web can open a macro-enabled workbook, but it cannot create, run, or edit VBA macros. If you need VBA macros, open the file in the Excel desktop app.

What file type should I use so my macros are saved?

Save the workbook as a macro-enabled file, usually .xlsm. If you save as .xlsx, the macro will not be kept.

Why is the Developer tab missing?

It is not shown by default. On Windows, enable it through File, Options, Customize Ribbon. On Mac, enable it through Excel, Preferences, Ribbon and Toolbar, then check Developer.

Why is Record Macro grayed out or missing?

This is usually a permissions or security issue, or you are not in the desktop app. First confirm you are using Excel desktop. If you are on a managed work device, IT policies can disable macro features.

Why are macros blocked when I open the file?

Excel may be blocking macros through Trust Center settings, or because the file came from an untrusted source. Check macro settings, trusted locations, and whether the file was downloaded from the internet.

How do I assign a macro to a button?

Insert a shape, label it, right-click it, then select Assign Macro. Pick your macro name, then select OK.

Why does my recorded macro break when new rows are added?

Recorded macros often rely on the exact range you clicked while recording. If your dataset grows, consider using an Excel Table, then re-record the macro using the table so it expands with new data.

Should I use a button or a shortcut key to run macros?

Buttons are easier for shared workbooks because they are visible. Shortcut keys are faster for personal workflows. If other people will use the file, I usually start with buttons.

How do I stop coworkers from changing my macros?

Start with file permissions, and control who can edit the workbook. If you distribute a macro-heavy tool, consider protecting the VBA project so people can run the macros without casually editing them. You can also disable VBA macros in the security section of your settings or preferences. Just choose “Macro Security” and disable all macros option you need, with or without notifications.

Conclusion

Macros in Excel are a practical way to save time by automating repetitive work. Start with the macro recorder, build simple sort and format buttons, then expand into more complex workflows as you get comfortable.

Related: