Excel & Google Sheets for PPC: Hacks that will change your life

Blog | 20 Feb, 2018

It’s no secret that Excel saves a ton of precious time when analyzing PPC data. DoubleClick, Bing, and Google AdWords all use Excel as their default when downloading data. At Jellyfish, we’re all about efficiency, so here are some useful tips and tricks that will help you become an Excel superstar.

Our PPC team gathered the best Excel hacks for your benefit. We wanted to focus on the big-ticket formulas first, then we added some fun keyboard shortcuts you might find valuable. Being a Google DoubleClick Certified Marketing Partner, Jellyfish  recently made the transition to G Suite, so it’s worth noting that most of the formulas provided below work well with Google Sheets too...

Formulas:

  •  =Concatenate & =TextJoin (Excel and Sheets compatible)

These are simple functions to combine different lines/strings of text.

Concatenate combines multiple line items into one single text string.

Formula: =CONCATENATE([cell1],[cell2],[cell3],[cell4],...)

Let’s say you want to combine multiple parts of a URL into the final URL. Rather than manually copying and pasting, concatenate does all the work for you:

TextJoin works a little differently by adding in a delimiter (a separator between the text). 

Formula: =TEXTJOIN(“[delimiter]”,ignore_empty,[cell 1], [cell 2],...)

This is perfect for times when you need to bulk-add a comma, colon, space, or any other symbol between texts. Let’s say you have a bulk sheet of segments that you want to combine into a single campaign/ad group name. By adding “+” as the delimiter, you can combine all columns and have them separated to be easily read:

If you use DoubleClick Search for managing your campaigns, you can take this one step further. This function is really useful in combining multiple campaigns, using the “|” bar to separate the campaigns in DoubleClick when filtering:

Result:
Brand 1+Men+Shoes|Brand 1+Women+Handbags|Brand 1+Women+Tops|Brand 2+Women+Accessories|Brand 2+Men+Tops|Brand 3+Women+Bottoms|Brand 3+Men+Bottoms|Brand 3+Women+Shoes|Brand 3+Men+Accessories

  • =Text (Excel and Sheets compatible)

The text function lets you change the way a number appears in Excel or Sheets.

Function: =TEXT([cell],“format_text”)

The text options are endless, but a good one is knowing what day of the week a specific date is—especially if you are reporting on weekday trends.

  • =Len (Excel and Sheets compatible)

This super simple formula tells you how many characters are in a cell, which is essential if you are writing ad copy and need to remain aware of character limits.

Formula: =LEN([cell])

  • =Proper (Excel and Sheets compatible)

You never have to manually capitalize your ad copy text ever again with this formula!

Formula: =PROPER([cell])

  • =SumIf (Excel and Sheets compatible)

This very powerful tool comes in handy when summarizing segmented data. It works similar to a filter, but you can immediately know totals for specific areas, even when the data set changes.

Formula: =SUMIF([range],”criteria”,[sum_range])

Let’s say you pulled conversion data YTD for your account and want to know how many mobile conversions you have generated. By setting up this formula, you can continue to add or change the data (as long as it matches the same format) and it will always tell you how many mobile conversions you have.

Keyboard Shortcuts

Below we’ve compiled some bonus hacks you can break out to impress your co-workers with what an Excel and Google Sheets wizard you are.

  • Control+D, Control+R (Excel and Sheets compatible)

These two keyboard shortcuts let you copy and paste cells quickly—which is especially helpful with bulk sheets.

Click the cell you want to copy. Then, use shift+arrow to highlight all the cells below it  where you want to paste the copied data. Press Control+D and your content is pasted into the cells you highlighted. Use Control+R to do the same thing when pasting across a row.

  • Alt= (Excel only)

This keyboard shortcut only works in Excel, but it’s a simple way to get the sum of something without having to actually use the SUM formula. Click into the last cell of the column (below the data). Hold Alt and hit the = key and—voila! An instant sum.

  • Control+Shift+Arrows (Excel and Sheets compatible)

This shortcut allows you to select up to the edge of a data set to the left, right, up or down of the starting cell. It’s perfect for selecting data when creating a pivot table.

Bonus: Control+Shift+Page up & Control+Shift+Page down allows you to navigate through tabs on your sheet.

  • Control+E (Flash Fill) (Excel only)

This shortcut combines a lot of the formulas mentioned above. Excel tries to understand the rules for this based on patterns you’ve shown. It uses data surrounding the column to determine what action you want. You can teach it to concatenate, join text, or split text.

  1. First, show Excel the action you want it to do by entering it into the cell adjacent to it.
  2. Click Control+E in the cell below and it will auto flash-fill all cells with that same pattern.

For example, below is a list of names and addresses. Let’s say we want to combine the first and last name in a quick way. Following the two steps above, we have  manually typed the full name into cell D2:

Then, we clicked into cell D3, clicked Control+E and everything below that auto-filled, along with the headline.

This is also very useful if you are trying to add an identifier to multiple keywords for different match types:

  • F5 OR Control+G > Special (Excel only)

This shortcut will highlight anything you specify (e.g., blank cells, formula cells), making it perfect for mass deleting or changing specific cells.

We hope these tricks and shortcuts further your Excel and Google sheets knowledge and efficiency—because with efficiency comes expertise!

Contact
Please enter a valid Name.
Please enter a valid email address.
Please enter a valid Phone.
Please enter a valid Company Name.
  • Select Service
  • Analytics
  • Brand
  • Consultancy
  • Conversion Rate Optimization
  • Display
  • DoubleClick Partnership
  • Email
  • PPC
  • SEO
  • Social
  • UX
  • Video
  • Websites
  • All Services
By providing your email address you are allowing Jellyfish to send you email communications in accordance with our Privacy Policy and Terms and Conditions