How to Run Macro in Excel: A Step-by-Step Guide

Macros allow you to easily automate repetitive Microsoft Excel tasks, freeing up your time for more important activities, ensuring consistency, and improving your efficiency. Whether you're creating financial reports or managing data, learning how to run macro in Excel will improve your productivity and make your job easier! 

For example, if you often apply a particular format to a range of cells, you can create a macro that performs the formatting task with the click of a button. Or perhaps there is a set of headings you use often in your worksheets. After creating the appropriate macro, a click of a button will insert these headings anywhere in your workbook. In short, macros should be every accountant's best friend.

To help you get started, we're providing a guide to macros in Excel, including how to record a macro in Excel, run them, and use relative references. If you have issues with your macro, we'll also provide troubleshooting tips. 

Please note, while you can accomplish a lot with recorded macros, many macros cannot be recorded and must be created using the Excel Visual Basic for applications (VBA) programming language. We'll also cover how to view the VBA code underlying the macro.

Summary

The quick steps to record a macro in Excel are: 

1. Start recording
   1. In the Developer tab, click on the Record Macro button to open the dialog box.
2. Set up the macro
   1. Give it a descriptive name
   2. Assign a shortcut key - you'll use CTRL before the shortcut key to run the macro so avoid overwriting an existing shortcut 
   3. Choose where to save the macro in Excel (the default is the current workbook you have open)
   4. Add a clear, but short description of what your macro does
3. Perform the action
   1. Perform the actions in Excel you want to automate with your macro. Excel will record each step.
4. Stop recording 
   1. In the developer tab, click Stop Recording in the Code group.

Get the more in-depth walk-through with screenshots and steps below!

What Is a Macro in Excel?  

A macro is a set of recorded or written instructions that automates a task or series of tasks in Excel. Instead of manually clicking through the same formatting steps, formulas, or data manipulations every time, you capture that sequence once and then replay it whenever you need it, with a single click or keyboard shortcut.

Macros in Excel are built using Visual Basic for Applications (VBA), a programming language embedded directly in Microsoft Office. You don't need to write VBA code to use a basic macro. Excel includes a macro recorder that watches what you do, click by click and keystroke by keystroke, and translates those actions into VBA code automatically. More advanced macros, ones that need to adapt based on the data rather than repeat a fixed sequence, generally need to be written or edited directly in the VBA editor, which offers far more flexibility than the recorder alone.

Why Macros Matter in Accounting Work

Accounting and finance roles involve a disproportionate amount of repetitive, structured work: monthly close processes, recurring report formatting, data imports from source systems that need the same cleanup every time, and templates that get rebuilt period after period with new numbers. That repetition is exactly what macros are built to eliminate.

• Time savings on recurring tasks: A macro that takes thirty seconds to run can replace a process that used to take twenty minutes of manual clicking, and that compounds fast when a task repeats monthly or weekly across a busy season.
• Consistency across periods: A macro applies the same formatting, formulas, and structure every single time it runs, which matters when accuracy and comparability across reporting periods are part of the job.
• Fewer manual errors: Every manual step is a chance to fat-finger a formula or apply inconsistent formatting. Automating the repetitive parts of a workflow reduces that surface area and frees up review time for the parts of the work that actually require judgment.
• Handling larger datasets: As data volume grows, whether that's transaction detail, a general ledger export, or a multi-entity consolidation, manually working through it becomes both slower and riskier. Macros scale in a way manual processes don't.
   

How to Record a Macro in Excel

Now, we're going to walk you through how to record, write, and run macros in Excel. 

Adding the Developer tab

To create or run macros in Excel you need to access the Developer tab on the ribbon. If the Developer tab does not appear on the ribbon proceed as follows:

1. From the File tab click on Options.
2. In the left pane of the Excel Options dialog box, click Customize Ribbon.
3. Select the Developer check box in the pane on the right (see Figure 1). Then click OK.

Figure 1: Adding the Developer tab to the ribbon

How to Record Macros in Excel

To record a macro, proceed as follows:

1. After clicking the Developer tab, you will see the options shown in Figure 2.

Figure 2: The Developer tab

2. In the Code group on the Developer tab, click the Record Macro button. Alternatively, click the Ready icon in the lower-left corner of your screen. The Record Macro dialog box (see Figure 3) opens.

Figure 3: The Record Macro dialog box

3. In the Macro Name box, replace the default name with one that’s more descriptive.
4. If you want to use a shortcut key to run your macro, enter the key you want to use in the Shortcut Key box. A shortcut key is a sequence of keystrokes that can be used to run the macro. For example, if you enter Shift+F, then pressing Ctrl+Shift+F will cause your macro to run. When assigning a shortcut key, make sure you don’t choose one that’s already used to perform a task. For example, since Ctrl+B applies bold font, you cannot assign that shortcut key to your macro.
5. By default, Excel stores the macro in the current workbook, making it available to run whenever the workbook is open. If you want your macro to always be available no matter what workbook you’re using, open the Store Macro In the drop-down list and select Personal Macro Workbook.
6. Optionally, type a description of the macro in the Description box. Then click OK.
7. Perform the steps you want to repeat when you run the macro.
8. In the Code group on the Developer tab, click the Stop Recording button. Alternatively, click the small gray rectangle that appears in the lower left-hand corner of your screen under the worksheet tabs.

Recording and running your first macro

A Worked Example: Formatting Data with a Macro

Suppose your work often requires highlighting numerical data in bold red font. Here's how that becomes a macro:

1. In the Code group on the Developer tab, click Record Macro.
2. Name the macro something descriptive (for example, "redbold"), assign a shortcut like Ctrl+Shift+R, and click OK.
3. Press Ctrl+1 to open the Format Cells dialog box, click the Font tab, choose Bold, select a shade of red, and click OK.
4. Click Stop Recording.
5. Because the workbook now contains a macro, save it as an Excel macro-enabled workbook. The file extension changes to .xlsm, which signals that the file contains macro code. Saving as the standard .xlsx format will strip the macro out.
6. Test it: select any range of cells, press Ctrl+Shift+R, and that range should format in bold red.

From here, the same macro can be reused on any range, in that workbook, whenever the task comes up again.

Running a Macro from a Button

A shortcut key works well once you've memorized it, but a button on the worksheet itself is often more convenient, especially for a macro other people on your team will use.

1. In the Controls group on the Developer tab, click Insert, then choose the Button option from the Forms Control palette.
2. Click and drag on the worksheet to draw the button at the size you want.
3. When you release the mouse, the Assign Macro dialog box opens. Select the macro you want tied to that button and click OK.
4. Test it by selecting some data and clicking the button. It should apply exactly what the macro was recorded to do.

Viewing the VBA Code Behind a Macro

Every recorded macro is really just VBA code that Excel wrote on your behalf, and it's worth looking at even if you don't plan to write VBA from scratch.

1. On the Developer tab, click Macros in the Code group.
2. Select the macro and click Edit. This opens the VBA editor with the underlying code.

A few things are true of every macro's code: it opens with a statement containing "Sub" followed by the macro's name and parentheses (for example, Sub redbold()), and it closes with an End Sub statement. Any line starting with an apostrophe is just a comment, not executable code. The remaining lines, things like .FontStyle="Bold", are the actual instructions telling Excel what to change. Reading through a recorded macro's code this way is often the easiest path into learning VBA syntax, since you already know exactly what the code is supposed to do.

Using Relative and Absolute References with Macros in Excel

By default, a recorded macro references specific, fixed cells, which is called an absolute reference. Sometimes you want the macro's actions to depend on wherever your cursor happens to be instead, which is where relative references come in.

Turning on Relative References

In the Code group on the Developer tab, enable the Use Relative References option before recording. Once it's on, the macro records its actions relative to whichever cell was selected when recording started, rather than to a fixed cell address, so the same macro can run correctly starting from a different location.

Combining Absolute and Relative References

More complex macros often need both in the same sequence. For example, imagine a worksheet listing product sales for last year and this year, and you want a macro that calculates each product's change in sales, adds a red border around the data, and works correctly even if the number of products changes later. That kind of macro typically toggles relative references on and off partway through the recording: off while adding a fixed heading, on while copying a formula down an unknown number of rows, and off again while selecting the full data range for formatting. Toggling the setting at the right points in the process is what lets a single macro adapt to a data set that grows or shrinks between one use and the next.

Troubleshooting: "This File Is Untrusted" Warnings

Because a macro can technically execute any code, Microsoft treats macro-enabled files from outside sources cautiously by default. If you open an .xlsm file and Excel blocks the macro with a security warning, here's the fix, assuming the file is genuinely from a source you trust:

1. Close the file and locate it in File Explorer.
2. Right-click the file and choose Properties.
3. At the bottom of the General tab, check Unblock, then click OK.

The macro should now run normally. This warning exists precisely because a macro can carry out any action its code specifies, including harmful ones, so only unblock files you're confident came from a legitimate source, and be cautious about running macro-enabled files that arrive unexpectedly, even from familiar-looking senders.

Common Accounting Use Cases for Macros

A few patterns show up repeatedly in how accountants apply macros:

• Report formatting: Applying consistent number formats, borders, column widths, and headers to a report every time it's generated.
• Data cleanup after import: Standardizing data pulled from an ERP, accounting system, or bank feed (removing blank rows, correcting date formats, splitting combined fields) before it's usable in a model or report.
• Custom functions and calculations: Building repeatable calculations that aren't available as a built-in Excel formula.
• Consolidation and summary building: Pulling data from multiple worksheets or workbooks, department budgets, for instance, into a single summary tab.
• Variance and reconciliation templates: running the same comparison logic between budgeted and actual figures, or between two data sources during a reconciliation, without re-deriving the formulas each time.

Building Excel Skills for Accounting

Macros are one piece of a broader Excel skill set that tends to separate accountants who spend hours on manual reporting from those who don't. Formulas like lookup functions, conditional logic, and PivotTables generally come first, with macros layered on top once those fundamentals are solid.

Excel offers a wide variety of functions, formulas, and automation that can save you hours of work while improving accuracy and outcomes. To help you make the most out of this software, we offer a wide variety of CPE courses to support your learning! 

Unlock Unlimited CPE with a Prime Subscription

Becker makes it easy to meet your CPE requirements, gain new skills, and stay aware of critical updates and changes in the industry! 

With Prime, you can access over 1,700 courses for a full year and earn unlimited CPE credits. 

Choose Prime CPE