You have an Excel chart, but the data it uses is wrong or outdated. This happens when your source data changes location or you need to plot different information. Excel charts are linked to a specific cell range, and that link can be updated. This article shows you the steps to redefine which cells feed your chart.
Key Takeaways: Changing a Chart’s Data Source
- Select Chart > Chart Design > Select Data: Opens the dialog to edit the chart’s data series and axis labels.
- Edit the Chart Data Range: Manually type or select a new cell range for the entire chart on the worksheet.
- Edit Series in the Select Data Source dialog: Change the data for individual chart series one at a time for precise control.
How Excel Charts Connect to Data
An Excel chart is not a static picture. It is a dynamic object that pulls values directly from worksheet cells. The connection is defined by a data range, like A1:B10. Each series in the chart has its own range for values and often another for category labels. When you change the numbers in the source cells, the chart updates automatically. To change *which* cells are used, you must modify this link. You can update the entire source range at once or adjust individual data series separately. This is necessary when you expand a dataset, switch to a different table, or correct a mistaken selection.
Steps to Change a Chart’s Data Source
Use the Select Data Source dialog for the most control. This method works for all common chart types like column, line, and pie charts.
- Select the chart
Click anywhere on the chart border or plot area. You will see the Chart Design and Format tabs appear on the ribbon. - Open the Select Data Source dialog
Go to the Chart Design tab on the ribbon. In the Data group, click the Select Data button. A dialog box will open showing your current chart data range. - Change the entire Chart Data Range
In the dialog, look for the “Chart data range” field. Click the small range selector icon at the end of the field. The dialog will minimize. On your worksheet, use your mouse to drag and select the new cell range that contains your data and headers. Press Enter. The dialog will reopen. - Verify or edit individual series
In the “Legend Entries (Series)” list, you will see each data series. Select a series and click Edit. You can manually adjust the Series name and Series values ranges here. This is useful if only one part of your chart needs correction. - Update Horizontal Axis Labels
In the same dialog, find the “Horizontal (Category) Axis Labels” section. Click the Edit button. Select the new range on your sheet that contains your category labels, like months or product names. Click OK. - Apply the changes
Click OK in the Select Data Source dialog. Your chart will immediately update to reflect the new data source.
Using the Drag-and-Drop Method
For a quick visual adjustment, you can drag the chart’s data range directly on the worksheet. This method is best for simple charts with one data series.
- Select the chart
Click on the chart to select it. On the worksheet, the linked data range will be highlighted with colored outlines. - Drag the range handles
Move your mouse cursor to the corner of the highlighted blue border. When the cursor changes to a two-sided arrow, click and drag to expand or contract the selected range. Release the mouse button to update the chart.
Common Mistakes and Limitations
Chart Shows #REF! Errors After Changing Data
This error appears if you delete the worksheet that contained the original source data. The chart’s link points to cells that no longer exist. To fix this, you must use the Select Data Source dialog to point the chart to a valid range on an existing sheet. You cannot recover the link by undoing the sheet deletion.
New Data Series Not Appearing in the Chart
If you expand your data table by adding new rows or columns, the chart does not automatically include them. You must manually extend the chart’s data range using one of the methods above. Excel does not have a default setting for dynamic range expansion in basic charts.
Changing Source to a Non-Contiguous Range
The Chart Data Range field typically requires a single, contiguous rectangle of cells. To plot data from separate columns that are not next to each other, you cannot select them all at once in the main field. Instead, add each separate column as a new series individually using the Add button in the Select Data Source dialog.
Select Data Dialog vs. Dragging Handles: Key Differences
| Item | Select Data Source Dialog | Dragging Worksheet Handles |
|---|---|---|
| Control Level | High – edit series and labels individually | Low – only adjusts the overall rectangular range |
| Best For | Complex charts, non-contiguous data, correcting axis labels | Quickly extending or shrinking a simple data range |
| Access Path | Chart Design > Select Data | Select chart, then drag colored border on sheet |
| Ability to Change Series Order | Yes – use the up/down arrows in the dialog | No |
| Data on Another Worksheet | Yes – you can select ranges from any sheet | No – only works for data on the same sheet as the chart |
You can now update any Excel chart to use new or corrected data. Use the Select Data dialog for detailed control over each series and label. For a faster workflow with large datasets, explore using an Excel Table as your source, as charts based on Tables automatically expand. Remember the F4 key after selecting a new range in a dialog to lock the reference if needed for copying.