Excel is unavoidable for SEOs. There are many excellent keyword research tools and data sources available, but eventually you’ll find yourself exporting something into Excel or using it to fine tune your dataset. With this in mind, it’s beneficial to spend some time learning the most practical and commonly used formulas for SEO. We’ll walk you through some of our favorites and how we use them below.
Formulas
Concatenate
This allows you to combine two things, whether it be two cells or 1 cell + words.
We use it the most when exporting data from Google Analytics. Google Analytics doesn’t export the domain name with the landing page slug, so sometimes we need to add the domain to the slug.
Formula Structure
=CONCATENATE(Data 1, Data 2)
Example
=CONCATENATE(https://transistordigital.com/”, B2)
VLookUp
VLookUp allows you to pull data from one place (in any other tab in a spreadsheet) to another. This is super helpful when we are combining different data pulls into one place.
We use this extensively in our own keyword research spreadsheet to pull data together from different sources we use during the research process.
Formula Structure
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, false).
Example
=VLOOKUP(A2,Keywords!A:C,3,FALSE)
In this example, we want to lookup the query in cell A2 in the tab title Keywords using the data in columns A:C. We then want to return the value in the third column. In this case, it would be search volume.
IfError
IfError is added to another formula. This informs the formula that if the original formula doesn’t work, then include this copy or number instead of N/A.
Formula Structure
=IFERROR( value, value_if_error )
Example
=IFERROR((VLOOKUP(A2,Keyword!A:C,3,FALSE)), 0)
In this example, if we try to look up the search volume for a query and that information is not in our dataset, then the formula will return the value of 0 instead of displaying N/A.
SumIf
SumIf adds data from specific cells if they meet a certain criteria.
We could use this to add sessions only from pages that contain “blog” in the URL.
Formula Structure
=SUMIF(range, criteria, [sum_range])
Example
=SUMIF(A:A, “https://transistordigital.com/blog*”, B:B)
In this example, we are looking at the URLs located in column A that contain “blog” and returning the sum of the values (or sessions) in column B that are associated with the blog URLs.
Note that the “*” means that there can be any combination of characters after “https://transistordigital.com/blog”. This is necessary because all of the blog URLs are different.
Length
The LEN function is very easy to use and will count the characters in a cell.
This is really helpful for writing succinct metadata.
Formula Structure
=LEN(cell)
Other Tools
Deduplicate
Removing duplicates is important when combining any data.
One way we use this is when combining keywords during the keyword research process. Note that it is important to change the settings to only select the column with duplicated content.
0 Comments