Spreadsheets

Conditional formatting with a custom formula in Google Sheets

Three custom-formula rules for an expenses sheet in Google Sheets: highlight a Dining row, an amount above a limit cell, and today's date, step by step.

Editorial illustration for Conditional formatting with a custom formula in Google Sheets

In Google Sheets, open Format, then Conditional formatting, choose Add rule, set Format cells if to Custom formula is, and type a formula that starts with an equals sign and returns TRUE for rows you want coloured. For example, =$C2="Dining" colours every row whose category in column C says Dining. The formula is written for the first row of the range and shifts down automatically.

How do you set up the rule?

  1. Select the whole table range, for instance A2:E500, with the active cell at A2.
  2. Open Format, then Conditional formatting. A panel opens on the right.
  3. Under Format rules, set Format cells if to Custom formula is.
  4. Type the formula, pick a fill colour or text colour, and press Done.

Say your sheet has Date in A, Merchant in B, Category in C, Amount in D, and a limit you type in cell H1. The next three rules all use those columns.

Which formulas cover the common cases?

GoalCustom formula
Row where category is Dining=$C2="Dining"
Row where amount is above the limit in H1=$D2>$H$1
Row dated today=$A2=TODAY()
Dining and above the limit=AND($C2="Dining",$D2>$H$1)
Merchant mentions a word=ISNUMBER(SEARCH("cafe",$B2))

Text comparison in Sheets is not case sensitive, so "dining" and "Dining" both match. The H1 limit has dollar signs on both parts so it never moves; the column letters have a dollar sign so the test stays in that column while the row shifts.

One catch with the today rule: if your date cells also store a time, such as an imported timestamp, the cell is never exactly equal to TODAY(), which is midnight. Use =INT($A2)=TODAY() instead, since INT strips the time and keeps only the date. If the colour still refuses to appear, check that the column holds real dates, which sit at the right of the cell, and not text.

Why is the formula applied to the first row only?

The formula is always written as if for the top-left cell of the range, row 2 here. Sheets then adjusts the row number for each row below. If you write =$C500="Dining" while the range starts at row 2, every row is judged by a different row and the colours look random. A good habit is to check the first row of the range in the panel before typing.

Can one rule look at another sheet?

Not directly. A custom formula cannot point at another tab by name. Wrap the reference in INDIRECT, for example =$D2>INDIRECT("Limits!B1"), which reads cell B1 on a tab called Limits. Keep the tab name exactly as it appears, with no extra spaces, and consider simply copying the limit into H1 with =Limits!B1 and pointing the rule at H1, which is easier to audit.

What if two rules both match?

Rules are listed in order in the panel. When two rules both return TRUE for a cell, the one higher in the list wins for conflicting formatting, so drag your most important rule to the top. Use different attributes, such as fill for the limit rule and bold text for Dining, if you want both visible at once.

Quick checklist

  • Range starts at the same row the formula is written for.
  • Dollar sign before the column letter, none before the row number.
  • Amounts are numbers, not text; Dates are real dates.
  • One rule per idea, with the most important at the top.

Colouring helps you scan, but categorising hundreds of rows by hand is slower than reading them. SpendixAI's rules and history can assign categories to imported statement rows, so a sheet like this is best kept for the views you build yourself.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports