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 the range, and press Ctrl+T; for more information, see Chapter 22) and then add new rows to the table. Excel 2010 incorporates these rows into the chart without requiring you to do anything further.
If you add a new column immediately to the right of a table, Excel expands the table to include the 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 page 720.) 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 21-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 21-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 that it includes the 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 21-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 21-17, indicates the current extent of the chart’s source data. You can edit the reference in place or drag your pointer over the range you want 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 21-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.
There may be times when the data you want to plot does not lie in a contiguous block of cells. Suppose, for example, that from the following table you want to generate a column chart that compares Isabel Martins with Lola Jacobsen, omitting the other four salespersons:
The simplest way to do this is to use the filter control next to the Salesperson heading to restrict the table to Martins and Jacobsen. Excel responds by hiding rows 3, 5, 7, and 8, and any chart generated from the table will plot only the visible rows. If you don’t have filter controls, you can accomplish the same feat by manually hiding the rows you want to omit using the Format command in the Cells group of the Home tab.
You can also plot noncontiguous data by Ctrl-selecting the rows or columns you want to plot. In this example, you could hold down Ctrl, select rows 2, 4, and 6, and then click the appropriate command from the Charts group on the Insert tab. If you use this method, be sure to include the header row (row 2 in this example) in your selection so that Excel can provide appropriate labels for your chart series.
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 21-18 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 21-18. 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.