‘Twas the night before Christmas and all through the house, people were stirring – some with a mouse…
The beauty of paid search is that it’s always on. If your customers are searching, you can always be found. Plus, reporting is real-time (or pretty dang close) and changes can be made at any time to improve performance. There are a few days out of the year, though, (Christmas, for instance) that you might not have quite as much time to spend optimizing as you typically would, which makes it especially important to be as productive as possible. Our next few posts are focused on efficiency, so that you can spend as much time with your family as possible – without sacrificing campaign performance!
Today’s post features a few Excel tricks and formulas that every paid search professional should be familiar with, to work as quickly and efficiently as possible!
Excel Shortcuts
Although they may seem basic, we’d be remiss if we didn’t mention the time savings achieved with Excel shortcuts. We’ve listed a few of the simplest below but there are many more, which can be found, here. (Note: when using a Mac, Command will be used instead of Control)
- Select All: CTRL+A
- Navigate to the end of a column or row: CTRL+Shift+Arrow (using whichever arrow points in the direction that you are trying to get to)
- Select an Entire Column : CTRL+Spacebar
- New Pivot Table: ALT+NV
- Copy: CTRL+C
- Cut: CTRL+X
- Paste: CTRL+V
- Undo: CTRL+Z
- CTRL+0: Hide Selected Columns
- CTRL+9: Hide Selected Rows
- Hold CTRL to select multiple items at once.
Concatenate
The concatenate function is used for combining the content of two or more cells, along with any other text constants. The concatenate function is valuable in several different instances. Personally, among other things, I love the concatenate feature for building out new keyword variations and creating URL builders.
The formula is simple: =Concatenate(CR, CR) where C = Column and R = Row.
Note: Concatenate will not add spaces. If you want to include a space you need to put ,” “, between the two cells you want to combine. If you want to add text, you would likewise add it between quotations marks.
VLookup
The VLookup function is useful for comparing multiple data sets. With this formula, you can select an item in one data set and then ask Excel to return a data point for the same item in a specific data set. For instance, you can take two keyword reports for the same keyword set using two different data ranges and then use the VLookup function to quickly pull all of the data points side-by-side for comparisons. I wrote a post about this a few years ago over at PPC Hero. Although the post is a few years old, the principles still stand. Once you get the hang of this function, you’ll find a million uses for it! It really warrants a whole blog post of its own.
Substitute
The substitute function allows you to replace a character with another. This is another formula that is helpful for creating URL builders, because you can set it up to automatically replace spaces with plus signs, etc.
Learn more about the Substitute function, here.
Pivot Table
Pivot tables are infinitely useful in analyzing performance. Plus, in newer versions of Excel, they are ridiculously easy to set up. If you’ve never set up a Pivot table, check out this YouTube tutorial. Note: it’s 6 minutes but you’ll use and reuse your learnings, so it’s worth it.
Pivot tables are great for summarizing data for quick analyses and comparisons. You can use a pivot table to make quick sense of a large export of raw data. I could link to a bunch of different articles outlining the role of pivot tables in PPC but Melissa Mackey’s already done the legwork on that, so why re-invent the wheel? 😉
Subtotals
Subtotals are a great way to look at summaries of data subsets without taking the time to manually break out each subset.
You’re probably thinking “Wait, why wouldn’t I use a Pivot table to summarize data subsets?” Good question. I usually use subtotals if I need to find a data point about each subset but am not comparing multiple subsets. For instance, subtotals are a great way to quickly see which of your ad groups have less than 2 ads running or more than 3. The perk is that the subtotals are inserted right into the worksheet you are using, so you can quickly and easily delete the data that you don’t need – make any necessary adjustments and re-upload the file.
If you aren’t familiar with Subtotals, check out the how-to, here.
Text-to-Columns, Data Delimited
Have you ever wanted to break apart the contents of a cell into multiple cells? This is where the data delimiter comes in handy. I use this feature most frequently with URLs, it seems like, but there are multiple uses where it can come in handy.
Read more about this feature, here.
Remove Duplicates
This function does exactly as it says. You can select a column, or multiple columns, and then ask Excel to remove all duplicates, leaving you with only unique values.
The ability to remove duplicates is pretty handy. Recently, I needed to check all of my URLs for errors. The account was new to us and we found a few URLs that were 404ing, although AdWords wasn’t disapproving the ads. Since the URLs were at the keyword level and manually tagged, the quickest thing to do was to download all of the data to excel, delimit the data to separate the parameters from the URL and then de-dupe the URLs to remove duplicates. The end result was a short list of URLs to check through.
If you haven’t ever used this function, you should check out the tutorial, here.
Character Counts
With strict character limits in ads, it’s really handy to use the =Len() formula to count the number of characters in a cell. To use this formula, all you have to do is type =Len(CR) into a cell, where C is the column and R is the row for the cell of which you are trying to count the characters.
Conditional Formatting
When you’re working with a big file full of data, you can go cross-eyed trying to scan the whole workbook for anomalies. This is where conditional formatting comes in handy. You can use it to automatically color code your worksheet based upon the parameters that you choose. I like to use it stoplight-style to highlight positive performance in green and poor performance in yellow or red. There are a ton of ways to creatively use conditional formatting and it helps to make sure you are spending the most time on the areas that need the most attention.
Learn more about conditional formatting, here.
This is only a short list of the helpful features in Excel – what Excel formulas and tricks do you use? We’d love to hear about them in the comments below!
