Data reigns supreme in the world of digital marketing and Excel is the tool needed to digest that data in a meaningful way. While the SEO landscape boasts a plethora of sophisticated tools and robust data sources, Excel remains the timeless cornerstone of every SEO professional's toolkit. Picture it as your trusted Swiss army knife, ready to dissect, refine, and optimize your data at a moment's notice. In this article, we'll reveal our Excel formulas that we couldn't live without.
Formulas: Using Excel's Formulas for SEO Success
Concatenate
This allows you to combine two things, whether it be two cells or 1 cell + words.
We use it the most when exporting data from Google Analytics. Google Analytics doesn’t export the domain name with the landing page slug, so sometimes we need to add the domain to the slug.
Formula Structure
=CONCATENATE(Data 1, Data 2)
Example
=CONCATENATE(https://transistordigital.com/", B2)
Your Title Goes Here
Stupped on what to do next? Reach out to our expert SEO team for all the answers.
VLookUp
VLookUp allows you to pull data from one place (in any other tab in a spreadsheet) to another. This is super helpful when we are combining different data pulls into one place.
We use this extensively in our own keyword research spreadsheet to pull data together from different sources we use during the research process.
Formula Structure
=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, false).
Example
=VLOOKUP(A2,Keywords!A:C,3,FALSE)
In this example, we want to lookup the query in cell A2 in the tab title Keywords using the data in columns A:C. We then want to return the value in the third column. In this case, it would be search volume.
IfError
IfError is added to another formula. This informs the formula that if the original formula doesn't work, then include this copy or number instead of N/A. This is GREAT when you are combining data sources. We use IfErrors to make data consistent in our keyword research process.
Formula Structure
=IFERROR( value, value_if_error )
Example
=IFERROR((VLOOKUP(A2,Keyword!A:C,3,FALSE)), 0)
In this example, if we try to look up the search volume for a query and that information is not in our dataset, then the formula will return the value of 0 instead of displaying N/A.
SumIf
SumIf adds data from specific cells if they meet a certain criteria.
We could use this to add sessions only from pages that contain “blog” in the URL.
Formula Structure
=SUMIF(range, criteria, [sum_range])
Example
=SUMIF(A:A, “https://transistordigital.com/blog*”, B:B)
In this example, we are looking at the URLs located in column A that contain “blog” and returning the sum of the values (or sessions) in column B that are associated with the blog URLs.
Note that the “*” means that there can be any combination of characters after “https://transistordigital.com/blog". This is necessary because all of the blog URLs are different.
Length
The LEN function is very easy to use and will count the characters in a cell.
This is really helpful for writing succinct metadata.
Formula Structure
=LEN(cell)
Other Tools
Deduplicate
Removing duplicates is important when combining any data.
One way we use this is when combining keywords during the keyword research process. Note that it is important to change the settings to only select the column with duplicated content.
Excel proficiency is an invaluable skill set for any marketing professional looking to optimize their workflow and improve their results. By harnessing the power of Excel, you can improve your data analysis, automate repetitive tasks, and gain deeper insights into your SEO performance. Whether you're a beginner or an experienced SEO expert, mastering Excel SEO formulas, tools, and tips can significantly enhance your efficiency and effectiveness in digital marketing. Embrace the power of Excel, and watch your SEO efforts transform into business success. Remember, the key to mastering Excel SEO lies in continuous learning and practice, so keep experimenting and refining your techniques to stay ahead in the ever-evolving world of SEO.
