Want to make your Excel tasks easier and faster? The Excel Macro Recorder can automate your repetitive tasks by recording your actions. Envision the efficiency of having Excel handle 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).
What’s an Excel Macro?
An Excel macro is a series of steps that help you accomplish a task. For example, I help out with our local election. One of my jobs is to do the analysis and create a voter address file. This all started 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 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 them frequently. The beauty of macros is they offer consistency and efficiency. Think about how often you introduce Excel formula errors. And once you learn how to record a macro, you can enhance it using Excel’s macro editor.
How to Record a Macro in Excel
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 macro recorder tutorial, I want to start by adding an icon to Excel’s 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. 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 with four fields appears like the one below.
[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.
Create a Macro to Record 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.
- 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.
Test Your Macro
After you’ve created the macro, you should test it 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
You’ll want to save your work once you’re satisfied with your macro. 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.
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:
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.
Show Me How Video 
Click the image below to open the video and transcript page.
