Automation 101: Macros

May 02, 2022

When we think about automation or automating tasks we either think of a magic solution that will do our work and let us sip margaritas while a computer does our job for us, or, if your experience has been bad, you might think of automation as too complex of a solution for a simple issue that constantly breaks. The reality is that automation can come in many forms, it can be as complex as creating a new communication system to as simple as creating filters that will optimize your time.

There are many simple tools that can help create these solutions from basic excel functions and formulas to paid software like supermetrics.

A great tool to help you do this is Excel’s Macros. Don’t worry, no coding is coming up. 

We all have recurring tasks that have the same processes, maybe we download the file from Google, we delete a certain number of rows, then we create a filter, order by a specific row and then keep on doing the same steps exactly like we did the last time.“Exactly” is an important word here

Macros allow us to automate this process, we teach Excel certain specific steps and Excel will repeat them exactly on any given file. So if your file is coming from a different source or your columns are in a different order your Macro will not do the job correctly. 

The first step to access macros is to activate the developer tab in excel. To do so, you will have to go to File / Options, then from the right column click on Customize Ribbon and under Main Tabs check the Developer box.

Now you will have the Developer tab in our menu! to create our first Macro, click on “Record Macro” under the Developer Tab.

This will open up the “Record Macro” popup, this simple menu contains four fields:

  • Macro name: Here you insert the macro’s name, it has to start with a letter and have no spaces, I suggest using the underscore for space but camel casing is another option to separate words.
  • Shortcut key: If this task is something you do daily you can assign it a shortcut (Ctrl + one key of the keyboard)
  • Store macro in: This is the important part. You will be given three possibilities of where to store the macro, if it’s something you will do repeatedly I suggest you store it on the Personal Macro Workbook, as you will be able to summon the Macro when you open different files. If it’s something you do on a single file that you always work on you can save it there as it is a file unique Macro.
  • Description: A Short description of what the Macro does

Once you have finished setting up you press “ok” and you will see that the “Record Macro” button on the Developer tab has changed to “Stop Recording”, this shows that Excel is recording your moves.

If you hide a column, delete a row, format numbers or do a Vlookup, Excel is recording. Once you are done with your process, click on Stop Recording.

Now, if you press on the “Macros” button next to the “Record Macro” button a new popup menu will appear showing your previously recorded Macros, here you can run, edit or delete your Macros.

The next step is to close the excel file and a new popup will appear press Save so the Macro is recorded on your system to be reused.