7 Essential Tips in Using Excel SEO Must-Know

Excel is a way of life for many marketers, being beloved not only by some but by many. Whether used for analyzing survey data or calculating sales data, at some point, you’re probably going to open an Excel doc. Here are 7 Essential Tips in Using Excel SEO Must Know.

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, it’s a huge time-waster if you have to do everything manually.

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 should you learn Excel for SEO?

If you’re not already using some spreadsheet software, you should be. The 7 Essential Tips in Using Excel SEO Must Know will help you.

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 an excellent 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.

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.

Having to learn or remember how to use specific formulas can feel like a bummer.

You might enjoy using it once you know how it works.

Need to know more about advanced hacks for Excel? Here are some of the 7 Essential Tips in Using Excel SEO Must Know.

1. Use the “IF” formula to create keyword categories

The one thing you learn when doing keyword research is to embrace long lists.

Other tools, like Ahrefs or Moz, might give you lists of thousands of keyword ideas, volume metrics, and competition numbers.

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:

Given an “Ad Group” column when you export your list of suggestions that will separate seed keywords from keyword ideas.

Let’s say you want to break down this list into just the keywords that have the word “tools” in them.

If you look manually, you have to sort through 700+ rows. Of course, we don’t want to do that.

So instead, you’re going to use the Excel formula =IF to sort it for me.

The first thing you must do is remove the Ad Group column because you want to search for all of them.

Then you are going to add a “Category” column next to my search volume.

In the first available cell, you will put the word “Tools” and then use the formula:

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 into specific categories.

This is a game-changer for SEO organizations.

If you want only the keywords that contain the word “local,” for example, you could create a “Local” category.

=if(is number(search(“tools,A12)),”Tools”)

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.

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. That’s why 7 Essential Tips in Using Excel SEO Must Know will make it easier.

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 automatically place 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 specific columns.

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 spot high quickly or low search volumes or cost-per-click (CPC) counts for specific 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. Find out more about the 7 Essential Tips in Using Excel SEO Must Know.

When you download data from a keyword tool, like Keyword Planner, the CSV files don’t always look pretty.

The keyword volumes, for example, look like this:

They also look this way when using Keyword Planner.

But when you’re 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 the 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:

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 with “000000” using the same method. It will look like this:

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):

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:

=SUBSTITUTE(C2,”K”,”000″)

And maximum:

And average:

Keep in mind that you might have to add additional columns to use these formulas, so be sure to label them appropriately.

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.

=SUBSTITUTE(C2,”M”,”000000″)

Sometimes you will have to sort through URLs, domain names, blog titles, or email addresses.

You might need to find individual blog posts or landing pages that have an HTTP versus and HTTPS, for example.

=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000″), “M”, “000000”)

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 (a string of code) to work.

The full formula with syntax for REGEXTRACT looks like this:

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:

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 to 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.

=REGEXEXTRACT(A2,”^(?:https?://)?(?:[^@n][email protected])?(?:www.)?([^:/n]+)”)

You’re not going to sort through all of your rows and columns capitalizing your text.

Or, at least, hopefully, 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:

That’s easy.

You want to create a new column and insert the formula there.

Here’s what that looks like:

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?://)?(?:[^@n][email protected])?(?:www.)?([^:/n]+)”)),””)

And the final result should be this:

This works well if you have a lot of title tags you want to capitalize on, 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 straightforward 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 on 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”

=PROPER(C2)

Some marketers like to segment their data into different spreadsheets.

But sometimes you might have all of that information in one place.

While it’s nice to have an extensive database of SEO information, it can be annoying to sort through all of those different columns, rows, or charts to find 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:

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 for is 1; the second column is 2, and so on.

Since we’re trying to find keywords in the first column, we have to go with an one1 and follow it with a “TRUE” statement because we want an exact match.

We can also use “FALSE” if you 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:
Here is 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.

=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

You can 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:

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 an excellent 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 at 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 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 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.

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.

=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

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 complicated (but helpful) things like pivot tables will save you so much time and energy in the end.

Though SEO is complicated enough as it is, you can ease the burden by learning a few Excel hacks in the process.

What is your most recommended Excel hack for cleaning up your spreadsheets? Here are the 7 Essential Tips in Using Excel SEO Must Know.

=COUNTIF(A:A,A2)