Conditional Highlighting based on other cell

Question

Conditional Highlighting based on other cell

There are older threads about this that have been closed due to inactivity.

I have a predictions spreadsheet. By adjusting variables and assumptions, the spreadsheet creates "best guess" monthly predictions spanning several years.

Because my clients do not like staring at a spreadsheet of just black numbers on white, I highlight the cells when the numbers cross specific financial milestones.

In Excel, I would use a formula in the Auto-formatting such as:

IF((previous cell<Milestone) and (current cell>Milestone))

In this way, the formula knows when the current cell crosses the Milestone number and highlights it.

Unfortunately, I have not figured out how to similarly automate this in Numbers. Even if writing formulas is not possible, I cannot figure out how to make one rule reference the current cell and another to reference the previous cell.

Is this possible?

Posted on Feb 22, 2025 7:22 AM

Reply

Answer 1

Badunit

Level 7

30,366 points

Feb 24, 2025 8:57 AM in response to TonyPSchaefer

I cannot think of a way to do it in Numbers without adding a column to the the table. Conditional highlighting rules in Numbers have to be about the current cell. Cell B2 cannot have rule "if B2 > 100 and B1<100..." because the "B1<100" part is all about B1, nothing to do with B2. "If B2>100 and B2>B1" is acceptable as a rule but not what you are looking for.

Reply

Answer 2

Badunit

Level 7

30,366 points

Feb 22, 2025 10:23 AM in response to TonyPSchaefer

I am assuming you know how to create a custom format, just not how to reference a cell. When you want to reference another cell, click on the green oval, select a cell in your table, and click the green checkmark to accept it.

The screenshots below are for a "greater than" rule. There is a rule for "between" which is >= the first number and <= the second. Any value between or equal to the two milestone numbers will be highlighted.

Reply

Answer 3

Badunit

Level 7

30,366 points

Feb 22, 2025 10:44 AM in response to Badunit

If you are making a highlighting rule for a column of cells and they all reference the same two "milestone" cells, you can do them all at once.

Select the column of cells to be highlighted
Start to make the rule
After you get the cell reference into the rule, make it an absolute reference. Instead of C2 make it $C$2. All the rules in the column will refer to cell C2.
Continue making the rule.

Reply

Answer 4

Feb 22, 2025 11:31 AM in response to Badunit

Thank you for taking the time to reply. Perhaps I wasn't clear.

Using the "Greater than" logic, every cell above the threshold would be highlighted.

What I'm looking for is like this in which only the first instance is highlighted:

Take the first instance. In Excel, I would auto-highlight based on a formula similar to "=(B3<100)and(B4>100)". When B3 is less than 100 (50) AND B4 is greater than 100 (105) THEN the formatting condition is TRUE and the formatting is applied.

Then I stack the formatting formulas for each threshold: 200, 300, 400, etc.

By applying this formatting to every cell in the column, changing the variables not only modifies the calculated numbers but also adjusts the automatic formatting. Without this feature, every time we change the numbers, we have to manually identify and highlight every time a threshold is exceeded.

This is what I wish to accomplish in Numbers.

Reply

Answer 5

Badunit

Level 7

30,366 points

Feb 22, 2025 12:56 PM in response to TonyPSchaefer

Yes, I misunderstood exactly what you meant. So the first cell to reach 100 gets highlighted, then the first to reach 200, and so on. And you did this in Excel with a highlighting formula like "=(B3<100)and(B4>100)". Where does the 200, 300, etc enter into that formula? I can't think of a way to do this without a column that keeps a running track of the milestone. Maybe if I understood how you did this in Excel a more elegant solution might pop into my mind.

Reply

Answer 6

Feb 23, 2025 8:01 AM in response to Badunit

I'm starting to think that this is not possible in Numbers but - ever the optimist - do not want to give up hope just yet. Below is a screenshot of the exact Excel Auto-formatting rules I would use to create the image from above. Please do not get hung up on the 100s because in reality the numbers are much larger and the span between milestones is months or years apart.

You can see that each milestone is hardcoded as a rule. Once a single rule is verified, it is duplicated and the next milestone is hardcoded into the new rule. Duplicate and repeat. Once all the milestone rules are created for one cell, the formatting is copied to every cell such that every cell has all the rules. The entire process sounds daunting, but in reality takes just a couple minutes. Even with the little effort put into it, the end result - in which the highlighting automatically shifts with the forecasts - is pretty impressive.

Another thing worth noting is that the rule formulas in the image below only reference cells B2 and B1 but this is because B2 is selected. Each cell adjusts with its own reference.

This formulaic approach might not be the way to accomplish this in Numbers. But it is the only way I have gotten it to work in Excel.

Reply

Conditional Highlighting based on other cell

Similar questions