Tell stories with your data.

Good Ol' Excel Is The Ultimate Data Visualization Tool (In Most Cases)

Posted by Cyrille Vincey

Find me on:

Since our post about Infographics, a *lot* of people, stunned by Tufte's "less is more" gif, have been asking us lately what's our recommendation for a data visualization package.

Considering the size of their datasets, and the kind of story they wanna tell, our answer has been invariably the same: "Well, use your dusty ol' Excel". Surprising isn't it?

Data Visualization, you said?

I already wrote earlier that we want to stay away from this mainstream worship of the Infographics golden calf. Instead, we want to stick with the Data Visualization Tables of the Law, given by God to Edward Tufte:

  1. Talk to my eye, not to my brain: rely on proven types of visualization to make sure you
  2. Less is more: suppress everything that's not absolutely usefull for the understanding of a chart
  3. One chart = one takeaway: when looking at your chart, I should be able to infer your message in a snap
  4. Tell a story: build a directed sequence of messages

tables_law"Take this, Edward Tufte, and tell your disciples at qunb
this is the worst introduction ever in the History of Blogging"

Is there a Raise and Fall of Excel as a Data Visualization tool?

I've been a consultant for, well, too long. Excel was my bitch. Unfortunately, I have to concede that Microsoft missed the boat of modern Data Visualizations.

People are now expecting well designed, webby, and interactive data visualizations. Hence data nerds have turned to advanced data analysis tools like Tableau.

If you're a quant, and if you can afford the time and the money, that's an awesome tool. But if you aren't, you're stuck with Excel, and its shitty charts.

This is why qunb was created: to help anyone to seamlessly create and tell visual stories. Today with your Google Analytics data (check it out), tomorrow with SalesForce, and in 2014 with any excel data.

The wait is killing you? Well, meanwhile, is Excel that bad? 

Yes, default charts in Excel look like they were done by your father in 1993

Let's take as a simple dataset of the latest funding deals, based on Form D information. By default, here's how a chart on that dataset would look like:

InitialUgly, pointless...

But don't cry! Excel is (still) the Ultimate Data Visualization tool in most cases

Yes, my friends, no need to waste time nor money to start mastering a complex data visualization package.

We can do a pretty good job at publishing good looking charts, with regard to the current design best practices, only by using the standard version of Excel. 

Provided that you don't need interactive charts (charts enabling data discovery for the reader), nor maps, nor fancy visualization types that your reader will probably struggle to read anyway.

If you just need good looking modern - yet static - charts, like donuts, 2D charts, Excel is still the best option. Doubtful? Well, go through the data story below, 100% made with Excel. Then we'll see the tips & tricks to issue those kind of charts.

Here's a 100%-Excel visual story of startup funding

First let's focus on the proportion of tech deals vs deals in other industries:

Donut

Then let's show how this proportion evolves over time:

Trend2

Now we're intrigued. Is this trend compensated by the size of funding deals? Let's see:

average_deal_size

So, what does the global amount raised look like?

Amounts

That's it for today. I know, it's frustrating. Be patient, we're baking a complete and compelling data story with this dataset of funding deals data. Crispy, unexpected things lying in this data. 

So if you need to leave us now...

 

 

 

Ok, you're still here? Let's look together how to make your excel charts look like this.

Our 8-step recipe to "tufterize" your excel charts

I wont cover in this section the tips and tricks for data discovery and data analysis with Excel, I'll keep that for a later post. Soon, I promise. We'll talk about aggregation, clustering, segmentation, made easy with excel. Can't wait.

For now, Let's only talk here about visual improvements to turn the default excel chart into one of those charts. Build your pivot table, then build your default chart (here a stacked column: Chart > Column > Stacked Column):

Screenshot_10_16_13_10_33_AM

Then move the chart to a new page (right click > Move charts > New sheet).

#1. Start with deleting all the visual pollution

Delete the legend, the horizontal gridlines, and the Y axis.Screenshot_10_16_13_10_40_AM

#2. Color set : one vivid color, and grays. No more.

Double-click on each category (blue, red, green, ...), then go:

  • in the "Fill" menu: change to color
  • in the "Line" menu: apply the same color
  • in the "Shadow" menu: disable the "shadow" option

Screenshot_10_16_13_10_45_AM

#3. Optimize the x axis

Double-click on the x axis, then go:

  • in the "Ticks" menu: select "Major Tick Mark Type" [none]
  • In the "Fill" menu: change Color from [Automatic] to [No Fill]
  • in the "Line" menu : change Color from [Automatic] to [no Line]

Then tweak the x axis labels:

  • Change the font (Calibri smells MS Office like hell). I personally like "Century Gothic".
  • Enlarge the font
  • Chage the Font Color (black => gray)

Screenshot_10_16_13_10_59_AM

#4. Add Data Labels

Legends should be banned from Excel. You should make sure you use direct labelling only, not only for values, but also for your categories. Here is a little trick.

First add data labels on every category (right click > "Add Data Labels"). Change the font, the font size, and the font color.

Screenshot_10_16_13_11_19_AM

#5. Delete useless labels

You should keep only the values you want your reader to pay attention to. Usually, only extreme values are really helpful to support your message.

Select useless labels one by one and delete them. Just keep one useless label by category (you'll use it as a legend in the next step).

Screenshot_10_16_13_11_22_AM

#6. Add direct labelling to your chart

For each "useless" data label you kept in the previous step, double-click on it, then in the dialog box

  • in the "Labels“ menu: uncheck "Value", and check "Series name"
  • In the "Font" menu: change the "Font Color" to the color of your category (here, red or gray)

Then select the Plot area (click somewhere in an empty place in the chart zone) and select the right handle to reduce the chart width a bit and let some room for the category names. At last, move the category names you just created on the right to place them as category legends.

Screenshot_10_16_13_11_32_AM

#7. Add an explicit takeaway to your chart

Even if you think your chart is self-explanatory, you should add a title to comment your chart. You should also make sure that this comment explains what the chart is talking about. Global amounts raised? Turnover? ...

If your comment does not explicitely tell the reader what the chart is actually about, make sure you add a technical description of your chart somewhere (ex: "Cumulated turnover of...").

Screenshot_10_16_13_11_42_AM

#8. Remove frames and borders

If you copy your chart like this, you'll see an ugly border. Go in the "Chart Format" dialog box (right click outside the plotting area > "Format Chart Area"). Then

  • In the "Fill" menu: change Color from [Automatic] to [No Fill]
  • In the "Line" menu: change Color from [Automatic] to [No Line]

Your chart is ready to get copied and paste wherever you want.

Congrats, your data looks better naked, just with excel

To conclude, here's a Darkhorse Analytics's awesome gif summarizing this loooooong post.

 

Now, if you prefer to see the future in action...

 

 

 

 

 

Topics: Data Storytelling

Published on Oct 16, 2013 2:07:00 PM

Subscribe to Email Updates