Forum Discussion

Copper Contributor

Jul 02, 2021

Format to return a blank cell

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 i...

excel

Riny_van_Eekelen

Platinum Contributor

Jul 02, 2021

garry 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;

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.

garry

Copper Contributor

Jul 03, 2021

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

SergeiBaklan
Diamond Contributor
Jul 03, 2021
garry

In general you may simplify format to

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