40 Excel tricks for PPC pros

In the PPC world, Excel is a given. They go hand in hand. Like peanut butter and jelly. Not only will you become better at analyzing account performance, but you’ll also save a lot of precious time by becoming savvy with Excel and all of it’s features, formulas, and shortcuts. This whitepaper will list out handy Excel features and tricks, as well as examples of how to use them for your PPC endeavors.

TRICK #1 — PIVOT TABLES

Pivot Tables might be the most important Excel feature that gets used daily in PPC work.

Simply click that button and Excel will put

your data into a pivot table. Then you can start manipulating data in any way that you choose.

Use It For:

• General Account analysis

Helpful Hint: Use calculated fields for metrics like cost/conversion, CTR, and conversion rates.

TRICK #2 — CONDITIONAL FORMATTING – HEAT MAPPING

Conditional formatting is great to get quick insights from a set of data. There are multiple pre-set rules that you can use, or you can create a custom rule.

Here we show a heat map of conversion metrics by time of day. By applying the conditional formatting, we can easily pick out any times that aren’t performing well and set a bid adjustment accordingly.

If you don’t want to highlight the entire cell in one color, choose the icon option.

Use It For:

• Dayparting
• Visualizing data

TRICK #3 — VLOOKUP

I use VLOOKUP very frequently to (you guessed it) look up a value on a separate sheet in my workbook. This is great for compiling data from two different reports.

TRICK #4 — INDEX MATCH

Index match is another way to look up data using both the Index function and the Match function nested together. The perk of using Index Match vs. VOOKUP, is that Index Match can read left or right, while VLOOKUP can only read to the right.

In this example, the function listed would return “yellow”.

TRICK #5 — FIND & REPLACE

Use It For:

• Making keywords BMM (Find “ “ and Replace with “ +”)
• Keyword buildouts
• Changing URLs

Helpful Hint: Command+F = Find function. From there, you can replace what you’re looking for with something else.

TRICK #6 — LENGTH

Have you ever been writing ad copy and just can’t quite get the right number of characters in each line? Don’t fret; you can use the length function to calculate how many characters are in a cell.

Use It For:

• Ad writing

Helpful hint: Use conditional formatting (listed above) to automatically highlight cells that are over the allotted character count.

TRICK #7 — TEXT-TO-COLUMN

Text-to-column is a neat little tool to help you separate data that’s contained in one column. Two of the most practical uses I have found for this function are manipulating destination URLs and splitting campaign names.

For example, if I structure my account like this: State – Theme – Match Type, then I can use Text-to-column to easily pivot my data by one of the three categories.

TRICK #8 — FORMAT PAINTER

You’ve probably wondered what that little paintbrush icon is up in your toolbar in Excel. This tool allows you to copy the formatting from one (or many) cells and copy it to another.

Helpful hint: Double-click the icon and you can paste the formatting on multiple selections.

TRICK #9 — FREEZE PANES

We’ve all been in a situation where you have a huge Excel file with tons of info. The problem is, you can’t scroll down and still see your headers. All you have to do is Freeze your header row and voila, it’ll stick! No more frantic scrolling back to the top to remind yourself what those numbers mean.

Use It For:

• Viewing large amounts of data
• Making reports more client friendly

TRICK #10 — CUSTOM SHORTCUTS

Do you love using shortcuts? Sometimes the preset shortcuts don’t work for you, or you want to create a shortcut for a feature that doesn’t have one by default.

Navigate to: Tools > Customize Keyboard, then scroll through the various categories of available shortcuts.

Use it for:

• Freeze panes, format painter

TRICK #11 — CHARTS & GRAPHS

Charts and Graphs are great to show to clients, and can be very helpful in recognizing trends to figure out what’s happening in your account. Select the data you want to represent in a graph or chart, and select the chart layout you want.

Use it for:

• Visualizing data
• Creating client-facing reports
• Analyzing your account and uncovering trends

TRICK #12 — TRENDLINES

Now that we’ve gone through how to create graphs, adding a trendline is a great way to quickly see an overall performance trend. As you can see here, there are a few different options as to what kind of trendline you would like to use:

TRICK #13 — MACROS

Macros are a recorded series of actions that can automate simple tasks. For example, here at Hanapin we frequently use account snapshot macros that give the user a quick overview of number of keywords, match types, and quality scores across the account.

Use it for:

• Analyzing your whole account!

TRICK #14 — CONCATENATE

This function allows you to combine the contents of multiple cells.

TRICK #15 — THE “&” OPERATOR

Similarly to concatenate, using the “&” allows you to combine cells.

Helpful hint: Use “ “ to include a space.

TRICK #16 — FILTERS

To enable filters, click the icon that looks like a funnel (pictured to the right). This will allow you to filter your data by any column of your choosing.

Use this for sifting through keyword reports, campaign reports, or any other report you might export from AdWords and/or Bing.

TRICK #17 — SPELL CHECK

The Spell Check feature is located in the Review tab. Click the Spelling button and Excel will go through the sheet and suggest corrections.

Use it for:

• Ad writing
• Double checking client-facing reports

TRICK #18 — CAPITALIZATION

Excel has functions built-in that will automatically change the capitalization of the text in a cell. These are:

Use it For:

• Ad writing

TRICK #19 — TRIM

The Trim function deletes extra spaces before and after the words in a cell. For example, if you have “free shipping today only “, Trim will change it to “free shipping today only”. Those extra spaces can be quite frustrating, because you might not necessarily see them, but they use up some of that precious character count for ad copy.

Use It For:

• Ad Writing

TRICK #20 — REMOVE DUPLICATES

Another useful feature from the Data tab is the remove duplicates function. It does exactly what you would expect it to. Simply select the column(s) that you would like to de-dupe and voila, duplicates-be-gone!

TRICK #21 — SUMIF

SUMIF allows you to sum the values of a range of cells, given that they meet the criteria you specify.

TRICK #22 — COUNTIF

Countif works the same way that Sumif does, but it counts the number of non-empty cells rather than summing the values.

TRICK #23 — IF FUNCTIONS

The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE. Click here for more info on IF.

TRICK #24 — GROUPING

Grouping does exactly what it sounds like: it groups rows or columns together.

The most frequent use of grouping that I’ve seen is calculating your optimal average position. To do this, download a campaign or keyword report segmented by week (or day). Then, create a pivot table with Average Position as your Row Label. Then select the rows, right click, and click “group”.

TRICK #25 — WRAP TEXT

Got some lengthy text in a cell, but still want all the words to be visible? Just click on the Wrap Text button. Use this to make client facing excel documents more presentable and easy to read.

TRICK #26 — TEMPLATES

Templates make Excel work go much quicker if you have a template pre-made. Templates for bid changes and ad writing are among the most frequently used. Simply set up the “shell” of the worksheet that includes the formulas used, and then paste in your updated data.

Here’s an example of an ad writing template that automatically has length counters that are conditionally formatted to turn red if your line is over the character limit.

TRICK #27 — ANCHOR CELLS

Say you want to want to reference a single cell for a whole column of calculations. Obviously you want to fill the formula down, but you want Excel to still reference that one cell. To do this, place a $ before each aspect of the cell reference you want to anchor. For example: $A$14 will always reference that cell, but $A14 will only reference the A column, and the row will change as you move the formula.

TRICK #28 — PROJECTIONS

Excel makes projections easy. Typically the formula used is MTD + ((Last 7/7)*Days Left In Month). This can be used for cost, conversions, clicks, whichever metric you want. The beauty in using Excel, is that once you set up the basic formula, you can copy and paste in your data and it will automatically calculate it for you!

Helpful Hint: Use the =today( ) function to automatically insert today’s date, and set Days Left to subtract today from month end, and add 1 to include today.

TRICK #29 — SOLVER

Excel solver uses given parameters and limits to minimize or maximize a column/cell based on existing data.

Use it for:

• Calculating campaign budgets

TRICK #30 — NESTED FUNCTIONS

Sometimes one function just won’t cut it. Luckily, Excel allows you to “nest” functions inside each other.

In this example, I’ve nested an AND function inside of the IF function. We want both the number of conversions to be greater than 1, and CPL to be below $40 for the keyword to get a 10% bid adjustment. Keywords that either have a CPL over $40 or have spent over $99 without converting get a -10% adjustment.

TRICK #31 — NAMING RANGES

Naming ranges accomplishes two things for the excel user:

1. Making formulas easier to write
2. Making your spreadsheets easier for others to understand

Luckily, Excel makes it very simple to name ranges. Simply select your data, navigate to the Formulas tab, and select the “Create from Selection” button.

From here, check the “Top row” box to indicate where your desired names are located.

Now you can easily write formulas using the column names, rather than cell references (think “clicks” vs. “B3”).

If you’d like to edit your named ranges at any point, navigate to the Formulas tab again and click the “Define Name” button. This will prompt a pop up menu that shows each name and the associated range of cells.

This definitely comes in handy when you start building complicated formulas, such as advanced bidding worksheets.

TRICK #32 — SLICERS

Slicers are like filters for your pivot tables, but way cooler.

Create your pivot as usual, and then click the “Insert Slicer” Button on the PivotTable Analyze tab.

This opens up a menu, where you can select which dimension you’d like to filter by. Now you’ve got a simple way to filter your pivot table with a click of a button.

The slicer (shown here on the right of the pivot table) allows you to select a campaign to view (or multiple campaigns!) by date.

TRICK #33 — WILDCARDS

Use wildcards within functions such as SUMIF, COUNTIF, etc.

Example:

You can just type them in your formula using quotation marks:

Alternatively, you can reference a cell, just be sure to use “&” to attach the wildcard to the text you’re referencing in another cell.

Note that wildcards also work within column filters!

Without the space in “*CAN *”, the campaign: “US – Cantines – Search” would also have been included in this filter, which is incorrect, because what we’re really filtering for is Canada campaigns.

TRICK #34 — ARRAY FORMULAS

Arrays are Excel formulas that store and use multiple variables in one formula. So rather than having to create “helper” cells that calculate part of your equation, an array handles it for you.

In this example, we want to create a list of the top spending keywords with zero conversions, and sort them largest to smallest with the Large function. By using an array, Excel treats the columns from our original keyword data sheet as arrays, and does all the calculations behind the scenes to properly rank them.

Trick #35 — SUBSTITUTE

The substitute function is quite simple, but can be useful for things like ad copy or keyword buildouts.

In this example, we have two currently running ad variations, and need to change the promotions being advertised.

Simply select the cell, enter what you are looking for, and what you want to replace it with.

Trick #36 — DATE FUNCTIONS

If you have a workbook that involves dynamic dates, it’s a good idea to be familiar with the various Date functions. These include:

• Today()
• Day()
• Month()
• Year()
• EOMonth()
• Date()
• Text()

Dynamically generating the first of the month, yesterday, and last day of the month is an easy way to automate your projections. The less time you spend on updating your workbooks manually, the more time you have to adjust your campaign budgets and optimize your account. Win win!

Trick #37 — LEFT AND RIGHT

There may be some instances where you don’t need to split out every single aspect of a cell, but you do want to pull out the first category (campaign name example shown below).

This is a great use case for the LEFT function used with the SEARCH function. The SEARCH function returns the position in the cell where the characters first appears. Note that we must subtract one from this search function so we don’t include the “_” in our end result.

Trick #38 — WORKDAY FORMULAS

The WORKDAY function works very similarly to NETWORKDAYS. Rather than calculating the number of work days, this one calculates the work day based on the start date, number of days, and any holidays.

These are great formulas to have in your back pocket when building out project docs or status docs to keep your team on track to meet important deadlines.

Trick #39 — TABLES

Table formatting in Excel comes with a few benefits:

• Formatting – alternating row colors
• Total rows
• Easy to add calculated columns

Highlight your data and navigate to “format as table” and select which formatting you’d like.

When you add a new calculated column, you’ll see that the formula will reference the column names rather than cell references.

Once you type in your desired formula (CTR in this example) the formula will automatically fill down the whole table.

Another benefit of the table formatting is how easy it is to add and format total rows at the bottom of your table. When you click into a cell, you’ll get a dropdown menu that allows you to choose how you want to calculate that field.

Trick #40 —TRANSPOSE

To transpose cells in Excel means to paste rows into columns or vice versa.

There are two ways to do this; the Transpose formula or Copy > Paste Special > Transpose

Transpose Formula

First, select the cells that you want to paste the values into. Then type the transpose formula, referencing the original cells.

This next step is where the magic happens. Click into your formula and press control + shift + return to turn the formula into an array (this puts curly brackets around the entire formula).

Voila!

Paste Special

This second method is arguably easier. Simply copy the values, then navigate to the paste menu:

Become an Excel expert

Becoming Excel savvy will not only help you do account work and analysis better, but you’ll work faster too. It might take an initial investment of time to learn which functions and/or features are useful for which task, but you’ll reap the rewards long after you’ve mastered these Excel functions.