Graphed LogoGraphed
Login

How to Change the Range of a Graph in Excel

Cody Schneider•

Nothing is more frustrating than creating the perfect chart in Excel, only to realize the data has changed. Whether you're adding new sales figures for the latest month or need to remove an outlier campaign, you don't have to delete your chart and start all over again. Modifying the data range of an existing graph is a straightforward skill that saves you time and keeps your reports current. This guide will walk you through several methods, from a quick drag-and-drop adjustment to setting up a fully automated dynamic chart.

Why You Need to Change a Graph's Range

Before jumping into the how-to, let's look at a few common scenarios where you might need to adjust your chart's data source. This isn't just a technical task, it's a fundamental part of keeping your data visualizations relevant and accurate.

  • Updating with New Data: The most common reason is also the simplest. You've been tracking weekly website traffic, and a new week's data is available. You need to extend your chart's range to include the new row.

  • Correcting Selection Errors: It's easy to accidentally select an extra row or column when first creating your chart. Changing the range allows you to tighten up the selection to show only the intended data without starting from scratch.

  • Focusing on Specific Time Periods: Your original chart might show performance for the entire year, but for your Q2 review meeting, you only want to display data for April, May, and June. You'll need to shrink the data range to focus on this specific period.

  • Removing Irrelevant Data: Maybe you included preliminary or projected figures in your initial graph. Now that the final numbers are in, you need to remove the placeholder columns to avoid confusion.

  • Switching Between Datasets: You might want to compare the performance of Facebook Ads to Google Ads. Instead of creating a new chart, you can simply edit the current one to point to a different data series.

In all these cases, the goal is the same: to make your chart accurately reflect the story you want your data to tell - a story that evolves as new information becomes available.

Method 1: The Quick Drag-and-Drop

For quick updates where your new data is right next to your old data, the drag-and-drop method is the fastest way to get the job done. It's visual, intuitive, and takes just a couple of seconds.

Follow these simple steps:

  1. Select Your Chart: Click anywhere on your chart. When you do, Excel will highlight the source data in your worksheet with colored outlines. You'll typically see a blue outline around your data values and a purple outline around your axis labels.

  2. Locate the Corner Handle: Move your cursor to the small square handle at the bottom-right corner of the colored outline. Your cursor will change into a double-sided diagonal arrow.

  3. Click and Drag to Resize: Click the handle and drag the box to expand or shrink your selection. If you're adding a new row of data and you've already entered it into spreadsheets (like the new month), drag the outline down to include that row. If you need to remove the last data point, drag it up.

That's it! As you drag the outline, the chart will update in real-time, giving you instant visual feedback.

When to use this method: This is perfect for adding or removing contiguous rows or columns of data. For example, if you track sales by month, and you just added data for March next to February's numbers, this method is ideal.

When to avoid it: It can be cumbersome for very large datasets where the data source isn't visible on the same screen as the chart. It also won't work if you need to add data that isn't directly adjacent to your original selection (non-contiguous data).

Method 2: The 'Select Data' Dialog Box for Precision

When you need more control, want to add non-contiguous data, or simply prefer working with dialog boxes over dragging, the Select Data Source tool is your best friend. This is the most reliable way to manage a chart's data range and customize individual elements.

Here’s how to use it:

  1. Open the Select Data Source Window: Right-click on your chart area and choose "Select Data..." from the menu. Alternatively, you can click the chart, go to the Chart Design tab on the Ribbon, and click the "Select Data" button.

  2. Understand the Dialog Box: This window gives you full control over every part of your chart's data. It's broken into three main sections:

    • Chart data range: This field at the top shows the entire selection of cells that make up your chart. You can edit this directly or click the icon next to it to re-select the range from your worksheet.

    • Legend Entries (Series): This box on the left lists all the individual data series plotted on your chart. For a column chart comparing traffic sources, you might have "Organic Search," "Paid Search," and "Direct" listed here. You can Add, Edit, or Remove series from this section.

    • Horizontal (Category) Axis Labels: This box on the right controls the labels along your X-axis (e.g., dates, months, campaign names). You can edit this range independently of the data series.

Example: Adding a New Month's Sales Data

Imagine your chart currently shows sales for January (cell B2) and February (B3), with the months listed in cells A2 and A3. You've just added March's data in cells A4 and B4.

  • Open the Select Data Source window.

  • In the Chart data range box, you’ll see something like =Sheet1!$A$1:$B$3.

  • Simply change it to cover the new data: =Sheet1!$A$1:$B$4.

  • Click "OK." Your chart will now include March's sales data, and the X-axis will have a new "March" label.

Example: Editing a Single Data Series

What if you only want to change where one line in your line chart gets its data? In the Legend Entries (Series) box, select the series you want to change (e.g., "Facebook Ads CPC") and click the "Edit" button. A new, smaller window will pop up:

  • Series name: This is what appears in your legend. You can either type a name directly or link it to a cell content (like a header).

  • Series values: This is where the magic happens. Here, you can define the exact range of cells this specific series should use for its data points. Delete the old range and select the new one in your worksheet.

This method provides the granular control that the drag-and-drop tool lacks, making it essential for complex charts or precise adjustments.

Method 3: Set It and Forget It with Dynamic Charts

If you're tired of manually updating your chart range every week or month, you can automate the process. A dynamic chart automatically expands to include new data as you add it. There are two primary ways to create one in Excel.

The Easy Way: Using Excel Tables

Converting your data range into an official Excel Table is the easiest way to make your charts dynamic. When a chart is based on a Table, it "smartly" recognizes when new rows or columns are added and updates itself automatically.

  1. Convert Your Data to a Table: Click any cell within your data range. Go to the Insert tab and click Table (or use the shortcut Ctrl+T). A small dialog will appear, ensure the correct data range is detected and check the box for "My table has headers." Click OK.

  2. Create Your Chart: With any cell in your new Table selected, create your chart as you normally would (Insert > Recommended Charts, or pick a specific type).

  3. Add New Data: Now, simply type or paste new data into the first empty row below your Table. Excel will automatically expand the Table to include the new row.

Instantly, your chart will update itself to reflect the new data point. No dragging, no selecting data, no manual anything. This is a game-changer for any recurring report you manage in Excel.

The Advanced Way: Named Ranges with Formulas

For ultimate flexibility, especially in complex dashboards, you can use a combination of formulas and Named Ranges. This method tells Excel to define your data range based on how many entries it finds, rather than looking at a fixed group of cells. This sounds more complex than it is - let's break it down.

We'll use two key formulas:

  • COUNTA: Counts the number of cells that are not empty. We'll use this to find out how many rows of data we have.

  • OFFSET: Returns a range of cells that is a specified number of rows and columns from an original starting point. We use this to define a range that is exactly as tall as the number delivered by our COUNTA function.

Step-by-Step for a Dynamic Named Range:

  1. Open the Name Manager: Go to the Formulas tab and click Name Manager.

  2. Create a Named Range for Your Axis Labels:

    • Click "New..."

    • For the Name, type something intuitive like ChartMonths.

    • In the Refers to: field, enter the following formula, adjusting the cell references for your sheet:=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

    • Breaking this formula down: It starts at cell A2 and creates a range that's one column wide and a number of rows tall equal to all the entries in column A (minus one, to account for the header in A1).

  3. Create a Named Range for Your Data Values:

    • Click "New..." again.

    • For the Name, type ChartSales.

    • In the Refers to: field, enter a similar formula for your data column:=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1)

    • The important thing is to hit the close button after these last two entries and then save everything.

  4. Connect Your Chart to the Named Ranges:

    • Open the Select Data Source window for your chart (right-click chart, "Select Data...").

    • Edit your data series. In the Series values field, replace the static range (like =Sheet1!$B$2:$B$10) with your new named range. You must include the sheet name: =Sheet1!ChartSales.

    • Next, edit your Horizontal (Category) Axis Labels. In the Axis label range box, replace the static range with your other named range: =Sheet1!ChartMonths.

Click the OK or the Save button!

Now, whenever you add a new row of data to the bottom of columns A and B, COUNTA will detect it, OFFSET will resize the named range, and your chart will automatically update. It takes a few minutes to set up, but it will save you countless hours in the long run.

Final Thoughts

Updating a graph's data range in Excel doesn't have to be a chore. From the simple drag-and-drop to a fully automated chart built on Excel Tables or dynamic formulas, you can easily adapt your visualizations to new data. Knowing these methods allows you to keep your charts current, accurate, and ready for any presentation without the tedious task of rebuilding them.

Still, all these Excel clicks and formulas are workarounds for a bigger challenge: data rarely lives in one place, and keeping reports up to date is a constant, manual chore. We built Graphed to solve this very problem. Instead of exporting CSVs and wrestling with named ranges, we connect directly to your live data from sources like Shopify, Google Analytics, Salesforce, and Facebook Ads. When you want to build a dashboard, you simply ask in plain English, and your report is created with real-time data that updates automatically.