Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Conditional Formatting in Numbers not applying the rules

I am trying to set up pretty simple conditional formatting on a spreadsheet, to show numbers above 140 with a red fill and numbers below 110 with a blue fill. I have managed this successfully on previous spreadsheets so (sort of) know what I'm doing, but numbers is just not 'playing ball' this time.

As per historical posts on here, I have tried:

Saving and quitting numbers, then rebooting

Opening new document, copying & pasting spreadsheet

Referencing a cell (with the number 140) in the rule

Typing the text '140' in the rule

Nothing is working?!

Interestingly, I've just tried again to apply the 'less than 120' rule with a blue fill....and it has filled an EMPTY cell???

Can anyone help please?

I'm on MacBook Pro, macOS Catalina and Numbers v 11.1 - I can't update because I don't have room on the system for Monterrey 😣

Image shows first column where a different rule is being applied. The right hand column is the latest 'attempt' showing the filled empty cell?!

MacBook Pro 13″, macOS 10.15

Posted on Jan 14, 2023 12:15 PM

Reply

Similar questions

6 replies
Sort By: 

Jan 14, 2023 3:04 PM in response to Mitramonday

Hi Mitramonday,

could you please post a screenshot of your Conditional Highlighting, this would help to understand your problem.


Please check if these values are really numbers or if they are text, you can do this by changing the decimals in your format settings for these cells.


Here Conditional Highlighting that should work


Hope this will solve your question, please let me know if this worked for you or if something in unclear.


Regards Ralf


Reply

Jan 14, 2023 4:54 PM in response to Mitramonday

A blank cell is the same as zero when compared to or used as a number. A blank cell will be less than 140. You will have to catch that condition with a rule before the "blue fill" rule is applied.


For the non-blank cells in that column, assuming your conditional format was created correctly it appears from the results you have those "numbers" formatted as text. As text, they are no longer numbers so no numeric comparison can be made. They are neither less than 110 nor greater than 110, it is like asking if ABC is less than or greater than 110.





Reply

Jan 16, 2023 5:21 AM in response to Mitramonday

It is a common mistake and easily made. Nothing looks obviously wrong but things don't work right. The usual visual clue is to see which side of the cell the data naturally aligns. Text aligns to the left, actual numbers to the right. However, if you have manually set the text justification to one side or the other (or centered), that visual clue goes away.

Reply

Jan 16, 2023 6:15 PM in response to Mitramonday

Have you checked the data format of the cells in the column showing the blue fill.


If the cell content is formatted as text, the rules will likely not recognize the text '111' as being a number. Given an empty cell, though, it apppears the rules recognize that as zero, which is less than 120.


Try selecting the column, then changing the data format setting to "Number". If that works with the number values less than 120, including the empty cell, you could add a new first rule that sets the cell to no fill if it is blank.


Regards,

Barry


:^) looks like I should have read to the end of the thread before replying.

      Happy to see your problem is solved. And don't worry about the 'Doh!' moment; we've all had many of those. ;^)

B.

Reply

Conditional Formatting in Numbers not applying the rules

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.