Tutorials & Guides

Shortcuts & Excel Hacks

A working reference for the Microsoft Office apps finance teams live in — Excel, Word, Outlook and PowerPoint — with Windows and Mac keys side by side, plus the Excel data-cleaning moves that save the most time. Search it; don't memorize it.

Browse all step-by-step guides → — Excel how-tos plus reconciliation, close and bookkeeping walkthroughs.

Featured guide

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.