Return to Charts Home

How to Create a Dynamic Chart Range in Excel

This tutorial will demonstrate how to create a dynamic chart range in all versions of Excel: 2007, 2010, 2013, 2016, and 2019.

Dynamic Chart Range – Free Template Download

Download our free Dynamic Chart Range Template for Excel.

Download Now

By default, when you expand or contract a data set used for plotting a chart in Excel, the underlying source data also has to be manually adjusted.

However, by creating dynamic chart ranges, you can avoid this hassle.

Dynamic chart ranges allow you to automatically update the source data every time you add or remove values from the data range, saving a great deal of time and effort.

How to create a dynamic chart range in Excel

In this tutorial, you will learn everything you need to know to unleash the power of Dynamic Chart Ranges.

Dynamic Chart Ranges – Intro

Consider the following sample data set analyzing profit margin fluctuations:

Dynamic chart range original data

Basically, there are two ways to set up a dynamic chart range:

  1. Converting the data range into a table
  2. Using dynamic named ranges as the chart’s source data.

Both methods have their pros and cons, so we will talk about each of them in greater detail to help you determine which will work best for you.

Without further ado, let’s get started.

Download our free Dynamic Chart Range Template for Excel.

Download Now

The Table Method

Let me start off by showing you the fastest and easiest way to accomplish the task at hand. So, here’s the drill: Turn the data range into a table, and you’re golden—easier than shelling peas.

That way, everything you type into the cells at the end of that table will automatically be included in the chart’s source data.

Here is how you can make that happen in just two simple steps.

Step #1: Convert the data range into a table.

Right out of the gate, transform the cell range containing your chart data into a table.

  1. Highlight the entire data range (A1:B6).
  2. Click the Insert tab.
  3. Hit the “Table” button.

Convert the data range into a table

In the Create Table dialog box, do the following:

  1. Double-check that the highlighted cell range matches the entire data table.
  2. If your table contains no header row, uncheck the “My table has headers” box.
  3. Click “OK.

Create Table dialog box

As a result, you should end up with this table:

Chart data turned into a table

Step #2: Create a chart based on the table.

The foundation has been laid, which means you can now set up a chart using the table.

  1. Highlight the entire table (A1:B6).
  2. Navigate to the Insert tab.
  3. Create any 2-D chart. For illustration purposes, let’s set up a simple column chart (Insert Column or Bar Chart > Clustered Column).

Create a chart based on the table

That’s it! To test the technique, try adding new data points at the bottom of the table to see them automatically charted on the plot. How much simpler can it get?

Add new data points to the table

NOTE: With this approach, the data set should never contain empty cells in it—that will ruin the chart.

The Dynamic Named Range Method

Though easy to apply, the previously demonstrated, Table Method has some serious cons. For instance, the chart gets messed up whenever the fresh data set ends up being smaller than the initial data table—plus, sometimes you simply don’t want the data range to be converted into a table.

Opting for named ranges may take slightly more time and effort on your part, but the technique negates the cons of the table method and, on top of that, makes the dynamic range a lot more comfortable to work with in the long haul.

Step #1: Create the dynamic named ranges.

To start with, set up the named ranges that will eventually be used as the source data for your future chart.

  1. Go to the Formulas tab.
  2. Click “Name Manager.
  3. In the Name Manager dialog box that appears, select “New.

Name Manager dialog box

In the New Name dialog box, create a brand new named range:

  1. Type “Quarter” next to the “Name” field. For your convenience, make the name of the dynamic range match the corresponding header row cell of column A (A1).
  2. In the “Scope” field, select the current worksheet. In our case, that’s Sheet1.
  3. Enter the following formula into the “Refers to” field: =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

Create a dynamic named range

In plain English, every time you change any cell in the worksheet, the OFFSET function returns only the actual values in column A, leaving out the header row cell (A1), while the COUNTA function recalculates the number of the values in the column each time the worksheet is updated—effectively doing all the dirty work for you.

Let’s break down the formula in greater detail to help you understand how it works:

The OFFSET formula

NOTE: The name of a named range must start with a letter or underscore and should contain no spaces.

By the same token, set up another named range based on column Profit Margin (column B) with the help of this formula and label it “Profit_Margin”:

Set up a named range based on column Profit Margin

Repeat the same process if your data table contains multiple columns with actual values. In our case, as a result, you should have two named ranges ready for action:

Named ranges overview

Step #2: Create an empty chart.

We’ve made it through the trickiest part. Now, it’s time to set up an empty chart so that you can manually insert the dynamic named ranges into it.

  1. Select any empty cell in the current worksheet (Sheet1).
  2. Go back over to the Insert tab.
  3. Set up any 2-D chart you want. For our example, we will create a column chart (Insert Column or Bar Chart > Clustered Column).

Set up an empty chart

Step #3: Add the named range/ranges containing the actual values.

First, insert the named range (Profit_Margin) linked to the actual values (column B) into the chart.

Right-click on the empty chart and choose “Select Data” from the contextual menu.

Choose "Select Data" from the contextual menu

In the Select Data Source dialog window, click “Add.

Hit the "Add" button

In the Edit Series box, create a new data series:

  1. Under “Series name,” highlight the corresponding header row cell (B1).
  2. Under “Series values,” specify the named range to be plotted on the chart by typing the following: “=Sheet1!Profit_Margin.” The reference is made up of two parts: the names of the current worksheet (=Sheet1) and the respective dynamic named range (Profit_Margin). The exclamation mark is used to bind the two variables together.
  3. Select “OK.

Add a new data series to the chart

Once there, Excel will automatically chart the values:

The named range containing the actual values plotted on the chart

Step #4: Insert the named range with the axis labels.

Finally, replace the default category axis labels with the named range comprised of column A (Quarter).

In the Select Data Source dialog box, under “Horizontal (Category) Axis Labels,” select the “Edit” button.

Click the "Edit" button

Then, insert the named range into the chart by entering the following reference under “Axis label range:

Insert the named range with the axis labels

Finally, the column chart based on the dynamic chart range is ready:

Sample column chart

Check this out: The chart gets updated automatically whenever you add or remove data in the dynamic range.

Testing the dynamic chart ranges

Download Dynamic Chart Range Template

Download our free Dynamic Chart Range Template for Excel.

Download Now