7 Excel Formulas Every SEO Professional Must Know

SEO professionals deal with ever increasing volumes of data. 

Fortunately, Excel can lend a helping hand with data cleaning and analysis provided you understand how to use it. 

One of the UK’s leading Excel training providers, Acuity Training, and has produced this guide to the 7 Excel formulas that every SEO Service professional must learn. SEO professionals deal with ever increasing volumes of data. Fortunately, Excel can lend a helping hand with data cleaning and analysis provided you understand how to use it. For those looking to go beyond manual spreadsheets and adopt a new way of working in SEO, blending automation with strategy is becoming the norm. 

1. LEFT, RIGHT, and MID Formula

These formulas are great for dealing with text data.

You can use them to extract certain part of a string of data - for example a URL or a page title or metadescription.

• LEFT

By using the LEFT formula, you can extract a set number of characters starting on the left from a string of characters. 

=LEFT(text, [num_chars])

Text: Text string from which you want to extract the required characters

Num_chars: The number of characters required from the text

Let's use the LEFT function in a real-world scenario.

In this example, we want to extract the domain from a list of URLs. 

To do this the LEFT function is used in conjunction with the FIND function. 

The inner FIND function first calculates the number of left most characters that need to be extracted by finding where last forward slash “/” can be found in our string, ignoring the first two.

Once FIND has calculated the required number of characters, the LEFT function then extracts the characters. 
 

We can use RIGHT and MID functions to tackle similar situations.

• RIGHT

The RIGHT function is exactly the same as the LEFT function except that it counts the characters it will extract from the right, not left. 

The arguments for the RIGHT function are the same as the LEFT function.

In the below example, 4 characters from the right side of the cell are extracted using the RIGHT function.

• MID

The MID function is used to extract the required number of characters from the middle of a string.

= MID(text, start_num, num_chars)

Text: Text string from which the characters are to be extracted

Start_num: Defines the starting point for extracting the text, it refers to the left most character in the string.

Num_chars: Number of characters to be extracted from the starting point

In the below example, we used the MID function to extract the website from the text.
 

1. LEN

The LEN function returns the length of a text string. 

In the SEO world, the length of your title and description matters so this is a quick way to check that it is correct. Technically the length will be measured in pixels not characters (see this article for full details) but limiting your character length is a good first approximation.

LEN function can be a great help to quickly get the length of your textual data.

=LEN(text)

Text: Refers to the text for which the number of characters is to be calculated

LEN will count a space as a character. 
 

1. CONCATENATE

CONCATENATE is another great function to work with textual data. It is used to join separate text strings into one.

SEO Expert can use this function in a number scenarios to join multiple text strings. For example creating titles for large ecommerce sites using a spreadsheet which contains the product description for each page and combining it with the website name.

=CONCATENATE(text1, [text2], …)

TEXT1, TEXT2, ….: Refers to the individual text strings that you want to join.

1. SUMIF

SUMIF is used to calculate the sum of values which meet a specific criteria. 

SEOs use it to quickly sum values for specified criteria, like summing up traffic or conversions from a specific region or product category.

The SUMIF function requires three simple arguments.

=SUMIF(range, criteria, sum_range)

Range: Range to look for the specified condition

Criteria: Actual condition to be used for the calculation

Sum_range: [Optional] Range of values to perform the sum operation, if not specified separately, Range will be summed up

In the below example SUMIF in C10 is used to sum the total no of visits for the countries having more than 1,000 total individual visits. 

In Cell C11, SUMIF is used to sum the total no of visits for the countries having less than 1,000 total individual visits. 

1. COUNTIF

Like SUMIF, COUNTIF can be used to count the number of occurrences that meet a specific criteria. 

COUNTIF is frequently used in conjunction with SUMIF to find an average for the specific criteria. 

COUNTIF works with two simple arguments.

=COUNTIF(range, criteria)

Range: Range of values that you want to count

Criteria: Condition that is to be counted

See the below example using the same data. Obviously, to find the average traffic for high traffic (>1,000 visitors) and low traffic (<1,000 visitors) countries you simply divide the SUMIF result by the COUNTIF result.
 

1. XLOOKUP

XLOOKUP is the improved lookup function that was introduced in Excel’s 2019 update.

It is the successor to lookup functions like VLOOKUP, HLOOKUP, and LOOKUP.

Needing to look up data from a download or combine data is something SEOs do most days. For example, if one spreadsheet contains keywords and one contains conversions, you might want to combine them to see which keywords drive conversions.

You can use XLOOKUP in the following scenarios.

• To look up the value from the left or right of the sheet

• Looking up from the horizontal or vertical data

• To look up the values in reverse order

• Looking up from different sheets

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Lookup_value: Target value that is to be found from the data

Lookup_array: Range of values to look up from

Return_array: Range containing the output for the target value

[If_not_found]: [Optional] Returned value if target value is not found

[match_mode]: [Optional] 0 = exact match (by default), -1 = exact or next closest match, 1 = exact or next larger match, 2 = wildcard entries

[search_mode]: [Optional] 1 = look up from first value (default), -1 look up from the last value
 

More details on Xlookup can be found in this article.

Also Read this Article: Holiday Shopping SEO Tips for Successful E-Commerce Business

1. Pivot Tables

Pivot table is yet another great feature of Excel allowing you to summarise data instantly.

Pivot Tables allow SEO professionals to slice and dice data quickly. So they are used a huge amount, whether it is creating standard reports or exploring new data to look for correlations or anomalies.

Follow these simple steps to form a pivot table.

Pivot table works with data in a table. If your data is not in a table, you can transform it by pressing CTRL+T.

Step 1: Go to the Insert tab, click on the PivotTable option.

Step 2: PivotTable window will appear to define the table or data range

Step 3: After defining the table in the option, your pivot table will be created in a new sheet.

On the right side of the screen, Pivot table options will appear from which you can define the fields the way you want.

Like in this example, we have used “Region” as Rows and “No. of visits” as Values.

Final Thoughts

This is just a taster of the possibilities in Excel and will hopefully inspire you to learn more Excel functions. If it has this is a great place to start.