Summary

To make a dynamic chart that automatically skips empty values, you can use dynamic named ranges created with formulas. When a new value is added, the chart automatically expands to include the value. If a value is deleted, the chart automatically removes the label.

In the chart shown, data is plotted in one series. Values come from a named range called "values", defined with the formula provided below:

=$C$4:INDEX($C$4:$C$30,COUNT($C$4:$C$30)) // values

Axis labels come from a named range called "groups", defined with this formula:

=$B$4:INDEX($B$4:$B$30,COUNT($C$4:$C$30)) // groups

This page explains dynamic named ranges created with INDEX in more detail. 

How to make this chart

1. Create a normal chart, based on the values shown in the table. If you include all rows, Excel will plot empty values as well.

2. Using the name manager (control + F3) define the name "groups". In the "refers to" box, use a formula like this:

=$B$4:INDEX($B$4:$B$30,COUNT($C$4:$C$30))

3. Define a name for "values" with the same process, using this formula:

=$C$4:INDEX($C$4:$C$30,COUNT($C$4:$C$30))

4. Edit the data series with the Select data command. For series values, use the defined name "values" with the sheet name prepended:

=Sheet1!values

dynamic chart values with dynamic named range

For category labels, use the defined name "groups" with the sheet name prepended:

=Sheet1!groups

dynamic chart axis labels with dynamic named range

5. Click OK twice to save changes and exit the Select Data dialog.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.