Diagram hidden and empty cells settings not all options available

Copper Contributor



I experienced many times in Excel, when I create diagrams and selected my X and Y data, after some time I want to change the hidden and empty cells settings, and choose one of the options, gaps, zero, connect data points with lines, I realize that suddenly you can not choose all of the options anymore and sometimes just one option remains available, such as e.g. zero.


When I then create a complete new diagram from scratch, with the exact same X and Y data, and try to set the settings, I have all the options available.


This is quite annoying, because it would mean, that I have to create all my 5 diagrams from scratch again, set all the colors on the graphs, set all the formats on the axis data and so on. This would be a bigger work and I don't understand why suddenly these options disappear.


The diagram Chest is the existing one, where only Nullwerte (zeros) can be choosen:

Highlighted the X and Y dataHighlighted the X and Y dataOnly option Nullwerte (zeros)Only option Nullwerte (zeros)


When I created a new diagram from scratch (red one below) with the same X and Y data, I am suddenly able to choose the different options again:

X and Y dataX and Y dataAll options availableAll options available


I hope and expert knows why these options disappear after some time.




1 Reply

@Sheen1255 What version of Excel do you have? You should see another option below the drop-down. I don't know why it gives you fewer options in the drop-down in those 2 cases, but maybe this issue has been fixed in a recent version.  Go to Help > Check for Updates and make sure you install recent updates.


A more recent version should have the option for "Show #N/A....".