There has been a lot of criticism regarding Excel 2007, particularly about the new chart engine, the UI inconsistencies and the Ribbon. Jorge yesterday concluded that Excel 2007 is Useless and Jon wrote a comprehensive analysis about Changes to Charting in Excel 2007 where he expressed his concerns regarding the new Charting. In 2006 Stephen Few reviewed Excel 2007 charting and concluded Excel 2007 charting Preview of an Opportunity Missed. Charley Kyd did a survey about How Do Business Users Like the Ribbon and came back with the result that about 56% of all respondents had a negative opinion about the Ribbon
Every good discipline needs a missing link. Evolutionary biology had a missing link between humans and the ‘lower’ animals. Physics has a missing link between quantum mechanics and general relativity. The Information Visualization community discovered the Missing Link Between Information Visualization and Art.
Now we discovered the missing link in Excel Data Visualization.
As we can see in Wades winning Bank Dashboard we can greatly increase the amount of information that can be included in a dashboard by using sparklines in an overview table
In financial report we are constantly comparing the actual numbers with our projections using variances.
A quick reminder
Absolute variance = Actual – Budget
Relative variance in % = (Actual – Budget) / Budget
Doing this over a report of even 10 lines requires row by row number comparison, and is not something which can be easily & quickly scanned. Showing the variances in a classical Excel charts is problematic, as you constantly have to go back and forth between the table and the chart to scan exact numbers and the variance tend in the chart.
In-Cell variance charts are a compact, data rich and efficient alternative.
To create in-cell variance charts you have basically three alternatives:
1. In-Cell Chart Using the REPT Function
Rolf Hichert presents a couple of years ago on his web site (German) a character based approach. The method got quite popular when it was first presented on the Juice Blog. The main idea is to create the bars repeating a character using the REPT function. The function REPT(“●”,5) returns “●●●●●”. The disadvantage of this approach is that you have to set up all the formulas and that don’t have a continuous scale. E.g. to show 15% you either show “●” or “●●”, but there is no way creating fractions of a character. One of the advantages of the rept approach is that the bars are automatically aligned in the grid. When you increase the row height, bars are still perfectly aligned with the numbers.
Click here to download the Excel file
2. In-Cell Chart Using MicroCharts
Another smart way to overcome the setup and scale problems of REPT is using MicroCharts. Charts are created from MicroCharts fonts including bars, line segments and pies. A chart is represented as text that is formatted with the MicroCharts fonts. Similar to the rept fuction charts, you can utilize all the rich Excel capabilities for MicroCharts that you would for normal text!:
- Text alignment
- Text orientation
- Font size
- Conditional formatting
- Automatic alignment when the row height changes
Unlike the rept function MicroCharts support continues scales value axis. The chart formatting is very similar to the options with standard Excel charts:
Hitesh Patel Winner of 2008 Excel Dashboard Competition used the Micro Bar Charts to visualize variances over areas and territories in a very cleanly presented table in his Pharmaceutical Sales Dashboard .
3. In-Cell Charts Aligning a regular Excel Chart in the Grid
A third approach is to use regular Excel bar charts:
The trick is to set chart area to transparent and to manually align them to the grid. The main problem with this method is that the setup of the chart can be very tedious. You will have to fiddle quite a while till the bars align perfrectly with the grid, and each time you change the row or column height width you have to readjust the chart area:
/* Style Definitions */
mso-padding-alt:0in 5.4pt 0in 5.4pt;
font-family:”Times New Roman”;
Update 06/28/2008: Jon added some insights how to handle variable rows and fourth approach for incell charts using graphical objects:
One problem arises when the number of rows covered by the chart is variable. When you have an update, you need to rely on VBA code to realign the chart, and this is not always reliable. To counter this (and to apply some “interesting” formatting requested by the client, I developed some VBA code that read values from each row and built chart using a series of rectangles and other shapes. These were aligned appropriately with the grid more simply and reliably than a chart would have been. A sample of this is shown in In Cell Charting with Shapes.
I ran into additional issues with another client who insisted that his worksheets be viewed at a zoom that filled the entire width of the screen. This has all kinds of negative issues, especially with charts. (And on my widescreen laptop, it needed a zoom of around 150%, so I had minimal rows visible. But a client is a client.) So I built some additional graphs based on Excel shapes, as illustrated in In Cell Bar Charts.
You could therefore add a fourth approach, though it isn’t accessible without code, and that is “Graphs using Graphical Objects”. This is after all how we all started with graphing data in second grade, with paper and crayons and perhaps a straightedge.
Extra tip: To further enrich your variance report with some historical context, provide the historical performance in an extra column as a sparkline:
Here the history of the Abortion data set
1) Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart,
2) In Small Multiples – Abortion Data 1980-2003, I have written about Jorge’s panel chart. I like the panel (small multiples) chart, but felt that it can be improved.
Here is a different view on the data set as a Graphical Table using MicroCharts. The Graphical Table is a hybrid between the panel (small multiples) chart and a plain table of numbers. It nicely integrates the numbers with small charts, so-called sparklines, in a table. The sparklines show you the trend of the abortions over the years and the in-cell bar charts give you a feel for the distribution over the age groups:
Jorge used some U.S. Census Bureau data (original Excel file) to visualize the abortion ratio as a small multiples chart.
I like this chart as I am a big fan of small multiples displays. They help us to understand the nature of multi-dimensional data.
Jorge asked for suggestions to improve his chart and I came up with these:
- The abortion ratio is calculated as the number of pregnancies that end in an abortion per 1000 pregnancies. I would put the definition of the ratio in the title or a footnote (Number of abortions per 1,000 live births). Most places where I have seen this data presented tend to do it in this way too, although my preference would be for a % format.
- I think that the data for Race, Marital Status and Age Group are actually 3 different stories. It took me several scans of the whole chart set to realize this. I would add some white space between the Age group charts, the Race and Martial Status charts, to make it clear that are comparing different variables.
- Jorge shows an integrated line chart for Marital State and for Race but a small multiples chart for Age Groups. For a more consistent chart reading I would use small multiples charts for all variables.
- The axes of the Percent Distribution bars are not labeled, and have no scale. This coupled with varying meaning of the color encoding is confusing. The percentage distribution data for the Age < 15 lies around the 1% level and so cannot be seen on this scale even though data does exist. We also cannot encode the way this measure also varies with time over the same period. In fact we miss the fact that for women over 25 years the percentage distribution is actually increasing.
- I’m not sure if you need to show the all age groups in the Age group charts as light Grey lines. I know that you want the reader to compare the current age group in the context of the other age groups but this exactly what the small multiples are for anyway.
I like Jorge’s idea of integrating the abortion ratio and abortion percentages, but I found having another group of small multiples provides a more consistent view. If we don’t have the percentage distribution numbers then we can easily draw the wrong conclusions about the data.
Here my small multiples chart for the Abortion by Age Groups:
After much deliberation and debate, we are pleased to announce the winners of the 2008 Excel Dashboard Competition. We were impressed by many of the entries, and thanks to all of you who entered. It’s good to see MicroCharts being put to effective use, and adding value in such a variety of business scenarios and sectors. We had entrants from a broad range of industries including Banking, Insurance, Healthcare, Manufacturing, Oil and Gas and Pharmaceuticals.
The winners are:
1) Wade Stokes – Bank Dashboard
Displaying many disparate Banking Key Performance Indicators, and designed as the basis for the Management review of business performance, it truly achieves More Information per Pixel.
2) Jim Uden – Outpatient Surgery Center Dashboard
Developed for Meridian Surgical Partners, as a one page snapshot for the review and presentation of partnership level business operations and trends. Jim also includes probably the best associated description of dashboard content and the thought processes involved which we’ve seen.
3) Hitesh Patel – Pharmaceutical Sales Dashboard
Developed by Hitesh Patel and Mike Askew of Data Intelligence, for Bristol Myers Squibb. A key report for the Regional Sales Managers, containing the information required to run the business in terms of cash, growth, share, and competitive performance.
Congratulations to all 3 of our winners. Have a look at our competition page for screenshots and some background on the winning entries. Over the next few weeks we’ll be analyzing the entries in more depth at http://blog.xlcubed.com. We’ll overview each, cover some of the techniques used, and hopefully suggest some further improvements.
[…] on a slide, you want to convey one message. your graph must NOT carry any information that can be interpreted differently than the point you are trying to make. the corollary is that in virtually all cases, you should display as little data points as possible: 1 if possible, 2 but no more than 3. If you need more than 3 data points, use handouts. […]
which is very much in line with what Seth Godin said in his famous post about Chart Rules:
No, the reason you put a chart in a presentation is to tell a story. A single story, one story per chart
Why should a presentation display as little data as possible? Why should a slide contain only one chart? I demand More Information Per Pixel. Why not have a data-rich chart in a slide – no, even a couple of charts to support my message?
My friend Rolf Hichert has a totally different design philosophy.
Components of good presentations slides:
- A clear message
- A clear title (should be a complete sentence, including units like K$)
- Each slide to conveys only one message
- More tables and charts to support the message
- Choose the right chart type
- Use arrows, color etc. to highlight the message
Look at this sample taken from Rolf’s web site:
The slide has a clear title that conveys one message: "Further positive Development in Frankfurt, Vienna and Graz – Action needed in Lausanne and Linz".
The slide contains small multiple charts to support the message, where Rolf has chosen a line chart to emphasize trends or patterns. The problematic regions mentioned in the title are colored in red; those that made the CEO happy in green.
In chart design it’s good to make things simple, but you certainly should avoid oversimplification. As Einstein said:
"Things should be made as simple as possible, but not any simpler"
Seth Godin presented in his blog The three laws of great graphs:
1. One Story
2. No Bar Charts
Effective chart design rules are simple, but reducing it to this set of 3 rules certainly is an over-oversimplification.
Particularly rule 2 is flawed. Seth details rule 2:
"NO BAR CHARTS
Bar charts are dramatically overrated, primarily because they’re the first choice in many graphing programs.
The problem with bar charts is that they should either be line/area charts (when graphing a change over time, like unemployment rates) or they should be a simple pie chart (when comparing two or three items at the same scale).
Choose Chart Types Intelligently
We are working tightly together with Stephen Few on a new product that helps business users creating effective charts with Excel and are therefore we are quite familiar with Stephen’s design principles.
Its an easy to learn set of rules
1. Determine the relationship you want to display
|Sales in different regions|
|Best selling products|
|Sales in the last 12 months|
|Revenue Actual vs Budget in the last 12 months|
|Support response times|
|Relationship between employee’s heights in inches and their salary|
2. Determine if you want to emphasize individual values or the overall pattern
3. Determine the chart type
Bars and Columns
Bars and Columns
Lines to emphasize the overall trends or pattern
Points connected by lines to slightly emphasize individual values
Columns to emphasize and support comparisons between individual values
Bars and Columns
Lines to emphasize the overall shape of the data
Points connected by lines to slightly emphasize individual data points
Bars and Columns to emphasize individual values
Columns to emphasize individual values
Lines to emphasize the overall shape of he data
Points and a trend line in the form of a scatter plot
Armed with this set of rules you would rule out Seth’s pie chart, and use the bar chart in the appropriated business context.
An interesting optical illusion is the so-called Hermann Grid illusion: the effect of seeing gray dots at the intersections of a black grid on a white background or a white grid on a black background.
While it’s an interesting optical illusion, it’s something we should avoid in management reporting:
Tables formatted with medium or thick black or gray borders tend to produce Hermann Grids. Just scan the table above and you should see the gray dots in the grid intersections.
To avoid this unpleasant and distracting effect, and to maximize the data-ink ratio follow this simple but very effective table design rule:
- Avoid using dark and heavy grids
- Use light gray grids instead
Above is the same table with light gray borders. This eliminates the Hermann Grid illusion and – by de-emphasizing the grid – puts more emphasis on the numbers.
Here are some images I found on Google Image Search that show how popular it is to put your data behind Hermann Grids:
So I hope you are with me – get rid of the heavy grids and free your data!
A very, very cool Flash Movie about Color in Motion. An interactive Experience of Color Communication and Color Symbolism of Claudia Cortes.