SEO

Excel for SEO Success: Top 10 Formulas Every SEO Needs to Know

All SEOs need basic Excel skills — whether it’s conducting SEO audits, putting together reports, diving into exported Conductor platform data, or making changes through our Bulk Update feature. Luckily, these frequently asked about Excel for SEO tricks are about to make your life just a little bit easier.

The Top 10 SEO Excel Formulas

  1. How do I combine the contents of Cell A with Cell B?
  2. How can I easily change out a folder in a URL when putting together a redirect document?
  3. How can I calculate the length in characters of my Title and Meta Descriptions?
  4. How do I remove all these extraneous spaces?
  5. How to a change these keywords to all lowercase?
  6. How to a change these keywords to all uppercase – they are acronyms?
  7. How to a format a list of names into propercase that come in as All Caps?
  8. How can I check to see if a keyword in List B is in another list of keywords (List A)?
  9. How can I check to see if a keyword in List B is in another list of keywords (List A) – but information is in rows?
  10. How do I clean-up #N/A’s from other Excel Formulas?

Have any more questions about Excel? Are we missing something? Ask us in the comments below!

1. How do I combine the contents of Cell A with Cell B?

Excel Function: CONCATENATE

Merge text from multiple cells with the formula =CONCATENATE(text1, [text2], …)
concatenate formula copy in excel spreadsheet

This is helpful when you need to:

a) Combine contents of different cells with information.
For example, put the string (or text) in quotes: =CONCATENATE(A1,”, “,A2) will combine the contents of A1 with a comma and space and then display A2.

concatenate formula example for a string in excel spreadsheet

b) Combine columns for Standard Categories to add to Conductor by Keyword.
concatenate use example for keyword organization and updating in excel spreadsheet

c) Perform Keyword Research.
Use Concatenate in keyword research by working with modifiers before or after a set of keywords. For example, if you have the keyword “dog” and attributes such as color “brown” or “white” or “black,” size “large” or “lap” or “under 30 lbs,” or geo location “Park in NYC” or “Vet in San Fran” or “Daycare in Vancouver” – you can make a number of combinations by changing out what is combined:

concatenate formula use in excel for keyword research and variation

Note: It is also useful to append Google Analytics parameters to URLs for various marketing campaigns – having a sheet of all the parameters in use can really help you keep track and minimize duplicates due to case sensitivity.

2. How can I easily change out a folder in a URL when putting together a redirect document?

Excel Function: SUBSTITUTE

If you have a list of URLs that are set to be redirected, you can quickly generate the new list using =SUBSTITUTE(text, old_text, new_text, [instance_num])

substitute excel function formula used to generate a new list

text: the cell you want to source to make changes based on.
old_text: represents the part of that source material you wish to change.
new_text: what you wish to replace “old_text” with.
[instance_num]: a count in case there are multiple instances of “old_text” for you to replace. For example, if the folder structure is used in part a second time in the URL, it is optional.

showing how to use substitute excel function formula with domain folder URL structure

This is similar to the actions of Find & Replace, however, it allows for a second list to be generated without saving over the first list.

There are a number of keyword and URL level applications with the SUBSTITUTE Function:
-Given keywords with underscores instead of spaces? Reformat URLs from _ to –
-Switching from .htm to .html or another variation.
-Moving to a Secure Site and need a list of your new pages: http to https.
-Duplicating Clusters of Keywords: Blue-related Keywords to Red-related Keywords? Just Substitute “blue” for “red” and have a whole new keyword list.

3. How can I calculate the length in characters of my Title and Meta Descriptions?

Excel Function: LEN

len function formula in excel for SEO

There comes a time for every SEO to know this formula. =LEN(text) is a simple way to get the Length of a URL, a Title, a Meta Description, or an Alt Tag for a large number of cells.

showing how to use the len function in excel to get lengths of specific content across website pages

4. How do I remove all these extraneous spaces?

Excel Function: TRIM

trim excel SEO function

Ever collected a large amount of data and found it formatted strangely with lots of extra spaces included? There’s a really easy way to cut out the space you don’t want, but keep the spaces between the words you need. SEO, meet formula: =TRIM(text)

example of how to use the trim function in excel to eliminate extra spaces

5. How to a change these keywords to all lowercase?

Excel Function: LOWER

lower function in excel for SEO needs

Have inconsistent caps for your keywords? =LOWER (text) is a pretty straightforward formula for making everything lowercase.

shows how to use the lower formula in excel to update content to lower case

6. How to a change these keywords to all uppercase when they’re acronyms?

Excel Function: UPPER

excel formula for upper function

=UPPER(text) is an easy way to convert H1s to all caps. Another great formula for cleaning up data. 

showing how to use upper formula function in excel to batch update website content to uppercase

7. How do you format a list of names into proper case that are all caps?

Excel Function: PROPER

proper formula function in excel

=Proper(text) is an easy way to change a list of names into proper case.

showing how to use the proper formula or function in excel to adjust copy and fix so all proper nouns are capitalized

8. How can I check to see if a keyword in List B is in another list of keywords (List A)?

Excel Function: VLOOKUP

vlookup formula in excel for SEO

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) looks for a value in the left column of a table, and then gives you the value in the row from a column you want.

Lookup_value: this is what you want to look for
Table_array: this is where you want to look for it
Col_index_num: this is what you want the formula to return
Range_lookup: if you want an exact match, mark this is FALSE

Explains why a value must be in the leftmost column of a table and be in ascending order to work properly when using vlookup function in excel

Note: VLOOKUP only works if the data you are looking for is in the first column (the leftmost column) and the data must be in ascending order (A-Z).

Let’s go to an example. If we look up A20 (Cavalier King Charles Spaniel) in the Dog Breeds table (A1:B16 – named here as Table14567(#All)) using VLOOKUP in cell B20, and are looking to find out its country of origin, we would select 2 for the Col_index_num to look for Origin Country. If Cavalier King Charles Spaniel was not found in the Dog Breeds table the formula would return #N/A.

dog breed example showing how to use vlookup in excel to find specific information

9. How can I check to see if a keyword in List B is in another list of keywords (List A) – but the information is in rows?

Excel Function: HLOOKUP

definition of hlookup function in excel

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) is similar to VLOOKUP but with rows rather than columns.

Lookup_value: this is what you want to look for
Table_array: this is where you want to look for it
Row_index_num: this is what you want the formula to return
Range_lookup: if you want an exact match, mark this is FALSE

using hlookup formula in excel for seo needs

how to use hlookup formula in excel for seo needs

10. How do I clean-up #N/As from other Excel Formulas?

Excel Function: IFERROR

definition of iferror function in excel

Use =IFERROR around VLOOKUP or HLOOKUP to clean up your data.

Notice in the example that if we add “Puggle” to the list of Dogs are looking up, but it is not in the Dog Breeds table a VLOOKUP formula will return “#N/A” (Cell B24). If we add the IFERROR formula around the VLOOKUP formula with the string “Not Found” to be displayed instead of “#N/A” we see it in Cell B31.

example of how to use iferror function in excel for specific SEO needs

We would also love to hear what Excel tips you have up your sleeve and any experience you have had with cleaning up your keyword lists!

One Response to Excel for SEO Success: Top 10 Formulas Every SEO Needs to Know

  1. Mehul Singh says:

    Hey Thanks for this article about excel tricks for seo

Excel Power User's SEO Formula Power Pack

Read More