Spreadsheets find their way into all types of environments. I see everyone from corporate finance professionals to creative freelancers use them to bring structure and organization to their work. You might use them for calculations, or you might use them just to log information. In either case, it's hard to debate how useful a spreadsheet tool like Excel can be.
So, what do you do if spreadsheets are a part of your day-to-day work, but you just spend too much time doing the same task over and over? The answer is that you create Microsoft Excel macros, a recorded set of steps that you can play back. Record a macro, then apply it to a new set of data and you'll see it transformed in the same set of recorded steps.
In this tutorial, I'm going to teach you how to create a macro in Excel. You'll learn how to use these macros to record a set of steps and play them back so that you can stop doing repetitive tasks. Let's dive in.
What Are Macros in Excel & What Are Macros For?
As I mentioned earlier, Microsoft Excel macros can help you save a ton of time in working with your data. But to truly benefit from macros, you need to understand the use of macros in Excel.
Let's say that you download data from a website, then import it into Excel. Then, you've got to clean it up and change it from the source, maybe splitting it into columns or otherwise altering it. This takes time, and it's hard to do it exactly the same each time.
That's where macros come into play. If you know what you're doing, you can record your steps and simply play them back on other data.
The benefits of creating a macro include:
- automating the boring, repetitive work that can free up more time for meaningful work
- applying consistent adjustments to your data to ensure that the changes are the same every time
- reducing manual data input errors because the steps are automatically repeated
Read on to find out how to write macros in Excel yourself to take advantage of these benefits and more.
How to Make a Macro in Excel (Watch & Learn)
One of the best ways to learn is by watching. Check out the screencast below to watch me create a simple macro and apply it to data to automate a process.
For an illustrated guide to this process, keep reading the tutorial below. I'll show you an example of how useful macros can be to save time while working with data in Excel.
How to Create a Macro in Excel
Use the steps below to create Microsoft Excel macros:
1. Open the Developer Tab
To create a macro in Excel, find the Developer tab on Excel's ribbon and click on it. If you don't see the Developer menu, you may need to jump into File > Options > Customize Ribbon. Make sure that the Developer box is checked so that it's visible on the ribbon:
Now, click on the Developer tab. On the left side of this menu, you'll see several buttons related to Microsoft Excel macros that we'll use in this tutorial. Make sure you can find the Macros, Record Macro, and Use Relative References buttons:
2. Record Your First Macro
In this example, let's create a macro that helps us to automate a common task in Excel: deleting every other row in a set of data. This isn't a hard task, but it just takes too much time and too many clicks to achieve.
Let's create a macro to automate this. First of all, start with some example data in a spreadsheet. I'll use a list of data that I've created as you can see in the screenshots below.
To start, click on Use Relative References. This feature helps make our macro more flexible. Basically, if we use relative references then a macro will automatically adjust to the cells we want to modify.
I think of this setting as telling Excel how specific it should be with the macro. If you don't turn the feature on, the macro will be very specific with the transformation it applies. For example, if you record a macro with this feature turned off and adjust cells A1, A2, and A3, the macro will always adjust those exact same cells.
But with relative references, Excel takes a less "literal" view on the cell references. It thinks of applying the macro in relative steps, such as adjusting that is "one row below, one column over" and is flexible enough to adapt to the data at hand. I always recommend recording macros with Use Relative References turned on so that your macros stay flexible.
Now, click on Record Macro. You'll see a new box pop up where you can give the macro a name. Give it a useful name so that when you start to record many macros you'll remember what each one does for your data. Also, set a keyboard shortcut:
One important option here is to change the Store macro in drop-down to Personal Macro Workbook.
Now, the macro is watching what we do to learn the steps to follow. Start taking the steps. In my case, I'll start clicking on every other row to capture these steps as a part of my macro.
The reason that I'm clicking on the entire row on the left side is that Microsoft Excel macros are very specific. If I only highlighted three columns in a row, it would only delete those three columns when we run the macro. So instead, I select the entire row. That helps you handle datasets of practically any width:
You'll want to include enough rows so that the macro can help you work quickly in the future. It's okay if you need to play the macro multiple times through to clean up an entire data set.
Once you've highlighted enough rows, right click and delete the rows from the menu. Then, press Stop Recording on the ribbon. The macro has finished recording your steps.
That's it! We're ready to play back the macro and delete rows in an entirely new set of data.
3. Play Back a Macro in Excel
It's time to reap the benefits of our hard work by playing back a macro and applying the same transformations to a new list of data. Here's how to run a macro in Excel.
Important note on using Microsoft Excel macros: They start playing from where you position your mouse cursor. In the Delete Every Other Row macro, it'll start deleting from the row we start on. Keep that in mind so that the macro starts and impacts the set of data that you expect it to.
Now, click on the Macros menu. You'll see the options pop up and a list of any macros you've recorded for use on your data:
Now, Excel plays back the data transformation steps. You'll watch it magically walk through the steps and delete every other row:
The best part of an Excel macro is that they're consistently applied. That helps you avoid errors in your data. Unfortunately, that can be a challenge too. If your source data changes significantly, the macro may not know how to handle the data and apply the adjustments incorrectly.
Spend some time evaluating if a macro in Excel is the right way to handle your data. If you need it to be consistent and repeatable, a macro is perfect. But if the format and structure of the source data is constantly changing, a macro may not work.
This is how Microsoft Excel macros work: press Record, follow a series of steps, and then play it back.
Learn More About Excel
You've just learned how to write macros in Excel, but there's so much more that Excel can do. Spreadsheets can bring a sense of organization and structure to your project. Even better, a macro in Excel can help you automate those repetitive tasks and boring work. Spend some time thinking about what tasks you find yourself doing over and over, then try to create a macro to solve it.
Excel is a powerful tool that has so many purposes. Check out the tutorials below to learn more ways to work with data in Excel and tame your spreadsheets:
How To Convert Columns To Rows In Excel With Power Query
How to Combine Two (Or More) Excel Spreadsheets Together
How to Format Your Excel Spreadsheets (Complete Guide)
What are your favorite tools to save time in Excel? Do you use macros or other data tools to keep your spreadsheet work manageable? Let me know in the comments section below and share a favorite tip with your fellow Envato Tuts+ reader.
