Now that I’ve built up a few posts on other tools, processes, techniques and strategies for becoming more efficient and productive at work, I think it’s time to move onto my favourite tool: Microsoft Excel.
Excel is something that many of us used in school or college at some point, and some of us did better with it than others. I was one of those who did a bit better than others, it seems like all those times I played Battleship style games on computers at school had paid off!
I mention this because it is something that I at the time, and many other people through most of their lives, take for granted. I’ve seen people who can just about manage VLOOKUP formulas put themselves as advanced in Excel on their CV, and employers to agree with that. I just want to let you know, as a reader of my site and therefore hopefully someone interested in better ways of working, being really advanced in Excel is a whole lot more than that.
The real secret to being advanced in Excel is to start using macros. A macro is basically a recorded action or set of actions that can be played over and over like any recording to repeat those actions. Quick example, if you always have to delete particular rows from a set of data and you know they will always be the same rows, you can record a macro doing this and then put a button on the worksheet or assign it to a keyboard shortcut. Next time you have that data set in front of you, run the macro and voila, the rows are deleted and you didn’t even have to pick them out yourself.
The best thing about recording macros in Excel is that it is the first step in learning VBA to automate your tasks in Excel, and one that you will return to over and over to learn new things, as I do on a regular basis.
So what? Does having the computer delete out a few rows for you really increase productivity? There is the benefit that it will always pick the same one, and it will usually do it in a fraction of a second whereas you might take a couple minutes to highlight and delete those rows, but you aren’t going to do much with those extra minutes, right?
Those are good points I have heard from others many times, but that is only barely scratching the surface of what’s really possible.
The Power of VBA
Once you’ve recorded a macro, you can then edit it in the Visual Basic Editor (VBE). This opens up the possibilities of automated working tremendously.
If we go back to my row deleting example, perhaps there is something about the rows that is always the same, but they aren’t always in the same place, which means that your recorded macro wouldn’t be deleting the correct row every time. This is where editing the macro is important and super useful.
With VBA, you don’t have to stick to specifying which rows to delete by their row number, you can instead write a sort of rule that tells it to only delete rows that match a certain criteria. Perhaps the rows to delete always have a particular word in them, or follow a pattern, or have a particular background colour on them? You can write in additional code to have Excel look for those things for you and delete them.
Let’s go one step further and suppose that you want to export a single tab as a new workbook before you email or share it with others? You can also record a macro doing that and then edit it to change the file name or save location based on whatever parameters you like, from the date to the file’s current saved location.
It takes time, but you’ll find that learning how to automate routine/repetitive tasks with VBA to be a massive boost to your productivity. When I started writing this blog a month ago, I was snowed under with project work and was struggling to get my regular reporting done at the same time. I had already automated a great deal, but I added in many more small tweaks, such as a button that copies multiple files into one, and another that shows me numbers related to the one I’m looking at in other sheets. All those little improvements added up, and now, a month later, I’ve managed to get the bulk of my reporting work for a month done in two weeks.
There is also still room for improvement, I now have many small macros dotted around my reports which I run one at a time, I could now create a macro that runs them all for me in the sequence I specify and tie that to a button that basically does all that work for me. There are still checks that I would need to do manually to ensure there aren’t any errors, but a year ago I would spend half a week working on a set of reports that I can now get done in an afternoon.
So that’s my tip for the day, don’t keep following the same routine, let your computer do it for you with a macro!