Useful Excel Macros & Other Hacks To Save Time

One of the primary differences between agency life and working in-house is the number of resources available. For example, in a typical agency environment, you’d have anywhere between 2-6 people working on an account. However, in my in-house role, I am the only SEO and SEM on the marketing team- so the level of output coming from a bigger team to just me was an adjustment. I want things to go from idea to implementation quickly so I can test theories and optimize both programs.

• Feature 1: Pivot Table
• Feature 2: Macros
• Feature 3: Concatenate (Formula)

Thankfully, I use Excel and have some tried and true features that save me well over 15 hours each week in time that would otherwise be spent on manual processes. Paired with Accelo, and the efficiency of my work has vastly improved. Here are my top three

Pivot Table

There are a plethora of reasons to use pivot tables. If you have a data set and a need to compare, pivot tables (and charts) are the easiest way to go about it. For my purposes, I want to answer the following questions about our AdWords program:

• What keywords drive conversions?
• Where in the world do my conversions come from?
• What days are driving conversions?

These three questions need answering as soon as possible when taking over an AdWords account - to start to build a strategy and keep moving forward. The best way I have found to get these answers is to export keyword-level data (be sure you are filtering by ALL and not All Enabled or All But Removed). Export as an Excel file and create a pivot table. This data will give you conversions by keyword.

Navigating over to the Dimensions tab will help answer the other two questions. Download the data in the Time>Day of the Week tab and the User Location tab.

Add each data set in a tab and be sure you use the pivot chart all in one tab so you can quickly scroll through findings. The best thing about utilizing pivot tables and charts is the ease of adding and deleting data from these views. My next steps would be analyzing clicks and conversions relative to ad position which can be added to our analysis and thrown into a chart for data visualization.

Bonus tip: Conditional formatting will help you easily spot high and low outliers in your data for quick insights!

Macros

What is a macro?

A macro can be defined as the recording of a series of tasks. It's the simplest form of automation – just show your software program the steps you take to get something done, and the software will follow along. When used right, macros can save you hours by automating simple, repetitive processes.

Non-programmers or coders might shy away from Visual Basic for Applications (VBA) and creating macros, but the process is extremely streamlined from the older versions of Excel.  You no longer need to be able to see the Matrix in order to use macros!

So how do we use macros? In our display ads, we have both managed and automated placements. Before our implementation of macros, I was trying to sift through 2,000 different placements to make sure we were on sites that were going to be relevant to our market.

Despite excluding categories like dating, gaming, and kids, we were still getting a lot of erroneous placements. So, our automatic placements need daily pruning.  We developed a list of exclusion keywords and created a macro that would use the Find And Replace feature to highlight every placement that had one of the keywords on the list. Now we can prune our lists easily by downloading the placements report and pulling the data into our workbook with the saved macro.

Here's how you do it:

Step one, Record Macro (located in the View tab in the top menu ribbon).

Step two, Find and Replace keyword list with highlighted versions. Note: if you don't know how to add formatting, click 'Options >>' in the Find and Replace box and you will see the options below. 

Step three, after you have gone through your negative keywords list, end the recording.

Be sure to save your workbook in a macro-enabled format, I use either .xlsm or .xltm.

Concatenate (formula)

The ability to analyze what happens when users get to your website is key for optimization and success. But how do you keep tabs on all of your tracking parameters? Are they all in one spot? Or more like a ‘fly by the seat of your pants’ kind of thing? Personally, I like my pants and my tracking too much to not have a set processes.

Keeping a running list of your what your UTMs are for tracking can be extremely helpful to see when a tracking parameter was created, for what campaign, and to keep the naming conventions all the same. With Excel, you can create a workbook that can generate your UTMs for your entire marketing program by using the Concatenate function.

These are a few of my favorite time-saving Excel functions. With macros alone, you can save a ton of time scrubbing keyword lists and updating reports that stem from a data set. This blog post only scratches the surface of how Excel can be used to save time.

Have a favorite function I didn’t mention? Let me know in the comments below.