Working with Chart Data

After you create a chart, you might want to change the data range on which it is based, or how this data is plotted. The following sections explain how you can do this.

Plotting by Rows versus by Columns

By default, the columns of the datasheet form the data series. However, if you want, you can switch the data around so that the rows form the series. Figures 14.7 and 14.8 show the same chart plotted both ways so that you can see the difference.

Note 

What does the term data seriesmean? Take a look at Figures 14.7 and 14.8. Notice that there is a legend next to each chart that shows what each color (or shade of gray) represents. Each of these colors, and the label associated with it, is a series. The other variable (the one that is not the series) is plotted on the chart's horizontal axis.

Figure 14.7: A chart with the columns representing the series.

Figure 14.8: A chart with the rows representing the series.

To switch back and forth between plotting by rows and by columns, click the Switch Row/Column button on the Chart Tools Design tab.

EXPERT TIP 

A chart can carry a very different message when you arrange it by rows versus by columns. For example, in Figure 14.7, the chart compares the quarters. The message here is about improvement-or lack thereof-over time. Contrast this to Figure 14.8, where the series are the regions. Here, you can compare one region to another. The overriding message here is about competition-which division performed the best in each quarter? It's easy to see how the same data can convey very different messages; make sure that you pick the arrangement that tells the story that you want to tell in your presentation.

Redefining the Data Range

After you have created your chart, you may decide that you need to use more or less data. Perhaps you want to exclude a month or quarter of data, or to add another region or salesperson. To add or remove a data series, you can simply edit the datasheet. To do so, follow these steps:

1. On the Chart Tools Design tab, click Edit Data. The Excel datasheet appears. A blue outline appears around the range that is to be plotted.

2. (Optional) To change the data range to be plotted, drag the bottom-right corner of the blue outline. For example, in Figure 14.9, the West division is being excluded.

   You can also enlarge the data range by expanding the blue outline. For example, you could enter another series in column E in Figure 14.9 and then extend the outline to encompass column E.

   
   Figure 14.9: You can redefine the range for the chart by dragging the blue outline on the datasheet.

The preceding steps work well if the range that you want to include is contiguous, but what if you wanted to exclude a row or column that is in the middle of the range? To define the range more precisely, follow these steps:

1. On the Chart Tools Design tab, click Select Data. The Select Data Source dialog box opens, along with the Excel datasheet, as shown in Figure 14.10.

   
   Figure 14.10: To fine-tune the data ranges, you can use the Select Data Source dialog box.

2. Do any of the following:

   • To remove a series, select it from the Legend Entries (Series) list and click Remove.

   • To add a series, click Add, and then drag across the range on the datasheet to enter it into the Edit Series dialog box; then click OK to accept it.

   • To edit a series, select it in the Legend Entries (Series) list and click Edit. Then drag across the range or make a change in the Edit Series dialog box, and click OK.

3. (Optional) To redefine the range from which to pull the horizontal axis labels, click the Edit button in the Horizontal (Category) Axis Labels section. A dotted outline appears around the current range; drag to redefine that range and click OK.

4. (Optional) To redefine how empty or hidden cells should be treated, click the Hidden and Empty Cells button. In the Hidden and Empty Cell Settings dialog box that appears, choose whether to show data in hidden rows and columns, and whether to define empty cells as gaps in the chart or as zero values. Then click OK. The Hidden and Empty Cells Settings dialog box is shown in Figure 14.11.

   
   Figure 14.11: Specify what should happen when the data range contains blank or hidden cells.

5. When you are finished editing the settings for the data ranges, click OK to close the Select Data Source dialog box.

6. (Optional) Close the Excel datasheet window, or leave it open for later reference.