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.
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.
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.
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?
Image via Shutterstock.
Want to learn more?
Attend ClickZ Live New York March 30 - April 1. With over 15 years' experience delivering industry-leading events, ClickZ Live brings together over 60 expert speakers to offer an action-packed, educationally-focused agenda covering all aspects of digital marketing. Register today!
Crispin Sheridan is the Senior Director, Global Search at SAP. As part of the digital team, he established and leads the search and testing practices at SAP. Crispin is responsible for paid, natural, and mobile search and all online testing. Search and testing at SAP are fully centralized and globally funded and run under a hybrid in-house and agency model.
Crispin has proven that search learnings and keyword insights work hand in hand with social media marketing and together can effectively drive B2B lead generation. Furthermore, the development of the SAP.com Test Lab has contributed significant success to SAP's digital marketing efforts.
A frequent guest speaker at conferences, including SES New York, San Francisco, Toronto, London, Delhi, Shanghai, and Hong Kong, Crispin was appointed to the SES Advisory Board in December 2009. He has also been a guest speaker at the e-Metrics Summit and ad:Tech, and is a member of Google's B2B Technology Council. You can follow him on Twitter at @crispinsheridan and read his monthly SEO column on ClickZ.
Singapore, 5-6 March
Bangkok, 17-18 March
Hong Kong, April 2015
A Buyer's Guide to Affiliate Management Software
Manage your performance marketing with the right solution. Choose a platform that will mutually empower advertisers and media partners!
Google My Business Listings Demystified
To help brands control how they appear online, Google has developed a new offering: Google My Business Locations. This whitepaper helps marketers understand how to use this powerful new tool.
March 3, 2015
1:00pm ET/10:00am PT