a

Excel for SEOs

Aug 15, 2022featured, SEO

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. 

WordPress SEO: Optimization, Tips, And Best Practices

In the ever-expanding digital landscape, a website's visibility in search engine results is often the key to its success. WordPress, a versatile and widely used content management system, offers a ton of tools and features to optimize your website's SEO performance....

Organic Traffic Loss After A Site Migration

Listen to the experts discuss.

How to Prioritize SEO & PPC for Startups

Listen to the experts discuss.

Magento SEO: Optimization, Tips, And Best Practices

Magento - also known as Adobe Commerce - is a “flexible and scalable commerce platform that lets you create uniquely personalized B2B and B2C experiences, no matter how many brands you have.” Magento is one of the most popular ecommerce platforms we encounter every...

Boosting Tourism Success: A Case Study of Effective SEO Strategies

Tourism was massively hit by the COVID-19 pandemic and we were tasked with growing organic traffic.

Empowering Startups: The CMS Choice From the SEO Experts

What is a CMS? A Content Management System - or CMS - is a powerful software application that enables website owners to create, edit, and manage digital content on their websites without requiring extensive technical expertise. A good CMS provides a user-friendly...

5 SEO Quick Wins You Can Do In 2023

SEO is complex. Understand the basics and empower yourself to improve your own site today.

Will AI Kill SEO?

Short answer - not today.  Still, it is a big deal. Don’t take this conclusion as a denial of the transformational potential of this moment we’re in. But with all big shifts in technology, there’s a rush to draw conclusions about how our world will change. For...

SEO for Site Migrations – Listen Up Developers!

I’ve seen sites lose as much as 75-80% of their traffic.

What do you think? Share your thoughts below.

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Want to learn even more cool stuff? Ali Kresken can help!

Reach out today! We’re happy to help talk more about whatever search marketing issue is keeping you up at night.