Selecting Data to Plot
Excel maintains links between worksheet data and the data series on a chart. When you create a chart, Excel automatically detects the data to be charted based on the current selection. If you select a single cell, Excel bases the chart data on the current region—an area that extends in each direction until you encounter the edge of the worksheet or a blank row or blank column. On the other hand, if you select a range of cells, Excel uses that range for the chart data.
Note
The number of points per series is limited to 32,000, and the total number of points per chart is limited to 256,000. The maximum number of series you can use in a chart is 255. If you have more series than this, you must filter your list before creating your chart. You should also seriously reconsider the point you're trying to make because even Stephen Hawking would have trouble absorbing that much information at once.
Be sure the range you select includes all the data to be charted, as well as the labels you'll use for the categories. The range does not have to be contiguous. For example, to create a pie chart, you might want to select a row of column labels and a row of totals, ignoring the detail rows in between. Nor do you need to select all the data in a table, if all you want to chart is a subset of the data—for example, on a 12-month budget worksheet, you might want to show sales totals only for the months of October through December.
Caution
If the range you plan to chart ends with a row or column of totals, don't include those totals in your selection; otherwise, the totals will create one column or pie slice that overwhelms all the others in the chart.
When you select the data source, Excel attempts to identify category headings, value axis labels, and data series; it also chooses whether to plot data by rows or by columns. This choice is based on the number of items—if there are more columns than rows, Excel plots the data by column, placing the column headings along the category axis; if there are more rows than columns, or an equal number of rows and columns, Excel plots by row.
Changing the way data is plotted can help emphasize different trends and patterns. For example, Figure 24.6 shows a worksheet that contains a small range of data. When plotted by row, the data emphasizes the full year's results for each region, and you can see at a glance that the Northwest had a great Q3. When plotted by column, however, the chart encourages comparing how each region did on a quarter-by-quarter basis, and it's easy to see that the West was dominant throughout the year.
Figure 24.6. Changing the way data is plotted—by rows or columns—can change the story a chart tells.
To reverse the order in which Excel plots the selected data, use one of two buttons on the Chart toolbar (if this toolbar is not visible, right-click the menu bar or any visible toolbar and choose its entry from the list). Click the Series in Rows or Series in Columns button to shift orientation. (This option is also available when you right-click the plot area and select Data Series, or in Step 2 of the Chart Wizard).) With some chart types and data, making this switch could render the chart incomprehensible; click the Undo button if that happens.
Normally, Excel plots data series from left to right and top to bottom. What do you do if your data source is arranged in alphabetical order, but you want to display the series in a different order—say, with the two most productive regions listed first, or with dates in reverse order? If you don't want to change the arrangement of data on the worksheet, you can change the plotting order of the data series:
Click any data series in the chart you want to change.
Select Format, Selected Data Series (or press Ctrl+1), and click the Series Order tab.
In the Series Order box (see Figure 24.7), select the series you want to move, and then click Move Up or Move Down. Repeat this step for each additional series you want to move. Watch the display in the Preview window to see the effect of your changes.
Figure 24.7.
