If you run reports from the SEOmoz toolset, you may find it difficult to make your data look as sexy as the graphs you see on the site. I hope to close that gap with this post and give you the tools to format your data like a pro. To accomplish this objective, I've provided several resources:
- A video walkthrough of how to format your data like one of the SEOmoz charts for you DIY'ers out there.
- Chart template files for all of the charts on the SEOmoz site that have export options (there are three in total).
- Instructions on how to load the template files.
- A video walkthrough of the data prep and cleanup steps required with the templates.
Here's a screenshot of the three charts you'll be able to replicate:
Download the Excel file
Editor's note: These downloads are no longer available.
You can download the Excel file I worked from to create these charts to follow along. Whether you watch the video to learn how to steal all of SEOmoz's formatting ideas format like an SEOmoz data scientist or take the shortcut and download the chart template files, you may want to check out the Excel file for tips.
Watch over my shoulder as I create one
The report I’ll be demonstrating is the Historical Domain Analysis chart, found under Pro Dashboard (or Campaigns) > [choose your campaign] > Link Analysis > History. Here's a before/after picture of what I show you how to do:
I could write out all of the steps to replicate the Historical Domain Analysis chart, but most people find it easier to watch over someone’s shoulder. Plus, it takes a fraction of the time to watch the steps as opposed to reading and interpreting all of the screenshots. I go through the steps on my Mac, but provide the steps for PC each step of the way. They really are variations on a theme.
Load chart template files
I went through all the SEOmoz reports that a) offer an export, and b) include a graph of some type on the site and created templates, so you don’t have to format the data yourselves. It’s pretty easy to use them. However, some of the raw data needs to be massaged a bit before you can apply the templates.
Editor's note: The following download is no longer available.
To load them on your computer, unzip and drop these template files in the chart templates folder on your hard drive.
- PC: C:/Users/[Your User Name]/AppData/Roaming/Microsoft/Templates/Charts
- Mac: Users/[Your User Name]/Library/Application Support/Microsoft/Office/Chart Templates
Note: If your Charts directory isn't in the default location and you're on a PC, you can click the Manage Templates button by navigating to Insert > Charts > Other Charts > All Chart Types.
Data prep and chart cleanup
Each report has its own steps that you have to take to prepare the export for charting and a few cleanup steps you have to take afterwards. I don't normally do two videos in one post because I don't have a death wish; however, after writing out all the steps and taking the screenshots, I realized it looked scarier than it is. So I fired off another video to hopefully allay some of the intimidation.
Make your own
If you want to modify a chart(s) to your liking, no worries. It won't hurt my feelings. :) When you're finished with your changes, you can save it as your own template. On a PC, you would just choose Chart Tools > Design > Save As Template. On a Mac, the easiest way is to right-click on the chart and choose Save as Template. But if you prefer using the Ribbon, go to Charts > Change Chart Type > Other > Save as Template. (Like I said, use the contextual menu.)
More resources
Here are links to the two posts I referenced in the second video for a deeper explanation:
Easy To Advanced Uses Of Cell Formatting In Excel
How To Make A Two-Sided Bar Chart [VIDEO]
If you need help
So, if you want to regale your boss, client, or mom by replicating SEOmoz’s charts, hopefully this will give you the tools you need to do that. If you’ve picked up a few data visualization tips in the process, even better. But if you get stuck along the way or run into bugs I didn't run into (I didn't test these on older versions of Excel), feel free to comment below or hit me up on Twitter. Thanks for reading!
Love the guide Annie! You're an Excel magician. Super useful as always. This can be the beginning of a series about: How to turn your Excel data to look like the beautiful graphics of your favorite tools ;)
Thanks, Aleyda. That thought has crossed my mind. :)
I thinks that a fabulous idea!
Loving the SEO exposure Excel is getting at the moment - SEO Tools for Excel, the SEOGadget Links API extension... lots of data analysis opportunities :)
Well, Excel since always is the real secret weapon of SEOs :)
True! This weapon can fail however when it becomes unresponsive for about half an hour due to intensive use :)
Oh man, this is so true. I've crashed Excel so many times trying to hurry it along.
I generally use excelforum.com to get all my excel related queries answered, great excel forum ...
Oh yeah, I've stopped by. I also love the MrExcel forum.
Thanks for the resources Annie, this will save a lot of time and work putting things together. What would SEO's do without Excel I wonder?!
Ha - just had an argument on Glipho about that - whilst I don't know why you would want to replicate someone else, in my previous role I was about obsessive about creating our own templates and using them - got very irrate with AMs who were creating their own templates or more usually, not understanding how to use templates!!!
This is a great article and I plan on reviewing it again out of work time, just wish it was on a Mac! The tool we use at work is Comscore (formerly Nedstat) analytics and I find visualisation and presenting data a significant time consuming part of my job so I love articles like this! I am sure you have all seen Excel for Ninjas (Distilled) another awesome reasource. I swear no one knows Excel like web analysts!
I think you'll be pleasantly surprised that both tutorials *were* done on a Mac. So your wish has already been granted. :)
And the Excel for Ninjas resource is an absolute must have for any marketer. I could sell it door to door if it weren't already free!
Annie, I think reading your Excel tutorials is a "must have for any marketer!" You rocked this post! THANK YOU! I feel empowered.
We sure wouldn't be as effective. This trend toward data analysis and visualization is here to stay.
Annie, I have to say I love to follow your excel write ups. I have always programmed everything, but your well written posts with examples have pushed me to spend more time in excel. Thanks.
That's so good to hear! Very happy to have played a role in that. :)
I just came back to look through your excel templates, but drop box isn't working. Woe is me. :)
My mistake. I just tried it on another network, seems to be working ok.
Oh good! Glad you're not missing out. :)
Nice tut on "excel for seo".Thank you Annie for this!
My pleasure. Hope it helps. :)
This is awesome. Thanks Annie
You're welcome. :)
Annie, you totally rock! Thank you so so so much for this post.You granted my wish. :-) Soooooooooooooo awesome!. I just think this is incredible. You've turned me from an Excel moron into an Excel diva (well I can't touch you girlfriend....but at least I'm not in Kansas anymore!) .
Dana
I didn't want you to think I forgot about you. The Moz engineers even fixed the export, so it would be easier to chart. So glad it helped. Thanks for the inspiration. :)
Annie, you make me smile...and no, thank YOU for the inspiration. :-)
Then my job here is done. :)
Brilliant. you really did a great job
Thanks!
Great Stuff Annie thanks for sharing such a nice information to customize data.
You bet! Hope it helps.
You really did a good job putting this together. Thanks for taking the time!
Thanks! It was a fun challenge. :)
What a valuable post with downloads, videos, explanations and images. I've book marked so that I can come back and go through in more detail. Thanks for sharing!
You are very welcome! I hope it's a valuable resource for you when you circle back around. :)
very helpful:)
So glad. :)
You did a great job Annie and you make it very easy through your guideline to use Excel for making such amazing and wonderful graphs. I prefer to use graphs rather than reading the content a lot because they offer more appropriate facts and figures in visuals.
Thanks! I'm with you. I look at the visualizations, and then decide if I want to delve deeper into the content.
The dropbox file for this post is empty now, would it be possible to re-share the template files? If so, can you email them to me at [email protected]? Thanks so much for the wonderful insight and tutorials!
Hey there! Here's the Dropbox link: https://www.dropbox.com/s/cekajyh1eghf1tv/Downloads.zip. I don't know what happened to the link in the post. Sorry about that!
Thanks!!!
You're welcome! :)
Glad to see your Post Annie
Thanks!
good job thanks :)
My pleasure. :)
Extremely useful article. I'll be using these templates now itself. Thank you for such a useful post.:)
Thannks very good
Thanks. :)
This is awesome Annie. Not only is it awesome, it's not very hard to replicate with your easy to follow steps. Love it.
That's exactly what I was hoping for. Thanks!
Annie - Great Excel post; very thorough and well done. I know a lot of people are intimidated by creating their own infographics so this fills a need in the community. Maybe you should run a ScreenCast Thursday series. :-)
Thanks! I've been gravitating more and more toward screencasts on my personal blog (annielytics.com) and want to do them more and more in blog posts. I think it's easier for most people to watch than read.
Very cool, nice to be able to customise / pick and choose SEOmoz data (and make it just as pretty within Excel) for client reports. Really appreciate the walkthrough video.
I was pleasantly surprised how close Excel was able to get to the SEOmoz ones. Not bad, Excel. Not bad at all ...
Very awesome walkthrough Annie. Just yesterday I was trying to create some sexy looking reports in Excel. This will help a lot.
Music to my ears. Sexy data brings all the analysts to its yard. :)
Annie this article is amazing, i'm part of a new start up site "Object Reference" and we are planning an article section, could we possibly link your content above? Many thanks :-)
Absolutely, Jessica. And good luck with the startup!
Very skillful.
Thanks. :)
Another spreadsheet full of super useful tools and resources. Big thanks, Annie.
You're very welcome. Glad it's useful. :)
GREAT post Annie!!! Thank You so much... Now even I can make appealing charts and graphs! :)
Boom. Mission accomplished!
Wow Annie, you have definitely cemented your namesake lately. I need to add a section solely for analytics on my SEO resources page with your name on top. Great stuff, and another bookmark added to the list. BTW, your site redesign looks amazing :). Thanks!
Thanks, George! I've been a busy girl these days. :)
Awesome job Annie! I loved this guide. I may get ahold of you later and see if we can add something like this to our documentation on our help hub!
Absolutely!
Great post, I've been trying to present the data like this for a while.
Thanks! Hope this helps you along that path.
Amazed :)
Thanks, Gianluca. Hope it helps. :)
That's really true. I am Amazed too. Some of the advanced excel tips by a power excel user. This can, not only make our work easy but also get the quality output (graphs) in no time.
So glad it's helped!
I'm amazed SEOmoz let this through! A lot of people use Pro just for the professionalism behind exporting reports and documents in General :D
The better you can format the data you pull from SEOmoz, the more you'll love it and want to use it. Everyone wins. :)
Touché :) Reports are vital and especially in a corporate industry, they love reports and as much information crammed into a beautiful design looks great!
Amazing Excel visualization tutorial! Great job Annie!
Thanks, Virgil! Excel whisperers unite. :)
Wow, thank you for this post, Annie! I'm bookmarking this for the next report I have to do. A beautifully formatted spreadsheet always makes the data look better. :)
That's what I always say. :)
Awesome post Annie. Data presentation is so important. That's awesome that you not only showed us how to perform the steps through 2 videos, but gave us the files so that we could follow along and leave this post with everything we need to create top notch charts in Excel. Thanks!
You're very welcome! That was my goal exactly. I like to imagine all of these bosses and clients getting an influx of super-sexy data, but I've been known to have an over-active imagination. :)
What a fantastic article you have created, there is so much that i and evryone else can take and develope from this.
Great work.
[links removed]
Great article, many thanks!
You're very welcome. :)
I LOVE excel. It may be an old tool, but it's still a powerful tool to keep on your belt :) I've got several Excel workbook that I've managed to piece together over 2012, but basically, I can check SERPs automatically, track who your competitors are for a certain keyword (or group of key words), etc. using Excel and some fancy macros (& VBA Code, of course).
Thank you for this guide. It will help to "lively up" the workbook :)
Link to this amazing Excel spreadsheet or it didn't happen. :)
Seriously, glad I could help you sexy up the workbook!
I guess I need to be a premium member in order to have the option to import the details
Is that correct?
To use Moz's tools you need to be a member, yes.