Excel is a way of life for many marketers.
Whether it’s used for analyzing survey data or calculating sales data, at some point, you’re probably going to open an Excel doc.
The thing about Excel, or any spreadsheet software, is that there are a plethora of functions.
This means there are plenty of ways to get stuck when you’re trying to set them up.
When you have to use Excel every day, you don’t want to have to do everything manually.
It’s a huge time-waster.
Most marketing pros have these functions down pat, but as they’ll tell you, it’s just a learning process.
Once you get it down, you’ll be good to go.
Why you should learn Excel for SEO
If you’re not already using some type of spreadsheet software, you should be.
One of the most important parts of developing a marketing strategy is reporting metrics, whether it be a marketing plan, weekly SEO tracking, or an annual report.
Spreadsheets help you keep all that data organized in one easy-to-access space.
When it comes to something like SEO, in particular, getting organized is key. Especially when you have hundreds or even thousands of keywords.
The reason that Excel is such a great tool for SEO is that it has add-ons and plugins that are designed to make things as easy as possible.
SEOTools for Excel, for example, comes with pre-made functions designed for marketers.
Tools like these are sometimes a necessity for those who have to use Excel but don’t love it.
You know who you are.
But aside from the specialty tools, Excel has a lot of formulas that can help with curating keywords, segmenting lists, and analyzing your data.
All of which are SEO essentials.
But many people still shy away from Excel because they don’t understand how to use those formulas.
I understand that. Having to learn or remember how to use specific formulas can feel like a bummer.
But it doesn’t have to be.
I’m here to tell you that Excel doesn’t have to represent the big mystery that it looks like.
In fact, you might actually enjoy using it once you know how it works.
So, without further ado, here are seven ways you can use Excel’s built-in formulas to really up your SEO game.
1. Use the “IF” formula to create keyword categories
The one thing you learn when doing keyword research is to embrace long lists.
Even if you’re using a free tool like Google’s Keyword Planner, you’re going to get a big list.
Other tools, like Ahrefs or Moz, might give you lists of thousands of keyword ideas, volume metrics, and competition numbers.
That’s a lot of data.
Putting it into a spreadsheet will help you sort out that information into rows and columns, sure. But you also need a way to make sense of it.
You need a way to segment those keywords into usable information.
For example, if you use more than one seed keyword in Keyword Planner, like this:
You’re given an “Ad Group” column when you export your list of suggestions that will separate seed keywords from keyword ideas.
Let’s say I want to break down this list into just the keywords that have the word “tools” in them.
If I look manually, I have to sort through 700+ rows. I don’t want to do that.
So instead, I’m going to use the Excel formula =IF to sort it for me.
The first thing I’m going to do is remove the Ad Group column because I want to search all of them.
Then I’m going to add a “Category” column next to my search volume.
In the first available cell, I’m going to put the word “Tools” and then use the formula:
=if(isnumber(search(“tools,A12)),”Tools”)
Then when a “tools” keyword is found in the first column (my keyword list), it will be put into the “Tools” category.
You can use this formula across your spreadsheets to put all of your keywords in certain categories.
This is a game-changer for SEO organization.
If you want only the keywords that contain the word “local,” for example, you could create a “Local” category.
This is an incredibly useful hack for organizing all of your keyword ideas for a fast search.
That way, if you’re working with a team of people who all need access to the same Excel file but for different reasons, none of your data gets messed up, but people still find what they need.
Moz has a good example of how this can be done for all of your spreadsheet data here.
To do this for all of your rows and columns, you can use a multi-cell ARRAY formula.
This will spare you from having to insert the same complex formula into every single cell of your spreadsheet.
Even if you don’t have 700+ cells to work with, it’s still a handy formula.
2. Create pivot tables for spotting data outliers
Analyzing the data in your spreadsheets can be chaotic if there’s no organization.
Even when you can filter by categories, you might not spot outliers or “bad data” just by glancing at your list.
The good news is that Excel has an easy way for you to spot positive and negative trends in your data using pivot tables.
To create one, highlight a cell on your spreadsheet and click Insert > Pivot Table.
Next, you will see a dialogue box that will ask you to choose the data to analyze and where to place your pivot table.
Your table will be automatically placed in the cell you highlighted, but you can also create a new table or put it somewhere else from this box.
Once you’ve hit “OK” you will see a field box that corresponds with your columns.
You can drag-and-drop fields wherever you need them, filter them, add columns, and so on.
It’s completely customizable, and you can add or remove things later if needed.
You can use these tables for things like organizing links or URLs, grouping inbound links by DA/PA, keyword search per domain, or creating drop-downs for certain columns.
Here’s an example of what a functional pivot table might look like:
Not only does it organize your data in a more readable way, but it gives you several options for categorizing your data.
In the above example, you can immediately spot high or low cost-per-action (CPA) numbers for specific keywords.
This is done with conditional formatting within the pivot table (you can see that there’s color coding involved).
Pivot tables like this can be helpful if you’re using your keywords to inform your Adwords strategy, or if you want to quickly spot high or low search volumes or cost-per-click (CPC) counts for certain keywords.
You won’t have to scroll up and down a list of hundreds of keywords to spot outliers.
And it looks pretty good, too.
3. Convert volume numbers using “SUBSTITUTE”
There are plenty of different ways you can sort your data in Excel.
Finding the right option is key to productivity, however.
When you download data from a keyword tool, like Keyword Planner, the CSV files don’t always look pretty.
My keyword volumes, for example, look like this:
To be fair, they also look this way when I’m using Keyword Planner.
But when I’m trying to search those volumes or organize them by highest-to-lowest value, the format presents a bit of a problem.
Excel can’t sort my list based on terms like “10K” or “1M.” The M and K throw it off.
It needs real numbers.
Thankfully, Excel does have a somewhat quick formula that will help you sort volumes properly.
First, you want to replace the K and M and convert them into “000” or “000000.”
Create a new column called “Low to High” or something similar.
Select a cell in your new column, and insert =SUBSTITUTE:
Your formula should look something like this:
=SUBSTITUTE(C2,”K”,”000”)
The cell number will change depending on the row you’re converting.
Here’s what that looks like when you insert the formula:
And the final results:
You can also replace the M to “000000” using the same formula. It will look like this:
=SUBSTITUTE(C2,”M”,”000000”)
This should take care of all the Ks and Ms.
You can also do both at the same time (if you have a range of 100K – 1M, for example) using the following formula (replacing the cell number):
=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), “M”, “000000”)
There are also several formulas you can use to find the minimum, maximum, and average search volume for your new column.
Here’s the formula for minimum:
And maximum:
And average:
Keep in mind that you might have to add additional columns in order to use these formulas, so be sure to label them properly.
You should be able to quickly discern between a high-to-low and low-to-high column without having to do a lot of sorting or rearranging manually.
These formulas are supposed to save you time and energy, after all.
4. Extract specific data using “REGEXTRACT”
Of course, keywords aren’t the only data you will have in your SEO spreadsheets.
Sometimes you will have to sort through URLs, domain names, blog titles or email addresses.
You might need to find certain blog posts or landing pages that have an HTTP versus and HTTPS, for example.
You can always use Excel’s search bar to find them individually, but if you have a long list of URLs, this can be time-consuming.
Instead, use REGEXTRACT to pull specific data from your list.
REGEXTRACT isn’t like other Excel formulas, however. It requires a special syntax (string of code) to work.
You can learn more about it here.
The full formula with syntax for REGEXTRACT looks like this:
=REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)
That funny bit on the end is the syntax.
You can copy-and-paste the full formula into a new column (changing the cell number), and your results will look something like this:
You can use an ARRAY formula to add this to multiple rows at once.
The entire thing looks like this:
=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)”)),“”)
It’s messy, but it works.
Here’s what that looks like using our example:
Here’s the end result:
While this takes a little bit of time to set up initially, it will save you countless hours in the long run, especially if you have a lot of data to sort through.
If you’re still confused about the process, or about using syntax with REGEXTRACT, Ahrefs has a spreadsheet that shows how this formula works.
This is a lifesaver for those who don’t necessarily like using Excel formulas.
5. Format title tags with the “PROPER” formula
Another tedious task you might have to do with your SEO spreadsheets is reformat your data.
Maybe your title tags or keywords were imported in a lower case format, for example, but you wanted them to be capitalized.
You’re not going to sort through all of your rows and columns capitalizing your text.
Or, at least, I hope you’re not going to do that because you can easily toggle lower and upper case characters in bulk using the PROPER formula, which looks like this:
=PROPER(C2)
Easy, right?
You want to create a new column and insert the formula there.
Here’s what that looks like:
And the final result should be this:
This works well if you have a lot of title tags you want to capitalize, too.
You can search for individual title tags using the HLOOKUP formula (which I’ll get to later).
You can also use the PROPER formula to reformat format names that are in all caps, even if the characters are mixed between upper and lower case.
If you want to change a lower case keyword or title tag to uppercase (e.g., an acronym), you can use the formula UPPER.
Here’s how that looks:
As you might imagine, the LOWER formula reverses the process.
Even though this is a really simple formula, I’ve included it because a lot of people forget about it.
And marketers don’t always have time to go through keywords, title tags, or other text to capitalize things.
So if there’s a fast, easy way to do it for you, then you should use it.
6. Search large spreadsheets with “VLOOKUP”
Some marketers like to segment their data into different spreadsheets.
You might have one for keyword research, one for emails, one for domains, and so on.
But sometimes you might have all of that information in one place.
While it’s nice to have a large database of SEO information, it can be annoying to sort through all of those different columns, rows, or charts finding what you need.
As I mentioned earlier, Excel’s search functions are limited if your data isn’t already somewhat organized.
And it’s even harder when you have data that’s spread out over multiple rows and columns.
So, there’s a formula for that (which is the Excel version of “there’s an app for that”).
You want to use the VLOOKUP formula, which looks something like this:
=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)
If you wanted to look up specific keywords between two sheets on the same spreadsheet, for example, it might look like this:
This formula might seem a little complicated at first, but it gets easier once you break it down.
The first section is the name of the item you’re searching for. You’ll put it in quotes.
Next, you add your column range.
Then you need to add a column index number. The first column in the range you’re searching is 1, the second column is 2, and so on.
Since I’m trying to find keywords in the first column, I’ll go with a 1, and I’ll follow it with a “TRUE” statement because I want an exact match.
I can also use “FALSE” if I don’t need an exact match.
Note that VLOOKUP only works if the data you need is in the first column (the left-most column) and the data is in ascending order (A-Z).
So you might need to format your columns before performing a VLOOKUP search.
But it’s still handy nonetheless.
You can also use this for things like finding a product price or a specific category, like so:
If you need to search rows, you can use the HLOOKUP formula instead.
(V is for Vertical search and H is for Horizontal.)
An example HLOOKUP formula might be something like this:
=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)
Click here for an example of the HLOOKUP function.
Both are quick ways of finding what you need, particularly if you need to search multiple rows, columns, or sheets within your document.
You can also use both features between two separate documents, if needed, and search from drop-downs and pivot tables.
There are plenty of tutorials out there on how to use both formulas to find and calculate different data between sheets.
You can really become a pro at these two formulas if you want.
That’s why they far excel (no pun intended) the traditional search bar.
7. Quickly identify duplicates using “COUNTIF”
Not all of the data in your spreadsheet will be correct.
You’re going to want a quick, easy way to sort through it, remove duplicates, and move on with your day.
Excel’s formula for that action is COUNTIF, and it looks like this:
=COUNTIF(A:A,A2)
Let’s say you have a list of items in your first column (column A) that you want to check for duplicates.
First, create a new column and input the formula with the cell range from column A, like so:
Then, drag the fill formula to copy it to other cells, and you should see something like this:
TRUE indicates that multiples exist, while false means that there are no exact duplicates.
This is a nice little formula for searching keyword lists for duplicates because it only finds exact matches.
So you see in the example above, even though the word “seo strategy” shows up different times (seo marketing strategy, for instance), it’s not considered a duplicate.
If you simply want to highlight those duplicates, you can also do that with conditional formatting.
Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
You can then select a color that you want your duplicates to have.
This is nice if you just want to highlight them, but you’re not ready to remove them.
If you do want to remove them, you can do this by clicking Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.
The whole process takes a couple of minutes, and it can really clean up your data quickly.
Some of these formulas can take some time to “master,” but Excel offers plenty of buttons and quick formulas for helping you out.
The COUNTIF formula is one of them.
Conclusion
There are plenty of people who don’t like using spreadsheets or messing around with formulas.
I get it.
Some of the formulas are a little complicated and bulky to remember (see REGEXTRACT).
But if you’re using spreadsheets — especially Excel spreadsheets — for SEO or marketing research, you really should brush up on some of the formulas.
More specifically, look at ones that will help you search or clean up your data quickly, like VLOOKUP and COUNTIF.
Being able to convert your volume numbers using SUBSTITUTE is a must for keyword research.
And learning how to create more complex (but helpful) things like pivot tables will save you so much time and energy in the end.
SEO is complicated enough as it is.
Ease the burden by learning a few Excel hacks in the process.
What is your most recommended Excel hack for cleaning up your spreadsheets?
Grow your traffic