Conditional formatting for accountants: let the sheet point at the problems

In a long list of balances or expense claims, a credit balance or an invoice keyed in twice looks like every other number until someone reads each line. Conditional formatting turns that reading into a rule: the spreadsheet colours the cells that break it, every time the data changes. This page covers the rule types accountants use most, how to write them so they keep working, and the mistakes that make a rule highlight the wrong cells.

What conditional formatting is

Conditional formatting attaches a test to a range of cells. For each cell, the spreadsheet asks the test; if the answer is true, the cell gets a format, usually a fill colour, and if the answer is false, it keeps its normal look. Unlike a fill you paint by hand, the rule is checked again whenever a value changes. A balance that turns negative tomorrow lights up tomorrow, without anyone touching the formatting.

Like a number format, a rule only changes how a cell looks, never its value. For the look of the numbers themselves, see the Accounting number format in Excel.

Why accountants use it to spot anomalies

Review work is mostly a search for the few lines that do not fit. Four searches come up again and again:

• Credit balances. A customer balance below zero usually means a payment was posted twice or to the wrong account. Rule: cell value less than 0.
• Amounts over a limit. Expense claims above an approval limit need sign-off. Rule: cell value greater than the limit cell.
• Duplicate invoice numbers. A supplier invoice entered twice would be paid twice. Rule: duplicate values.
• Lines off budget. A budget line where the actual differs from the budget by more than a tolerance, in either direction, deserves a question. Rule: a formula.

None of these rules decides whether something is wrong. They decide what a person should look at first.

The rule types

Cell value comparisons

The simplest rule compares each cell's own value with something: greater than, less than, between, equal to, and their "or equal to" forms. The steps are the same in our practice grid and in the desktop spreadsheet:

1. Select the range to test, for example the balances in B5:B20.
2. Open Conditional formatting and add a new rule.
3. Choose "Cell value is", then the comparison, for example "less than".
4. Enter the value to compare with, for example 0.
5. Pick a fill colour and save the rule.

Refer to a limit cell instead of typing the number

When a threshold lives on the sheet, such as an approval limit in G2, the rule should point at that cell. Enter =$G$2 as the value to compare with, not the number you can see in G2. Both highlight the same cells today. When the limit changes, the rule that refers to $G$2 follows it, and the rule with the number typed in keeps testing the old one. The dollar signs lock the reference so every cell in the range compares with the same limit cell.

Duplicate values

The duplicate values rule highlights every value that appears more than once in the selected range. For an invoice register, select the invoice numbers only. Every copy is highlighted, including the first, so you can compare both entries. Select the right column: two different invoices can have the same amount, so a duplicate rule on the amounts flags innocent lines.

Formula rules

When the test is more than a simple comparison, or when you want a whole row to light up, use the rule type that takes a formula. The formula must return TRUE or FALSE, and you write it for the first cell of the range, the top-left one. The spreadsheet then copies it across the range exactly as if you had filled it, adjusting every reference that is not locked.

That is where the dollar sign earns its keep. To highlight a whole row of A5:D16 when the budget line is off target, every cell in row 5 has to test the same two cells, the budget in B5 and the actual in C5. Writing $B5 and $C5 locks the column but lets the row move, so cell D7 still looks at $B7 and $C7. The tolerance is one cell for the whole sheet, so it is locked both ways: $G$2. For more on how references shift when copied, see finding and fixing formula errors in Excel.

ABS for "either way"

A line far under budget deserves a question as much as one over it: it can mean a missing invoice or a cost posted to the wrong line. The difference $C5-$B5 is positive for an overspend and negative for an underspend. Wrapping it in ABS drops the sign, so one test catches both: =ABS($C5-$B5)/$B5>$G$2. Dividing by the budget turns the difference into a share, which is what a percentage tolerance compares with.

Strict or inclusive: read the boundary

Most rule errors are not about the rule type but about the edge. Two cases come up in our exercises:

• A zero balance. A customer who owes nothing is settled, not a problem. "Less than 0" leaves the zero alone; "less than or equal to 0" highlights it.
• An amount exactly at the limit. A claim of exactly the limit is within the limit. "Over the limit" means strictly greater than; "greater than or equal to" flags the claim that needed no sign-off.

Before choosing the comparison, ask what should happen to a value exactly on the line.

Worked example: claims over the approval limit

Pine Street Dental lists its expense claims for September in A5:D24, with the amount in column D. The approval limit, $2,500.00, sits in G2. The rule: select D5:D24, open Conditional formatting, choose "Cell value is greater than", enter =$G$2 and pick a fill. Here are some of the rows:

Across all twenty claims, five are highlighted: rows 8, 11, 12, 16 and 18. Row 10 is the trap: at exactly $2,500.00 it is within the limit, and a "greater than or equal to" rule would wrongly flag it. Row 8, only $5.81 over, is the kind of line a person scanning the list misses.

If the limit drops to $2,250.00, change G2 and the rule follows at once.

Second example: budget lines off by more than 5%

Juniper Landscaping compares budget and actual for twelve lines in A5:D16, with a tolerance of 5% in G2. Select A5:D16, choose the formula rule type and enter =ABS($C5-$B5)/$B5>$G$2.

Software and Office supplies are under budget, so a formula without ABS would skip them. With ABS and locked columns, each of their rows lights up from the line name to the difference.

Conditional formatting or a manual fill?

You could find the five claims by eye and paint them yellow. But a painted cell stays yellow after the amount is corrected, and a new claim over the limit stays plain. A rule describes what an anomaly is, so it stays right as the data changes. That is also how our exercises check your work: after reading your rule, the checker applies it to changed versions of the sheet, with a different limit or edited amounts, and expects the right cells to light up there as well. A manual fill, or a rule with the limit typed in, cannot pass that.

Managing and deleting rules

Rules pile up. Each time you apply a rule to a range, it is added to the rules already there, so a second attempt does not replace the first. Open the list of rules for the selection or the sheet to see each rule, the range it applies to and its format. From there you can edit a rule, change its range or delete it. If cells are highlighted and you cannot see why, look there first: an old rule from an earlier attempt is a common cause. The page on formatting financial statements in Excel covers the rest of making a report readable.

Common mistakes

Zero balances highlighted too

A rule of "less than or equal to 0" flags customers who owe nothing. A zero balance is settled. Use "less than 0".

Comparison the wrong way round

"Greater than 0" on customer balances highlights the ordinary balances customers owe and leaves the credit balances plain. Check that the rule points at the anomaly, not the normal case.

Limit typed into the rule

The rule shows the right cells today but ignores any change to G2. Compare with =$G$2 instead of the number.

Claims at the limit highlighted

"Greater than or equal to" flags a claim exactly at the limit, which needs no sign-off. Over the limit means strictly greater than.

Only the later copies highlighted

A formula that counts each number only down to the current row misses the first copy. Use the duplicate values rule, which highlights every copy.

Duplicate amounts instead of duplicate numbers

Applying the duplicate rule to the amount column flags different invoices that happen to cost the same. Duplicates are repeated invoice numbers.

Only overspending flagged

Without ABS, the budget rule misses lines far under budget. Take the absolute value of the difference.

Columns not locked in the formula

With ABS(C5-B5)/B5 the references slide across the row, so only some cells light up. Lock the columns: $C5, $B5.

Where this fits in MO-220 practice

Using conditional formatting to surface errors is part of preparing and analysing financial data. Our MO-220 practice tasks describe the result a reviewer needs, such as "highlight claims over the limit so the highlighting follows the limit", and leave the choice of rule to you. Accounting Exercises is not affiliated with Microsoft or Certiport.