PPC | October 7, 2016

Excel Formulas for PPC: 7 Tricks to Make Paid Search Simpler

As a general rule, PPC experts are highly proficient in Microsoft Excel. It’s not necessarily an all-essential piece of kit if you’re looking to win at paid search, but it does harbour a number of features that makes average account management more efficient.

Here are 7 simple Excel formulas for PPC that I find useful during my day-to-day account management:

Using =PROPER to Capitalise Effectively

Part of creating effective ad copy involves making it stand out visually. With that in mind, it’s common for PPC professionals to capitalise every word in an advert, as in this Google example.

new-coll

Thanks to a handy Excel formula, you don’t have to manually capitalise your text ad copy. Introducing the =PROPER function by typing =proper(cell number) will change the sentence into a fully capitalised block of text. this-is-a-sen

Using =LEN to count characters

Nah, not that Len.

When it comes to text ads, there are usually character limits in place. For example, Google AdWords recently introduced expanded text ads (ETAs) which gives you 2 headlines, both with 30 characters available and a description section of 80 characters.

The Len function (inputted as =LEN(cell number)) counts the characters you have used, making it easier to ensure you’re making the most of the available characters without going over the limit.

Using =TRIM to…. Trim

As the name suggests, the =TRIM function trims the extra unnecessary spaces in a cell. This is perfect when you’re editing in bulk and helps to stay within the character limits when writing PPC text ads.

Using =SUBSTITUTE to Replace Keywords

The =SUBSTITUTE function allows you to replace text when you know what text needs replacing.

From a PPC perspective, this can be used to transform your keywords to broad match modified keywords. Whilst it is possible to do this in AdWords Editor, this can be time consuming and a little fiddly. The customisable Substitute feature is a little more powerful and flexible. For example, to modify all of your keywords you could use a formula like:

=SUBSTITUTE(“+”&A2,” “,” +”).3

Using =IF Formulas to Make Comparisons

The IF Formula is a “logical function”. In its simplest form it allows you to make rules depending on two possible outcomes. For example it tells Excel that IF one thing is true then do that, otherwise do the other option).

As a larger example:
=IF(A4=”Yes”,1,2)

This is telling Excel that if B2 returns a “yes” then return a ‘1’ – otherwise, return a ‘2’. This is particularly helpful when reporting – showing clients which specific results are providing value. 4

Using =VLOOKUP to Find Results Fast

For PPC usage, this is particularly helpful when referencing something or looking something up – particularly in an ecommerce industry where there can be thousands of products/types etc.

This can carry a complex explanation as to what it does/how it works, however in its simplest form, it’s written as =VLOOKUP. This allows you to look up a value by matching it to your first column. For example:5

Using =CONCATENATE to Join Cells Together

This quite literally does what it says on the tin. Concatenate allows you to amalgamate cells. The formula is particularly simple too – simply type =CONCATENATE, hold CTRL and select the cells you want to concatenate.

This helps in many ways, but for PPC I find it particularly useful when creating text ads. For example:

6

There’s no hard and fast rule when it comes to Excel for PPC (if you use Excel at all), but I’ve found these formulas make my day-to-day that little bit simpler. I hope they help for you too.

Read more paid search wisdom

Should You be Bidding on Your Own Branded Keywords?

Google Launches a New Feature: Price Extensions 

Google Revamps AdWords Automated Bidding: What You Need to Know

We’re proud to be an official Premier Google Partner.