One of the perennial problems for SEOs is that they find themselves drowning in data; they have all of the information they need at their fingertips, but they don’t know how to process it so that they can see the big picture. There are dozens of tools out there for SEOs, including keyword search tools, graphing systems for analytics, traffic estimators and more but none are as adaptable, upgradable, versatile and as universally compatible as Microsoft Excel.
Most of us have had some experience in using Excel and often have a passing familiarity with how to use it. The tips below will help you to make the leap from reluctant Excel user to confident power user and start bringing meaning back to your endless reams of SEO data.
- Import Your Backlink Data
The main search engines and analytics tools offer a lot of ways to export your backlink data and your referral data. It’s easy to import this kind of information into Excel. In fact, you can even go one further than that and import RSS feeds into an Excel spreadsheet (albeit using a workaround and importing the feed into a Google Spreadsheet, then saving that in Excel format). Once the data is in Excel, you can use it to track keywords, count referring domains and more.
- Learn to Love Pivot Tables
Pivot tables are one of the most valuable data analysis tools available in Excel and they are ideal for keyword research. At the a basic level a pivot table is a powerful means of taking a large and often unwieldy table of data and presenting it in a different way. Imagine being able to create a chart that would let you view keywords by both search volume and by ranking. In fact you can pivot on various data headers in any way you see fit, whilst filtering out anything you don’t want to see using a report filter. Such a table would be a brilliant tool for prioritizing your AdWords campaigns for example.
Image courtesy of Richard Baxter of Built Visible
- Visualize Keywords Easily
Did you know that you can use find and replace in Excel to not just replace words but also to highlight cells. This means that you can search for a given keyword within a key phrase and then change the colour of that cell to highlight the presence of the keyword. When the search and replace is completed, you will be told how many times that keyword was found, giving you an idea of the keyword’s popularity.
This isn’t the only thing you can do with highlighting, either. Excel’s conditional formatting can be used to track character count. This means that you can import metadata from your pages and highlight pages that have meta descriptions that are too short or too long.
- Speed Up Link Prospecting
Another handy feature of Excel is the option to extract URLs from a list, and then make them clickable using a simple macro such as this one. This is great for link prospecting because it means that you can sort the list by whatever metrics you have access to (page rank, domain authority, traffic predictions) and then click on each link to visit it and check out the website, saving you the tedious job of having to copy and paste the URL into your browser every time.
- Import Data from Other Services
Every SEO worker should appreciate the value of Majestic SEO and SEO Moz. You can import data from those services into Excel to create your own graphs and charts, or to manipulate the reports in ways that their services do not support. If you aren’t a wizard with macros and scripts though then SEO Tools is a powerful and totally free plugin (at the time of writing) for Excel which gives you direct access to Majestic, Google Analytics and a whole host of other onsite and offsite functionality.
Excel is a great tool, and it is well worth taking the time to learn some basics such as how to make pivot tables and how to use regular expressions. The day or two that you invest in picking up such skills will more than be offset in terms of enhanced productivity and understanding of your data. Whether you choose to use Excel at a basic level or delve into the world of formulas and macros is really up to you but once you’ve got some of these basics under your belt you’ll wonder how you ever did without it.
About the Author: Joe Cox is Head of Content for Bristol digital marketing and SEO agency, Bespoke Digital. He has written about SEO, social media marketing and digital PR for the likes of Smart Insights, Ad Age, JeffBullas.com, Social Media Today and Search Engine People. You can follow him on Google Plus, or connect with Bespoke Digital on Twitter and Facebook.