Update the data in an existing chart

After you create a chart, you might have to change its source data on the worksheet. To incorporate these changes in the chart, Microsoft Office Excel provides various ways to update a chart. You can instantly update a chart with changed values, or you can dynamically change the underlying source data. You can also update a chart by adding, changing, or removing data.

What do you want to do?

Change the underlying data that a chart is based on

Add data to an existing chart

Change the data in an existing chart

Remove data from a chart

Change the underlying data that a chart is based on

When you update the underlying data of a chart, the chart's data and appearance also changes. The extent of those changes depends on how you define the chart's data source. You can automatically update changed worksheet values in a chart, or you can use ways to dynamically change a chart's underlying data source.

Automatically update changed worksheet values in an existing chart

The values in a chart are linked to the worksheet data from which the chart is created. With calculation options set to automatic (Microsoft Office Button , Excel Options, Formulas category or Formulas tab, Calculation group, Calculation Options button), changes that you make to the worksheet data automatically appear in the chart.

1. Open the worksheet that contains the data that is plotted in the chart.

2. In the cell that contains the value that you want to change, type a new value.

3. Press ENTER.

Top of Page

Dynamically change the underlying data source of an existing chart

If you want the chart's data source to dynamically grow, there are two approaches you can take. You can base a chart on data in an Excel table, or you can base it on a defined name.

Base the chart on an Excel table

If you create a chart based on a cell range, updates to data within the original range are reflected in the chart. But to expand the size of the range, by adding rows and columns, and change the chart's data and appearance, you must manually change the original data source in the chart by changing the cell range that the chart is based on.

If you want to change the chart's data and appearance when the data source expands, you can use an Excel table as the underlying data source.

1. Click the chart for which you want to change the cell range of the source data.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. In the Select Data Source dialog box, note the location of the data range, and then click OK.

4. Go to the location of the data range, and click any cell in the data range.

5. On the Insert tab, in the Tables group, click Table.

   

   Keyboard shortcut  You can also press CTRL+L or CTRL+T.

For more information, see Overview of Excel tables and Create or delete an Excel table in a worksheet.

Top of Page

Base the chart on a defined name

Another approach to dynamically changing the chart's data and appearance when the data source expands is to use a defined name with the OFFSET function. This approach is useful when you need a solution that also works with previous versions of Excel.

For more information, see How to use defined names to automatically update a chart range in Excel.

Top of Page

Add data to an existing chart

You can use one of several ways to include additional source data in an existing chart. You can quickly add another data series, drag the sizing handles of ranges to include data on a chart that is embedded on the same worksheet, or copy additional worksheet data to an embedded chart or to a separate chart sheet.

Add a data series to a chart

1. Click the chart to which you want to add another data series.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. In the Select Data Source dialog box, under Legend Entries (Series), click Add.

4. In the Edit Series dialog box, do the following:

   • In the Series name box, type the name that you want to use for the series, or select the name on the worksheet.

   • In the Series values box, type the reference of the data range of the data series that you want to add, or select the range on the worksheet.

     You can click the Collapse Dialog button , at the right end of the Series name or Series values box, and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the whole dialog box.

     If you use arrow keys to position the pointer to type the reference, you can press F2 to ensure that you are in Edit mode. Pressing F2 again switches back to Point mode. You can verify the current mode on the status bar.

Top of Page

Drag the sizing handles of ranges to add data to an embedded chart

If you created an embedded chart from adjacent worksheet cells, you can add data by dragging the sizing handles of source data ranges. The chart must be on the same worksheet as the data that you used to create the chart.

1. On the worksheet, type the data and labels that you want to add to the chart in cells that are adjacent to the existing worksheet data.

2. Click the chart to display the sizing handles around the source data on the worksheet.

3. On the worksheet, do one of the following:

   • To add new categories and data series to the chart, drag a blue sizing handle to include the new data and labels in the rectangle.

   • To add new data series only, drag a green sizing handle to include the new data and labels in the rectangle.

   • To add new categories and data points, drag a purple sizing handle to include the new data and categories in the rectangle.

Top of Page

Copy worksheet data to a chart

If you created an embedded chart from nonadjacent selections or if the chart is on a separate chart sheet, you can copy additional worksheet data into the chart.

1. On the worksheet, select the cells that contain the data that you want to add to the chart.

   If you want the column or row label for the new data to appear in the chart, include the cell that contains the label in the selection.

2. On the Home tab, in the Clipboard group, click Copy .

   

   Keyboard shortcut  You can also press CTRL+C.

3. Click the chart sheet or the embedded chart into which you want to paste the copied data.

4. Do one of the following:

   • To paste the data in the chart, on the Home tab, in the Clipboard group, click Paste .

     Keyboard shortcut  You can also press CTRL+V.

   • To specify how the copied data should be plotted in the chart, on the Home tab, in the Clipboard group, click the arrow on the Paste button, click Paste Special, and then select the options that you want.

Top of Page

Change the data in an existing chart

You can change an existing chart by changing the cell range that the chart is based on or by editing the individual data series that are displayed in the chart. You can also change the axis labels on the horizontal (category) axis.

Change the cell range that a chart is based on

1. Click the chart for which you want to change the cell range of the source data.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. In the Select Data Source dialog box, make sure that the whole reference in the Chart data range box is selected.

   You can click the Collapse Dialog button , at the right end of the Chart data range box, and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the whole dialog box.

4. On the worksheet, select the cells that contain the data that you want to appear in the chart.

   If you want the column and row labels to appear in the chart, include the cells that contain them in the selection.

   If you use arrow keys to position the pointer to type the reference, you can press F2 to make sure that you are in Edit mode. Pressing F2 again switches back to Point mode. You can verify the current mode on the status bar.

Top of Page

Rename or edit a data series that is displayed in a chart

You can modify the name and values of existing data series without affecting the data on the worksheet.

1. Click the chart that contains the data series that you want to change.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. In the Select Data Source dialog box, under Legend Entries (Series), select the data series that you want to change, and then click Edit.

4. To rename the data series, in the Series name box, type the name that you want to use for the series, or select the name on the worksheet. The name that you type appears in the chart legend, but is not added to the worksheet.

5. To change the data range for the data series, in the Series values box, type the reference of the data range of the data series that you want to add, select the range on the worksheet, or enter the values in the boxes. The values that you type are not added to the worksheet.

   If the chart is an xy (scatter), the Series X values and Series Y values boxes appear so you can change the data range for those values. If the chart is a bubble chart, the Series X values, Series Y values, and Series bubble sizes boxes appear so you can change the data range for those values.

   For more information, see Present your data in a scatter chart or a line chart and Present your data in a bubble chart.

For each of the series boxes, you can click the Collapse Dialog button , at the right end of the Series name or Series values box, and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the whole dialog box.

If you use arrow keys to position the pointer to type the reference, you can press F2 to ensure that you are in Edit mode. Pressing F2 again switches back to Point mode. You can verify the current mode on the status bar.

Top of Page

Change the order of the data series

1. Click the chart that contains the data series that you want to change.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. In the Select Data Source dialog box, under Legend Entries (Series), select the data series that you want to move to a different location in the sequence.

4. Click the Move Up or Move Down arrows to move the data series.

5. Repeat step 3 and 4 for all data series that you want to move.

Top of Page

Change the horizontal (category) axis labels

When you change the horizontal (category) axis labels, all horizontal axis labels will be changed. You cannot change individual, horizontal axis labels.

1. Click the chart that contains the horizontal axis labels that you want to change.

   This displays the Chart Tools, adding the Design, Layout, and Format tabs.

2. On the Design tab, in the Data group, click Select Data.

   

3. Under Horizontal (Category) Axis Labels, click Edit.

4. In the Axis label range box, type the reference of the data range of the data series that you want to use for the horizontal axis labels, or select the range on the worksheet.

   You can click the Collapse Dialog button , at the right end of the Series name or Series values box, and then select the range that you want to use for the table on the worksheet. When you finish, click the Collapse Dialog button again to display the whole dialog box.

   If you use arrow keys to position the pointer to type the reference, you can press F2 to make sure that you are in Edit mode. Pressing F2 again switches back to Point mode. You can verify the current mode on the status bar.

Top of Page

Remove data from a chart

With calculation options set to automatic (Microsoft Office Button or Formulas tab, Calculation group, Calculation Options button, Excel Options, Formulas category), data that is deleted from the worksheet will automatically be removed from the chart. You can also remove data from the chart without affecting the source data on the worksheet.

Delete source data on the worksheet

• On the worksheet, select the cell or range of cells that contains the data that you want to remove from the chart, and then press DELETE.

  Notes: 

  • If you remove data from selected cells, empty cells are plotted in the chart. For more information about how to change how empty cells are plotted, see Display hidden worksheet data and empty cells in a chart.

  • If you delete a column, the whole data series is removed from the chart. If you delete a row, you may receive an error message. When you click OK, the chart may display one or more data points of the data series that you deleted. You can click those data points, and then press DELETE.

Top of Page

Remove a data series from the chart

1. Click the chart or the data series that you want to remove, or do the following to select the chart or data series from a list of chart elements:

   1. Click a chart.

      This displays the Chart Tools, adding the Design, Layout, and Format tabs.

   2. On the Format tab, in the Current Selection group, click the arrow next to the Chart Elements box, and then click the chart element that you want to use.

      

2. Do one of the following:

   1. If you selected the chart, do the following:

      1. On the Design tab, in the Data group, click Select Data.

         

      2. In the Select Data Source dialog box, under Legend Entries (Series), select the data series that you want to remove, and then click Remove.

   2. If you selected a data series on the chart, press DELETE.

Top of Page