SOLVED

force gap with Hidden and Empty Cells

%3CLINGO-SUB%20id%3D%22lingo-sub-1558747%22%20slang%3D%22en-US%22%3Eforce%20gap%20with%20Hidden%20and%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1558747%22%20slang%3D%22en-US%22%3E%3CP%3EExcel%20365%20MSO%3CBR%20%2F%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20some%20series%20of%20values%20covering%20several%20years%20and%20I%20would%20need%20to%20make%20a%20visual%20separation%20between%20each%20year.%3C%2FP%3E%3CP%3EI%20thus%20added%20a%20blank%20line%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22takemi_5-1596208510956.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209340i9DDAA692DD3D7081%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22takemi_5-1596208510956.png%22%20alt%3D%22takemi_5-1596208510956.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(Note%20I%20tried%20blank%20line%20and%20adding%20values%20%23N%2FA%20with%20same%20result).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20several%20charts%20with%20same%20option%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22takemi_1-1596208271066.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209329i40B2C17E00EE4F1C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22takemi_1-1596208271066.png%22%20alt%3D%22takemi_1-1596208271066.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E(Note%20I%20also%20tried%20checking%20%22Show%20%23N%2FA%20as%20an%20empty%20cell%22%20with%20same%20result)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20one%20of%20my%20charts%20it%20works%20a%20expected%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22takemi_6-1596208574679.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209343i8F38DE11DE53B108%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22takemi_6-1596208574679.png%22%20alt%3D%22takemi_6-1596208574679.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20not%20for%20other%20ones%20like%20%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22takemi_3-1596208371240.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209335iD2A2FF62F0FF2711%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22takemi_3-1596208371240.png%22%20alt%3D%22takemi_3-1596208371240.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22takemi_4-1596208390134.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209336i8052EB35215F6C6C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22takemi_4-1596208390134.png%22%20alt%3D%22takemi_4-1596208390134.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20it%20linked%20to%20my%20values%20or%20to%20the%20type%20of%20chart%20%3F%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20minimal%20sample.%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20kind%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1558747%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1559572%22%20slang%3D%22en-US%22%3ERe%3A%20force%20gap%20with%20Hidden%20and%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559572%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746083%22%20target%3D%22_blank%22%3E%40takemi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20format%20X-axis%20as%20the%20text%20(not%20dates)%20or%20change%20format%20of%20linear%20series%20on%20Scatter%20one%20setting%20gaps%20for%20empty%20cells.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20433px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209490i4051A4C7C0575916%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560070%22%20slang%3D%22en-US%22%3ERe%3A%20force%20gap%20with%20Hidden%20and%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560070%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20a%20lot%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EChanging%20x-axis%20format%20from%20date%20to%20text%20did%20the%20trick%20for%20all%20charts%20type%20I%20could%20test.%3C%2FP%3E%3CP%3EThanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561673%22%20slang%3D%22en-US%22%3ERe%3A%20force%20gap%20with%20Hidden%20and%20Empty%20Cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746083%22%20target%3D%22_blank%22%3E%40takemi%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome.%20Please%20note%2C%20with%20text%20gaps%20will%20be%20in%20X-axis%20as%20well%2C%20it%20won't%20be%20continuous%20dates%20axis.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Excel 365 MSO
Hello,

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 :

takemi_5-1596208510956.png

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

 

I have several charts with same option :

takemi_1-1596208271066.png

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

 

For one of my charts it works a expected :

takemi_6-1596208574679.png

 

But not for other ones like :

takemi_3-1596208371240.png

takemi_4-1596208390134.png

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
Highlighted
Best Response confirmed by takemi (New Contributor)
Solution

@takemi 

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

image.png

Highlighted

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.

Highlighted

@takemi 

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