Select your rows, open Conditional Formatting, choose New Rule, Use a formula, and enter something like =$C2<0 for negative amounts or =$D2="Dr" for a Dr flag. Pick a font or fill colour, and the rule colours the whole row. Turn the data into a Table and new rows pick it up on their own.
How do you colour by sign?
- Select the cells you want coloured, for example A2:F500. Start from the top left of the data, not the header.
- On the Home tab, click Conditional Formatting, then New Rule.
- Choose Use a formula to determine which cells to format.
- Type =$C2<0 and press Format to choose a colour. Credits get their own rule, =$C2>0.
- Click OK twice.
The dollar sign before C locks the column, while 2 stays relative. That means every cell in a row looks at column C of that row, so the whole row changes colour together. If you wrote =C2<0 without the dollar, each column would test a different cell and the colouring would look random.
How do you colour by the Dr/Cr flag instead?
- Debit rule: =$D2="Dr". Excel ignores capitals in this comparison, so DR works too.
- Credit rule: =$D2="Cr".
- If stray spaces are possible, use =TRIM($D2)="Dr" and =TRIM($D2)="Cr".
| Goal | Applies to | Formula |
|---|---|---|
| Money out in a signed column | $A$2:$F$500 | =$C2<0 |
| Money in in a signed column | $A$2:$F$500 | =$C2>0 |
| Debit flag | $A$2:$F$500 | =$D2="Dr" |
| Large debits only | $A$2:$F$500 | =AND($D2="Dr",$C2>=10000) |
Should debits always be red?
Not necessarily. Red says danger, and a statement is mostly ordinary spending. If every debit is red, the sheet looks alarming, and a refund or a reversal, which is good news, may appear in the same colour family. Try a calm approach: keep debits in the default dark text, colour credits a soft green, and reserve red for something specific, such as large debits or a negative balance.
Also do not rely on colour alone. Some people cannot tell red from green, and printed copies lose the colour. Use the number format as a backup: a custom format such as #,##0.00;-#,##0.00 shows a minus on debits, so meaning survives without colour.
How do you make the rule work for new rows?
There are two ways. The better one is to turn the range into a Table: click inside the data and press Ctrl+T. When you type or paste a new row directly below, the Table expands and the conditional format and formulas extend with it. The second way is to open Conditional Formatting, Manage Rules and change Applies to for each rule from $A$2:$F$500 to a larger range such as $A$2:$F$5000. Blank rows will not be coloured, so the extra length does no harm.
Does Google Sheets work the same way?
Almost. Select the range, choose Format, Conditional formatting, set Format cells if to Custom formula is, and enter the same formula, =$C2<0. The dollar sign works the same way and the rule is applied to the range you give.
A worked example
Say rows hold rent -18,000, groceries -2,350, salary 52,000 and refund 1,299 in column C. With the sign rules, the first two rows are in the debit colour and the last two in the credit colour. If the refund is typed as 1,299 Cr in a text cell instead, neither rule fires, and the row stays uncoloured. That is a signal to convert the amount column.
Before you finish
- Apply rules to the full data block, with a locked column reference.
- Choose colours that do not shout.
- Convert amounts to numbers first.
- Turn the range into a Table so new rows inherit the rule.
If you want a clearer picture without formatting, SpendixAI has a dashboard and insights built from your imported statements.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


