You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

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

Numbers: conditional formatting multiple values for one style

I'm having a minor problem with Number's Conditional Formatting, that I am hoping can be solved. I have 20 values I'm trying to highlight in one of 5 styles. Everything was working great, except I have to enter each value separately. I can't figure out how to add multiple entries in the values field (see below). For example, to highlight all cells of value "3" and "8", I'd like them to be highlighted in red; however, the way I'm doing is with two rules, one for each, when it seems like it should be only one rule with two values.


It was annoying at first, but manageable, until I hit the rules limit at 15. It seems like there should be a way to add multiple values for the same condition, but I haven't been able to figure it out. I am hoping someone here might be able to enlighten me.


Many thanks!


MacBook Air 13″, macOS 10.15

Posted on Jan 27, 2021 8:13 PM

Reply
Question marked as Top-ranking reply

Posted on Jan 28, 2021 12:47 AM

Hi hoopsmiller,


Insert some extra columns into the table where you can perform the IFs. Then you will end up with fewer rules for Conditional Highlighting. Here is an example of combining two or more IFs with the OR function.



Formula in B2 IF(OR($A2=3,$A2=8),$A2,"")



Formula in C2 IF(OR($A2=1,$A2=2),$A2,"")


Repeat for other IF OR comparisons.


Conditional Highlighting in Column A:



3 or 8, 1 or 2 reduced to two Conditional Highlighting rules.


Regards,

Ian.

5 replies
Question marked as Top-ranking reply

Jan 28, 2021 12:47 AM in response to hoopsmiller

Hi hoopsmiller,


Insert some extra columns into the table where you can perform the IFs. Then you will end up with fewer rules for Conditional Highlighting. Here is an example of combining two or more IFs with the OR function.



Formula in B2 IF(OR($A2=3,$A2=8),$A2,"")



Formula in C2 IF(OR($A2=1,$A2=2),$A2,"")


Repeat for other IF OR comparisons.


Conditional Highlighting in Column A:



3 or 8, 1 or 2 reduced to two Conditional Highlighting rules.


Regards,

Ian.

Jan 28, 2021 12:11 AM in response to hoopsmiller

"It seems like there should be a way to add multiple values for the same condition, "


It may 'seem like' there should, but it 'is that' there's not.


Conditional highlighting rules compare the value in the cell to be highlighted with another value. The other value may be written into the rule, or may be contained in another cell.


With 20 possible number values and five styles, you'll need a formula that can place one of five different values in the 'partner' cell that will provide the value to be compared with the value in the cell to be highlighted. Each comparison must make (at least) one if the comparisons return True, but must not be evaluated before another comparison which would also return true, but for a narrower range of values.


CH rules for number values offer a limited range of choices:

With a list of the 20 values, sorted into sets of numbers that are to be highlighted with the same style, it might be possible to compose a working set of rules and supporting formulas. Without that, it's a game of darts in the dark.


Regards,

Barry




Jan 30, 2021 3:19 PM in response to Yellowbox

Nicely done, Ian!


I played with this a bit to extend it to five columns to match hoopsmiller's description of '20 values and five styles', and tried a different means of getting the numbers of each style into the proper column.


Row 1 contains a list of the numbers in each set, separated by commas and with leading zeroes for the single digit numbers to make them all the same length. The cells are formatted as Text (before entering the values) to ensure retention of the leading zeroes.


The formula, set in B2, then filled down to row 21 and right to column E, is shown below:


B2: IF(ISERROR(FIND(RIGHT(0&$A2,2),B$1)),"",$A2)


Right(0&$A2,2) joins a 0 and the numeral(s) in A2 into a single text string, then takes the two rightmost characters of that string. (needed to make a consistent 2 character string to FIND)


FIND then attempts to locate the 2 character string in the longer string in row 1 of column B.

IF it fails to find a matching string, FIND throws an error, and ISERROR returns TRUE; if it does find the two character string, Find returns a number, which is not an error, so ISERROR returns False


IF gets the TRUE value from ISERROR and inserts a null string in the cell, or gets the FALSE value from ISERROR and puts the number from A2 in the cell.


The Conditional Highlighting rules for cells in column A are the same ones as used in Ian's solution, with three more added to

apply different fills for a match with the values in the aded columns.


Interesting exercise. Thanks, hoopsmiller, for the question, and Good on'ya, Mate, to Ian for the solution!


Regards,

Barry

Numbers: conditional formatting multiple values for one style

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