Essential SEM Excel Skills

Excel is a crucial part of any Paid Marketer’s arsenal. In this post, we’re going to give a quick rundown of the essential skills any marketer should have when using Excel. Let’s dive in:

 

Calculations with functions

Use Case:
Calculating CTR, CPC, CPA, CVR for an ad test analysis

 

 

To make a calculation using a function in excel, start with an “=”. Then, select the cells you want to execute a calculation with, separated by:

• * for multiplication and / for division
• + for addition and - for subtraction

 

Vlookup

Use Case:

1. Integrating 3rd party data for ad analysis
2. Verifying if a keyword already exists when creating new keywords

Use this function when trying to match information in two different lists based on a piece of information they have in common. For example, if list A has a list of Ads and respective impressions and list B has the same Ads with conversion information, you could use vlookup to create list C with the Ad, impressions, and conversion information.

 

 

SumIf/SumIfs

Use Case:
Integrating 3rd party data for ad analysis

Use this function for similar purposes as the vlookup function if the values from one list need to be summed before being pulled in. In the above example, if a given ad had 2 entries with 10 and 5 conversions, the value that would be matched is 15. If the vlookup function was used, excel would pick the first value in the list.

In this example, we are summing the cost for the redacted headlines in blue:

 

In this example, we are summing the cost for redacted headlines in blue headlines that are also in the “brand” category:

 

Concatenate

Use Case:

1. Combining category, headline, and description of an ad to create an ad test analysis report
2. Combining a “+” with search terms to create new keywords or negative keywords

 

Combine values from multiple cells into one. The formula will usually look something like this “=b4&c4&d4”

 

Substitute

Use Case:
Substituting spaces in search terms for “ +” when creating new or negative broad match keywords

Use to replace a piece of text with another. In this example, we are replacing spaces in the headline with “ +”:

="+"&SUBSTITUTE(B2," "," +") will make a result like this: +cell +contents

 

Text to Columns

Use Case:
Parse out combined category, headline, and description of an ad for the ad test analysis report

Text to column is a feature in excel that allows you to separate the values from one cell out into multiple others based on a “delimiting” character like a semi-colon. For example, I could separate the values that are the “Row Labels” column here based on the semi-colon:

 

 

Remove Duplicates

Use Case:
Create a list of unique search terms to evaluate for new and negative keyword scrubs.

Remove duplicate values in a range of cells over a single or multiple columns.

 

Find and Replace

Use Case:
1. Isolate ad ID from an ad’s tracking template
2. Isolate LP ID from an ad’s tracking template
In ad builds replace city abbreviations with the full city name (ex: San Francisco for SF)

Find a value and replace it with another. If you find a value and replace it with a blank it can be an effective way of deleting information.

This will delete anything before the text “adid-”. Use the * when you’d like to select everything:

 

 

Number Formatting

Use Case:
Format numbers in ad test analysis reports for clients

 

 

Conditional Formatting

Use Case:
Format ad test analysis reports so clients and account managers can easily see which ads are performing well and which are performing poorly.

 

 

IfError

Use Case:
To avoid an error when calculating CPA and there are 0 conversions

=IFERROR(H4/J4,”-”)

Instead of displaying #DIV/0! the cell will just have a hyphen in it.

 

Pivot Table

Use Case:
Creating a table that sums impressions, clicks, cost, and conversions for each unique ad.

 

 

There is a lot more you can do with pivot tables but this is the most common use case.