Hiding empty cells in Excel chart

Question

I would like to set up a graphic that is fixed to a cell range. The values there change depending on the data input. If a cell is empty, the axis labelling is still displayed in the graphic. How can I prevent this and still not adjust my data range manually every time?

I have tried the method to indicate an error if the field is empty. The axis values are still displayed.

Answer 1

I'm going to assume your data is starts at A2 into column B for the values. What you want to do is create named ranges in the Name Manager, which can be found in the Formulas tab of the ribbon, by hitting Define Name. I'm also going to assume the max length of your table is 100. If you need more or less adjust accordingly. Both data points will need to be filled in to appear in the chart.

Assuming your labels are in Col A, =$A$2:Index($A$2:$A$100,count($A$2:$A$100)) then name this as _Labels

Then for the values (Col B), put =$B$2:Index($B$2:$B$100,count($B$2:$B$100)) then name this as _Values

Then under "Select Data" of your chart, in the legend series add the values like so, replacing the sheet name with what is relevant for you.

Then, on the horizontal category, hit "Edit", and add the _Labels.

You'll notice I have Book1, that's just because I made it a workbook wide name, but you can make these sheet specific and if you do just make sure they're labelled accordingly. This will hide blanks from your bar graph.