Simplifying the Excel Macro Recorder

Certain words seem to scare folks when it comes to Excel. One is “macro.” But what if I told you Excel could capture your keystrokes and clicks and turn the sequence into a macro? The feature is called the Excel Macro Recorder, and it does the heavy lifting for you. In this tutorial and video, I’ll show how to record a macro in Excel. And you don’t need to know Visual Basic for Applications (VBA code).

This tutorial includes 2 resources at the end:

  • A sample Excel workbook
  • A 4-minute video

What’s an Excel Macro?

An Excel macro is a series of defined steps that help you accomplish a task. To give an example, I volunteered to help out with our local election. One of my jobs is to do the data analysis and create a voter address file. This all starts with me getting a huge data file from the county. The problem is I don’t need many of the columns from their file.

Instead of manually deleting the extra columns each time I get a new file, I type my assigned keystrokes that trigger the Excel macro. Then, Excel takes over and deletes the extra columns. It’s a bit similar but much more powerful than creating your own Word keyboard shortcuts.

A recorded macro can be useful when you have to do repetitive tasks such as deleting the non-essential columns. However, they can also be beneficial if you have a complicated process that requires many steps, even if you don’t use it frequently. The beauty of macros is they offer consistency and efficiency. Think about how often you introduce Excel formula errors. And once learn how to record a macro, you can enhance it using Excel’s macro editor.

How to Record an Excel Macro

There are several ways to create the macro. When you start learning, I think the easiest way is to use the Excel Macro Recorder. Like many keystroke recorders, it silently watches and saves your actions until you stop recording. From there, you can save the macro and run it when needed.

The key to this process is thinking about the problem you’re trying to solve before recording. In my case with the voter file, I know there are certain columns I always need to delete. For example, columns containing:

  • Voter ID
  • Middle Name
  • Party Affiliation
  • Registration Dates

Using the Macro Recorder

For this tutorial, I want to start by adding an icon to the status bar. Once you’re more familiar with the process, you can explore other options like the Developer tab.

  1. Move down to the Status bar, which is the grey bar at the bottom with the word Ready to the left.
  2. Right-click and select Macro Recording from the Customize Status Bar list.
Excel customize status bar
Adding Macro Recording option to Status bar
  1. You should now see a small icon in the lower left.
Excel macro recording icon showing on status bar
Excel Macro Recording icon

You might think of this macro icon as an ON/OFF switch. One nice touch is that Excel changes the icon face and hover message to let you know you’re in Record mode. You can see the macro is not recording in the picture because I haven’t clicked the icon.

Assign a Macro Name and Shortcut Key

Once you click the macro recorder, a dialog box appears like the one below with four fields.

Excel Record Macro dialog.
Describing the macro

[1] The Macro name field is required. You can’t use spaces or start with a number. However, you can use _ and -. Some people may prefer “camel casing.” This is where you string the words together and have each new word start with a capital letter. For example, “DeleteExtraVoterColumns”.

[2] The Shortcut key field is optional and allows you to assign a keyboard combination that triggers the macro. I tend to add in the Shift key so I don’t conflict with other shortcuts.

[3] The Store Macro in field is required. There are various places you can assign macros based on their intent. For example, the macro I’ve created here will be used for new files I receive, so I want to have the macro in my Personal Macro Workbook. However, if I were creating a macro that I would always use in the same workbook, I would attach it to that workbook.

Record Your Spreadsheet Actions

After you’ve thought about what the macro should do, it’s time to start. In my example, I’m going to delete some columns I don’t need, such as A, D, J, K, L, and N. I’ve put a link to this Excel workbook at the bottom of the tutorial so you can practice. And yes, this is a small file with fictional names.

  1. Scroll down the Status bar and click the Macro Recorder icon.
  2. Move back to the top of the spreadsheet.
  3. Click cell A1 to select the first column to delete.
  4. Press Ctrl and cell D1.
  5. Keep the Ctrl pressed and select the other columns to delete.
  6. After the final column, right-click and select Delete.
  7. Return to the Status bar and click off the Macro Recorder.

Test Your Macro

After you’ve created the macro, you should test to see that it works as you expected. If you download the sample spreadsheet, you’ll see that I have an extra Tab called excel-macro-test. You can use that worksheet to test the macro you’ve created.

You can invoke the macro using the shortcut keys you assigned. Once it finishes, you should see some columns in blue. You can click any cell to remove the highlighting. The test version should have the extra columns removed.

Save the Macro and File

Once you’re satisfied with your macro, you’ll want to save your work. Most likely, you’ll save your macro with your workbook. When you do this, you need to use a different file extension. Normally when you save your work, it’s a .xlsx file. However, workbooks that have attached macros use the .xlsm file format. So if you just did a regular File | Save, Excel would give you this error message.

Excel error message for macro
Oops. You need to save in a different format

The workaround is pretty simple. Click No and then save the file as an Excel-Macro Enabled Workbook (*.xlsm).

Take Note of Security Warnings

Based on your program settings, you may encounter scenarios where your macros don’t run. Over the years, Microsoft has put in security measures to protect you. For example, you might encounter a notification bar like the following:

Excel security notification bar
Excel Security Notification bar

If you know the workbook source, you can click Enable Content Microsoft has a good article on these security settings and how to change them.

Watch Excel Recorder Video

You can also view this video in full-screen mode by using the icon to the right of the Wistia logo.

Additional Resources