Referencing an Unchanging Data Range for a Chart

Dave has a chart that pulls its data from a data range that starts with column B. Every month he needs to add new data before column B, so he inserts a new column and adds the data. This means that Dave then has to go into the chart and adjust the data range. He wonders if there is a way to make sure that the chart always refers to his data range (B1:F32) so he doesn't need to edit the data range each month.

At first guess, you might think that an answer to Dave's question is to define a name, such as MyData, to refer to $B$1:$F$32. However, this won't work. You can successfully set up your named range, and you can use the named range as the source for your chart data, using this notation:

=Sheet1!MyData

Excel accepts this, but immediately changes the chart data range to this:

=Sheet1!$B$1:$F$32

In other words, Excel tosses out the named range and replaces it, instead, with the actual data range to which the named range refers. When you later select column B and insert a column, Excel changes both the chart's data range and the named range to this:

=Sheet1!$C$1:$G$32

So, using a named data range won't work for Dave's purposes.

You might also think you can try an "insertion process," where you insert the new column between the current columns B and C. Then, copy the values from column B to the new, blank column C. Finally, replace the values in column B with the new values.

This doesn't really solve Dave's problem, though, because when you insert the new column C, Excel automatically changes the chart's data range to something like this:

 =Sheet1!$B$1:$B$32,Sheet1!$D$1:$G$32

In other words, it splits the chart's data range, which definitely won't give Dave what he needs.

Some people suggested using the INDIRECT function to specify the chart's data range. This approach is often used to specify non-changing ranges, so it is natural to think it might be useful for Dave's purpose. The chart data range, under this approach, could be entered like this:

=INDIRECT("B1:F32")

The problem is that Excel won't allow the INDIRECT function to be used to specify a chart's data range; you'll get an error.

There is a way you can still use the INDIRECT function, however. Let's assume that Dave's data ends at column F and that all columns to the right are empty. In cell H1 enter the same formula just mentioned:

=INDIRECT("B1:F32")

Now you have, in the range H1:L32 a "mirror" of what is in cells B1:F32. It is this mirror range that should be entered into the chart as its data range:

=Sheet1!$H$1:$L$32

When you add a new column B, it will obviously push everything to the right. In other words, your mirror range will now be at I1:M32 and the chart's data range is automatically updated to reference I1:M32. However, the data contained in I1:M32 now mirrors what is in B1:F32, just as Dave wants. It will always reference B1:F32 because of the use of the INDIRECT function to create the mirror.

The INDIRECT function works because it uses a string as a parameter. That string contains the desired range, but because it is a string, Excel doesn't update it when inserting rows or columns. That means it never changes and always references the data that Dave wants.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8152) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.