**This is the first post in our “Making Data Sexy” series. For Part 2, visit 7 Excel Charts You Should Use Daily.**
My tagline is, “I make data sexy.” And with good reason. For the same reason this ginger baby seal received no respect from his colony and was even rejected by his own mother, your data — regardless how riveting it might seem to you — will be under-appreciated at best and possibly completely disregarded if it appears anything like this:
How, I ask you, are you supposed to draw conclusions from a bourgeois table like that about the keywords that are working best for your site? This is exactly why I’m starting my “Making Data Sexy” series, where I’ll show you my best Excel tips and tricks. We’ll crawl, then walk, then run.
The only drawback to this series is, when you see just how easy dressing up your data can be, a lot of the mystery behind what I do will be lost. And you might not respect me in the morning. But it’s a risk I’m willing to take in my quest to liberate data from the drab garbs that give analysts a bad name (okay, some of that we do to ourselves, but sending your data out the door in Polyester certainly doesn’t help.).
Alright, enough stalling. Let’s jump in ….
If you’d like to follow along, you can download my starting document, ugly-data.xlsx. Oh, and I’m not actually the analyst for Fluevog. The data in the first tab is as fake as Botox. But a girl with a passion for funky shoes can dream. However, the data in the second tab of the downloadable worksheet is legit — pulled from SEMRush, one of my fave SEO tools.
Format as a Table
The first thing I always, always do is format my data as a table. One, it’s an instant data revitalizer in that it adds all kinds of pretty colors and functionality (in order of importance). Because I go through how to do all of these steps on both a PC and a Mac, I won’t get into the weeds here — watch the videos at the end of this post. But this step should be mandatory. And I will judge you if you don’t take this very simple step.
Here’s what data dressed up in a table looks like. Pretty, right? Don’t feel compelled to use BlueGlass branded colors though. Excel provides many :)
Click for larger image.
Now for the bad news. If you are using Excel 2008 for the Mac, you do not have the option to format as a table. Don’t ask me why. Excel for the Mac is lame, period. Even 2011. The fact that you still can’t work with pivot charts in 2011 (when PC users have had them since ohhh Excel 2000) or even use custom RGB colors is beyond irritating to me. But don’t get me going. When I rant I get all blotchy.
On the plus side, we do get really pretty crayons to choose from with names that will take you back to third grade. Because that’s what Mac users like to do — frolic with crayons. Right?
// for now
I can’t underscore the importance of formatting your data. This includes adding thousandths separators (known to normal people as commas), percent signs, dollar signs, etc. People really don’t want to interpret that 0.38 = 38%.
I show you how to do this formatting from the Home tab, but at any point you can press Ctrl/Cmd-1 to open the formatting dialog. If you have a cell selected, it will open formatting for the cell; if you have a chart selected, it will open the chart formatting dialog; if you have an axis open, it will open axis formatting options; etc. If in doubt, hit that.
This data is already starting to clean up and should be ready for the runway in no time.
Click for larger image.
Pro Tip: Make sure you choose the appropriate decimal place for your data. Will the decision makers in your company really care if the bounce rate is 17.3956% as opposed to 17%? Prolly not. At the same time, you don’t want to show a conversion rate of 0% if it is actually 0.3%. As a general rule, if you want your data to be able to get picked up, kick those duct-taped frames to the curb and make it as clean as possible.
Ditch the Gridlines (Please!)
Want to know one of my biggest pet peeves in spreadsheets? Gridlines. Hate them. They add so much clutter and noise. Blech. Truth be told, I get rid of them before I do anything else.
Click for larger image.
Fun with Text and Number Filters
Okay, they’re not that fun, but they are very useful. If you have a spreadsheet with lots and lots of data (like tab 2 in the previously mentioned download file), filters are your highway to heaven. You can use both text and number filters to simplify your data and tell the story you want to tell. You can even use wildcard characters. It’s not exactly regex, but nice nonetheless. You can substitute any single character with ? or multiple characters with *.
In this screenshot, I filtered out brand terms by just choosing Does Not Contain = flu to filter out Fluevog’s brand terms. I tested it first to make sure it didn’t over-filter, and I got lucky. But you might have to tinker a bit. You can see by the blue row labels every place where a keyword was filtered.
Click for larger image.
The nice thing about filters is you don’t delete data you might need later, and Excel automatically detects if the data is text or number values and offers you the appropriate filter. Excel 2011 for Mac gives you both, regardless of what’s in your column. But Excel for the PC is a little more cantankerous (go figure).
Pro Tip: If you are getting text filters for numerical columns, you have numbers that are formatted as text. I run into this all the time with webmaster tools data. Your first clue that you have rogue characters in there is your whole column will be left justified. Even one text character will ruin the entire column … like that bad apple your mom warned you about. Numbers are always right justified.
Here’s where we start to separate the men from the boys. If you want to make tabular data jump out as much as it can in a table, use conditional formatting. It’s easy to add and makes your data pop, especially if you have a lot in a single table. Although it is rarely my preference to use a table by itself, it’s the next best thing to pulling your data into a chart. We’ll get to displaying your data visually in the next post.
Again, if you’re using Excel 2008 for Mac, you’re out of luck. No conditional formatting for you. Who cares PC users have had it since Excel97? You should be thankful you can format your data at all. Now go back to coloring.
Pro Tip: I try to reserve “negative” colors like red for metrics that have negative connotations, like bounce rate, and use green for revenue values. But that’s just my personal preference. There are also more options that just the data bars I demonstrate in the videos below. I use the icon sets sometimes, but I really don’t like color scales. Probably for the same reason I don’t like my foods to mix on my plate. It’s just messy. But if you pile your whole Thanksgiving dinner onto one plate indiscriminately, you might love them. Go for it. You have my blessing.
The last thing I do before shipping out my spreadsheet is clean it up. Tab names like Sheet1 and whatever your download file saves as really aren’t all that useful. So I change those. And then I’ll add a title above my table. You can download the final, pretty file here.
Check Out the Videos
If you’re using Excel on a PC you can view my fast and furious overview here …
If you’re using Excel on a Mac (I’m sorry), you can experience all the goodness of Microsoft’s B Team here …
In the next post, I’m going to show you how to take your table and transform it into pretty charts and graphs. You’ll learn how to even chart metrics that normally don’t play well together, like visits with bounce rate — and how to make it pop by adding data bling. I can hardly wait …