CSV this XLS that. Whether you’re exporting from Screaming Frog, Ahrefs, OnCraw, SEMRush, Ubersuggest… anything really, the chances are you’ll end up in Google Sheets or Excel. That’s just the way the life of an SEO goes.
When I started my career in SEO, I have to admit my Excel skills weren’t the best – but I was lucky to have a good manager. His football team (Tottenham) is questionable but his SEO and excel skills are not. I’ve been lucky enough to learn from some of the best in the game.
So today, I’m going to list (and show you how to do) 10 essential spreadsheet skills that will hopefully help you on your SEO journey.
10 Excel & Google Sheet Functions For SEO Professionals
1. VLOOKUP
VLOOKUP is an essential function that allows you to look for specific information in your spreadsheet. It’s perfect for linking data between different tabs or sheets. This is number 1 because it’s the first SEO / Excel still that amazed me.
Let’s say you have a list of keywords on one sheet and their positions on another. The VLOOKUP function would look something like this:
=VLOOKUP(“your keyword”, ‘Rankings’!A2:B100, 2, FALSE)
In my example below I want to see the keyword ranking for “zara”, so cell A2 is selected. The data is pulled from another sheet, columns A to H and the actual data is selected from column 8.
Vlookup is handy for keyword reporting, content gap analysis, keyword research and more!
2. COUNTIF and COUNTIFS
COUNTIF and COUNTIFS are essential functions for filtering and counting data that meets specific conditions.
Imagine you want to count the number of keywords that have a search volume over 1,000. You’d use the COUNTIF function like this:
=COUNTIF(B2:B100, “>1000”)
This would give you the number of keywords with a search volume greater than 1,000.
3. Pivot Tables
Pivot tables allow you to summarize, analyze, explore, and present your data in an organized way, which is essential for any SEO professional.
For example, you may have a list of keywords and their corresponding search volumes, competition levels, and more. You can create a pivot table to summarise this data quickly. Simply select your data and go to `Data > Pivot Table`.
Your pivot table will open in a new tab. On the right hand side simply check the pivot table fields you want to see. I am now about to see how many keywords are ranking for each position.
4. IF, AND, OR
These functions allow you to perform different actions based on whether certain conditions are met.
Let’s say you want to flag keywords that have a search volume over 1,000 and a competition level of less than 40′. You might use the following formula:
‘=IF(AND(C2>1000, D2<40), “Flag”, “”)’
This formula will return “Flag” for keywords that meet both conditions, and an empty string for those that don’t.
See how Keywords that have more than 1000 volume and less than 40 KD have the word Flag in column L.
5. SUMIF and AVERAGEIF
These are great functions to use when you want to sum or average data that meets certain conditions.
Perhaps you want to find the average search volume of all keywords with a competition level of less than 30. You could use the AVERAGEIF function like this:
=AVERAGEIF(D2:D5000, “<30”, C2:C100)
This is a great function to pull up quick numbers for reports – another use case is using SUMIF to see the total number of pageviews for pages that received less than 100 clicks.
6. Splitting Text with Text to Columns
One of my favorites – this is a handy feature when dealing with URL structures or strings where you need to extract specific information.
Say you have a list of URLs and you need to extract the slug. Highlight the column with your URLs, go to `Data > Text to Columns`, choose ‘Delimited’, and set ‘/’ as your delimiter.
See how Excel has now split my URLs up into different columns, this feature is great when running technical SEO analysis, trying to find information about page depth, or just analyzing data after a migration.
The longer you’re in SEO, the more you’ll be using this function.
7. CONCATENATE or “&”
Another one of my favourites.
This function is helpful when you need to join two or more text strings into one text string. It’s useful when you’re creating title tags, meta descriptions, or URLs.
It’s also useful when you’re making keywords (check out my guide to programmatic SEO).
For instance, to create a title tag from a keyword and a site name, you might use:
=CONCATENATE(B2,C2)
8. LEN
Since I just showed you a title tag, it’s only right to look at LEN next.
The LEN function is used to find the length (number of characters) of a given text string.
This is great when you want to see the character count to make sure you’re within your target range.
9. Conditional Formatting
Now it’s only right that we move onto conditional formatting. There’s so many uses for this function.
Simply highlight the column you want to add conditions to, in this case H.
Go to Home, then Conditional Formatting and you can highlight cells based on your criteria. For example, you might want to highlight all keywords in the top 3 with green.
10. Cleaning Data with TRIM and CLEAN
With these two functions there’s no need to manually spend hours trimming or cleaning data.
The TRIM function removes extra spaces from your data, while the CLEAN function removes non-printable characters.
They’re useful when you’ve copied data from different sources and need to clean it up.
To clean a cell of non-printable characters and extra spaces, you would use:
`=TRIM(CLEAN(A2))`
See how the extra spaces have been removed, this is a great function to help you get things done faster.
Spreadsheets Skills For SEO =(Your,Road,To,Success)
These 10 functions are just the start, Excel, Google Sheets & other spreadsheet tools are vital for pretty much everyone working in SEO. If you found them useful, then please do share with your audience.
Mastering the basics, and then moving on to more advanced functions will not only make your day-to-day work more efficient, but it will also open new avenues for data analysis, optimizations and making you look smarter in the office.
If you liked this article, then check out some of our other explainers. You’ll find guides on mastering the art of keyword research, performing world class SEO audits, how to use faceted navigation and more.
Subscribe to our newsletter for more SEO tips, news, insights and more!
