MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Prevent Numbers From Considering a Blank Cell To Be Equal To Zero?

I would like to use conditional highlighting to highlight cells that are equal to 0 but are not blank. It seems that Number assumes a blank cell that is formatted as a number is equal to 0. I know from your videos that you can use conditional formatting to make a cell that contains zero a blank, is there a way to do the opposite, i.e. make a cell that is blank not be zero?

Everyday I enter hours worked for various activities on a task tracking spreadsheet. The hours field is blank until I enter the hours worked for that activity for that day. I want to use conditional highlighting to distinguish between blank cells and cells that contain zero.

Comments: 2 Responses to “How Do I Prevent Numbers From Considering a Blank Cell To Be Equal To Zero?”

    1 year ago

    Add two rules. The first is "Cell is Blank." Set that to whatever style you like. The second is "Equal to" 0. Set that to another style.

    The rules are checked in order, so if the cell is blank it gets the first style, and that's it. If the cell is not blank the second condition is checked and it gets the second style if the value is 0.

    I have found if you want a 0 to be a style, but blank cells to have no style at all, you can either set the blank style to something that matches the regular style. Or, you can have one rule that is "Text is" and a "0" as the value. This will set the style for cells with a 0 value, but not affect cells that are blank at all.

    1 year ago

    Thank you so much! The "Text is" solution seems to be the most elegant for my purposes. I didn't realize that when a blank cell is formatted as a number that it can also be read as a text for conditional highlighting without impacting it's use as a number in subsequent calculations.

Comments Closed.