The simplest way to add new data points to an existing series on a chart is to make the chart's source range a table (select any cell within it, and press Ctrl+T; for more information, see Chapter 21, "Managing Information in Tables") and then add new rows to the table. Excel 2007 incorporates these news rows into the chart without requiring you to do anything further.
If you add a new column immediately to the right of an existing table, Excel expands the table to include the new column. If you've created a chart from such a table, the new column becomes a new data series. That might or might not be desirable, depending on the contents of the new column. If you find yourself with an unwanted new series, you can delete it with the help of the Select Data Source dialog box (see "Modifying Data with the Select Data Source Command" on the next page). Alternatively, and more simply, you can delete a series by selecting it on the chart and pressing the Delete key.
If you choose not to make your source data a table, you can still use your mouse to add data points to an existing chart series. Figure 20-16 shows a candlestick chart that plots data through May 2. Notice that because the chart area is selected, Excel draws two rectangles around the source data. The first rectangle, in column A, outlines the range that the chart is using for its category axis labels. The second, encompassing columns B through E, outlines the four data series. Excel uses magenta for the first rectangle and blue for the second to help you distinguish the two.
Figure 20-16: When you select the chart area, Excel outlines the source data on your worksheet. You can drag a fill handle to alter the source range.
To extend the chart so it includes the new data in rows 750 through 755, drag the fill handle in the lower-right corner of either rectangle.
Be sure to drag the fill handle, not the bottom of the rectangle if you intend to add data. Dragging the edge of the rectangle moves the data selection without changing its size. Dragging the bottom down five rows in Figure 20-16, for example, would move both the start date and the end date forward.
Another simple way to expand or contract a chart is to right-click it and click Select Data. The Chart Data Range box in the Select Data Source dialog box, shown in Figure 20-17, indicates the current extent of the chart's source data. You can edit the reference in place or drag your pointer over the desired range on the worksheet. If your intention is to add or remove an entire series (as opposed to data points within an existing series), you can take advantage of the Add and Remove buttons on the left side of the dialog box. The Remove button is particularly useful if the series you want to excise lies somewhere within the source range rather than at its edge. If the one you want to rid yourself of is the third column of five, for example, it's easiest to select it in the Legend Entries (Series) list and then click Remove.
Figure 20-17: You can expand or contract an existing chart by going to the Select Data Source dialog box and modifying the reference in Chart Data Range.
It's not always convenient to set out your source data in a neat, contiguous range. Suppose, for example, that you've downloaded from the United States Department of Agriculture the block of information shown in Figure 20-18. You'd like to whip out a quick column chart comparing turkey production across two states (North Carolina and South Carolina) and three years (2002 through 2004). You could copy the data you need to another part of your worksheet and turn it into a table, but you're in a hurry.
Figure 20-18: Sometimes your source data will arrive in a form that's awkward for plotting.
Here's another way (not better, perhaps, but an alternative):
Hold down the Ctrl key, because you're about create a discontiguous selection.
Click North Carolina, and then drag the pointer across the three Turkeys numbers for that state.
Click South Carolina, and then drag across its Turkeys numbers.
With the appropriate data selected, create your chart in the customary way. Excel gives you something like this:
That's an excellent start. Excel has the data markers and legend labels right. But you need to fix the horizontal axis labels.
Right-click the chart anywhere, and click Select Data Source (or just click Select Data on the Design tab on the Ribbon). The Select Data Source dialog box appears, with the useless category-axis labels listed in the right side of the dialog box:
On the right side of the dialog box, under Horizontal (Category) Axis Labels, click Edit. The Axis Labels dialog box arrives:
Drag the pointer across the worksheet cells containing the appropriate labels-2002, 2003, and 2004. Then click OK.
Excel returns you to the Edit Data Source dialog box (in case you have any further duties to perform there). Click OK, and you have your chart:
Yet another way to create this chart without copying the desired cells to another part of the worksheet is to hide all the intervening rows and columns in which you're not interested. Excel normally excludes hidden rows and columns within a chart selection, so you'd certainly get the chart you want. The difficulty with this approach is that the moment you reveal those hidden rows and columns, you lose your focused chart.
Excel ordinarily plots empty cells as gaps-that is, it doesn't plot them. It also omits cells in hidden rows and columns. As Figure 20-19 shows, you can change both defaults if you want. The alternatives for empty cells are to plot them as zeros or to fill the gap with a straight line. The latter option is available only in line and X Y (scatter) charts.
Figure 20-19: By default Excel omits missing data in a source range and ignores data in hidden rows and columns.
Hidden and empty cell settings are chart-specific. To modify the behavior for a particular chart, right-click the chart, and click Select Data. Then click Hidden And Empty Cells.