Chart Advisor

Today's author, Scott Ruble, a Program Manager on the Excel team, is seeking feedback on a new prototype for building charts.

When Office 2007 was released, one of the strong pieces of feedback was Excel needs to do a better job guiding users in the proper selection of charts to effectively communicate their data. Though it wasn't our intent, some of the new formatting options such as glow and legacy 3D charts can be used inappropriately, which obscure the meaning of a chart. Some people felt that these features contributed to creating more "chart junk." In an effort to improve this situation, we have created a prototype called the Chart Advisor. The Chart Advisor intelligently suggests different chart types based on an analysis of your data in Excel 2007. Depending on the feedback we get on Chart Advisor, we may incorporate this as a native Excel feature in a future release. For a quick tour of Chart Advisor, please check out the video or read the steps below and give it a try.

Video: Chart Advisor Overview

Background

Chart Advisor was developed by Office Labs which is a new organization within Office that focuses on concept validation and incubation. Over the course of several months, we compiled a set of well defined heuristics such as when to use a line chart versus a column chart. These heuristics were incorporated into an advanced rules engine which scans your data and scores all of the relevant chart types. Top scoring charts are presented for previewing, tweaking, and inserting into your Excel worksheet. The rules used for scoring can be complex and we know that some rules are missing or need fine tuning. Given the breadth of possible chart types and data sets this will probably always be the case - even for a fully functional shipping version of Chart Advisor. To address this, the rules were separated from the rules engine into an XML file which can be modified by you to meet your individual business needs. Other notable features of the Chart Advisor are shown below.

• Automatic pivoting to generate more useful charts
• Hidden rows and columns are ignored in the spreadsheet
• Totals for columns or rows are ignored in the charts so as not to skew the data plotted
• Ability to quickly change the data displayed in the chart through the Modify chart pane
• Ability to filter data
• Intelligent mapping of data to the axes
• Scoring of charts to show the best charts first
• Chart formatting (legend on/off/location, chart style) is rules-based - e.g., no legend if only one value series

The vision for Chart Advisor was for it to address four different areas of recommendation:

• Chart type (e.g., column, line, scatter, etc.)
• Formatting (e.g., fill, color, effects, etc.)
• Chart element (e.g., legend, trendline, error bars, etc.)
• Aggregations and pivots

The Chart Advisor is a proof of concept. As such, it doesn't address all aspects of our vision just yet. Currently it focuses on providing chart type suggestions and to a more limited extent formatting, chart element, aggregation, and pivot suggestions. However, I'd love to get your feedback on other ways you think the Chart Advisor could add value.

Steps to Use

Using the Chart Advisor is pretty straight forward. As noted previously, please check out the video for a quick tour or use the following steps.

Sample Table of Data

1. Install Chart Advisor.
2. Copy the data from the sample table above into an Excel 2007 workbook.
3. Select the data that you would like to chart. If you select all of the data in the sample table, Chart Advisor will analyze all of the rows and columns and provide suggestions based on all of data and subsets of the data. This is useful when you don't have any idea how to visualize the data or want to explore a variety of possibilities. If you would like a more narrow view of the data, select just those columns. For example, select the columns for Region and Sales.
4. From the "Insert" tab in Excel 2007, click on the "Chart Advisor" button in the ribbon. This will launch the Chart Advisor and start the data analysis process. Note that this analysis may take a long time for large data sets. As noted previously, Chart Advisor is a prototype and does not have the performance of a truly native feature in Excel. The performance would significantly be improved if Chart Advisor shipped with a future version of Excel.
5. After the Chart Advisor dialog comes up, hover over the recommendations at the top of the dialog. This will show a live preview of the suggested chart in the main window. To better understand the scoring, hover over the percentage in the lower right corner of the chart thumbnail. For the sample data, notice that the rules engine detected repeating rows for the Region column and suggests several aggregated views of the data.
6. Modify the chart data as you see fit by turning on or off fields in the "Modify Chart" panel on the left. You can also filter the data by using the "Filter Data" panel.
7. Once you are done modifying the chart, click the "Insert Chart" button in the lower right corner of the dialog. Note that you won't be able to re-enter this dialog to make changes after clicking the insert button. Again, this is a prototype with some limitations.

Caveats

Our intent was to make the Chart Advisor functional enough for experimentation and validating the concept. As such, it does have several limitations which I've mentioned previously but I'll summarize here.

• Not all aspects of the intended vision have been addressed. The focus for this first prototype was primarily on chart type recommendations.
• The rules engine needs fine tuning for some data sets. As such, it may suggest inappropriate chart types periodically.
• For large data sets, the performance may be slow.
• There may be bugs in the prototype that limit its use. The prototype has been tested but not to the degree that a typical shipping product would receive.

Feedback

As mentioned previously, we would love to get your feedback on the Chart Advisor. Though it isn't fully functionally just yet, conceptually speaking would it meet your needs, what changes would you like to see, and what other ways could it add value to you or your company? Please give it a try and tell a friend.