Format to return a blank cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2509700%22%20slang%3D%22en-US%22%3EFormat%20to%20return%20a%20blank%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509700%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20reading%20this%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20an%20Excel%20spreadsheet%20which%20contains%20a%20formula%20which%20draws%20its%20data%20from%20another%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIncome!O7%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20format%20that%20cell%20so%20that%20it%20returns%20a%20number%20with%20two%20decimal%20points%20(red%20if%20a%20minus%20figure)%20but%20blank%20if%20there%20is%20no%20number%20in%20the%20source%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EGarry%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2509700%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2509832%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20to%20return%20a%20blank%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2509832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1093772%22%20target%3D%22_blank%22%3E%40toastmaster%3C%2FA%3E%26nbsp%3BYou%20mention%20%22%3CSPAN%3Ebut%20blank%20if%20there%20is%20no%20number%20in%20the%20source%20spreadsheet%22.%20Would%20it%20be%20that%20there%20is%20a%20number%20or%20a%20an%20empty%20cell%3F%20Or%20could%20there%20be%20a%20text%20in%20the%20source%20cell%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20of%20the%20first%20option%2C%20you%20can%20achieve%20all%20in%20one%20go%20by%20custom%20formatting%20like%3A%26nbsp%3B0.00%3B%5BRed%5D0.00%3B%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%22Screenshot%202021-07-02%20at%2009.41.53.png%22%20style%3D%22width%3A%20295px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F293085i34B2C1521E880592%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-02%20at%2009.41.53.png%22%20alt%3D%22Screenshot%202021-07-02%20at%2009.41.53.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3EThe%20above%20picture%20is%20taken%20on%20a%20Mac%2C%20but%20it's%20similar%20for%20PC.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%2C%20on%20the%20other%20hand%2C%20the%20second%20option%20may%20occur%2C%20you%20would%20need%20a%20formula%20like%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(ISNUMBER(Income!O7)%2CIncome!O7%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20custom%20format%20the%20same%20as%20above.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2513120%22%20slang%3D%22en-US%22%3ERe%3A%20Format%20to%20return%20a%20blank%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2513120%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response%2C%20unfortunately%20neither%20option%20provided%20me%20with%20exactly%20the%20right%20result%20however%2C%20it%20did%20encourage%20me%20to%20try%20lots%20more%20formatting%20options%20and%20I%20eventually%20came%20up%20with%20a%20custom%20format%20which%20seemed%20to%20produce%20the%20required%20format%20(although%20I'm%20not%20sure%20how%20I%20got%20to%20it).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E_-*%20%23%2C%23%230.00_-%3B%5BRed%5D-*%20%23%2C%23%230.00_-%3B%22%22%3B_-%40_-%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGarry%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

Thanks for reading this post.

 

I have an Excel spreadsheet which contains a formula which draws its data from another spreadsheet.

 

=Income!O7

 

I'd like to format that cell so that it returns a number with two decimal points (red if a minus figure) but blank if there is no number in the source spreadsheet.

 

Any help would be greatly appreciated.

 

Thanks,

Garry

 

3 Replies

@toastmaster You mention "but blank if there is no number in the source spreadsheet". Would it be that there is a number or a an empty cell? Or could there be a text in the source cell?

 

In case of the first option, you can achieve all in one go by custom formatting like: 0.00;[Red]0.00;

 

Screenshot 2021-07-02 at 09.41.53.png

The above picture is taken on a Mac, but it's similar for PC.

 

If, on the other hand, the second option may occur, you would need a formula like this:

=IF(ISNUMBER(Income!O7),Income!O7,"")

and custom format the same as above.

Hi @Riny_van_Eekelen 

 

Thank you for your response, unfortunately neither option provided me with exactly the right result however, it did encourage me to try lots more formatting options and I eventually came up with a custom format which seemed to produce the required format (although I'm not sure how I got to it).

 

_-* #,##0.00_-;[Red]-* #,##0.00_-;"";_-@_-

 

Regards,

 

Garry

@toastmaster 

In general you may simplify format to

#,##0.00;[Red]-#,##0.00;;