Excel Techniques – How to Use Macros to Speed ​​Up Your Day

0

Spreadsheets are essential to the operation of any modern business – they gather information, they usually give accurate views of company finances, payroll, profit, loss, tax liabilities, etc., and so on, almost ad infinitum. Trying to run even a moderately successful business without a spreadsheet in the 2020s would likely involve a lot of paper and a brain like the Riddler on speed. While many large companies use ultra-powerful spreadsheets, and even bespoke spreadsheets to keep the wheels turning and the lights on from week to week, it’s undeniable that Microsoft’s ubiquity in office culture traditional has put its Office 365 programs – Word, Outlook, and Excel above all else – not only on the desks of most office workers around the world, but also in their training, their learned instincts, and their understanding of what user-friendly software in the workplace.

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

But if you only used Excel in its simplest form, it would soon also reveal limitations and obstacles, moments of “Why can’t we do this?” that would make him more and more frustrating, rather than more and more useful.

This is where macros come into their own.

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

Macros are to Excel what magic spells are to Harry Potter – quick, easy ways to do things that would otherwise be tedious, endless, or impossible.

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

VBA – the language of magic

If we say macros are created in some kind of programmable code, there is a chance that most Excel users will run for the hills before the sentence is finished.

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

Excel VBA is easy to learn and lets you write instructions in English that act like mini-programs (spells, to continue the metaphor), which will help you automate your Excel processes. Its user interface lets you drag and drop controls into your spreadsheet and lets you customize how Excel works to suit your needs. In the Excel spreadsheet, all you need to know about VBA is that it’s what lets you record and replicate actions and functions.

How to write a macro

Step 1: Enable the Developer tab

This is hidden on your Excel Ribbon by default to ensure initial ease of use. Right-clicking on the ribbon, anywhere you want, displays the customize ribbon option. Click on it, then go to Customize Ribbon and scroll down until you see “Developer” next to a checkbox. Click in the checkbox to enable the Developer tab, then click “OK”.

Step 2: Record your macro

Once you activate the Developer tab, you should see it in the ribbon. Click on it and you will see the Code group. Clicking it will open options including “Record Macro”.

By clicking on it, you will get 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 that make your life easier. You can choose which keys you want to assign the macro to at this point, and even, if you want, add a little description in case the name of your macro doesn’t simply explain its purpose.

CAUTION – once you press OK at this point, you are saving your macro. Everything you do, every button you press after this point will be part of your macro, so it’s wise to type carefully from now on.

Now do the action you want to be able to replicate quickly – whether it’s adding a set of title columns, adding color to the title columns, both things in order, add a mathematical formula to a particular cell, etc. Anything you can do in standard Excel, you can save as a macro. And the joy is that it doesn’t have to be a single action. As we say, once you start recording the macro, everything you do next bECOMES the macro, you can automate multiple processes in a macro if you want.

Step 3: stop recording

Once you’ve performed all the functions you want to automate as a macro, go back to the Developer tab and click “Stop Recording”. You have now created the macro you need and can use it at any time by pressing the key combination you assigned to the macro.

Step 4: Add buttons as needed

From the Developer tab, you can also create buttons that override your macro keys.

Go to Illustrations, select Shapes and choose the shape you want for your macro button.

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

Right-click on 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 good thing about macros in Excel is that there are very few limits to what you can do with them. If you create tables, perform equations or sums, add rows and columns, or do anything repeatedly in your use of Excel, recording simple macros to automate these tasks will help speed you up. your day, reducing boredom and minimizing repetitive tasks. labor and the likelihood of human error.

Share.

About Author

Comments are closed.