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.
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.
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,” “,” +”).
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:
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.
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:
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:
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.