How Best To Increase Your Excel Power Use

Spreadsheets are essential to the running of any modern business – they collate information, they give usually accurate views of the company’s finances, wage bills, profits, losses, tax liabilities, and so on, and so on, almost ad infinitum. Trying to run any even moderately successful enterprise without a spreadsheet in the 2020s would probably involve an awful lot of paper and a brain like the Riddler on speed. While many larger enterprises use ultra-powerful spreadsheets, and even bespoke spreadsheets to keep the wheels turning and the lights on from week to week, there’s no escaping that the ubiquity of Microsoft in mainstream office culture has put its Office 365 programs – Word, Outlook, and Excel above all – not only onto the desktop of most office workers in the world, but also into their training, their learned instincts, and their understanding of what user-friendly software looks like in the workplace.

Excel, in its bare-bones form, is at least not bad at its job. It takes the fundamental processes of spreadsheets and makes them relatively intuitive, so that with only a little training, people can use it for some of their day-to-day work, keeping SMEs moving, around the world.

But if you only ever used Excel in its bare-bones form, it would soon also reveal some limitations and road-blocks, some moments of “Why can’t we do this?” that would make it increasingly frustrating, rather than increasingly useful.

That’s where macros come into their own.

Macros are a kind of record and playback tool. They allow you to capture a sequence of actions in Excel, and then replicate them anywhere else in the Excel sheet. What that means is that you can automate repetitive work, speed up your day, and minimize the risk of errors creeping in to repeated actions – as can happen easily when people do the same task over and over again.

Macros are to Excel what magic spells are to Harry Potter – quick, easy, ways of doing things that would otherwise be tedious, long-winded, or impossible.

So how do you make the most of macros in your Excel workday?

VBA – the Language of Magic

If we say that macros are a created in a kind of programmable code, there’s a chance most Excel users will be running for the hills before the end of the sentence.

Come back. VBA – Visual Basic for Applications – is not programming in the terrifying sense. It’s programming for people who use Microsoft packages. User-friendly programming, if you like.

Excel VBA is easy to learn, and lets you write statements in English that act as mini-programs (spells, to continue the metaphor), that will help you to automate your Excel processes. Its user Interface allows you to drag and drop controls into your worksheet, and lets you customize the way Excel works to the way you need it to behave. Within the Excel spreadsheet though, all you need to know about VBA is that it’s what powers your ability to record and replicate actions and functions.

How to Write A Macro

Step 1: Enable the Developer Tab

This comes hidden on your Excel ribbon by default to ensure ease of initial use. Right-clicking on the ribbon, anywhere you like, brings up the option to customize the ribbon. Click that, then navigate to Customize The Ribbon, and scroll down until you see “Developer” alongside a checkbox. Click in the checkbox to enable the Developer tab, and click “OK.”

Step 2: Record Your Macro

Once you’ve enabled the Developer tab, you should see it in the ribbon. Click on it, and you’ll see the Code group. Clicking on that will open up options, including “Record Macro.”

Clicking that will give you a dialog box. It will ask you to name your macro – choose something memorable and that describes what you want it to do. After all, macros are tools to make your life easier. You can choose which keys you want to assign the macro to at this stage, and even, if you want, add a little description in case your macro’s name doesn’t simply explain its purpose.

BE AWARE – once you’ve pressed OK at this stage, you’re recording your macro. Everything you do, every button you press after this point will be a part of your macro, so it’s wise to type carefully from here on out.

Now, perform the action you want to be able to rapidly replicate – whether it’s adding a set of title columns, adding color to title columns, both things in sequence, adding a mathematical formula to a particular cell, etc. Anything you can do in standard Excel, you can record as a macro. And the joy is that it doesn’t have to be a single action. As we say, once you’ve begun recording the macro, everything you do after that becomes the macro, you can automate several processes in one macro if you want to.

Step 3: Stop Recording

Once you’ve performed all the functions you want to automate as the macro, go back to the Developer tab and click on “Stop Recording.” You’ve now created the macro you need, and can use it any time you like by pressing the combination of keys you ascribed to the macro.

Step 4: Add Buttons As Required

From the Developer tab, you can also create buttons that take the place of your macro-keys.

Go to Illustrations, select Shapes, and choose any shape you like for your macro button.

Right-click on the shape and select “Edit Text.”

Right-click the shape and select “Assign Macro.” Then select your newly created macro, to assign it to the button. Now you have a one-click magic spell to speed up your day in Excel.

The joyful thing about macros in Excel is that there’s very little limit to what you can do with them. If you create tables, perform equations or sums, add rows and columns, or in fact do anything repeatedly in your use of Excel, recording simple macros to automate those tasks will help speed you through your day, cutting out the tedium and minimizing both repetitive work and the likelihood of human error.