There are certain words that seem to scare folks when it comes to Excel. One of those words is “macro”. I think this is a classic case of assuming the worst. We think we have to learn how to use code, logic and a bunch of other stuff like Visual Basic for Applications (VBA). The truth is Excel has a simple feature to get you started. It’s a recorder that does the heavy lifting and converts your actions into an Excel macro. In this tutorial and video, I’ll show one way to get started.
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 by navigating through the worksheet and clicking away, I type my assigned keystrokes that trigger the macro. Excel takes over and deletes the extra columns. It’s a bit similar to how you can type Ctrl + A to select everything in many Windows applications.
Macros can be handy when you have to do repetitive tasks such as my 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. Why reinvent the wheel.
Creating Your First 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 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
As mentioned, there are multiple ways to make a macro. 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.
- Move down to the Status bar, which is the grey bar at the bottom with the word Ready to the left.
- Right-click and select Macro Recording from the Customize Status Bar list.
- You should now see a small icon in the lower left.
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. In the picture above, you can see the macro is not recording because I haven’t clicked on the icon.
Assigning a Macro Name and Shortcut
Once you click the macro recorder, a dialog box appears like the one below with four fields.
 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”.
 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 some other shortcut.
 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 would always be used in the same workbook, I would attach it to that workbook.
Recording Your Actions
After you’ve thought about what the macro should do, it’s time to start. In my example, I’m going to delete a number of 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.
- Scroll down the Status bar and click the Macro Recorder icon.
- Move back to the top of the spreadsheet.
- Click cell A1 to select the first column to delete.
- Press Ctrl and cell D1.
- Keep the Ctrl pressed and select the other columns to delete.
- After the final column, right-click and select Delete
- Return to the Status bar and click off the Macro Recorder.
Testing 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 a number of columns in blue. You can click any cell to remove the highlighting. The test version should have the extra columns removed.
Saving 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. If you just did a regular File | Save, Excel would give you this nifty error message.
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 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:
If you know the source of the workbook, you can click Enable Content Microsoft has a good article on these security settings and how to change them.
Watch the 4 Minute Video
You can also view this video in full-screen mode by using the icon to the right of the Wistia logo.