I often read that you should make your charts “memorable”. Well, I’m not sure if this is a good advice, specially when people use “memorable” and “professional-looking” in the same sentence.
It’s OK if you are a graphic designer and you want to spend some time crafting an unique chart that draws the attention of the casual reader. For the rest of us, simple mortals, who just need to make those 300 charts before the end of the day, crafting a chart and make it memorable is a laughable idea.
In business visualization, “memorable” can only mean two things:
the chart makes a good use of the working memory and
the chart is very efficient at producing insights, and that leads to better, memorable decisions.
Here is a memorable chart.
Suppose I’m testing a new ad in a small market. The outlier show that the ad is working pretty well. Now I can test it in larger markets. Do I need a “memorable”, “professional-looking chart?
There is nothing special about this chart, you didn’t spend hours perfecting its design. Just a clean and simple message.
Unfortunately, this is not what many people mean by “memorable”. They mean something that belongs to the realm of graphic design and that’s very unhelpful from a business point of view.
So, if you are making charts make your insights memorable. Make sure patterns, trends and outliers are clear and easily spotted and offer different views to explain and support the decision making.
Creativity is such a positive quality that it is almost painful to argue that you shouldn’t try to be creative when making charts. But you shouldn’t. Really.
If you are too creative and the users can’t understand the chart in a few seconds they will dismiss it as useless. If you break basic conventions (time series in a horizontal axis, from left to right) users will have a hard time trying to figure out what the chart really mean. If you add 3D and other gratuitous verbiage you will obscure, truncate and distort your message.
Use creativity to improve your communication skills. Play with colors to emphasize your arguments. Show several charts instead of one. Show the data from different angles. But do everything using a familiar framework .
Try to enlarge the users comfort zone one step at a time. Remove 3D effects but keep the pie chart. Use pale colors instead of primary colors. Gray out grid lines. Reduce the number of pie charts. Add simple scatter plots. Make smaller charts. Do it slowly.
If you are not a graphic designer, if you work in a business environment, if your business needs go beyond a simple chart from time to time, then there is no room for creativity (or perhaps misplaced creativity). Your audience is busy. Respect that and make your charts as clear as possible.
How creative can we be when making charts for business? Share your thoughts below.
Regular readers know that English is not my mother language , but are kind enough to forgive me for my many mistakes.
I am always willing to learn. Today, while researching for an upcoming post, I came across an expression I never heard before: “spiffy charts”. I felt in love with “spiffy charts” the moment I read it. And I read it straight from the horse’s mouth (I mean Microsoft).
If you don’t know how to make a chart and are keen to preserve that blissful ignorance, I highly recommend Microsoft Office Online Training, specially the module Create a professional looking chart (regular readers also know how I love “professional-looking charts“). You’ll learn how to “customize your charts to make them more attractive, memorable, and effective“. This means useless charts.
So, let’s see how to turn a humdrum (this is a new word, too…) chart into a spiffy one. First, to declutter your chart remove grid lines:
As you know, grid lines are useless, specially if you don’t care about the data. I would remove the gray background and the border around the legend. And I’d give the chart a more descriptive title to tell the users what they are seeing, but that’s my personal taste.
Then you should remove the y-axis and add labels to each column, further “decluttering” the chart. At this point the readers start sighing for a nicely designed chart table.
Want to give your chart a little more “flair” and make them more “professional-looking”? Just add a gradient fill and a subtle shadow:
Now comes the spiffy part. Imagine that you have a 3D column chart with two series, and one obscures the other. What do you do? No, you can’t remove the stupid 3D effect (remember: you want to make t spiffy chart, not a humdrum one). Well, all you have to do is to change the order of the series:
Much better now, don’t you think? They accept that 3D charts “can be more attractive, but sometimes more difficult to read accurately” (surprise, surprise!). Apparently that’s a detail in the grand scheme of things. You are excused from making accurate charts if you are making professional-looking ones.
So, what else can you do to improve your chart? Ah, yes: the y-axis in a humdrum column chart always starts at zero. We don’t want that, do we?
Now you know how to make inaccurate, professional-looking, spiffy-with-a-flair marmalade charts. Please go straight to the kitchen, make some real marmalade and forget all you’ve learned about data visualization in the Microsoft Office Online Training.
(This is not a real Microsoft Office Training site, is it? It must be some kind of spoof site, and I fell for that trick. Right? Right?)
Great data visualization is hard to measure: you can’t prove you have a good chart. Unless you can convince your employer to deploy at least two different formats/layouts and are able to compare results, you can say “this is a good chart” but that’s an act of faith, not an act of science.
It’s True Because It Rhymes
Information visualization experts like to evaluate a chart based on its compliance to some more or less accepted standards (Tufte’s data-ink ratio, for example). That’s like saying “it must be true because it rhymes”: the truth is defined by the language itself, not by the real world. Now, please close the curtains of our ivory tower…
I know, it’s not easy to assess the efficiency and effectiveness of good displays. They look natural and obvious, undeserving of praise and, probably, boring and uninspiring. Compare these charts:
This is a true story: users wanted to evaluate sales territories, one at a time. Color-coding each bubble (Example A) was pointless, while Example B provided context without distractions. Guess what chart they would choose if they were allowed to… (happy ending: they reluctantly accepted Example B). (A word of advice: if you are looking for a promotion, a kindergarten chart variety always outperforms a “serious” chart.)
If your chart is doing a good job at helping people, no one will actually be aware of the chart’s role at making sense of the data. That’s why it is so hard to find good examples of data visualization using standard charts. If people actually like them, they like them because of their usability and/or interactive features.
When Stephen Few asks the readers “true stories about the benefits of data visualization” that’s almost an admission of impotence. He should have hundreds if not thousands of good examples to share with us, right? Well, I know there are many examples out there, but I can give you none, sorry. Is data visualization some kind of astrology? I know it works. Why? Because I have faith. (On second thought, he is not asking for good data visualization examples. It really doesn’t matter if you use Tableau or Xcelsius, and that’s a relief.)
Opening the Pandora Box
Ultimately, what makes a good chart is how it resonates with your audience. Assuming that your are not unethically distorting the data, a chart that forces people to act is better than another one that only makes people aware of the subject.
If a single chart can save the world, it will not be a Few’s or Tufte’s 100% compliant chart. It will be a glossy Xcelsius pie chart.
(Wow, that’s depressing…)
If you read this blog that’s a clear sign of intelligence and sophistication . Unfortunately, you are not representative of the typical data visualization user and/or producer. The real world loves pie charts and doesn’t understand scatter plots.
Here is my Pandora box: give the audience what it expects and understands, even if that hurts your data visualization soul (OK, give it 90% of what it expects and use the remaining 10% to educate it.)
Cultural Relativism? Not So Fast.
Please don’t misrepresent these arguments. I’m not saying that all charts are born equal. There is a reference point and some misconceptions should be avoided A chart that maximizes insights, removes clutter, uses color wisely and clearly shows the patterns hidden in vast amounts of data, that’s probably a good chart and that’s what you should aim for. And yes, you should avoid pie charts.
If you present some sophisticated charts to your unsophisticated audience you’ll lose it. Relax. Draw a line but don’t forget the candies. You can take a horse to the water, but you can’t make him drink, unless you give him some sugar cubes…
If you want to add animation to your charts that’s a clear sign that you have too much free time. Go out and play with the kids instead.
Yes, animation is a powerful attention-grabber, even more powerful than a glossy 3D pie chart in Crystal Xcelsius. And yes, it can actually be helpful (from time to time). But.
A good example of animation in data visualization is the famous Hans Rosling’s TED presentation, where a long-term pattern is clearly seen (at min 4:00):
This works well because the trend is easily identifiable and you don’t care much about the details. It would be much more difficult to make sense of this data if there were multiple trends and short-term variability. (More on the use of animated charts.)
After watching this presentation people often ask me: “Wow! Can we do that in Excel?” Wrong question. The right question would be “Wow! Can our data do that?”.
You can always create a simple animation in Excel, but it’s hard for a non-programmer to get a smooth transition effect. Here is an example from my dynamic Excel dashboard:
Although you can see a pattern emerging, you would need to add a complex interpolation routine to make it look better (read Jon’s post to see how a simple interpolation can be used).
Animation is better used if there is a pattern to be discovered, but you need something more: the ability to interact with the data. You must be able to stop, go back, get the details. While you can create a simple animation effect with a for/next loop in VBA, interaction with the chart is much easier using a Google spreadsheet with a motion chart:
(This is just an image. Click here to play with the chart.)
Dynamic Charts in Excel
In Excel, instead of creating a VBA routine, consider using a scroll bar linked to the value you want to change (year, for example):
Using a scroll bar adds some level of interaction because you can scroll back and forth, pause and examine the details for a specific year. Obviously, you need to change the chart data source dynamically.
Let’s recap how to create a dynamic chart in Excel. You can do it by changing values or by changing the data source itself (using a different range).
Option #1: Copying the Data
The range A1:E11 is our data set and we are comparing regions. Moving the scroll bar at the bottom changes the year. Column F is our data source. You can enter this formula there:
ROW() gets the current row and MATCH() returns the position of value 2002 (A13) in the range B1:E1. So, the formula in cell F2 reads something like this: start at A1, go down one row, go right three columns and get the data in that cell (range of width 1 and height 1). When the user selects a different year the data is copied to column F.
Option #2: Using a Dynamic Named Range
The second option is to create a dynamic named range. Create a named range and, instead of entering a fixed range, enter this formula:
As you can see, it is very similar to the one above, but now the number of rows down is fixed (1) and it returns a range of 10×1 cells (because we have 10 regions, but if the number of regions varies you may use COUNTA(A:A)-1 to count the number of regions, excluding header – and don’t forget to move the year to a different cell…).
When you verify your range this is what you get:
You just need to use this range in your chart (when entering the range you must add the workbook name: “=Book1!SourceData”). You’ll also need a range for the category axis labels:
If you want to animate your charts make sure you do it because it adds value, not because you want to show off your skills. You can do it in Excel but it looks much better in Rosling’s Trendalyzer or in the Google spreadsheet gadget.
To create animations your data source must change dynamically, and that requires some work (and skills). I advise you to shift your focus from animation to interaction and use all that work to design a better user experience. Do you prefer a “wow!” (animation) or a “wow, thank you!” (interaction that actually helps the user)?
You need a better color palette for your Excel charts, but you are a mere mortal and your artistic skills are less than stellar. Hell, you can’t even choose the right tie for a suit! So, what do you do? (hint: watch the video below)
What palette of colors should we choose to represent and illuminate information? A grand strategy is to use colors found in nature (…). Nature’s colors are familiar and coherent, possessing a widely accepted harmony to the human eye (…). A palette of nature’s colors helps suppress production of garish and content-empty chartjunk.
Better said than done, right? Well, let me tell you a secret: it’s easier than it seems.
How to create a new Excel color palette
I’m going to show you how to create a palette of colors found in nature ( you can create a palette of colors not found in nature, if that suits you better). Here are the steps:
Select a photo you like. If you happen to live in a big city, finding nature can be challenging. Google for “[your location]+park” and you may get lucky. Go there, take some photos and download them to your computer. If that looks like a lot of work, you can try Flickr. Search for “autumn colors” and you’ll get some useful results. I’ve picked the one on the right;
Upload it to an online palette generator. There are many, so choose whichever you like. I like Genopal because it is clean (no ads) and extracts exactly the number of colors we need (eight).
Convert color codes to RGB. Copy the color codes to Excel and use the HEX2DEC() function to get the RGB values.
Create the Excel palette. In Excel 2003, replace the existing colors with the new ones. In Excel 2007 create a new color theme.
This is it. You can try it in your next chart.
A new Excel palette: the making of
If you need the details, here is a step by step tutorial:
Is this good enough?
As you can see, the results are heavily dependent on the photo you choose, so you should try different photos and test the resulting palette in a chart. If you find it hard to come up with a good color scheme, you can use this method to create a basic color palette that you can tweak to meet your needs.
Templates and defaults are very useful when you are not a subject-matter expert. You don’t have to know much, but if you choose the wrong templates you are on the wrong track.
Cooking is a good example. I don’t know how to cook and, frankly, I don’t want to learn. But my wife is coming late from work, I’m tired of take-away food and we must feed the kids. So I guess I have to add cooking to my daily routine.
Here in Europe, Thermomix (also known as “Bimby”) is a very popular kitchen appliance (specially among men, so I’m told). You just add ingredients, press three buttons (temperature, speed and time) et voilà, diner is ready (sort of). Follow the recipe and what you get what you expect. That’s good enough for me.
This is a gadget most people love or hate. A friend of mine hates it and tried to persuade me that I should learn how to cook using the traditional pots and pans. Only ill-informed people buys it, he says.
Yes, but he’s single. No kids, no blog, loves to cook. How can he possibly understand my motives? I just want to get this thing done with minimal fuss, no random results, small learning curve. Some good templates, that’s all I want.
I had to buy one. I just did.
OK, but what about making better charts?
Apparently, most people love making charts as much as I love cooking. Even if they need them on a daily basis, there are so many things fighting for their attention that the need for a better data visualization can easily be overlooked and pushed to the bottom of their list.
Junk food, junk charts. Some times it’s great fun to use them. And it’s very easy to take them for real food, real charts. But they are unhealthy, and if you use them often you and your business will pay a price, sooner or later.
It’s not about food or charts, it’s about business and personal health (or wellness). You don’t have to know the details, you don’t have to know how to use the tools. But you have to have some kind of framework to guide you. Fat is bad, sugar is bad, 3D effects are bad. Then you need to find some templates that implement that framework and tools that use them by default (if you make charts in Excel, using its defaults is not an option).
We played with the machine over the weekend and I’ve very pleased with the results. We eat better than we usually do and saved money and time. Not bad. Now, do yourself a favor and find your own charting machine. If you’ll excuse me, mine is calling me. Dinner is ready.
It’s very easy to use charts to support false arguments, distortions, omissions or outright lies. But you can use words and statistics too. If you want to deceive nothing will stop you. (Required reading: How To Lie With Charts and How to Lie with Statistics).
Simple lies are often easy to spot and not very interesting. More interesting are our biases. Germans call it weltanschauung (“world view”) and without it we wouldn’t be more than boring rational machines. Our biases help us to select the data and interpret it the way it makes sense to us, reinforcing our believes.
Lying with charts, if done properly (!), is more an act of omission (what you hide) than an act of commission (what you show). To better understand the differences, let me give you an example of how data visualization amateurs lie with charts.
In a post titled “charts can be deceiving”, E. D. Kain writes:
I’m not a huge fan of charts because I think they’re usually just used to create illusions and sales pitches.(…) Numbers don’t lie, but how we present them can make all the difference in the world.
Then he goes on and offers an example of how deceiving charts can be. I’ll recreate them for you. Chart A is the original chart, Chart B is his:
It’s funny to see how a lack of action (the original chart accepts the Excel default scale) induces an over reaction (an absurd “theoretical” scale). Manipulating the y-axis scale is “How to Lie With Charts 101”.
Yes, charts can be deceiving. Words too. Numbers don’t lie? Bullsh*t. The political discourse is full of “illusions and sales pitches” and carefully selected and biased numbers. Yes, charts can be deceiving. It takes one to know one, I guess.
Deconstructing Lies with Charts
The original chart reveals a clear act of omission: how can you conclude anything relevant if you have no reference to compare the trend to?
So, let’s try to answer this question with the available data: are the wealthiest 1% of households getting a more favorable tax treatment, or not? First, we need some contextual data:
So, the wealthier you are the more taxes you pay; a downward trend is also visible across quintiles (although the highest quintile shows a slight increase over the last two years).
And what happens within the highest quintile?
Well, this is interesting: tax rates increased, but not for the top 1%. But the general rule is kept: the higher the income, the higher the tax rate.
What if we chart, not the tax rate but the change, assuming 1993=100? Again, some contextual data:
Tax rates for the lowest quintile declined sharply. And here is another general rule: the higher the tax rate, the less it changes.
Here is the detail chart for the highest quintile:
Well, it seems that at the very top some rules don’t apply, after all (surprise, surprise). The top 1% households did get a more favorable tax treatment after 1996, when compared to the top 5% and the top 10% households.
Takeaways
The world is never black and white, and your own shade of gray is as unique as your fingerprints. If you want to use charts to support your arguments, please don’t resort to scale tricks and make sure you add enough detail and contextual data.
There is no intrinsic objectivity in a chart, but if you want to support your story you should cover your bases and make sure it’s hard for someone else to come up with a different narrative. This is valid for charts but also for words and numbers.
If you want to sell better data visualization practices you can’t use the same approach with everyone. Marketers use archetypes and like to create stories around them like if they were real people. Their marketing messages are then tailored for Jane (archetype #1) or Theresa (archetype #2).
Let’s try this. Allow me to introduce you to three of my co-workers.
Co-worker #1: Anna, a Newbie
Anna was asked to create a chart, something that she rarely needs to. After playing with the wrong Excel options, she comes up with a really ugly and inefficient chart (misconceptions about charts don’t help). I show her how a simpler one could solve several perceptual issues. She changes her chart but keeps some of the chart junk (she finds my chart too minimalistic and laments her lack of of graphic design skills).
Anna is now aware of what “data visualization” means – clearly more than creating a few Excel charts. She should work on her data analysis and communication skills and stop worrying about graphic design skills. Corporate culture and peer pressure will push her to the dark side chart junk side of data visualization. I hope this seed is strong enough to withstand it, but only time can tell.
Co-Worker #2: Peter, a Middle Manager
Peter agrees that some of our processes are terribly inefficient and wants to change them. We could try to improve data visualization, but that doesn’t make much sense if everything else remains the same.
I’ve been sharing some tips with Peter on how to create better charts and he starts to recognize a bad chart when he sees one. Problem is, his previous model (Excel defaults, PowerPoint templates) is shattered, but he is unable to create a new one. He feels lost.
My tips seem to make sense at the lunch table but when he tries to apply them something is missing. Tips have this effect on people: they create an illusion of knowledge but the lack of context renders them almost useless. He needs a crash course on information visualization.
At this level, we are not discussing how to improve a chart. Instead, we must discuss how to add best practices in information visualization to the data management model. Selling this to top managers isn’t always easy. But Peter likes to bang his head on a wall…
Co-Worker #3: Frank, A Professional Chart Maker
Frank creates presentation charts every single day. Ten years ago he was creating exactly the same charts, 3D effects and primary colors. He doesn’t recognize the problem and the audience seems to be ok with this routine. If he needs something a little more complex than 3D pies and bars his manager asks me to help. This could spark his curiosity, but it doesn’t.
I believe Frank will never try to improve his data analysis, management and visualization skills, unless he’s formally ordered to do so. He’s not dumb, he’s just a little too comfortable in his comfort zone.
It’s Your Turn
I want to add more details about Anna, Peter and Frank and I’d like you to help me. How do you see them? How are you going to sell them our product (better data visualization practices)? Would you like to add your own characters? An imaginary boss, perhaps? The IT guy? Tell us about them!
Here is a memorable chart.
. Unfortunately, you are not representative of the typical data visualization user and/or producer. The 
, he writes:
Here in Europe, 
and 
).
If you want to sell better data visualization practices you can’t use the same approach with everyone. Marketers use archetypes and like to create stories around them like if they were real people. Their marketing messages are then tailored for Jane (archetype #1) or Theresa (archetype #2).
