Forum Discussion

garry's avatar
garry
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 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

 

excel

3 Replies

  • Riny_van_Eekelen's avatar
    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's avatar
      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

Resources