When conditional formatting does nothing, the cause is almost always one of five things: the formula references drift from row to row, the Applies to range is wrong, a rule above is stopping the one below, the cells hold text instead of numbers, or copy and paste has split the rule into pieces. Work through them in this order and most sheets are fixed in minutes.
Is the formula locked and written for the right cell?
In both Excel and Google Sheets the formula is written for the first cell of the range and then shifts for the others. For a row rule such as =$C2>$B2, the dollar sign belongs before the column letters and not before the row number. In Excel the first cell means the active cell when you opened the dialog, so click the top-left cell before selecting the range. In Sheets it is the first row of the range you typed.
Does the Applies to range still cover your data?
Insert a row, delete a column, or paste cells over the range and the rule often splits into fragments or stops one row short. In Excel, open Conditional Formatting, Manage Rules and set Show formatting rules for to This Worksheet. In Sheets, open Conditional formatting in the Format menu and look at the range of each rule. If you see five rules that were meant to be one, delete them and rebuild.
Are the cells really numbers or dates?
Amounts pasted from a statement are frequently text. A text 1200 is not greater than a number 1000 in the way you expect. Signs: the number sits at the left of the cell, a small green triangle appears in Excel, or a test cell such as =ISNUMBER(C2) returns FALSE. Dates behave the same way: =ISNUMBER(A2) must return TRUE before =$A2=TODAY() can work. Also watch for trailing spaces in text, since "Dining " does not equal "Dining". Wrapping the cell in TRIM, as in =TRIM($C2)="Dining", fixes that.
| Symptom | Likely cause | Quick test |
|---|---|---|
| Rule works on row 2 only | Reference not locked | Check for $ before the column letter |
| Colours are off by one row | Formula written for a different first row | Compare the row in the formula with the first row of the range |
| Rule works, then stops after inserting rows | Range fragmented | Open Manage Rules and read Applies to |
| Number rule ignores some rows | Numbers stored as text | Try =ISNUMBER(C2) on one cell |
| A lower rule never shows | Stop If True on a rule above | Look at the rule order |
Is another rule getting in the way?
Excel runs rules from the top of the list down. If a rule above has Stop If True ticked and is true, the rules below it are skipped. If two rules clash on the same format, such as fill, the higher rule wins. Move the rule you care about up, untick Stop If True on rules that do not need it, or give each rule a different format so both can show. In Sheets, the first matching rule in the list wins when formats conflict.
Did copy and paste duplicate the rules?
Copying cells carries their formatting rules along, so a sheet used for years can hold dozens of overlapping copies. Use Paste Special, Values when you only need data. To clean up, choose Clear Rules from Entire Sheet in Excel and build the few rules you want once, or delete the extras one at a time in Sheets.
Other causes worth ruling out
- In Excel, calculation set to Manual on the Formulas tab means formulas used by the rule are stale. Set it to Automatic.
- In Sheets, a custom formula cannot point at another tab directly; use INDIRECT with the tab name in quotes.
- A manually applied fill on a cell is overridden by a rule that is true, but a rule whose colour matches the background looks like it did nothing.
If the data keeps arriving in a messy state, the root problem is often how it is gathered. SpendixAI imports bank and card statements so that amounts arrive as amounts, with a review step before anything is saved, which spares you the text-versus-number hunt.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


