To make a dynamic chart that automatically skips empty values, you could usedynamic named rangescreated 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.
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.
Using the name manager (control + F3) define the name “groups”.
In the “refers to” box, use a formula like this:
3.
Define a name for “values” with the same process, using this formula:
4.
Edit the data series with the Select data command.
Click OK twice to save changes and exit the Select Data dialog.
