Excel is one of the most powerful tools that can make you better and faster in PPC management, it’s a very crucial skill for every digital marketer. In this post, we’ll go over 5 formulas that will make your life much easier, starting from building campaigns, writing ads, and analyzing data sets.
1. IF contains this then that
The formula checks if a cell contains specific text and returns a value you determine for TRUE and another value for FALSE.
=IF(ISNUMBER(SEARCH(substring, cell)), text, text)
SEARCH function returns the position of the search string when found. And
ISNUMBER returns TRUE for numbers and FALSE for anything else. Wrapping both formulas in
IF can allow you to return specific values instead of TRUE and FALSE.
Let’s say, you want to analyze the performance of your brand campaigns vs non-brand campaigns. And you do include in your campaigns naming a unique identifier for brand related terms which is the word “brand”. You want to check if the campaign contains brand then returns “Brand” and if not then return “Non-Brand”.
2. Extract text before/after a specific character
To split a text string at a specific character, we can use a combination of
LEFT, RIGHT, and
This splits the text before the underscore character. The
FIND function is to locate the underscore(_) in the text, then we subtract 1 to move back to the “character before the special character.
This splits the text after the underscore character. The
FIND function is to locate the underscore(_) in the text, then we subtract the length of the cell (using
LEN) from the index location of the underscore character, the result gives us the number of characters to return from the right.
You want to pull out the language out of the campaign names which is located after a specific character “_”. You can use the formula below:
SUMIF adds all numbers in a range of cells based on one criteria, while
SUMIFs sum cells that meet multiple criteria.
=SUMIF( range, criteria, [sum_range] ) =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], ...)
- sum_range : The range to be summed.
- range : The range of cells that you want to apply the criteria against.
- criteria : The criteria used to determine which cells to add.
From our last example, let’s you want to know the amount spent on German non-brand campaigns. Your formula can look like below:
4. Substitute & Concatenate
SUBSTITUTE function replaces text in a given string, while
CONCATENATE joins multiple texts together.
=SUBSTITUTE (cell, old_text, new_text)
You want to create broad match modifier keywords by appending the plus to your exact match type keywords. By combining
CONCATENATE, you can append the + to the beginning of the text and replace the space by ” +”.
=SUBSTITUTE(CONCATENATE("+",A2)," "," +")
5. Index & Match
They look for specific information in your worksheet, they are similar to the popular VLOOKUP function. However,
MATCH are more useful since they are dynamic, they respond to change. This means deleting or adding columns in your search range won’t screw your formulas, unlike the VLOOKUP function which doesn’t respond to change.
=INDEX(range, MATCH(lookup_value, lookup_range, match_type))
- range: This is where you’d like to look up a given value.
- lookup_value: This is what you want to look up for.
- lookup_range: This is the range of your given value.
- match_type: Most of the times, it will be exact 0.
Let’s say you want to find the cost of a specific campaign in your data set, your formula can look like below: