How To Automate Repetitive Tasks Using Excel

Chances are if you’ve been using Excel for any length of time, you’ve come across repetitive tasks that have to be done but are tedious to complete. Excel has a feature for automating repetitive tasks called Macros. With a macro, you specify a list of commands you want Excel to execute. Once you’ve created the macro, you can simply run it to execute those same commands again.

You may have heard of macros and may be aware that they are created using a programming language called Visual Basic for Applications (VBA). While it’s true that creating complex macros requires some programming knowledge and familiarity with VBA, there’s a feature within Excel that lets you enjoy the benefits of macros without requiring any programming skills. This feature is called the Macro Recorder.

To use the Macro Recorder, you turn it on, perform the steps you want to include in the macro, then stop recording when you’re done. The recorded macro can be set up to be run from a single Excel workbook or from any Excel workbook you are working with.

Before Creating Macros

Before you can record your first macro, you’ll need to add the Developer tab to Excel’s command ribbon if it isn’t currently visible. Follow the steps below to add the Developer tab to the command ribbon:

1. Go to the Backstage View of Excel (where you Save, Open, Print, etc.)
2. Click the “Options” item on the menu.
3. Go to the “Customize Ribbon” section.
4. Check the “Developer” option in the right window.
5. Click “OK”.

Now when you look at your command ribbon, you should see the section labeled “Developer”.

You’ll also see a new icon on the status bar, near the right corner. This is the Record Macro icon.

Now that we have our environment set up let’s see how you can record a very simple macro.

Storing Your Macro

When you record a macro, you are asked where you want to store it. You have three options:

1. This Workbook
2. New Workbook
3. Personal Macro Workbook

Before you decide which option to use, you need to know where you will want to run the macro. If you only need to run the macro in one workbook, the first two options will work. If you want to run the macro from any workbook, you need to choose the third option. If you choose the third option, the first time you choose this option, an Excel file called “Personal.xlsb” will be created for you. Once this file is created, it opens as a hidden workbook every time you open Excel. This allows you to run any macro included in the workbook from any other workbook.

When you save a workbook that contains a macro, you can’t save it as a standard Excel file (*.xlsx); you must save the file as one of these Excel file types:

• Excel Macro-Enabled Worksheet (*.xlsm)
• Excel Binary Workbook (*.xlsb)
• Excel Macro-Enabled Template (*.xltm)

Now that you know a bit about creating macros let’s try recording one.

Creating a Macro

As a straightforward example, let’s say that you had a certain way that you wanted to format your company’s name on all your Excel worksheets. To ensure that the formatting is consistent every time, you could record a macro that applies that formatting to a cell. Then you could run the macro on any cell, and the formatting you specified in the macro would be applied to that cell.

So, let’s say that the formatting you want to be applied to your company name is:

To create a macro that would apply this formatting to a selection, you would:

1. Select a cell to apply the formatting to while recording the macro.
2. Click the “Record Macro” icon.
3. In the “Record Macro” dialog box:
   • Enter a name for your macro, for instance, “Company_Colors”.
   • Select where to store the macro.
   • Enter a description for the macro.
   • Click “OK”.
4. Execute the steps of the macro; in our case, we would set the:
   • Font Family to Bell MT.
   • Font Size to 22.
   • Font Weight to Bold.
   • Font Color to Blue.
5. Click the “Stop Recording” icon.

Some things to keep in mind when working with macros:

• As soon as you click the Record Macro button, everything you do in Excel will be recorded as a part of the macro.
• Macro names can contain:
  • Letters (a-z, A-Z)
  • Numbers (0-9)
  • Underscores (_)
• Macro names cannot start with a number.
• Macro names cannot include any spaces.

Now that we have a macro called Company_Colors, we can select any cell (or multiple cells) within our workbook and run the macro to apply those formatting options to that cell.

Running a Macro

Once you’ve recorded your macro, you’re going to want to run it. For our example of a macro that formats some text, follow these steps to run the macro:

1. Select the cell (or cells) that you want to apply the Company_Colors formatting to.
2. Switch to the “Developer” tab on the command ribbon.
3. Click the “Macros” icon.
4. Select the macro name from the list.
5. Click the “Run” button.

Now, whatever cell you selected before running the macro will have the Company_Colors formatting applied.

Adding a Macro Icon to the Quick Access Toolbar

To save you even more time, if you are going to run a macro frequently, you can add an icon for it to the Quick Access Toolbar and then run the macro from there.

Here’s how to add a macro to the Quick Access Toolbar:

1. Click the Customize Quick Access Toolbar icon.
2. Select the “More Commands…” option.
3. Select the “Macro” category from the “Choose commands from” dropdown.
4. Select the macro name from the list below the Macros category.
5. Click the “Add >>” button.
6. Click “OK”.

See the animated GIF below for a demonstration of the process.

Conclusion

Excel macros are a powerful tool. We looked at a simple example of how to record and run a macro, but this should give you a good starting point and encourage you to create your own macros. Of course, if you want to create more complex macros, you’ll need to learn VBA, but you can simply record your steps for many of the macros you need. Even for complex macros, you may be able to use a recorded macro as a starting point.

So, have you recorded macros for any of your work in Excel? Have you used recorded macros as a starting point for more complex macros? Let me know how you’re using macros in Excel. I’m always looking for new ideas for using them!

For more Excel tips and tricks, check out the following articles:

25 Excel Navigation and Selection Shortcuts You May Not Know

5 Excel Shortcuts to Drastically Speed Up Data Entry

3 Ways Excel Can Help You Track Your Freelance Income and Expenses

Top Excel Features Every Freelancer Needs To Know

---

Brenda Keller is a software coach who helps people feel comfortable with and learn how to use software. She has been an Excel user since it was first introduced in 1985 and has taught the program in various settings. Her new Microsoft Excel: Quick Start course was designed to help those new to Excel or those wanting a refresher to get a handle on Excel’s key features quickly. Check out her course on Udemy here or view her YouTube channel here to get more help with Excel. To learn more about Brenda Keller, check out her official website here.