In the chart shown, data is plotted in one series. Values come from a named range called “values”, defined with the formula provided below: Axis labels come come from a named range called “groups”, defined with this formula: 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:
  3. Define a name for “values” with the same process, using this formula:
  4. Edit the data series with the Select data command. For series values, use the defined name “values” with the sheet name prepended:

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

  1. Click OK twice to save changes and exist the Select Data dialog.

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.

Column chart example Dynamic chart ignore empty values - 51Column chart example Dynamic chart ignore empty values - 12Column chart example Dynamic chart ignore empty values - 50