Excel Formulas Every SEO Needs to Know

As an SEO, you really can’t escape Excel. Almost every month a new SEO tool pops up, that can do so much, but will never seem to replace those little things you can do in Excel.

You find yourself using a tool for 75% of the job, but will then always jump back into Excel to finish it off.

Well, that’s me anyway. If it’s not you, then you probably just don’t know many of the things that Excel can do!

These excel formulas for SEO will really help you out.

I run through VLOOKUPs, IF statements, LEN, TRIM, PROPER / UPPER / LOWER, COUNTIFs & SUMs and show you some basic usage examples. I’ve also added a few extras to the bottom that aren’t in the video!

Basic Excel Formulas for SEOs

Whether you’re starting out, or an experienced SEO, these are the basic building blocks to so many advanced uses of Excel for SEO.

Whilst you can use something like SEO tools for Excel, knowing these formulas will really help you skill up.

VLOOKUP – Find data for a value, within a range

The simplest summary of what a VLOOKUP will do is to match two sets of data where you have something to match them with.

So is long as one column in each data set has some sort of unique identifier, then you can VLOOKUP between the two datasets and bring the data together.

If you don’t have a unique identifier, will you have one if you match two columns together?

You might have a date that isn’t unique, but if you add it to another column (like colour) you will get a unique value that you can use to match the data sets.

=VLOOKUP(*CELL of what you want to search for*,*RANGE of where you want to look for it*,*COLUMN NUMBER the data is in you want to extract*,*TRUE (1) or FALSE (0) but 99% will be FALSE*)

LEN – Count character length of a cell

Nice and simple formula. The LEN formula is great for counting the lengths of page titles and meta descriptions!

Plenty of other uses, but that’s what the majority of people will use it for.

=LEN(*CELL to count*)

TRIM – Remove spaces before & after a cell

Sometimes when trying to match data up, the cell might visually look the same as another cell, but you can’t seem to match it up!

The amount of times I have run into this is crazy, and the majority of times it happens there is a pesky space before or after the text.

The TRIM formula will clean these up for you and make sure there isn’t something hidden there wasting your time!

=TRIM(*CELL to trim*)

SUM – Add all numbers in a range together

Just adds numbers. It’s that simple. Just select the range you’d like to add together and away it goes.

=SUM(*RANGE to add together*)

COUNT – Display a count of cells with numbers in them

Out of your selected range, how many of the cells have a number in them?

=COUNT(*RANGE to count for numbers*)

COUNTA – Display a count of cells with numbers and letters in them (so not empty)

Out of your selected range, how many of the cells have a number of letters in them? This is what you need to use if you are trying to count cells with characters rather than numbers.

=COUNTA(*RANGE to count for numbers*)

COUNTIF – Count all the cells when a specific criterion is met

Only count a cell that matches your criteria. This is great for being able to select cell as your criteria.

So if you have a column of ‘Colour’ and a cell that has the text of ‘Blue’, you could select that ‘Blue’ cell and then the range that has all the colours, and the formula will count how many times ‘Blue’ appears in the range.

=COUNTIF(*RANGE to count for numbers*,*CRITERIA for when to count*)

PROPER – Capitalise the first letter of every word in a cell

Great for cleaning up a list of titles, or any words, where you’d like to capitalise the first letter of every word.

=PROPER(*CELL to capitalise first letters*)

UPPER – Capitalise every letter of every word in a cell

This will format it AND MAKE IT LOOK LIKE YOU’RE SHOUTING. Just use the upper formula when you need to make sure everyone knows you’re SERIOUS.

=UPPER(*CELL to capitalise everything*)

LOWER – Make every letter of every word in a cell lowercase

The lower formula will make everything lower case. Great to run through your keywords with to ensure there isn’t any weird capitalisation going on.

=LOWER(*CELL to lowercase*)

LEFT & RIGHT – Extract x characters from the left or right of a cell

This is a simple way of extracting a certain amount of characters from the left or the right of some text.

Great if you want to cull the end of a title, remove a domain, or trim some trailing slashes from a URL.

=LEFT(*cell reference for text to look in*, *number of characters*)

=RIGHT(*cell reference for text to look in*, *number of characters*)

However, if you are looking trim everything before or after a certain character, instead of a number of characters, check below for a more advanced formula!

IF – Only do something when a certain condition is met

The IF formula lets you create rules for your cells. Maybe you need to filter out titles that are too long.

You could do an IF formula in a new column that says =IF(length>70,”LONG”,”OKAY”). This will give you back a cell that says LONG if the title is too long, or OKAY if everything is okay.

Probably not the best use case, but gives you the idea!

=IF(*The condition to run*,*What to do if it’s true,*What to do if it’s false*)

Does Cell Contains Text – Run condition if the cell contains specific text

A quick formula to use if you want to do something off the back of a cell containing a specific piece of text.

This formula will return TRUE or FALSE, which is what I normally use with this one, but you can expand it do use IF. So IF bla = TRUE, do this.

Great way to quickly segregate data by whether something is in a URL, or keyword.

=ISNUMBER(SEARCH(“text to look for”,*Cell to look in*))

& – Yeap, the ampersand bad boy is rather useful

If you’re trying to merge stuff together in Excel, you need the ampersand. This thing will let you attach anything, to anything else.

It will let you run multiple formulas, then just stick them all together to form what is essentially a sentence.

=”Stick this “&”,with this “&”, and merge all “&2+2&” sentences together”

Which will output:

Stick this, with this, and merge all 4 elements together

SUBSTITUTE – Swap out any text, with any other text

This formula is invaluable if you are doing any sort of text editing or manipulation.

You select the cell you’d like to manipulate, enter the text that currently exists, then enter the text you wish to change it to.

=SUBSTITUTE(*text to look in*,”old text here”,”new text here”)

Trim URL to Domain / Subdomain

This is something that you will find incredibly useful if you’ve never done it before. Some extra tools do it, but you can achieve almost perfect trimming with just a formula – SOOOO much easier!

Quick and Dirty Trim Domain Prefixes Method

If you are just looking to trim the HTTP part, here is a hacky formula that will just substitute out the HTTP part. It’ll leave subdomains / domains, and all their folders intact however.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(substitute(SUBSTITUTE(A2 ,”https://www.”,””),”https://”,””),”http://www.”,””),”http://”,””),”www.”,””)

Trim URL to Subdomain

Spend a bit more time playing with a formula, and you can get something that will do a much better job though!

This formula will trim any URL back to its subdomain. It will remove all prefixes, or any suffixes (aka folders) from a URL so that you are just left with the subdomain & domain combo.

=SUBSTITUTE(REPLACE(REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A2, 1, IFERROR(FIND(“//”, A2)+1, 0), TEXT(,))&”/”), LEN(A2), TEXT(,)), “www.”, TEXT(,))

And another formula is;

=REGEXREPLACE(REGEXREPLACE(A2,”(http(s)?://)?(www\.)?”,””),”/.*”,””)

With A2 being the cell reference to where you have a URL to trim.

Trim URL to Root Domain

If you are wanting to get rid of the subdomain too, so you are just left with the root domain, you can use this formula. It will remove folders, subdomains, prefixes, and anything else so that you are just left with the root domain stripped out.

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(REPLACE(REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”, FIND(“/”, REPLACE(A1, 1, IFERROR(FIND(“//”, A1)+1, 0), TEXT(,))&”/”), LEN(A1), TEXT(,)), CHAR(46), REPT(CHAR(32), LEN(A1))), LEN(A1)*2)), CHAR(32), CHAR(46))

Stripping Text Before/After Characters

This is something I have only picked up in the last couple of years, and these formulas allow you to strip out the text before or after a character or a set of characters.

I will leave this up to your imagination with how they work, but a great usage is stripped title tags of anything after a | (pipe).

Yes, you could find/replace this stuff out, or substitute it, but maybe there are 50 different items to swap out. That will take ages!

If they all have a pipe right before them, you can strip them all out with one formula.

Found these formulas here, thanks guys!

Strip Text Before a Character

This formula will remove everything before the pipe. Just edit the pipe out with any character, word, phrase, anything, and it will remove all the text before it.

=RIGHT(A1,LEN(A1)-FIND(“|”,A1))

Strip Text After a Character

Again, just replace the pipe with anything you want to have this formula strip all the text that is after it.

=LEFT(A1,FIND(“|”,A1)-1)

Insert Row Every Nth Row in Google Sheets

Got a list of keywords you need to break up? This formula is magic and will split your rows into lists of x values. This one in particularly breaks the keywords into groups of 20.

=ARRAYFORMULA(QUERY(FLATTEN({A2:A,IF(MOD(SEQUENCE(ROWS(A2:A),1,0),20)=19,” “,)}),”where Col1<>””))

Finding Your Own Excel Formulas

The biggest thing to remember with Excel formulas is that chances are someone else has tried what you’re doing before.

Just break down what you’re doing, and Google it piece by piece. You might end up putting something together yourself, through what you find from others.

Hopefully, you’ve found some new ways of using Excel for SEO.