How to Create a Dynamic Chart Range in Excel

Do you have a chart that you want to easily modify the range on, without needing to manually select the data again? Thanks to a new Excel feature, there are now multiple ways you can do that.

1. Creating the chart range as a table

One way you can set up a dynamic chart range in Excel is to put your data into a table. That way, Excel can easily see where you data starts and ends. Suppose you have the following data:

Table showing sales by store.

You could show this on a chart but if you needed to add or remove rows from it, your chart wouldn’t automatically re-size. If you deleted data, then there would be gaps on your chart. And if you just added a row, Excel wouldn’t add it to your chart unless you re-selected your range.

To fix this, you can convert you data into a table. To do this, go to the Insert tab on the ribbon and select Table. You may see some default formatting applied afterwards:

Data that has been converted into a table.

Tables will automatically expand as you add or remove data, and formulas will also copy down by default to any new rows. Currently, this is what my chart looks like for this table:

Chart showing sales by store.

In the below example, you’ll see data being added and removed from the table, and the change to the corresponding chart.

2. Using an array

If you don’t want to convert you data into a table, Excel has now made it possible to dynamically update your chart using just an array, which is new functionality. From the earlier example, I can create an array that populates the data using the following formula:


The first argument reflects the starting point of the data. The next two are left as zero since I don’t want to actually offset the range. The last two arguments indicate the size of the array, and this is key to making the chart automatically update.

By using the COUNTA function, the formula will automatically adjust based on the number of items in that column. That way, if you add or remove items, the offset function will adjust your range. The last argument (2) indicates that the data set is to two columns wide. Now by updating the source data, both my array will update and so too will the chart:

Arrays are not new to Excel but the ability for them to dynamically update a chart is a new feature. As of now, this feature hasn’t fully rolled out to the public and is only available through the Office Insiders program. To get access to that, you can sign up to be an Insider (free of charge) and then moving forward, you will have Excel’s latest and greatest features as soon as they become available.

If you liked this post on How to Create a Dynamic Chart Range in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

Add a Comment

You must be logged in to post a comment