Excel is one of the most-loved tools among SEOs - and with good reason.
Whether used for alphabetizing keyword lists, managing page-level data, or auditing your site’s SEO audit data, it’s easy to relay important information within an Excel spreadsheet. With that being said, some formulas and functions are trickier than others; but, those that master the basics and beyond are set up for enormous success.
In this post, I chat with Mary Kate from our marketing team to share the most sought-after Excel tricks I use with clients and how you can implement them in your everyday SEO workflows.
But first, you’re probably wondering:
Why Do SEOs Use Excel in the First Place?
It might seem counter-intuitive to talk about Excel when our amazing platform allows you to slice, dice and analyze data in a far more granular way than a spreadsheet. While that is true, Excel presents a foundational approach to working with your SEO data.
Looking for free solutions to propel your business forward? Try our free tools and templates and see how you can scale your SEO today using convenient solutions at little-to-no cost.
I encourage users to apply these formulas and functions for unique analysis based on their specific use cases in addition to using the platform. With Excel, you can go above and beyond what's in front of you in the UI of the seoClarity platform.
MK: How do you see clients using Excel within their SEO work?
HC: Typically, I’ve seen clients use Excel for the following reasons:
- Organize massive amounts of data
- Share data across teams
- House keyword research / keyword database
- Eliminate duplicate data
- Work against multiple sheets and workbooks-worth of data
MK: If the seoClarity platform solves for this, what specifically are some of the ways in which you see clients using Excel to perform these tasks?
HC: I like to share the following four core Excel functions with clients who are SEOs: VLOOKUP, UTF-8 encoding, conditional formatting, and text-to-columns.
- VLOOKUP helps to identify matching values between multiple data points in columns, sheets, workbooks, and so on.
- UTF-8 encoding provides translation of keywords away from special characters that would affect their addition to being managed.
- Conditional formatting highlights duplicate values and are useful when needing to compare multiple values such as multiple listings or SERP features.
- Text-to-columns is helpful to reorganize your keyword, tag and URL lists in the order in which they need to be added into the platform.
My opinion is that downloads from the UI in the seoClarity platform into Excel brings a flexible range of customization and filtering options that we’re used to doing from past experiences. For some who are used to using Excel over a platform, this is easier than having to search or manipulate a filter to replicate desired data sets.
The Best Approach for SEOs Using Excel
MK: When does an SEO typically use the above formulas, formatting, and functions?
HC: As I mentioned above, users are going to have their own specific use cases with which to apply these Excel tips in addition to using the platform. Typically, I tend to see the following:
- VLOOKUP functions are used when keywords have been added or deleted from the platform. Clients can bring in multiple data points and metrics from one workbook or sheet to another workbook or sheet on one consolidated spreadsheet.
- UTF-8 transposes special characters to English or Native Language of what those keywords are supposed to be. It also eliminates other special characters that are not accepted through seoClarity’s “Add Keyword” functionality.
- Conditional Formatting finds the duplicates of keywords within Excel, and analyzes multiple listings for the same keyword. Same for duplicate pages - if they are ranking for different keywords.
- Text-to-Columns formatting purposes that match the way keywords, tags and pages need to be uploaded in the seoClarity platform
MK: So often we tell users that, with an enterprise-level platform, you don’t need to be an “Excel jockey” anymore. What do you say to that?
HC: I see myself using Excel on a weekly basis, if not daily, in addition to using the platform's UI. seoClarity platform users can copy, paste, or delete columns or rows of exported data that they don’t need to know in an Excel spreadsheet.
So, to answer your question, SEOs may need to utilize Excel to sift through data more quickly and with granularity.
Often the formulas above will help on more special or custom projects that aid or work in tandem with some of seoClarity’s capabilities
MK: Is using Excel more for the expert-level SEOs or do you see Excel being used more by beginners?
HC: These functions are for all levels that need to utilize Excel. There have been advancements made to how VLOOKUP functions can be utilized, for example, but even I’m not at that level… yet - I’m working on it!
As a previous beginner, these Excel functions allowed me to relate how the platform can slice and dice the data within the UI rather than having to download the data.
MK: Would you say the need for Excel has decreased or increased as you’ve become more familiar or advanced with SEO?
HC: I believe that Excel is a great tool to analyze, verify, and validate your data. I’ve found it to be a really convenient spot-check on your rankings, keywords, and pages. Even some of the most advanced SEOs I know use Excel for the flexible components that it provides. And, it only assists me in my own usage of the platform.
Challenges with Using Excel
MK: Despite the ease of use, what additional challenges might arise where clients need to use an Excel function over an SEO tool or platform solution?
HC: With any tool or solution you’re going to deal with some nuances along the way. I remind clients to remove any invisible spacing, characters or elements in order for VLOOKUPs to work correctly, for example.
In addition to this, the column, sheet, or workbook you are working against should also have invisible spacing, characters or elements removed.
Other formatting concerns that need to be addressed: (such as eliminating the formula or function within your results to prevent an #N/A outcome) these aren’t so much of a formula-solution, but rather simple copy/paste functions to ensure the data is reflecting the outcome of the function, and not the function’s formula.
Lastly, UTF-8 encoding requires uploading your file as a .txt or .csv document. XLS-related documents will not work.
These formulas and functions will carry you far in your SEO journey, and while they take practice and patience to master, you’ll be an Excel wizard if you take the time, enhancing your overall usage of the seoClarity UI. For SEOs looking to scale their operations even faster, an SEO platform can help you do all of these things and more with ease and efficiency at scale.