Excel is like the Swiss Army knife of spreadsheets. You can do just about anything with it. When you combine it with SEO, there are endless possibilities. As an SEO marketer, it’s important to measure and monitor your link building campaigns, rank reports, and keyword rankings. I’ve found that using Excel with SEO tools is the most precise way to manage your campaigns over time.
You knew it was coming. A Google search for SEO tools will leave you with a few options, none of which are perfect. Tools that rely on Google’s API can be horribly inaccurate and rarely provide anything more than a one-off report. And premium tools like Moz and Ahrefs require significant investments, making them an unattractive option for hobby bloggers and bootstrappers.
So what follows are some things you might not know you can do with Excel.
Note: While this chapter focuses on Excel, you can perform most of these tasks in any spreadsheet program, including Google Sheets.
1. WildCards
Excel supports the * command as a wildcard.
If you want to cut off part of a data set, it’s easy to do.
For example, let’s say you have a very large list of links but you only want one sample URL from each domain.
After you find/replace for http:// and https://, you can reduce every single URL to only its top-level domain by finding and replacing for /* – the star is a wildcard that removes everything after the first backslash.
If you want the backslash left on, replace for /.ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
If you want the / gone, replace for an empty field.
Then you can use that de-duplication function to remove all but one example of every URL.
2. LEN
This one counts the number of characters in the string.
This is great for checking the length of titles or meta descriptions. But it can also be used to bring the longest string to the top.
Taking the previous example, let’s say you wanted one sample URL from each domain that you deduplicated, but you wanted an interior page instead of just the top level domain.
Unlock (not provided) with Keyword Hero
See all your organic keywords in GA and their specific performance metrics. Free Trial. Cancel anytime. Professional support. 4-minute setup.Free AccountADVERTISEMENT
You’d take the same steps as above, but instead of editing the URLs in place, you’d copy them to a new column (Source Domain) and edit those instead.
Then, use LEN to find the character length of the Source URL and sort by Col A ascending and Col B descending.
That will bring the longest URL for each domain to the top of the list and then when you de-duplicate, it will save the first instance of that domain – the longest URL.
3. Text to Columns
Text to columns is another brilliant feature of Excel.ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
You’ve probably used it to make a .csv file useful, by delimiting the data by commas. But did you know you can actually delimit any character you want?
I use this all the time to delimit by the backslash.
This is a terrific way to take an output from Screaming Frog or another crawler and quickly build out a visual architecture map.
By putting each subfolder in its own column, you can use a pivot table to find out exactly how many pages are in each level, then use the data from the pivot table to quickly build a visual map in something like Visio.
4. Find
The Find function is one of the most useful functions in Excel.ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
You can use it to eliminate things from lists of keywords.
For example, let’s say you have a huge list of keywords and you want to find things that only contain one version of a word (useful when categorizing).
You would start a new column, then use this formula where “word” is the word you want to find.
=FIND(“word”,B2,1)
This will show you the character count at which that word starts, and an error if the word isn’t there at all… like this list where I looked for anything with “mixed”:
Just sort by the “Sort” column descending, and you’ll bring all the keywords that contain “mixed” to the top of the list.ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
5. Concatenation
Concatenation is great for putting together simple rows of data, but it’s even better for automating the creation of a lot of URLs.
Let’s say you wanted to search a lot of sites to see if a particular keyword was in text on that site… maybe you’re doing a link audit and you need to see if your client’s domain name appears anywhere on a list of sites.
You could (theoretically) concatenate a simple list of commands that you could then run in a crawler like Screaming Frog to see if there are any results.
So, hypothetically speaking, you would do this:
Take the command you need to add and put it into a row in excel.
Let’s say the site is motorola.com, and you’re looking for the word “motorola” on a lot of websites, one of which is www.cnn.com.
Put the site you are searching on in A1. You’ll use this command in Excel:ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
=”https://www.google.com/search?q=site%3A”&A1&”%20motorola”
If each row in column A has a new value in it, then in theory, you could have a full set of Google commands to put in list mode in Screaming Frog.
Using the Custom configuration, you could look for any results that had “No results found for” motorola.com in the code.
This is all theoretical, of course, because scraping Google is a violation of their terms of service.ADVERTISEMENThttps://f71f62f5223803de090e7496077fa8cb.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.htmlCONTINUE READING BELOW
Conclusion
Need to create a list of keywords for SEO or manage your SEO workload? Our SEO tools for Excel allow you to create a workspace to store anything from a small number of keywords to an entire website. Then, you can use the internet shortcut keys to quickly perform tasks such as adding new keywords, exporting your entire database into a CSV file, and much more.