e

Like what you see? Let's talk about how we can help your business. Contact Us -->

Making Data Sexy — Excel Formatting 101

Making Data Sexy — Excel Formatting 101

**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:

Ugly, right?

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?

</rant>
// for now

Format Cells

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.

Conditional Formatting

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.

Final Primping

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 …

If you want to follow this series, make sure to subscribe to our blog. You can also always hit me up on Twitter with your Excel questions and follow BlueGlass for much more (if you act now).

Want to Get Inside?

Become a BlueGlass Insider Today!

  • Be the first to know about BlueGlass events, meetups, and surprise releases. Before they’re made public…
  • Exclusive access to the latest tools, tips and must-read posts.From people who have been doing this for years…
  • Insider perspective on the latest trends in digital marketing. Info that you won’t get anywhere else…

Enter your email below to join for free!




Comments

  1. Kerry Jones says:

    I am as mesmerized by your skillz as I am that ginger baby seal. Awesome post :)

  2. Chris Winfield says:

    Speechless…..

    • Annie Cushing says:

      Thanks, guys! I’ve been looking forward to this series for a long time. It’s long overdue. :)

  3. Hi Anne,

    I am not sure whether I should rejoice or cry in a corner. The fact that I got very excited about you tarting up excel tables must say a lot about my personality. @wilreynolds tweeted the post out and I had to check it out. Really looking forward to the next installment about make data sexy. It breaks my heart spending time on reports for them to get binned or imediately archived because the client doesn’t want to interpret numbers.
    Hoping this will hekp.

    PS: I am a big fan of using the design element in excel to color differnt cells to highlight data.

    • Annie Cushing says:

      I’m so glad it helped you and that Wil pointed you in this direction, Ross. Most people have no idea how easy it is to clean up their data. My goal is to rid the world of ugly data, one post at a time. :)

  4. Dr. Pete says:

    How did I not know about Data Bars? Whatever they’re paying you for today, double it!!

    • Annie Cushing says:

      Thanks, Dr. Pete! I’ll pass that along. :)

      And, yeah, conditional formatting is Excel’s best-kept secret. It still doesn’t satisfy my need for true aesthetics like charts do, but data bars especially are really nice for tabular data. I might do an entire post on how to use conditional formatting w/ formulas. That’s where they really earn their keep, especially when you’re sifting through giant data dumps like GWT data.

      One time I stitched together a client’s GWT and GA data and then write one formula that says, “Okay, if the bounce rate is lower than 30%, the search volume is at least 2,000/mo, and the conversion rate is greater than 2%, highlight the keyword. I was able to whittle a list of hundreds of keywords down to fewer than 20 to focus on for their SEO campaign. In a matter of a few minutes. Awww yeahhhh …

  5. Sara Borghi says:

    Very useful and informative Annie, thank you so much! :)
    Look forward to the next one.
    Just one question: what tool are you using to record the screen?
    Have a great weekend!

    • Annie Cushing says:

      Thanks, Sara. Looking forward to doing more. :)

      Oh, and I used Camtasia for Mac and PC.

  6. I was just feeling jealous a couple days ago of the pretty spreadsheets I see out there, and here are these super easy tricks. Can’t wait to play with these, especially the data bars. Very hot! Thanks for taking the time to share.

    • Annie Cushing says:

      See? No need to feel jealous anymore. You’ll be beast mode before long. :)

  7. Dave says:

    Nice indeed.. however I don’t get the whole concept.. the data is still the same and whatever time you spend formatting it.. it ain’t gonna change, right? So why bother? Is that a girl’s thing?

    • Annie Cushing says:

      Try taking a big old black and white table brimming with unformatted data to a stakeholders’ meeting. You’ll find out really fast making your actionable data pop has nothing to do with having a uterus.

  8. Sandeep says:

    Hi Annie,
    Thank you for the wonderful post and your initiative in running the series..the video has really helped to understand even better…i request you to make a similar video for one of your previous article: Competitive analysis using GA & webmaster tools.even though it will be a lengthy video but can benefit a lot of newbies like me. .Looking forward for next post..

    • Annie Cushing says:

      That is a great idea, Sandeep! I will try to do that.

  9. Annie, my Excel wants to out with your sexier Excel. Date?
    We both love you and your spreadsheet eye candy!

    • Annie Cushing says:

      Game on! What time should my data pick you up? :)
      (And thanks, lady!)

  10. I love this post. The writing style oozes with personality. I’m excited to see future posts in the series. I included the post in my monthly roundup in our company blog.

    • Annie Cushing says:

      Thanks, Ryan. Love that … “oozes with personality …” It’s about time data gets a fair trial. It can be incredibly cool and sexy with the right accoutrements. :)

  11. Eric Siu says:

    Annie,
    Thanks for the incredible vid – I feel like an excel god even now though I’m still really a newbie :) When does the second part come out??

  12. Alan Bleiweiss says:

    How come I didn’t watch these videos before? Yeah, I know some of the 101 stuff, but not all of these!

    • Annie Cushing says:

      This is our way of giving back to the community. There are more to come. :)

  13. Jeff says:

    Great post Annie, I do something very similar with my reports…glad I’m not the only one.

    However, I thought I’d ask the question – how do you EXPORT the data from Analytics to begin with….?

    • Annie Cushing says:

      If you’re using Google Analytics, the export option is in the upper-left corner of the screen. Just choose the csv option.

  14. Some great tips here, I do alot of reports that look bland and boring. I am going to include some of your tips to make them look great. Thanks

    • Annie Cushing says:

      Thanks. I’m so glad you learned some tips to add to your toolbox.

  15. HOW TO PROMOTE EXCELS

  16. Thank you for sharing this information. The information was very helpful and saved a lot of my time.

  17. James Piper says: