PPC | April 19, 2021
Excel Formulas for PPC: 10 Tricks to Make Paid Search Simpler
If you’re just starting out in PPC, there’s a chance it might feel slightly overwhelming. Digital advertising is large and complex, and can take a long time to master. However, there are a few key PPC Excel formulas that can really simplify things.
Excel is not necessarily an essential piece of kit if you’re looking to win at paid search, but a number of its features do help make account management more efficient. For this reason, PPC experts are generally highly proficient in Excel.
To help start your PPC journey on the right foot, our PPC service team gathered up our top simple Excel formulas for PPC. These are the formulas we find most useful during day-to-day account management.
1. 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.
By using the =PROPER formula in Excel, you can avoid having to manually capitalise all your various text ad copy. Simply by typing =proper(cell number), you can change the sentence into a fully capitalised block of text.
2. Using =LEN to Count Characters
When it comes to text ads, there are usually character limits in place. For example, with Google ETAs there is the option to add up to three headlines, both limited to 30 characters, and two 90-character descriptions.
The LEN function (inputted as =LEN(cell number) is a really useful little PPC trick, as it counts the characters in a chosen cell. This makes it much easier to stick within those limits, and to ensure you’re making the most of the available characters.
3. Using =TRIM to…. Trim
As the name suggests, the =TRIM function trims the extra unnecessary spaces in a cell. This is the perfect PPC trick for when you’re editing in bulk, and helps you to stay within the character limits when writing PPC text ads.
4. Using =SUBSTITUTE to Replace Keywords
The =SUBSTITUTE function allows you to replace specific text throughout a document. This is a really useful tool for PPC, as it can be used to bulk update keywords.
We like to use the Substitute formula to transform keywords to broad match modified keywords. Whilst it is possible to do this in AdWords Editor, it’s usually more time consuming – and a little fiddly.
By contrast, the Substitute feature is a little more powerful and flexible. You can customise it to suit your purpose – for example, to modify all of your keywords you could use a formula like:
=SUBSTITUTE(“+”&A2,” “,” +”).
5. Using =IF Formulas to Make Comparisons
The IF formula is a “logical function” which allows you to make rules depending on two possible outcomes. To put it simply, it tells Excel that IF one thing is true then do option 1, otherwise, do option 2.
As a larger example:
This is telling Excel that if returns a “yes” then return a ‘1’ – otherwise, return a ‘2’. This is particularly helpful when reporting – it can be used to show clients which specific results are providing value.
6. Using =VLOOKUP to Find Results Fast
As there can sometimes be hundreds – if not thousands – of product variations, having an easy way to differentiate between them is a must. The VLookup formula is one simple solution to this issue.
Essentially, this formula allows you to look up a value by matching it to your first column. For example:
7. Using =CONCATENATE to Join Cells Together
Concatenate allows you to amalgamate cells. The formula is particularly easy too – simply type =CONCATENATE, hold CTRL and select the cells you want to join together.
The Concatenate formula is useful for a variety of reasons, but with PPC in particular it can be a handy shortcut in ad creation. For example, you can use it to visualise how the complete ad will look, while still maintaining separate cells for each header or description:
You could also use Concatenate when repurposing ad copy for use on another platform, with longer ad copy limits.
8. Appending Text or Numbers to an Existing Cell
This is a formula with a number of useful PPC applications. For instance, you could use it to easily add client branding to your ad headlines, or to quickly add new labels to existing copy.
To append new content to an existing cell, you simply use this formula: =cellReference& “;text to be appended”:
9. Using =SUMIF to Group Things Together
SumIf will soon become one of your favourite Excel formulas, if you’re often looking for an easy way to collect data with common themes. Essentially, it lets you add up data for cells that match a specified criteria.
Typing =SUMIF(columnReference,”criteria to be met”,”columnReference”) will group the relevant data in a column. For example, this could mean easily finding the total impressions for a number of different – yet connected – ad groups:
10. Using =TEXT to Convert Dates
Being able to change the format of dates in Excel can be handy for a number of reasons: for filtering, for use in pivot tables and for a variety of other types of data manipulation. So, the ability to rapidly convert them is really useful.
This is exactly what you can achieve with the Text formula. Simply give the cell reference and the new date format you want, and there you have it: =TEXT(CellReference,”NewFormatForDate”).
The Tip of the Excel Iceberg
There’s no hard and fast rule when it comes to using Excel for PPC (if you use Excel at all), and these PPC tricks are just the tip of the iceberg when it comes to how useful it can be. However, if they help make your day-to-day just that little bit easier, then we’ve done what we set out to do.
Read more paid search wisdom
First published 7th October 2016; updated 19th April 2021.