force gap with Hidden and Empty Cells

New Contributor

Excel 365 MSO

I have some series of values covering several years and I would need to make a visual separation between each year.

I thus added a blank line :


(Note I tried blank line and adding values #N/A with same result).


I have several charts with same option :


(Note I also tried checking "Show #N/A as an empty cell" with same result)


For one of my charts it works a expected :



But not for other ones like :



Is it linked to my values or to the type of chart ?

I have attached a minimal sample.

Thank you for your kind help.




3 Replies
Best Response confirmed by takemi (New Contributor)


You may format X-axis as the text (not dates) or change format of linear series on Scatter one setting gaps for empty cells.


Thanks a lot @Sergei Baklan 

Changing x-axis format from date to text did the trick for all charts type I could test.

Thanks again.


You are welcome. Please note, with text gaps will be in X-axis as well, it won't be continuous dates axis.