Two different number formats

%3CLINGO-SUB%20id%3D%22lingo-sub-1891333%22%20slang%3D%22en-US%22%3ETwo%20different%20number%20formats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1891333%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20to%20create%20a%20customized%20number%20format%20that%20both%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E-%20hides%20all%20zeros%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E-%20separates%20thousands%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20i%20tried%20entering%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CEM%3E%23%26nbsp%3B%23%230%3B%200%3B%3B%26nbsp%3B%40%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ebut%20all%20the%20negative%20amounts%20turn%20positive%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Eand%20tried%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CEM%3E%23%26nbsp%3B%23%230%3B%20%5B%3D0%5D%22%22%3BStandard%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Ebut%20all%20the%20negative%20amounts'%20thousands%20are%20not%20separated%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20please%20help%20me%20%3F%20Thanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1891333%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1891477%22%20slang%3D%22en-US%22%3ERe%3A%20Two%20different%20number%20formats%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1891477%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F870551%22%20target%3D%22_blank%22%3E%40areej98%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20you%20attach%20a%20sample%20workbook%20of%20your%20attempts%3F%20Typically%2C%20there%20are%20already%20settings%20that%20you%20can%20apply%20that%20can%20already%20do%20this%20for%20you%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Hide%20all%20zeros%3C%2FP%3E%3CP%3EIf%20you%20go%20to%20File%20%26gt%3B%20Options%20%26gt%3B%20Advanced%20%26gt%3BDisplay%20options%20for%20this%20workbook%3C%2FP%3E%3CP%3EExcel%20can%20already%20do%20this%20for%20you%20that%20applies%20to%20only%20this%20current%20working%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_0-1605545272126.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233797i82D57F42B2AC80B4%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_0-1605545272126.png%22%20alt%3D%22adversi_0-1605545272126.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E2.%20Separate%20thousands%3C%2FP%3E%3CP%3EIn%20the%20Home%20Ribbon%2C%20you%20can%20click%20on%20the%20comma%20icon%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22adversi_1-1605545336121.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233798i2852EBE8A159157B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22adversi_1-1605545336121.png%22%20alt%3D%22adversi_1-1605545336121.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi everyone  

I'm looking to create a customized number format that both 

- hides all zeros 

- separates thousands 

 

So i tried entering this: 

# ##0; 0;; @

but all the negative amounts turn positive 

 

and tried: 

# ##0; [=0]"";Standard

but all the negative amounts' thousands are not separated

 

Could you please help me ? Thanks in advance

2 Replies
Highlighted

@areej98 

Can you attach a sample workbook of your attempts? Typically, there are already settings that you can apply that can already do this for you:

 

1. Hide all zeros

If you go to File > Options > Advanced >Display options for this workbook

Excel can already do this for you that applies to only this current working file.

 

adversi_0-1605545272126.png

2. Separate thousands

In the Home Ribbon, you can click on the comma icon

 

adversi_1-1605545336121.png

 

Highlighted

@areej98 

That could be

image.png

First block is for positive numbers, second for negative and third for zero.