How To Automate Repetitive Tasks In Excel

There’s nothing more frustrating to me than repeating a task over and over again. It just feels inefficient and like a waste of time. Luckily, computers are ideal for this and it’s easy to atuomate repetitive tasks in Excel. Let’s take a look at an example of Excel automation.

How to Automate Repetitive Tasks In Excel

If you’ve worked with Excel long enough you’ve probably heard the term, macro. But what is a macro?

What is a Macro?

According to Wikipedia, a macro is a rule, or set of rules, which, when applied to an input, provide an output. What?

Think of it like this, it’s a procedure. Like a recipe. Think of a recipe for a grilled cheese sandwich as a macro. When you supply the ingredients of bread, butter, and cheese (the inputs), the recipe turns those inputs into a grilled cheese sandwich (the output). The recipe in this example is like a “macro”, a predetermined sequence of steps to take.

Another way to describe a macro is like a little computer program. In Excel, macros are written in a programming language called Visual Basic for Applications, or VBA.

What is VBA?

VBA is an acronym for Visual Basic for Applications. VBA is a programming language basic on the language Visual Basic but it’s slightly different and can interact with applications such as Excel, Word, Access, and Outlook. Macros in Excel are actually little pieces of code written in VBA.

How to Create a Macro in Excel

There are two main ways to create a macro in Excel, record a macro or write a macro in VBA. When you’re first starting out you’ll probably “record a macro”. If you start using macros more heavily and/or are a VBA developer, you may write macros from scratch. For the purposes of this tutorial, we’ll assume you’re just starting out and don’t know how to write VBA code so we’ll walk you through “recording a macro”.

How to Record a Macro in Excel

Step 1: Make Sure the Developer Tab is Visible in Excel

The functions we’ll use to record a macro are in the ‘Developer’ part of the Excel menu. For some, this tab may not be visible. So, let’s first make sure you have access to the ‘developer’ tab and it’s functions. When you open Excel, you should see menu options across the top of the screen. Yours may differ but common menu options will be: File, Home, Insert, Draw, Page Layout, Formulas, Data, Review, View, Help. If you see ‘Developer’ as one of your options, great, proceed to step 2.

If you don’t see a Developer in your menu, right click on somewhere on the menu and click ‘Customize the Ribbon’. In the window that opens, on the right it should say ‘Customize the Ribbon’ with a list below and some items checked. Look for an item listed ‘Developer’ and check the box next to it. Then click OK. Confirm that you now see ‘Developer’ in the menu.

Step 2: Record a Macro in Excel

Let’s start with a small task that you want to repeat. Let’s say we have text we want to move one cell to the right and make it bold, as we have done below.

We will create a macro for these steps. Click on the Developer tab, then click ‘Use Relative References’, and finally click on ‘Record Macro’.

Note that we will opt to use relative references for this macro. This is an option you may or may not want to use, depending on what you’re trying to accomplish. Notice when we record the macro in the example below we are cutting content from cell B3 and pasting it into cell C3. If we did not use relative references for this macro, anytime we ran the macro, the content would be cut from the current cell and pasted into C3. We don’t want that. We wanted it pasted one cell to the right of the cell we are cutting from. C3, relative to B3, is one cell to the right. That’s what we want to repeat. So we turned on the ‘Use Relative References’ feature for this macro.

In the window that opens you have the option to do a few things. You can create a name for the macro. If you don’t, it will default to Macro1, or similar. You can also create a shortcut for the macro. In our example we’ll create a shortcut of ctrl-g. So, if we hold the control key down and click the ‘g’ key, our macro will run. You can also add a description so other users (or your future self) can understand what the macro does. Once you’re completed this to your liking, click Ok to start recording your macro.

Once the recording starts, just perfom the tasks that you don’t want to repeat anymore. Do everything, in the correct sequence, only once. Then go back to the ‘Developer’ tab and click ‘Stop Recording’. This will mark the end of your macro.

Step 3: Run A Macro In Excel (Test Your Macro!)

We will want to run a macro in Excel to test our newly created macro. First, click on the cell with the text you’d like to move to the right. If you created a shortcut for your macro, you can run it using that (ie: ctrl-g in our example above). Or, to run a macro in Excel, go to the ‘Developer’ tab, and click ‘Macros’. In the new window that opens, find the name you used for your macro, click it, and click the ‘Run’ button.

How to Write a Macro in VBA

If you’re a more advanced user or have experience with coding you may want to try and write a custom macro to automate repetitive tasks in Excel. To do so, you’ll need to access the area where Excel stores the VBA code. To do this, you’ll click on the ‘Developer’ tab of the menu, and click ‘Visual Basic’. This will open the window where you can store modules, macros, and classes for more advanced Excel automation tasks.

Writing VBA macros is a more in depth topic than we’ll be covering in this article. There are tutorials online if you want to get started learning VBA. Or, if you need more advanced automation and want some help, contact me. Logically Tech provides various Excel support services including Excel automation services via custom VBA programming.