How to record and run macros in Excel: a step-by-step guide

Macros allow you to easily automate repetitive Microsoft Excel tasks. 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 in Excel should be every accountant's best friend.

This article is your ultimate guide to macros in Excel, including how to enable macros in Excel, how to run them and how to 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.

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

Here’s an example that shows how you can record and run a useful macro. Suppose your work often requires you to highlight numerical data in a bold red font. Let’s record a macro to automate this task. Note that workbooks containing macros need to be saved in the .xlsm format.

To create and run this macro proceed as follows:

1. In the Code group on the Developer tab click Record Macro.
2. Fill in the Record Macro dialog box as shown in Figure 4. Then click OK.

Figure 4: Settings for the redbold macro

3. Press Ctrl+1 to open the Format Cells dialog box.
4. Click the Font tab, choose Bold in the Font Style list, and select a shade of red under Color. Then click OK.
5. Click Stop Recording.
6. Because the workbook contains a macro, save the workbook file as an Excel macro-enabled workbook. The workbook’s name is now Redbold.xlsm. The suffix .xlsm indicates the file is a macro-enabled workbook.
7. Test the macro by entering data in cells J5:J7. After selecting J5:J7 and pressing Ctrl+Shift+R, cells J5:J7 will be formatted in a red font and bolded.

How to use a button to run macros in Excel

You can run your macro shortcut more conveniently by creating a button. Here's how: 

1. In the Controls group on the Developer tab, click the Insert button. Then, choose the Button option from the Forms Control palette that appears.  

2. Click the spot in your worksheet where you want to create the button, and then drag until the button is the size and shape that you want. 

3. When you release the mouse button, the Assign Macro dialog box opens (see Figure 5). In the Macro Name box, select the redbold macro that we previously created and click OK. 

4. To test the button, enter some data, select it and click the button. The new data should be formatted in a bold red font. 

Figure 5: Creating a button to run macros in Excel

How to view the VBA macro code

When you record macros in Excel, you can later view it as VBA code. Here's how to view your macro's VBA code: 

1. On the Developer tab, select Macros from the Code group.  

2. Select your macro, in this case redbold, then select Edit. You should see the code pictured in Figure 6. 

Figure 6: Redbold macro VBA code

Please note the following: 

• A macro must begin with a statement that contains “Sub” followed by the name of the macro and (). For example, Sub redbold(). 

• A macro must end with an End Sub statement. 

• Any statements beginning with ‘ are simply comments.  

• All statements containing the word False are redundant and unnecessary. The other statements such as .Fontstyle=”Bold” instruct Excel to change the format of the selected range. 

How to create macros using relative references  

Sometimes you may want the location where your macro performs its magic to depend on the active cell. You can do this with relative references. Here is how to record a macro using relative references:  

1. In the Code Group section of the Developer tab, enable the Use Relative References option.  

Once Use Relative References is enabled, each macro that you create will only contain references to the cell that you selected before recording the macro. During the execution of the macro, all keystrokes will be executed relative to the currently active cell. 

How to create macros using both absolute and relative references 

There may be instances where you need a macro that uses both absolute and relative references. Here is an example. 

Figure 7 shows the sales of three products last year and this year. You want to record a macro that computes each product’s change in sales, places a red border around all your data and ends in cell E1. The macro should work even if there are fewer or more than three products. 

Figure 7: Starting sales data 

Here’s how to create a macro with absolute and relative references:  

1. Select any cell in the workbook.  

2. In the Code Group section of the Developer tab, make sure the Use Relative Reference option is unselected. 

3. Click the Record Macro button. 

4. In the Record Macro dialog box, type “changerel” in the Macro Name box. Enter Ctrl+Shift+R as the shortcut key. Choose to store the macro in the current workbook and click OK.  

5. Click cell D1 and type “change”. This will be the heading for the column that contains the change in sales.  

6. Click cell D2, type the formula “=C2-B2" and press Ctrl+Enter to leave the current cell selected.  

7. Press Ctrl+C to copy the formula in cell D2.  

8. Select cell C2. 

9. Press Ctrl+down arrow to move to the bottom of column C. That way, even if you change the number of products, this step will end at the bottom of your data. 

10. In the Code group on the Developer tab, deselect the Relative References option. 

11. Move one cell to the right and press Ctrl+shift+up arrow to select every filled cell in column D, except for cell D1.  

12. Press Ctrl+V to paste the formula in cell D2 to every cell in column D. That way, even if you change the number of products, this step will copy the formula in cell D2 to the last row of product data. 

13. In the Code group on the Develop tab, deselect the Relative References option. 

14. Press Ctrl+* to select all your data (currently cells A1:D4).  

15. Click the Home tab. Then the Font group. Click the Border button and choose More Options. 

16. The Format Cells dialog box opens with the Border tab displayed. Select the settings shown in Figure 8 and select OK. These settings will create a bold red border around the selected cells. 

Figure 8: Border settings for macro 

17. Click cell E1. Then click the Stop Recording button in the Code group on the Developer tab. 

18. To test the macro, delete the contents of D1:D4. Then, select a cell and press Ctrl+Shift+R to run the macro. The final result is shown in figure 9. If you were to add or delete products from the data set, the macro still works.  

Figure 9: Final results of the macro 

Troubleshooting macros in Excel

Recently, Office 365 made it more difficult to run a macro. If you open a .xlsm file and see the message shown in Figure 10, follow these instructions: 

1. Close the file and find it in File Explorer.
2. Right-click the file and choose Properties.
3. From the bottom of the General Tab check Unblock.

You should now be able to run the Macro.

Figure 10: New message indicating your macro is untrusted

If you’re ready to make your life easier as an accountant, you should further your Microsoft Excel skills. The Becker Microsoft ® Excel Data Analytics Certificate Program can show you how, with hands-on instruction on Excel skills relevant to finance and accounting professionals.  

Learn more and register now!