Jump to content


Photo

Leaving blank cells empty when some cells have 0.00 as a value

ISBLANK conditional formatting rules manager

  • Please log in to reply
2 replies to this topic

#1 gmedley

gmedley

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 13 April 2016 - 03:05 PM

I am working on a report where 0.00-4.25 is yellow, 4.26-4.49 is white, and 4.50 and greater is green.  Where there is no values, I want to leave the cells blank.  How do I do this.

 



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 843 posts

Posted 14 April 2016 - 12:13 PM

Hi gmedley,

 

Excel usually doesn't distinguish between a blank cell and one containing a zero value, but when there's a special need to tell the difference you can use the ISBLANK function.

 

ISBLANK will return TRUE if a cell is empty, but will return FALSE if it contains a zero (or any other value).

 

Knowing this, you can create a formula-based conditional format that will check whether cells are blank and set the colors accordingly.

You can see how to create a formula-based conditional format in the video lesson: Essential Skills Lesson 4-18 Create a formula driven conditional format.

 

Note that you will have to make sure that your conditional formatting rules are processed in the correct order. If the "yellow" rule is placed before the "blank" rule it will take precedence and make the blank cells yellow (because it still considers them to have a zero value).

You can see more about setting the order of conditional formatting rules in the video tutorial: Essential Skills Lesson 4-16 Manage multiple conditional formats using the Rules Manager.

 

I have also attached an example workbook showing this in practice:

 

Attached File  ISBLANKFormat.xlsx   9.61KB   72 downloads

ISBLANKFormat.png

 

I hope you're now able to create the workbook you need, but please feel free to reply if you need any more help with this.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 gmedley

gmedley

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 15 April 2016 - 03:28 PM

thanks very helpful







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users