09 Aug 2016

Use These 5 Basic Excel Functions to Make Smart SEO Decisions

If you’re like the majority of SEO professionals, day-to day processes involve dealing with many of the same data sets for a variety of projects. We develop a certain familiarity with raw data from our favorite and most frequently used SEO tools.

Among this common data, you’ll find exports from Screaming Frog, SEMRush, Google Search Console, and many other sources. Often times in an SEO agency environment or as an in-house SEO expert, it’s important to have the ability to gain quick insights into your data. This enables you to get quick optimization wins, focus the scope of your audits, and even simplify your competitive backlink analysis process. The following SEO excel functions are aimed at helping you unlock that important information contained within your data.

The LEN function:

How is it used?

The LEN function simply counts the number of characters in a given cell. It can be used by typing =LEN(cell-to-count)

How can it apply to SEO? 

As we all know by now, it’s important to play by Google’s rules. This means that we want to adhere to the basic length limits found in the search engine results page. Your metadata should be limited to their specific character ranges. Moz’s Title Tag & Meta Description best practices will contain the current recommended character limits.

Use the LEN function in its own column to check the character lengths of existing metadata, or to keep an eye on those titles and meta descriptions that you’re writing. You can even add conditional formatting to highlight instances where your character limit is exceeded, like in the example below:

length function for seo

The COUNTIF(S) function

How is it used?

The COUNTIF(S) functions tells you how many cells in a range meet given criteria. Use this function by using the format =COUNTIF(range-to-check, criteria-to-look-for), or if you want to use multiple criteria, use =COUNTIFS(range-to-check, criteria-to-look-for, range-to-check, criteria-to-look-for)

How can it apply to SEO?

COUNTIF(S) is one of my favorites for quick SEO data validation. It’s useful to quickly dive into certain data sets, and can help you get a feel for what you’re up against. SEMRush, for example, provides reports for comprehensive keyword research. Let’s say you want to know how many high volume “striking distance” keywords you’re currently ranking for. These are the keywords for which you rank near the top of page 2, but could be incorporated into your content for a lift to page 1 visibility. To count these keywords, you can perform a calculation like this:

=COUNTIFS(Range of Average Position, “>=11”, Range of Average Position, “<=14”)

This will return the number of keywords ranked in position 11-14, like in the example below:

countif function for seo

The SUBSTITUTE function

How is it used?

SUBSTITUTE does exactly what it sounds like, replaces existing text in a cell with new text. To use this function, type =SUBSTITUTE(cell-to-change, text-to-replace, new-text) There is also the option of an “instance number” at the end of the function, which allows you to specify which character to replace if there are multiple occurrences of the same character.

How can it apply to SEO? 

This function is ideal for cleaning up a list of URLs, as it can help you extract text from URL slugs. If you nest substitute functions within one another, you can start to get creative by drilling down exactly what you’d like to extract from your list of URLs. Here’s an example:

substitute function for seo

The PROPER function

How is it used?

The PROPER function is quite simple; it will capitalize the first letter of every word in a given string of text. It can be applied by typing =PROPER(cell-to-capitalize)

How can it apply to SEO?

Have you ever had to crank out a ridiculous amount of title tag recommendations? Well you’re not alone, and this is the perfect function for you! Apply the PROPER function to the adjacent column, and start crafting your page titles without the need to hit the shift key for every word. Here it is in action:

proper function for seo

The VLOOKUP function

How is it used?

Considered a must-know for SEO professionals, the VLOOKUP function allows you to efficiently and accurately import matching data from other ranges. It works by using a lookup value cell, finding that cell in another range, and returning data in that same row. To write a VLOOKUP function use the following syntax:

=VLOOKUP(lookup-value, array-to-pull-in-data-from, column-number-to-pull-from, “FALSE” for an exact match)

How can it apply to SEO?

VLOOKUP has unlimited uses in the field of SEO. It is utilized for simple tasks like data merging, to more advanced applications in tracking important KPIs. For this example, let’s look at merging two common keyword data sources; Search Console and Keyword Planner.

Google Search Console provides you with organic keyword data related directly to your site. This includes query, impressions, clicks, click-through-rate, and average position. Merging this data with Google Keyword Planner’s average monthly searches and competition can help us understand which keywords to target when optimizing content. An ideal keyword in this scenario might be reflected by high clickthrough, low impressions, high search volume, and low competition. Here’s how we would write a VLOOKUP to find and return search volume:

vlookup function for seo

This was demonstrated in one range for visual purposes, but typically this is performed to pull data from another spreadsheet or tab altogether.

The five functions covered in this post are a great jumping off point. They help you become accustomed to how Excel allows you to make smarter SEO decisions, and I highly encourage you to experiment with how these functions can benefit your personal SEO processes. It’s important to keep a curious mind in this industry, so if you find yourself repeating calculations there is likely a function to help make your life easier!

SEO 202 Series

Learn Advanced SEO Strategies

Explore our SEO 202 video tutorial library, where you can learn about local citation audits, international optimization, canonical mapping and more.

Click to Watch the Series