Tutorials & Guides

Practical finance, from someone who does it

Real-world techniques and Excel templates drawn from twelve years in multi-practice finance. Written for the person who has to reconcile the thing by Friday, not for a classroom.

How to compare two lists in Excel and instantly spot what doesn't match

You have expected payments in one column and actual bank deposits in another. Eyeballing them works until there are four hundred rows, and then it stops working in a way you won't notice until month-end. Here is the version that takes a minute.

The formula

In a helper column next to your first list:

=COUNTIF($B$2:$B$500, A2)

It counts how many times the value in A2 appears in list B. 0 means it's missing. 1 or more means it matched. Fill the formula down the whole column.

Make the misses obvious

Select your helper column
Home → Conditional Formatting → Highlight Cell Rules → Equal To
Enter 0 and pick a red fill

Every unmatched row now lights up.

Check the other direction too

This is where most people stop, and it's why reconciliations still don't tie. Running it one way only tells you what's in A but missing from B. Repeat the formula on the B side, pointing at A, to catch the rows that exist in your bank feed but never made it into your expected list. Both directions, every time.

Four things that create false mismatches

Leading and trailing spaces

A single trailing space makes an identical value fail to match. Wrap both sides in TRIM() before you compare.

Numbers stored as text

Invoice numbers pasted from a PDF are usually text. 1024 and "1024" will never match. Watch for the green corner triangle, and convert one side.

Hidden wildcard characters

COUNTIF treats * and ? as wildcards. A vendor name containing either will match things it shouldn't.

Case and punctuation drift

COUNTIF ignores case, which usually helps — but Acme, Inc. and Acme Inc are still two different values. Normalize before you compare.

Doing this across multiple entities every month? That's the problem IMS Made Simple was built to end. Or write to us at info@jmwinc-developers.com and tell us what your reconciliation looks like.