Top Excel Formulas To Manage SEO Data

Digital Marketing | SEO | MS Excel

SEOs and Excel — they always go hand in hand. Excel is an absolute must when working on something like SEOs. Excel is helps in data manipulation, visualization, complex analysis, data integrations, tabulation, reporting & dashboarding.

If you’re new to excel and have a very limited experience with MS Excel, here are – Top Excel formulas to managing SEO data.

Let’s begin with the Excel formulas:

(1) SUBSTITUTE – it is used to replace specific text in a text string

Formula: =SUBSTITUTE(text, old_text, new_text, [instance_num])

Inside this formula, “text” means the entire cell where you want to replace the text, “old_text” means the text piece that you want to replace, “new_text” means the new text that is to be added, and if required, “[instance_num]” is the occurrence of the old text you want to replace with the new one.

Use in SEO:

Data Replication: For instance, if you are working on product level urls in bulk and he these like have a similar nomenclature with only the category, size or color changes then using SUBSTITUTE can help you speed up with high accuracy.
Keyword Research: Modify your keywords and generate additional variations. For example, you can replace the letter “s” with “z” (like in analyse and analyze) to create a different spelling of a keyword or replace a prefix or suffix to generate long-tail keywords.
SEO Content: While working on this kind of content where in the only need is to replace a word rest all the content remains the same. Like Tags, Canonical, etc.

How to Use:

Select the cell, and enter the SUBSTITUTE formula in the cell.
Using below example, select cell C3 (type comma “,”)
Type the text you want to replace “red” (type comma “,”)
Type the new text “blue”
Hit Enter Key to see the result

(2) CONCATENATE – it is used to combine the content of 2 or more cells

Formula: =CONCATENATE(text1, [text2], …)

Inside this formula, “text1” means the 1st cell that you want to combine, “[text2]” means the 2nd cell that you want to combine, and so on.

Use in SEO:

Creating Unique Value: While working on analysis, you may get into a need to create unique value to do a vlookup, or a pivot. You can combine different data points into a single cell so for instance, you can combine the domain name, the page URL, and the page title into a single cell for easier analysis.
Keyword Research: If you are working in e-commerce where a lot of products are involved that differ by a set of variables (gender, color, rise, etc.), you may create all the needed combinations (male red t-shirt, female yellow t-shirt) much faster.

How to Use:

Select the cell, and enter the CONCATENATE formula in the cell.
Using the below example, select cell A1 (type comma “,”)
Select cell “B2” (type comma “,”)
Select cell “C2” (type comma “,”)
Select cell “D2” (type comma “,”)
Hit Enter Key to see the result

(3) AVERAGE – it is used to calculate the average of some metric

Formula: =AVERAGE(number1, [number2], …)

Inside this formula, “number1” means the 1st number, “[number2]” means the 2nd number, and so on. The formula adds all the numbers & calculates the average.

Use in SEO:

Keyword Analysis: sorting a group of keywords across multiple search engines or over time by taking an average of traffic, backlinks, page time, etc.
Website Analysis: Domain authority, page authority, or other backlink metrics for a group of websites or web pages.

How to Use:

Select the cell, and enter the AVERAGE formula in the cell
Using the below example, select cell B2 (type comma “,”)
Select cell “C2” (type comma “,”)
Select cell “D2” (type comma “,”)
Select cell “E2” (type comma “,”)
Select cell “F2” (type comma “,”)
Hit Enter Key to see the result

(4) AVERAGEIF – it is used to calculate the average of a range based on a specified condition

Formula:=AVERAGEIF(range, criteria, [average_range])

Inside this formula, “range” means the range where the condition is to be set, “criteria” is the condition, and “average_range” is the range for which the average is to be calculated.

Use in SEO:

Page Load Time Analysis: for a specific category of web pages to help you identify performance issues specific to certain sections of your site.
Keyword Analysis: Sorting for keywords that meet specific criteria like specific products or a search volume range.
Backlink Counts: analyze the backlink profiles of different websites.

How to Use:

Select the cell, and enter the AVERAGEIF formula in the cell
Using the below example, select cell range E3:E9, where the condition is to applied
Enter the condition, example the average of the above range is greater than 43, so type “>43”
Select the cell range for which the average is to be calculated when the condition is correct, C3:C9.
Hit the Enter Key to see the result.

(5) MEDIAN – it is used to find a median of numeric values

Formula: =MEDIAN(number1, [number2], …)

Inside this formula, “number1” means the 1st number, “[number2]” means the 2nd number, and so on. The formula calculates the median of all these numbers.

Use in SEO:

Traffic Analysis: calculate the median organic traffic volume for a set of search terms or landing pages. This can be used to understand the typical traffic level & sort pages based on performance.
Ranking Analysis: calculate the median ranking position of a group of keywords.

How to Use:

Select the cell, and enter the MEDIAN formula in the cell
Using the below example, select cell range B2:B8, where the formula will calculate the median of the range given
Hit the Enter Key to see the result.

(6) LEN – it is used to check the length of your text in a cell

Formula:=LEN(text)

Inside this formula, “text” means the cell with text for which you want to know the length.

Use in SEO:

URL Length: character count can be helpful in finding excessively long URLs, which can impact SEO.
SEO Tag: can be helpful in checking meta titles and descriptions for web pages.

How to Use:

Select the cell, and enter the LEN formula in the cell
Using the below example, select cell A2.
Hit the Enter Key to see the result.

(7) IF – it is used for a logical condition between two values/cells

Formula: =IF(logical_test, [value_if_true], [value_if_false])

Inside this formula, “logical_test” means the condition that is to be given between two values/cells, “[value_if_true]” is the output if the logical condition is true, and “[value_if_false]” if the logical condition is false.

Use in SEO:

Analysis: this can be looked at as the most basic formula for data analysis. It can be used for cost comparison, and data filtering based on value being greater or lower than another value.

How to Use:

Select the cell, and enter the IF formula in the cell
Using the below example, given the logical condition “D2>5%”
Give the output when the condition is found correct, “True”
Give the output when the condition is found incorrect, “False”
Hit the Enter Key to see the result.

You can also give numeric values in the output like 0 or 1, or other text values like select or don't select.

(8) COUNTA – it is used to count the number of cells in a range that are not empty

Formula: =COUNTA(value1, [value2], …)

Inside this formula, “value1” means the 1st number, “[number2]” means the 2nd number, and so on. The formula calculates the median of all these numbers.

Use in SEO:

Analysis: this can be looked at as the most basic formula for data analysis. It can be used for cost comparison, and data filtering based on value being greater or lower than another value.

How to Use: