Microsoft’s Office Labs has released an interesting new add-in for Excel 2007 (only!) called Chart Advisor. The purpose of this add-in is to help you to decide on the best chart type to represent your data. It is supposed to be a kind of charting expert system. I suspect that Jon Peltier will rip it apart, and that should be fun. Still, it seems to be an interesting idea so I thought I would provide an overview here. Note: I have updated this post at the bottom to reflect a recent (28 August 2008) update to the add-in.
The general concept of Chart Advisor is that you select some data in the worksheet, click the Chart Advisor button on the Insert tab, and then it analyzes the data and provides a range of potential charts sorted by desirability. To test it out I tried to create a chart of student grades across three exams (student names followed by three columns of exam grades). My thought was that a clustered column chart would be most appropriate. Instead, the add-in suggested an XY Scatter chart as its first choice. That is weird, especially since it ignored the text in the first column (student names). Something like this should recognize text in the first column and suggest a chart type with a category axis. Here is a screenshot of the Chart Advisor in action (click for a larger image).
The XY Scatter chart was given a "Recommended Chart Score" of 63%. The second favorite was a clustered bar chart (score 55%), which should have been the first choice. This chart scoring is interesting, particularly because it will tell you why the chart scored as it did when you hover the mouse over the choice at the top. For example, here is the breakdown for the XY chart (the new version, see bottom of post, now shows many more rules and points than this graphic):
Notice that it shows how the score was determined, and the points associated with each rule. That total is then multiplied by a "popularity" score. I imagine (not really sure, though) that Microsoft has some kind of research results that show how often each type of chart is used, and that is how the popularity multiplier is determined. Very interesting, but I think that the rules seem somewhat crude. Of course, assumptions have to be made in order to have a mathematically tractable model of the chart creation process. I’m sure these will be tweaked over time if this tool becomes popular.
My original choice of a clustered column chart got a score of only 22%, mainly because the category descriptions are more than 16 characters in length. That earned it a -15 on that rule, whereas the clustered bar chart got a +15. That makes sense and I can understand the logic. The difference in the two scores (55 vs 22) is due to the 30-point difference in that rule multiplied by the 1.10 popularity score (33 point difference = 30 × 1.10).
If you don’t approve of the top-ranked chart, you can easily explore the others. Just move the mouse over the other choices at the top of the dialog box and the pictured chart will appear. There are more choices available in the gallery, which can be expanded by clicking the scroll bar at the right.
Once you find one that you like you select it by clicking the chart in the gallery, and then you can further refine your chart. For example, in the Modify Chart pane you can turn individual data series on or off. However, there is no way to change any of the chart formatting. For example, you cannot turn off the "eye candy" (3D and shadow effects), or change the colors for any of the series. Still, these things can be modified after the chart is inserted into the worksheet by pressing the Insert Chart button.
Chart Data Filtering
One of the most interesting features of the Chart Advisor is its ability to filter the chart data (much like you can in a Pivot Table). You can filter based on any of the data series (even the category labels) that are in the chart, and you can apply multiple filters. For example, I could filter based on student names and/or any of the exam scores. Note that the add-in also ignores hidden columns, so they don’t have to be manually disabled.
Interestingly, when you insert the filtered chart into the worksheet it becomes de-linked from the data in the worksheet. That is, it doesn’t reference the original data. Since the actual data (not ranges) is stored within the chart, changing the original data does not affect the chart. I don’t like this, but I suppose that there was no way around it.
Unfortunately, the de-linking happens even if you haven’t applied any filters to the data. That shouldn’t happen and needs to be fixed in order for the Chart Advisor to be truly useful. I can understand de-linking with filtered data, but not if you are charting the entire data set.
My Overall Impressions
I really like the concept of the Chart Advisor, and I think that a lot of Excel users will find it to be useful in helping them to select an appropriate chart type. I see a lot of people using incorrect chart types (especially using Line charts when an XY Scatter would be much more appropriate) and this tool should help with that problem. I also like how easy it is to use. It is a little bit like the Chart Wizard from previous versions of Excel. Finally, the ability to filter the data and include or exclude a data series is very cool.
However, I dislike the fact that the charts that are created are not linked to the data in the worksheet. You can get around this problem by re-linking the data after you insert the chart. Just right-click the chart and choose Select Data, and then edit the data ranges for each series.
Overall, I would say that this is a good first attempt, but it could use some additional work. It is completely free, and you don’t even need to provide any personal data. All that you need is a copy of Excel 2007, and this add-in is certainly worth a download if you have that.
If you don’t like it, you can always uninstall it through the Add-ins manager in Excel Options. Note that it is a COM add-in, so you will have to select COM Add-ins in the manage list before getting to the dialog box that allows you to disable or remove it.
Updated Version Now Available
Office Labs has released an updated version of the Chart Advisor (the link is the same as before). This update has fixed problems that people were having with installation. However, they also have changed several other things. The “eye-candy” in the charts seems to have been turned off or, at least, toned down. Also, I originally lost the ability to see the reasoning behind the chart scoring (as shown above), but now it is back and more of the rules and points are now exposed. The chart scores that I am now getting for this same data set are now much higher than before, but I don’t know why. Also, it seems to ignore more of the data by default. For example, it is now ignoring my Exam 3 data in the XY Scatter chart (still top-ranked), and it ignores the exam scores for Exam 2 and 3 in the “clustered” column chart. So, the clustered column chart isn’t really clustered since it left out two of my three numeric series. Finally, there is now a brief message that notes that this tool is a prototype and it isn’t tuned for performance. I’m not sure, but I think that I liked the original version better.
Commenting is not available in this channel entry.