Exceljet

Quick, clean, and to the point

Dynamic chart ignore empty values

Dynamic chart example - ignore empty values

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 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 exist the Select Data dialog.

Chart type 
Author 
Dave Bruns