If you are reading this post, it’s because you know that Excel saves precious time when analyzing PPC data and you want to stay up-to-date on the latest and greatest Excel tricks.
I feel the same way! The problem is that we often forget to surf the digital marketing blogs for new Excel tips because we end up spending any training time we have learning about the newest PPC product updates.
I have been overdue for a self-taught Excel training for a while, so I recently decided to take the time to look for a few new “Excel for PPC” tools and tricks. Fortunately, I did not have to go far. I knew that our Hanapin team had created several Excel resources in the past, but now they are all conveniently located on one blog page. While this mention is very clearly a shameless plug, I honestly learned new or forgotten Excel tips that are going to save me time with my PPC analysis. (BONUS: we also included some of our most popular AdWords resources because Excel and AdWords are nearly inseparable).
In this blog post, I am going to share four Excel tricks for PPC that I hope you can also use to save yourself time.
1) Grouping Average Position Data In A Pivot Table
“Group and Outline” is one of the forgotten features of Excel pivot tables. In The Complete Guide To Using Excel for PPC, there’s a great example of how we can use the Group option of pivot tables to condense average position data into ranges of positions for a higher-level analysis. Another example that I have found useful is pivoting “hour of day” data from AdWords and then grouping the hours morning, afternoon, and night (8-hour increments). This is a simple way to show a client or manager AdWords metrics as they relate to customers searching for the company or product before, during, or after the customer service or call center hours are occurring. Below, you can see screenshots of both the average position and the hour of day grouped pivots.
2) Quick Keyword Tool
Don’t you love it when someone hands you an Excel function that looks super complex, but you don’t really have to learn it if you can copy and paste it into your own document? Yeah, me too. This next formula is one of those. Here it is:
=IF(ROW(B1)>COUNTA(B:B)*COUNTA(D:D),””,LOWER(INDEX(B:B,IF(MOD(ROW(B1),COUNTA(D:D))=0,ROW(B1)/COUNTA(D:D),INT(ROW(B1)/COUNTA(D:D))+1)))&” “&LOWER(INDEX(D:D,IF(MOD(ROW(B1),COUNTA(D:D))=0,COUNTA(D:D),MOD(ROW(D1),COUNTA(D:D))))))
That’s a LOT of nested functions. Again, you don’t need to be able to rebuild this one yourself. You can take it as is and use it for quickly creating keywords based on categories and modifiers. Let’s say your company sells trendy, yet thrifty furniture. Your categories and keywords might be as follows:
Then, you simply copy and paste the formula from above into your Excel sheet and drag/copy the calculation down the column until all variations of your categories + modifiers are generated. It’s a HUGE time saver for new campaign builds.
Another example can be found in The Complete Guide To Using Excel for PPC.
3) Custom Shortcuts
Simple, yet effective. That’s the best way to describe this Excel trick. There are tools in the Excel ribbon that we use all the time – so often that we could shave minutes off our day-to-day Excel analysis if we only had keyboard shortcuts for the buttons we use frequently. Below are the steps for creating custom shortcuts in Excel. Note: the following steps only apply to Mac OSX.
The following example creates a keyboard shortcut using CTRL+Q for “trace precedents.”
To add this keyboard shortcut simply do the following:
- Open System Preferences
- Select Keyboard
- Select the Shortcuts tab
- Click App Shortcuts on the left
- Click the + button to add a new one
- For “Application” choose “Microsoft Excel”
- For “Menu Title” enter “Trace Precedents”
- For “Keyboard Shortcut” press CTRL+Q
- Click Add
That’s it. CTRL+Q will now launch Trace Precedents in Excel 2016 for Mac.
I did not find a similar option for the latest versions of Excel for Windows. However, I did find this incredibly thorough post on powerspreadsheets.com that has tons of existing keyboard shortcuts as well as step-by-steps for learning those shortcuts.
4) Wildcards
This is NOT a poker reference.
Wildcards work in a variety of excel functions, several of which are described in Trick #33 of 34 Excel Tricks To Save Your PPC Life. Wildcards allow you to account for unknown characters within numbers or text. One example not mentioned in Trick #33 is using wildcards within “control F” searches. In PPC analysis, you might be turning an existing location-based campaign into a new campaign for a separate location. You’re nearly 100% certain that you changed all of the ad copy references from say, Arizona to New Mexico, but you want to be sure.
Final Thoughts
There are SO many other Excel tips and tricks in our blog as well as in other digital marketing blogs. The tips listed above stuck out to me because I have not used them recently or at all, but I can easily think of several uses for them. I encourage you to do your own deep-dive to update your Excel skill set. You might find that you’ve read most of the posts before, but you could still stumble across a few forgotten tools like I did.
