How to Change the Data Source of a Chart in Excel

Ever created the perfect Excel chart for a monthly report, only to realize you need to update its data source for the next month without rebuilding it from the ground up? Swapping out the underlying data powering your visualizations is a common task, and thankfully, you don’t have to start over every time. This article will walk you through several easy methods for changing the data source of a chart in Excel, from simple drag-and-drop adjustments to more powerful, scalable techniques that will save you time every week.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Would You Need to Change a Chart's Data Source?

Before we get into the "how," let's quickly cover the "why." You’ll often find yourself needing to update a chart’s data in a few common scenarios:

• Updating Time-Based Reports: You've built a sales dashboard for January and now need to update all the charts to show February's data, which is located in a different range.
• Adding New Data: Your original chart showed sales for three products, but now you’ve launched a fourth and need to add it to your comparison chart.
• Correcting Errors: You noticed the chart is accidentally pulling data from the wrong set of columns or rows.
• Switching Datasets: You have a chart template you love and want to apply it to a completely different, but similarly structured, spreadsheet of data.

Whatever your reason, mastering this skill is fundamental to efficient reporting in Excel.

Method 1: Using the 'Select Data' Dialog Box

The ‘Select Data’ dialog box is the most direct and versatile way to manage your chart's source data. It gives you full control over exactly what information is being visualized.

Let's say you have a simple chart showing sales figures for a few products from Quarter 1, and now you want to update it using Quarter 2 data located elsewhere on your sheet.

Here’s how to do it step-by-step:

1. Select Your Chart: Simply click anywhere on the chart you want to update. When you do this, you’ll see the contextual "Chart Design" and "Format" tabs appear in the Excel ribbon at the top of the window.
2. Open the 'Select Data' Menu: Navigate to the Chart Design tab and click on the Select Data button, which you can find in the “Data” group.

This opens the Select Data Source window. Let’s break down what you’re looking at:

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Understanding the Select Data Source Window

• Chart data range: This field shows the entire range of cells currently used by the chart (e.g., =Sheet1!$A$1:$B$5). This is the best place to quickly swap out the entire dataset if your new data is well-structured.
• Legend Entries (Series): This box on the left shows the individual data series plotted on your chart - like the different lines on a line chart or bars on a bar chart. You can add, edit, or remove individual series here for more granular control.
• Horizontal (Category) Axis Labels: This box on the right shows the labels used for the x-axis. Here, you can edit the labels if they need updating independently of the data series.

How to Swap the Entire Data Range:

If your new data is located in a different range but maintains the same structure as the old data, this is the fastest approach.

1. Inside the Select Data Source window, click into the Chart data range text box. You will see the current range surrounded by "marching ants" on your sheet.
2. Delete the existing range.
3. With your cursor still in the box, simply use your mouse to click and drag over the new range of data you want to use. You’ll see the new range address appear in the box.
4. Click OK. Your chart will instantly update to reflect the new data source.

How to Edit an Individual Data Series:

Sometimes you only need to adjust one data series instead of the whole chart. For example, maybe you want to update just the 'Projected Sales' line while keeping the 'Actual Sales' line as is.

1. Find the series you want to change in the Legend Entries (Series) box.
2. Select it, and then click the Edit button.
3. A new, smaller "Edit Series" dialog box will pop up.
4. Here you can independently change the Series name (by referencing the new header cell) and the Series values (by highlighting the new data points).
5. Click OK on both windows, and your chart will be updated.

Method 2: Simple Drag-and-Drop Resizing

If your new data is directly adjacent to your original data - for instance, you've just added a new row for the next month or a new column for an additional product - this drag-and-drop method is incredibly fast.

Here is how it works:

1. Click on your chart. Excel will automatically highlight the data source on your worksheet, surrounding it with colored borders.
2. Move your mouse to one of the corner handles of this highlighted range. Your cursor will change into a double-ended diagonal arrow.
3. Click and drag the corner handle to expand (or shrink) the boundary to include your new data.

For example, if your original chart data was in A1:B5 and you just added data for June in row 6, you can just click your chart and drag the blue border down to include A6:B6. The chart will update live as you drag.

Note: This method works best for simple, contiguous additions. It's not ideal if you need to switch the chart's data source to an entirely different location on the worksheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: For Dynamic Dashboards Using Named Ranges

If you're building a dashboard or a recurring report, where the data source will change regularly (e.g., from weekly sales to monthly sales), using Named Ranges is a game-changer. A Named Range gives a cell or range of cells a human-readable name, which you can then reference in your chart.

Why is this better? Instead of editing the chart itself, you just update what the Named Range refers to, and the chart updates automatically. It’s a classic “set it and forget it” approach.

Step 1: Create a Named Range

1. First, select the cell range you want to use as your chart's data source.
2. Go to the Formulas tab in the ribbon.
3. Click Define Name.
4. In the "New Name" dialog box, give your range a descriptive name like "MonthlySalesData." Avoid spaces or special characters.
5. Confirm that the "Refers to:" field shows the correct range and click OK.

Step 2: Link Your Chart to the Named Range

1. Right-click your chart and select Select Data.
2. Under Legend Entries (Series), click a series and then click Edit.
3. In the "Series values" field, delete the cell range and type an equals sign followed by your Named Range. For example: =Sheet1!MonthlySalesData (You must include the sheet name!)
4. If you also have named ranges for your axis labels or series name, you can update them here as well. Click OK.

Step 3: Update the Data Source Without Touching the Chart

Now, next month when you have a new set of data, you don’t even have to click on the chart. You simply redefine where "MonthlySalesData" points.

1. Go back to the Formulas tab and click Name Manager.
2. Find your Named Range ("MonthlySalesData") in the list and select it.
3. In the "Refers to:" box at the bottom, change the cell reference to your new month’s data range.
4. Click the checkmark to save, and then close the window. Your chart will magically update itself.

This technique is hugely valuable for creating templated reports that can be easily refreshed with minimal manual work.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Pro Tip: Format Your Data as an Excel Table

Perhaps the most robust method for creating dynamic charts is to base them on an Excel Table.

1. Select your data range.
2. Go to the Home tab and click Format as Table, or simply press Ctrl + T.
3. Confirm your range in the pop-up and make sure the "My table has headers" box is checked if it does.

When you create a chart from a Table, Excel no longer sees the data source as a static range like =$A$1:$B$5. It sees it as a dynamic object - the Table itself. Now, whenever you add a new row or column directly at the end of the Table, the Table automatically expands, and so does the chart's data source. No additional steps are needed. This is the ultimate method for recurring reports where you are consistently adding new data.

Final Thoughts

Updating an Excel chart’s data source doesn’t have to mean starting from scratch. Whether you're making a quick adjustment with a drag-and-drop, using the built-in 'Select Data' dialog for better control, or setting up dynamic Named Ranges for recurring reports, you have plenty of options to keep your visuals fresh and relevant.

This cycle of downloading data, cleaning it up in spreadsheets, and manually updating charts is something businesses do every single day. We built Graphed to break this time-consuming pattern. Instead of fighting with cell ranges after every export, you can connect your business apps (like Shopify, Google Ads, or Salesforce) just once. From there, your dashboards and reports stay live and update in real-time, completely hands-free. You can even build an entire report just by describing what you want to see, freeing you up to act on insights instead of just finding them.