SEO Number Crunching – 5 Great Excel Features
Excel includes several features that can be very useful in improving SEOs' efficiency and can also enhance their ability to analyze relevant site performance data.
Excel is one of the most diverse and widely used tools in managing data because so much can be accomplished with this single tool. For example, there are people who are able to create entire infographics, games, and even programs using only Excel. Have you considered how it can be utilized to improve SEO? Here are some examples of how that can be accomplished using bot native formulas and tools as well as an extension that makes Excel even more powerful.
Specific Tasks: Formulas for Beginners
The key to using Excel successfully is applying the right formula to the right data set. Excel can be used in a variety of ways, but the formulas are by far the most-used feature. Sometimes the Excel formulas may seem to be very complex. In truth, some are, but don’t be intimidated. That built-in complexity offers a savvy SEO significant power. Here are a few formulas that can be really useful for SEO analysis:
Action Performed: Returns the number of characters in a cell.
SEO Application: It’s particularly handy in creating the right title and meta descriptions that meet the search engines’ character counts.
Example » =len(A1)
Action Performed: Allows you to combine data from multiple cells. The text items can be text strings, numbers, or single-cell references.
SEO Application: A good use of it would be to create phrase match and exact match generator keywords. Another good use would be constructing large numbers of URLs for new content.
Example » =concatenate(A1,” “, A34,” “, B5)
Action Performed: The IF function is a simple logic test. If the result of that test is true, do something. If the result of that test is false, do something else.
SEO Application: A good application of this function lies where the filters can’t do the job. For example, perhaps you need to verify if a 404 error page (URL) is from a particular country site. Or perhaps verify if a PDF file and if has pageviews between 1,250 and 15,000. The IF function can find these answers for you.
Example » =if(A1 > 10,”do something if it is true”, “do something if it is false”)
Action Performed: Convert texts in a cell into lowercase letters.
SEO Application: It can be handy to fix keyword lists exported from different sources with malformed words. It could also be useful in modifying large numbers of URLs with upper- and lowercase letters.
Example » =lower(A1)
Action Performed: The Vlookup is one of the most powerful functions in Excel. It allows a user to do a vertical lookup to find a value in a range or cells.
SEO Application: For example, it can be used pull search volume data from AdWords report and aggregate it into another report to project the traffic based on the rankings.
Example » =vlookup(A1,A1:C29,2,true)
Broader Utility: Beyond the specific formulas discussed, Excel has some broader uses that make it useful for SEO.
Manage Large Amount of Data
Excel is an excellent tool to use when dealing with large amounts of data. It provides an extremely useful feature called a Pivot Table. It is perfect to summarize, analyze, explore, and present summary data.
The Pivot Table is not the only feature in Excel that can perform this function, but it is the fastest way to break down the data into smaller data segments easier to deal with later.
A Pivot Table report is useful when analyzing related totals, especially when managing a long list of figures to sum and you want to compare several facts about each figure.
Additional Features: Extensions
In addition to the various features Excel has to offer, there are some additional tools to speed up the process. In Excel they are called add-ins.
One very popular SEO add-in for Excel is the SeoTools created by Niels Bosma. It is an Excel add-in that adds a bunch of useful functions for working with SEO and other Web metrics directly in Excel. This add-in can save a lot of time gathering data from different sources. One of the best features is the fact that it allows you to create custom SEO scorecard/report, which provides a succinct status report on your site’s performance.
The installation is very simple. It doesn’t cost anything. Totally free.
In summary, with a bit of training and experimenting, Excel offers some very useful features that can improve an SEO’s efficiency, as well as greatly enhance the ability to analyze data relevant to a site’s performance. What do you think? What are your favorite Excel tools/tricks that you use related to your site’s optimization?