Practical Tips #2: Excel Macros

It's been a long time since your GCSE in IT.  If you're not a spreadsheet expert, and if you're anything like me, you need constant refreshers of a few key tricks when using Microsoft Excel.  One of the most handy is being able to effectively use macros  in order to automate processes and actions that you regularly perform. Doing this is a bit of a magic trick because usually, to write a macro, you'd have to learn a whole bunch of code.  The example I'll use here is a very simple one, but they can be used for much more complex processes,  macros really come in use when you are repeatedly doing the same manual task (or series of tasks), 

Here I have an example of a spreadsheet (you can click on the images to enlarge them), let's say you're regularly checking how much (on average), you're spending on salaries.  The first step is to go to the view tab, and click on Macros, and select 'record Macros.  You can name the macro so that you won't mix it up with others, and give it a keyboard shortcut and a description if you so wish.

Once you've started recording your macro, it's important to remember that every action you take is recorded, so if you make a mistake, it's best to end recording (by clicking the button in the drop down menu for macros).

In order for me to get an average salary for each department on my spreadsheet, I go to the data tab, and select subtotal, making sure the changes and functions are correct.  Once I've pressed OK, I get a lovely list of averages. At this point you need to remember to click on 'stop recording', in the drop down menu from the 'Macros' button.  Voila, you now have a handy macro, that can be used again and again!  

Once you've mastered the basics, It's possible to use edit VBA (the code that sits behind the Macros) to do some more complicated things or to make it more bespoke to what you need to do.  Of course these kinds of operations are much more complicated, though a quick google search will bring up plenty of advice on how to begin doing that.

Of course this is only a simple tip, but if you find yourself without enough time to learn or work through this kind of stuff, then why not book in a free consultation with us?  At Core Insights, we can provide a wide range of training on how you and your team can improve the way you manage your data.