A chart is always an answer to an underlying question. If you don’t know the question be prepared for random answers (300-slide Powerpoint presentations, anyone?).
Do yourself a favor and and write down the questions that define your project. Group them meaningfully and use them as chart titles. Each chart may prove irrelevant or force new questions. Write them down. Repeat the process.
Jacques Bertin tells us that a chart should be able to answer elementary (“how much did we sell in March?”), intermediate (“what happened in the North district?”) and global (“how does our product compares with the market?”) questions. If it doesn’t, then it is an inefficient construction and should be redesigned or removed. This is also a simple way to identify redundant charts.
Don’t replace information overload with chart overload. Similar questions may require a single answer. Create a single, interactive chart and let the users find their own answers.
Embrace the questions, delegate the answers.
How do you sell your outrageously expensive consulting services? Simple, just add a chart…
Not every chart will do, of course. Let me outline some basic design rules of what I call a “consulting chart”:
- It shouldn’t be recognizable as a standard chart that you could create in Excel;
- It shouldn’t use popular eye-catching design elements, like 3D or textures (hire a designer if you really want those);
- It should convey an impression of complexity but make everyone feel smart because they could actually understand it;
Conventional visualization rules do not apply to this kind of charts. It lays between a proper chart and an icon or a logo. Always remember that your report may be 300 hundred pages thick, but only this chart will be shared in internal presentations, and the managers (your clients) should be proud of presenting it (money well spent).
If you don’t feel creative today, just use the chart above. Every consultant likes it. A circular chart or a radar variant is always a good bet. Each color represents a dimension, and there is a set of five indicators for each dimension. A seven-point scale is used to measure each indicator.
As an example, a dimension could be “Human Resources”, and “turnover” one of the indicators. Display two of these charts side by side to compare “We” vs. “Them” or “Today” vs “Tomorrow”.
Here is a dilemma…
Strictly speaking, a simple bar chart would do a much more efficient job at displaying the data and letting the users compare multiple series.
But let’s be completely honest here. You know your client, and you know he will happily spend 12,000 for a report with these charts. If you use a regular bar chart you can’t ask for more than 10,000. What would you do? Share your thoughts in the comments.
I’d like to answer or comment on search queries that bring readers to my blog. Here are some of them involving pie charts:
What is the usefulness of pie chart in research?
Research found that many pie charts resemble Pac-man.
Pie charts of the american revolution
They go very well with the Powerpoint version of the Gettysburg address.
How to make excel pie charts look good
In “Format data series” set Area to “None” and Border to “None”.
Why are pie charts better than bar charts?
A perfect example of wrong assumptions.
What is diffence between 2d pie charts and 3d pie charts?
If it is thin ‘n’ crispy then it is 2D.
Finding accurate pie chart angles
Consider getting the OCA 30, it is really accurate. I use it all the time.
Founding fathers of the pie chart
Given the roundness of a pie, there must be a mother involved also.
Pie chart of communists
Shouldn’t you use a vanishing point instead?
Pie chart maker that looks like pie
I really don’t know; that’s one of those secrets that passes from mother to daughter.
Make a pie graph with 53 or more slices
I know we are facing recession, but shouldn’t you order some more pies?
All parts of a pie chart should add up to what?
Usually 100%, but if you use 53 or more slices I would say 843%.
Pie chart for multiple intelligence
This is an oxymoron.
World war one deaths on pie charts
Is it not enough that they are dead?
How to create a pie chart excel for stupid people
I will refrain from comment on this one…
Have a nice weekend!
I am sure Albert Einstein was thinking of Excel dashboards when he said “everything should be made as simple as possible, but not simpler”. Let me tell you why.
Demographic Dashboard: The VBA edition
I published some time ago a first version of my Demographic Dashboard to show how an average Excel user could design a dashboard. This version is relatively complex, because it uses pivot tables and some recorded macros to manage the data.
Demographic Dashboard: The VBA-free edition
Then, partially because of a discussion around the use of VBA, I launched the VBA-free edition. It still uses pivot tables but I managed to remove all the VBA. Although I believe that VBA is a powerful tool that should be used when needed, many users are not comfortable with a programming language (not even in the simple form of recorded macros), so this version was designed to address their concerns.
Demographic Dashboard: The Lookup edition
Prior versions use a pivot table connected to an external data source, but I believe that a large majority of Excel users are not aware of pivot tables and how powerful and helpful they can be. Wouldn’t it be nice to just copy/paste into the Excel sheet a simple flat file and design the dashboard around it? Isn’t that standard procedure for these users?
So, by popular demand (sort of) I decided to get rid of the pivot table and design a new version. No VBA, no pivot tables. Just a simple data table and some lookup functions.
Let me tell you this: If you want to follow this path, don’t. It can be really complicated. And creating or updating a data set in Excel by copy/pasting the data is unreliable, dangerous and can seriously compromise your dashboard. But if you need that adrenaline boost go ahead, try it!
Before that, let me share with you some interesting stats. Since I have three versions of a very similar dashboard it make sense to compare performances, so I implemented a simple metric: how long does each dashboard take to cycle through all the 55 time periods? Here are the results in my new computer:
- VBA version: 15 seconds;
- VBA-free version: 45 seconds;
- Lookup version: 2:15 minutes.
This is not exactly unexpected. If you need a (very) long formula to calculate something that you can easily get using a simple GETPIVOTABLE formula performance will drop sharply. On the other hand, the file size of the Lookup version is half the size of versions using pivot tables. If you are planning to email it this could be an option.
Get the Demographic Dashboard Lookup
The VBA-Free and the Lookup Editions of the Demographic Dashboard are bundled with the Demographic Dashboard Tutorial. If you want to create powerful Excel dashboards I am sure this tutorial can help you.
Some metaphors are really stupid. Convincing people that running a company is like driving a car and you need a similar dashboard is one of them. If you are naive enough to buy it, most dashboard vendors will happily fill up your precious screen real estate with irrelevant gauges and and speedometers designed to look “cool”.
Futuristic aesthetics sells very well, but what happens when the future arrives and doesn’t look exactly like the image you bought? Will you buy a new one?
Look at that eye-catching, high-tech gauges in your dashboard and now compare them to this dashboard in the Lamborghini Reventon. Suddenly you feel tired and lost in the past… Your dashboard looks as old as an old Flash Gordon movie… How embarrassing will be to show it again…
Now that you’ve learned your lesson, put the your dashboard in design mode and make it clean, elegant and timeless. Remove textures and add data. Keep it simple and to the point. When people asked you for those cool gauges don’t laugh in their faces. Help them. You know better now.
I’ll be adding some classic videos here, and Hans Rosling must of course be one of the first to be included. He is using visualization software that later was bought by Google. I am using it to display old vs young dependencies.
Bonavista Systems announced today an Excel Dashboard Competition. Participants stand a chance to win an iPhone, a workshop hosted by Stephen Few and a copy of Few’s “Information Dashboard Design”. Unlike other competitions, there is no data set. You just have to use Excel and MicroCharts, their sparkline generator. You don’t need to buy MicroCharts to enter the competition, just download it and use the trial period of 30 days to create the dashboard (it’s a nice add-in, and you’ll probably want to buy it after you test it).
Let me give you a piece of advice on this. If you are planning to enter the competition don’t send them pie charts, 3D charts or lots of primary colors. Your message will not pass their spam filter and they’ll do voodoo on you for the rest of your life. Keep the dashboard simple and clean and make sure that its purpose is clear.
So, good luck.
In my Demographic Dashboard I have a scatter plot that shows the trend towards an aging society. Today I wanted to test the new Google Chart API and the Motion Chart with the same data set.
The chart displays the old dependency ratio against young dependency ratio. It clearly shows the shift from high ratios of young dependency to high ratios of old dependency specially in Europe and other developed countries. This means tough times ahead (the adult population will not be able to pay for social security systems, expensive medical care, etc.).
If you click a specific country a persistent label will allow you to track its evolution.
If you want to play with the chart click on it to go to the published spreadsheet or follow this link. [Update: you could play with the chart directly in the post but I was getting some problems with Microsoft Explorer so I decided to remove it.]
If you are not familiar with the demographic concepts, this is what you need to know to understand the chart:
- Old dependency ratio: ratio between population 65+ years old / adult population (15-64 yeas old);
- Young dependency ratio: ratio between population 0-14 years old / adult population (15-64 yeas old);
- Total dependency ratio is the sum of the above two.
