Excel for bookkeepers and accountants

Categorize bank lines from a keyword list

Bank descriptions are messy: "POS ADOBE *CREATIVE CLD", "ONLINE PMT PACIFIC POWER". Instead of reading each one, keep a small rules table of keywords and categories (G2:H7 here) and let a formula find the first keyword that appears anywhere in the description.

SEARCH(G2:G7,B8) looks for every keyword inside B8 and returns a position where it finds one and an error where it does not. ISNUMBER turns that into TRUE and FALSE, MATCH finds the first TRUE, and INDEX returns that rule's category. In Excel 365 it works as typed; in Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter.

Two gotchas. The first matching rule wins, so put specific keywords above general ones. And short keywords match too much: "FEE" would also match "COFFEE", which is why the rule here is "SERVICE FEE".

Find bank lines that never made it into the books

The first pass of any bank reconciliation: what does the bank show that the books do not? Here the Booked amount column is blank where nothing was recorded. SUMIFS with an empty-text criteria ("") adds the bank amount only on those rows.

Usual suspects are bank fees, interest, customer payments that went straight to the bank, and automatic debits nobody told you about. COUNTBLANK(F2:F12) tells you how many lines there are.

Catch transposed digits (the divisible-by-9 check)

After you book the missing lines, a small difference often remains. The classic cause is two digits keyed in the wrong order, like 214.27 typed for 212.47. A transposition always leaves a difference that divides evenly by 9 once you count it in cents, so the check is a quick MOD.

The formula takes the total difference between bank and books, removes the unbooked lines found above, turns the leftover into cents and divides by 9. A remainder of 0 means: go looking for a swapped pair of digits. It is a clue, not proof, since plenty of other differences are also multiples of 9 cents.

Total spending by category, the right way round

Bank exports usually show money out as negative numbers. SUMIFS adds them as they are, so a category total comes back negative. Put a minus in front of SUMIFS to report spending as a positive number, which is what clients expect to read.

Matching on the cleaned Category column (not the raw bank text) means "Adobe" and "Google" both land in Software.

Spot possible duplicate payments

Paying the same bill twice is one of the most expensive mistakes in bookkeeping, and it hides easily in a long feed. COUNTIFS on vendor and amount counts how many rows look like this one. Anything above 1 deserves a look.

Add the month or the invoice number as a third condition when you can. A monthly subscription legitimately repeats every month, so the same vendor and amount in different months is normal; in the same month it usually is not.

Budget vs actual with a variance threshold

Clients do not need every variance explained, only the ones that matter. Work out actual spending, compare it with budget, and flag lines that run more than your threshold over (H11 holds 10% here; agree the number with the client or your manager).

Divide actual by budget and subtract 1 to get the variance as a percentage. A positive number means over budget on a cost line.

Prove the closing bank balance

The bank balance at month end should equal the opening balance plus every line in between. If your running total disagrees with the statement, a line is missing from the export or was downloaded twice, and it is far better to find that now than halfway through the reconciliation.

SUM over a signed amount column handles money in and money out in one go.