Applying complex conditional formatting in yearly budget to highlight overspending automatically

Hi everyone - i’m looking to modify the formula inside the yearly budget sheet, to automatically colour code cells based on overspend (actual compated to budget). Ideally, if it was 10-20% light red, 20-40% medium red, 40+% bright red…

It’s way beyond me - just wondering if there’s someone who knows how this would work?

Outcome is to be able to quickly view where I’ve been bad over the month… and how bad. :slight_smile:



:wave: @deanowilliams47

This is a great idea, I just also have no clue how to pull it off.

It might be easier to create a separate sheet in your template to calculate the % and then the conditional formatting would be a lot easier. But building out that sheet is probably a little complicated.

Wondering if @jpfieber @cculber2 @Brad.warren @yossiea or @jono have any ideas? :thinking:

This sounds conceptually similar to some formatting using custom formulas that @jpfieber introduced for his Transactions sheet here. I ended up implementing the same formatting for my sheet. The formulas I used are below.

  • Cell range: $A2:$S (all columns starting with row 2)
  • Transfer: =vlookup($D2,indirect(“Categories!$A$2:$C”),3,0)=“Transfer”
  • Positive transaction: =$E2>0
  • Negative transaction: =$E2<0

I believe you could leverage custom formulas in the conditional formatting rules to accomplish what you’re looking for. Assuming you want to apply the formatting to each Actual column on the sheet, you can use Actual / Budget in the custom formula to get your overspend ratio.

I tested out the following on a sample sheet and added the bottom three rules for the overall Actual column. The formulas will compare the overspend ratio $C7/$B7 to your specified breakpoints (>=1.4, >=1.2, >1) and only apply the formatting to category rows with a type of Expense. Keep in mind that conditional formatting rules are applied in order and the first matching rule will be used.

  • Cell range: $C7:$C (Google Sheets will expand this to your actual final row)
  • 40% overspend: =AND($C7/$B7>=1.4,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)
  • 20% overspend: =AND($C7/$B7>=1.2,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)
  • Up to 20% overspend: =AND($C7/$B7>1,vlookup($A7,indirect(“Categories!$A$2:$C”),3,0)=“Expense”)

And here are the results:

I think this should give you a good starting place to add the formatting you’re looking for, but if you have any other questions I can try to answer them as best I can.