Making Plotted Data Charts Easier to Decipher in Excel

The plot area of a new Excel chart often needs modification. What you can do to make this area easier to understand depends heavily on the type of chart you're working. Some chart types such as those in the Pie chart category allow for very little improvement to their plot areas, whereas others such as Clustered Column and Clustered Bar charts allow for a great deal.

For charts that use both an X-axis and a Y-axis, sometimes the easiest thing you can do to improve the legibility of the chart is simply to add minor gridlines. Depending upon the type of chart, these extra lines can guide the eye from the tick marks displaying the closest values on the Y-axis to the actual data points in the chart.

To add (or remove) gridlines from a chart that uses them, follow these steps:

1. Choose Chart --> Chart Options to open the Chart Options dialog box.

You need to select the chart to add the Chart menu to the menu bar when working with an embedded chart.

2. On the Gridlines tab, select (or deselect) the Major Gridlines and/or Minor Gridlines check boxes for the appropriate axis.

3. When you finish, click OK.

Figure 1 shows a Clustered Column chart with minor gridlines added to the Value (Y) axis and major gridlines on the Category (X) axis. Note how well the major vertical gridlines for the X-axis clearly delineate the clusters with each data series.

When you're dealing with a chart where displaying the underlying numerical data visually represented in the plot area is important to its understanding, you have a couple of choices:

• Display a data table that shows all the graphed numbers in a standard row-and-column table format appearing immediately beneath the chart area. To add a data table to a chart, just click the Data Table button on the Chart toolbar.

• Add data labels that display the numbers right next to each data point in the graph. To display the values as data labels, select the Data Labels tab of the Chart Options dialog box (Chart --> Chart Options) and then select the Value check box option.

When Excel adds a data table or displays values as data labels in a chart, it formats these values exactly as they are in the worksheet itself. To apply a more compact number format in these chart elements, you must assign the new truncated number format to the cells in the worksheet that contain the actually graphed numbers.

Figure 2 shows the final version of the embedded Clustered Column chart. Here, along with the major X-axis gridlines and the minor Y-axis gridlines, you see that the chart's legend is now gone and a data table appears. Because the data table automatically includes the legend's color keys as part of its row headings, there is no longer any need to give up any precious chart real estate to the legend. The final version of this embedded chart maintains a balance between the graphic and textual elements while at the same time is easy to read and to interpret.