Much of the day to day activity of digital marketing departments and professionals involves handling large amounts of data. The modern digital marketer or SEO is bombarded with tools for collecting, collating and crunching a whole range of data and metrics, from web page analytics and backlinks to SERP rankings. From website analytics to keyword research, bets are you'll find several competing tools out there that can produce it, track it and report on it.
Whatever your data looks like it's likely that at some point you'll need to export this data to manipulate it further and when you do this you're almost certainly going to be using Excel.
As an SEO in a small to medium sized company, Excel is probably the most versatile and powerful reporting and analysis tool you're ever likely to use. As an outreach tool it is, in my opinion, without equal in its versatility. It is this versatility that really sets the Microsoft flagship apart, allowing it to handle any kind of dataset, filtering, pivoting and presenting it in any number of useful ways.
One of Excel's greatest assets is its ability to use macros, integrate with add-ons and connect with APIs. Below are a few of my favourites.
Niels Bosma's SEO Tools
The absolute daddy of SEO plugins. If you work in digital marketing and you ever download anything Excel related it should be Niels Bosma's SEO Tools. This add-on is free and easy to install, bolting on a whole suite of SEO utilities, lookup and scraping tools into your Excel ribbon. These include onsite and offsite lookups that return everything from meta descriptions and keywords to PageRank and Link Count.
SEO Tools also allows you to connect Excel directly to your SEOlytics or Majestic SEO account as well and pull in data directly from there. These API plugins will inevitably save you so much time by allowing you to import the data you need directly into your pre-formatted spreadsheet.
Majestic SEO describes SEO Tools on its own blog thus:
Much of the major functionality you can do on Majestic's main site can be replicated straight into SEO Tools for Excel. From there, you can augment any data with your own, giving you potentially limitless information about any URL or link.
Another brilliant feature in SEO Tools (and there are too many to list them all here) is the Google Analytics extension. This allows you to pull in visitor data, traffic source data, Adwords data and much more straight into your Excel spreadsheet. The latest version (v4.1.1) also features integration with Google AdWords.
Below is a full list of all the functions available in SEO Tools. Niels site itself has tons of information on using the tool and features a complete list of all the tools' functions and how they work.
SEO Gadget have put together a very useful Excel tips video featuring SEO Tools and Find My Blog Way has created a more detailed video tutorial on using SEO Tools, which runs for over half an hour and is also well worth a watch.
2. URL Tools
After the power and versatility of SEO Tools comes a much more modest add-in for Excel. URL Tools is an add-in by James Taylor of iCrossing that I've found invaluable over the last couple of years. It may not have the multitude of functions that SEO Tools boasts but what it does do it does very well.
One of the problems with working on huge list of URLs in Excel is the inability to easily identify duplicate root domains. Whether importing, exporting or pasting URLs directly into your spreadsheet, you're likely to end up with a whole variety of URLs structures. Some URLs may feature the https:// or www. prefix whilst others dont. Some may include subdomains, some wont.
Whilst there are formulas that enable you to pull out the root domain of a given URL, they are complicated and arent always fool proof. Once installed URL Tools lets you enter a variety of simple formulas to use, but the only one you're ever really going to need is the =wwwsubdomain function.
This is without doubt the simplest way Ive found of taking a huge list of URLs and getting the root domain of each really quickly without any fuss. After this it's a simple case of copying and pasting as values and then applying conditional formatting to highlight duplicates.
3. Collaboration Macro for Dropbox
Whilst Dropbox isnt the only cloud networking solution for small businesses out there, it certainly is one of the most popular. But it does have its drawbacks. One of the most infuriating of these is the dreaded conflicted copy issue.
Many of us have been there: You've spent an entire morning labouring away at a spreadsheet only to discover that one of your colleagues has been hard at work on the very same spreadsheet and Dropbox has created a conflicted copy file, which you now have to go about amalgamating with your colleague's copy (that's your plans for the afternoon out the window).
Of course the obvious solution to the problem of conflicted copies is to invest in a server based network, on which two or more people can edit the same document at the same time. This costs money though, which isnt always worth it if you're a smaller businesses running on a tight budget.
The Collaboration Macro (or CoMac) effectively stops two people from going in and editing the same spreadsheet at the same time, by essentially checking out the spreadsheet once a user opens it on his / her local machine. Anyone then trying to open the same spreadsheet will be greeted with an on-screen message telling them the spreadsheet has been checked out and by whom. All that's involved from your part is copying and pasting a bit of VBA code.
The CoMac does have a few issues and bugs associated with it but hey, it's free right? You can download the VBA code here and then paste it directly into the Visual Basic Editor in Excel (open it through the Developer tab or Ctrl+F11).
Whilst the Our Community News website might not look the most obvious forum for VBA coders, I can assure you the code does work. OCN has also written similar VBA code for collaborative Word documents on Dropbox.
There are more professional looking solutions out there to solve collaboration issues on Dropbox but, unlike the CoMac code, they all seem to cost money. Conflicted Copy Pro is one of the more popular Ive found and comes with a free 30 day trial, after which it costs $7 USD a month for the licence.
4. The MyVlookup Macro
The holy grail of Excel formulas, the Vlookup typifies why Excel is such a trusted piece of software for SEOs and digital marketers when interrogating large amounts of data. Many of you will have undoubtedly have used the Vlookup formula before but few of you will have encountered the MyVlookup function before. This is a variant on the standard Vlookup that uses a simple bit of VBA code and allows the lookup to return multiple values instead of a single one.
First of all you need to save your Excel file as a Macro Enabled spreadsheet. Then reopen the file and go into the Developer tab (or press Ctrl +F11). Go to the Insert Menu and click Module and paste in the following piece of code.
Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long) Dim r As Range Dim result As String result = "" For Each r In lookuprange If r = lookupval Then result = result & " " & r.Offset(0, indexcol - 1) End If Next r MYVLOOKUP = result End Function
Save and exit the VBA Editor and go back into your spreadsheet. You should now be able to use the MyVlookup function (if you can't try saving, exiting and re-entering the spreadsheet).
Here's the MyVlookup formula used beneath a standard Vlookup. You'll notice that the original Vlookup has failed to indicate that Sarah has worked in HR and Marketing. The MyVlookup will on the other hand return all results from column 2 relating to Sarah.
The keen eyed Vlookup savvy of you out there will have noticed that the formula syntax for the MyVlookup is slightly different. Instead of selecting the entire table array to return a value from, you only need to designate the column (in this case A1:A12) before counting the rows across you want the formula to return a value from (in this case row 2).
5. XS Format Cleaner 1.1
Last but certainly not least we have the XS Format Cleaner.xla, an add-in that will clear up any excess formatting you might have lurking in the recesses of your Excel worksheet. Recommended by Microsoft this little bit of software will cut file size and address problems you might have with memory issues or printing out too many pages.
Dealing with large amounts of data it's all too easy to find yourself with excessive formatting in cells that appear to be blank. One way of finding out where your end cell is located is by pressing CTRL+SHIFT+END on the keyboard. This is the point at which Microsoft will save your data too. If you find a lot of the cells between hear and your actual data are blank then you might want to think about getting this tool.
A Final Word
If you're still learning the basics of Excel, then there are a host of excellent tips, tools and resources out there to help you get to grips with it. One of the best Ive found written specifically for SEOs is Distilled's comprehensive Excel for SEOs guide.
Unsurprisingly Microsoft have a load of Excel videos and tutorials available for the general user, ranging from complete beginner stuff like locking panes and adding columns to more advanced operations like recording macros.
Excel is probably the most versatile tool you'll ever have in your armoury when interrogating the masses of data you have collated. Learning to use it properly is a skill well worth having and with the right add-ons, formulas and macros at your fingertips there's no limit to what you can do.
