This is a small entry in the Chart Busters series. I received a request for something which was mechanically possible, but which was not visually desirable.
I was asked whether a certain type of chart could be done. The data was weekly sales of 14 products, but the user wanted to show only the top ten products each week, in a clustered column chart, with the bars within each cluster arranged in descending order. Here is a representative three-week period of data:
Well, long story short, this kind of chart is certainly possible. All it takes is a couple sets of formulas, one set to rank the weekly figures, the next to determine which product goes into which position for each week. The chart is reproduced below. No matter how much I’d like to show off how clever I am when it comes to writing formulas that define an intricate chart source data range, I won’t show how I did this. It’s really an ineffective way to display this kind of data, and I don’t want to encourage anyone by publishing the recipe.
What makes this chart so ineffective? There are too many colors (as Wayne or Garth once said, “That looks like a plate of hurl!”), and the colors are critical to understanding the information. The order of the colors (and the items they represent) changes from one cluster to the next. Because of the excessive colors and their variable positions, the user spends so much time bouncing from one cluster, to the legend, to the next cluster, back to the first cluster, then to the legend, then back to the cluster, then to the legend, ad nauseum. It’s very hard to see where a product falls in on any given week, and tracking trends from week to week is nigh impossible.
As we’ve discussed, the best way to show data over time is with line charts. Showing more than a few differently formatted lines will confuse the data, but I’ve shown how to work this out in other articles on this blog. If we treat the weekly line chart like a parallel coordinates chart, and provide a means for the user to selectively highlight a small subset of the data in the chart, it will speed understanding. For this exercise I’ve decided that three series will be highlighted.
Construction of this chart entails plotting of weekly data for all products, and formatting this data in the same dull color. This gives the user an overview of the data. Then a few more ranges are populated. Three columns are used for the weekly data of three highlighted series, then this data is plotted and given distinctive formatting. Three cells are used to identify each highlighted series so this weekly data can be easily extracted from the full data range using Excel’s INDEX function. Finally, the list of products is placed in a column so we can populate dropdown controls to enable the user to make his selections.
The resulting chart has all the data in a dull blue, with three highlighted series corresponding to the series which were best sellers in each of the weeks.
Can you even follow these three products in the column chart above? Probably not, and certainly nowhere near as easily as this line chart makes it.
I’ve described the dropdown-based chart highlighting techniques in Sample Parallel Coordinate Chart, Easier Interactive Multiple Line Chart, and Interactive Parallel Coordinates Chart, so I will not go over the construction of this chart. I have posted my workbook in a handy zip file, for any interested readers to peruse.