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
For category labels, use the defined name "groups" with the sheet name prepended:
=Sheet1!groups
5. Click OK twice to save changes and exit the Select Data dialog.
