What Is a macro?
A macro is shorthand for a programming term: macroinstruction. In the most basic terms, a macro is a way to automate simple tasks. They can be used in a variety of ways, but most of us will learn to love them as helpful shortcuts in Microsoft Excel.
Why do I need them?
A macro is a set of instructions used to execute repetitive tasks. You can record a set of commands and then play them back with one or two keystrokes.
That means that you can save A LOT of time when doing routine and repetitive tasks. Before learning about macros, most of us just resign ourselves to the fact that there will be certain mundane tasks in Excel that we can’t escape. But once you discover the power of macros, this can all change. If you’ve not used macros before you might be completely confused about how they can help you, so let’s look at a simple example:
Every week you must update a central budget spreadsheet, pulling in numbers from three different spreadsheets, all with different formatting that you then have to fix.
Every time you do this, you start by opening the main spreadsheet. Then you open spreadsheet one, copy over the data onto the central spreadsheet, and format it so it’s in the same style as the rest of the data. And then you do the same with spreadsheets two and three.
It’s a boring, monotonous task that could take 15-30 minutes each time.
But if you were to use a macro, you could do all of this automatically with a few clicks. (And watching a macro do the work for you is almost like watching magic happen!)
How do Macros Work?
When you want to use macros to handle repetitive tasks for you, this first thing you do is ‘record’ a macro. Basically, this means that your computer will record every step you take during a task. Once you’ve recorded all the steps you take, your computer will save this and next time you need to do the same process, you just ‘run’ the macro, and it will simply repeat every step you just did (and we mean every step – when you’re recording macros it’s essential to get your process right or it could cause issues).
It’s a great time saver, and helps reduce mistakes as the macro will simply perform the pre-programmed steps each time so it reduces human error.
That said, because macros simply repeat actions it’s important that whatever steps you follow are always going to be the same. If the data is in a different column on the spreadsheet you copy data from, that could cause havoc. So, while macros are a great time saver and can help decrease errors, they aren’t an excuse to not pay attention to the data you’re working with.
Going Beyond the Basics
There are a lot of innovative things that macros can be used for, but to get the most from them it does require a bit of technical know-how. Our Working with Macros course has been specifically designed for those looking to learn all about macros, and how to use, build and manage them. But the most powerful macros are developed using a programming language called Visual Basic Application (VBA). For those new to coding, getting your head around technical concepts can be tricky at first, and we’d highly recommend that anyone looking to develop their skills in this area, attends a face to face/virtual training session. We run a two-day VBA in Excel course for anyone interested in learning more advanced macros/programming skills. It’s far easier to learn these new skills with in-person support than via digital learning; having someone to ask questions of, and explain new terminology and concepts is the best way to learn these kinds of skills.
If you’d just like a taster of macros to see what they can do, our Excel Module 3 – Data Management Techniques offers a quick intro to macros – we promise they’re not as scary as they seem! Module 3 also covers a range of other Excel skills such as formulas and functions and auditing tools to help when things go wrong. Contact our training team for more information and to book.