Conditional formatting to monitor MoM differences in values

Hey team! First time caller!

I’m trying to conditionally format subsequent columns based on if the value is less than the previous column. The columns are month, and the values are a sum for that month. I’ve been racking my brain and can’t figure this one out.

Simply put: If Cell Y is less than Cell X in the same row, then Cell Y should be red, and so on and so on for each cell in that row.

Any ideas?

Hey there! Unfortunately, I don’t think there’s an easy way to do this. If I understand the problem correctly, I think the solution is to set up two conditional formatting rules for each column (one to turn the cell red if the cell is less than the previous cell in the row, one to turn the cell green if the cell is more than the previous cell in the row). So the custom formulas would look something like this:

For turning the cell green:
[Column B] > [Column A]

For turning the cell red:
[Column B] < [Column A]

Then you’d need to change out the columns in the formulas used above to apply to the subsequent columns/cells, i.e., [Column C] > [Column B] and [Column C] < [Column B] would be used for the next rules.

1 Like

That was my suspicion! I’m struggling to figure out how to actually call out/differentiate columns in the formula. Would it require a function like Lookup? It seems that a lot of the functions work well for generating new values and calculating a value, but customizing formatting using a lot of these functions isn’t quite there yet. I tried to use If, for example, but that won’t work.

Regardless, thanks for your help! Learning more every day :grinning:

If you originally meant to ask about how to do this for a Pivot Table with Pivot Columns as months, then you would need to use a Lookup. For example: Lookup([Sum of Cost], DateTrunc("month", Date("2022-12-01")), [Month of Date]) > Lookup([Sum of Cost], DateTrunc("month", Date("2022-11-01")), [Month of Date])could be one of the rules that you set up for December 2022.

On the other hand, if your columns are all in the same (normal) table that you’re setting up the conditional formatting rules for, then there shouldn’t be any need for a Lookup; simply type in your column names and use a logical operator (like < or >) to compare them. E.g., if you had one column per month in a year, with only one row representing the sum of something per month, the rule for making February’s sum have a green background would look like this: [February] > [January], and the rule for making February’s sum have a red background would look like this: [February] < [January].