To Excel! The cause of, and solution to, all of my work-related problems.
If you’re anything like me, you know that data in the PPC world is endless. Google reports, lead sheets, call forms, spend charts, daily reports, weekly reports, monthly reports! This list never ends. Enter Excel. Excel is everywhere and everything. Nearly every business and government in the world uses it every day. Though today, we are not here to talk about the Excel world we live in, but rather one specific tool that I find myself using time and time again. That tool is the Macro.
But what, you ask, can the Macro do for me? Well my friend, prepare yourself for the most amazing revelation you’ve had since your fingers first felt the soft click of a keyboard.
The Excel Macro can save you time.
In this post, we’ll talk about the steps necessary to get your macro set up, where it is best applied in your daily grind, and how it saves time. The example macro is one that sorts and filters a list of leads that is emailed to me daily. Before we dive in, and if you’re not familiar with exactly what a macro is, this awesome post should catch you up to speed.
Setting Up The Macro
To record a macro in Excel, first we need to enable the developer tab. Navigate to Excel Settings or Preferences (depending on whether you’re on mac or PC), select Ribbon and Toolbar, then check the “Developer” tab under the Customization Ribbon.
Once we’ve got the Developer tab installed, we’re ready to set up a macro.
We first need to know, in general terms, what these three buttons in the Developer tab do. Editor allows you to edit the Macro’s code (we’ll get to that), Macros shows you a list of available macros that are runnable in the workbook, and Record starts the process of making the macro.
As mentioned, Macros need to be enabled to be run in workbooks. To accomplish this, there are two routs you can follow.
You can either record the macro to the excel sheet you are working in and save it to the same workbook (useful if it’s a sheet you visit often and update with new data) or you can save it to a master sheet that houses all your Macros (useful if you are dealing with new files and sheets often). I use the latter and have an Excel file on my desktop labeled MACROS that houses over a dozen different sequences.
Now that we know where our labors of love will live, we are ready to record.
Open the sheet you intend to optimize and press Record in the Developer tab. Once it’s recording, every action your take (select a cell, press the enter key, click the mouse) is added to the sequence. This can be daunting, and if you’re anything like me, you’ll be afraid of making a mistake. Don’t worry too much, as everything we do can be edited later or we can start from scratch whenever we want.
Recording and When It Is Useful
Now when recording your actions, it helps to think about the differences between what the machine is seeing you do and what you are mentally accomplishing. For example, when I select cells A3-A27, I know I am highlighting all the data in this column. The Excel Macro recorder only knows that I selected those specific cells, so that is what it will select when this Macro is run again tomorrow.
For data that needs to be selected, try control+shift+arrow key instead of using your cursor to select the cells. That way, the recording knows to look for everything until a blank cell, not just a specific set of cells.
How Macros Save You Time
The Macro can be a bit daunting to build and troubleshoot, but once it’s running smoothly and performing well, it’s a game changer. The daily sorting and compiling of data that I need to perform used to take me over an hour. Even after I got better at Excel commands and navigating the interface with my keyboard, I was only able to shave it down to 40 minutes.
Now, with a Macro, that daily task takes me 3 minutes. The most time-consuming part of the process is Excel opening and saving the sheet. The computer makes no errors, is always consistent, and works as quickly as the processor can “think”. That is, if the Macro has been set up properly.
If my own experience is any indicator of a trend, then it would behoove you to spot-check the Macro’s work now and again as sometimes a little bit of bad data slips through the cracks. Furthermore, setting up a Macro to encompass any curve balls you throw at it takes trial and error, and most likely won’t come from a first iteration.
Closing Thoughts
The Macro is a tool that has almost replaced my need to spend hours sorting data. It is smart (at least, as smart as you), fast, and efficient. So, I implore you, look into automating some of your daily tasks. Find the best way to optimize, think about where a quick shortcut could save time, and above all, try to look busy once your job is nothing but Macros.
