The Cash Reconciled to the Penny and 27,667.95 Sat on the Wrong Account, Because Excel Keeps Fifteen Digits and the Reference Had Sixteen

The workbook is called Receipts_Match_March.xlsx. One sheet holds the BACS receipts exported from the bank, one holds the open sales ledger, and a column of VLOOKUP in between decides which customer's account each payment lands on. The key is the sixteen-digit customer reference the bank passes through from the payer's remittance.

It is the shape of every cash-allocation sheet in every small finance team in the country, and on the morning of the March run it reported this:

Every payment matched. The cash reconciled to the penny, in total, on the day, and in every month-end pack afterwards. And 27,667.95 — 18.96% of the run — was sitting on two accounts that had not paid it, because sixteen-digit references had been pasted into a column formatted as General, and Excel does not keep sixteen digits.

The one thing anybody escalated that week was a control cell that said CHECK above a difference that displayed 0.00. That took an afternoon.

What this covers. Everything here behaves the same in Excel 2016, 2019, 2021, 2024, Microsoft 365 and Excel for Mac, and most of it the same in Google Sheets and LibreOffice, because the fifteen-digit limit and the binary arithmetic underneath it are not an Excel bug — they are IEEE 754, the floating-point standard every spreadsheet, database and programming language on your desk uses. ROUND, ABS, TEXT, VALUE, LEN, RIGHT, EXACT, ISNUMBER, ISTEXT, SUM, SUMIF, SUMIFS, COUNTIF, COUNTIFS, SUMPRODUCT, VLOOKUP, INDEX, MATCH, IF and IFERROR work everywhere; XLOOKUP, LET, UNIQUE and FILTER need 365 or 2021 and later. A cash-matching sheet is the example because it is where the cost shows up as money, but this is the same job as a barcode column, a policy number, a bank account, an IMEI, an NHS or SSN-style identifier, an order number from an ERP, or any invoice total anybody has ever compared with =.

1) Sixteen References, Fourteen Keys

The sixteen references in the March file are all distinct. Read them and you can see it: two of them end …5671 and …5672, two more end …5694 and …5695. Sixteen customers, sixteen references, no ambiguity anywhere.

Then they were pasted into a column Excel considered to be a column of numbers.

Those three rows do not add up in the usual way, and that is the point. The bottom row is the damage; the middle row is only the part of the damage that happened to cost money this quarter. Fourteen references were also corrupted — they simply had no twin to collide with, so they went on looking, behaving and matching exactly like references that were fine.

🎯 Scenario: When an identifier is pasted into a sheet, the useful question is not "did everything match?" Everything matched here. The question is "how many distinct values does the key column contain, and is that the number of things there are?" Sixteen rows, fourteen keys, and nothing anywhere in Excel says so out loud.

2) What Excel Actually Stores

Excel holds every number as a 64-bit IEEE 754 double. That gives it a range from about 1E-308 to about 1E+308, which is more than anybody needs, and fifteen significant decimal digits of precision, which is less than some people need — and the people who need the sixteenth are almost always the people holding an identifier rather than a quantity.

Two separate things follow from it, and confusing them is why this subject has a reputation for being mystical.

The first is a hard limit applied on entry. Type or paste a number with more than fifteen significant digits into a cell, and Excel stores the first fifteen and replaces the rest with zeros. Not rounds — replaces. This happens as you press Enter, before any formula runs, and it is not a display setting:

You type:   7100480000345671
Excel holds: 7100480000345670        ← the 1 is gone, and it is gone from the file

There is no format, no column width, no option and no repair that brings the digit back, because it was never written down. The undo stack holds it until you close the workbook; after that the only copy of that digit is in whatever the data came from.

The second is the ordinary imprecision of binary fractions. 0.1 has no exact representation in base 2 for the same reason 1/3 has none in base 10, so a cell holding 0.1 holds something a fraction away from 0.1, and sums of such values land a fraction away from where the arithmetic says. That fraction is around 1E-16 relative, which is invisible in money and fatal in a comparison — section 5.

The first problem destroys data. The second problem destroys tests. They have the same cause and completely different fixes, and a workbook can have both at once, which this one did.

🎯 Scenario: If a column contains something you would read aloud digit by digit — an account, a barcode, a reference, a serial — it is not a number, whatever it looks like. Numbers are things you would add. Nobody has ever added two customer references together.

3) The Cell That Displayed the Right Thing

The reason this survives contact with careful people is that the corrupted cell usually looks correct.

A sixteen-digit number in a General-formatted column behaves in one of two ways depending on nothing more meaningful than how wide the column happens to be:

The scientific-notation version is the loud one. People see 7.1E+15, recognise it as wrong, widen the column or apply a number format, watch the digits come back, and conclude that they have fixed it. They have not fixed it — they have made the damage legible and then stopped looking, because the thing they can now read ends in a zero and there is no reason on the face of it to doubt a zero.

The tell is not in any single cell. It is in the column:

=SUMPRODUCT(--(RIGHT(B2:B17,1)="0"))        → 16

Sixteen references in a row all ending in the same digit is not a coincidence you should be willing to accept; if the references were genuinely random it is a one-in-ten-quadrillion event. It is the single fastest way to spot this from across a desk, and it works on somebody else's workbook with no knowledge of where the data came from.

The second tell takes two seconds:

=SUMPRODUCT(--ISNUMBER(B2:B17))             → 16

Sixteen identifiers held as numbers. A reference column in good health answers 0 to that formula, because every value in it is text.

🎯 Scenario: Widening a column changes what you can see and never changes what is stored. If the fix for a data problem was a column width, it was a display problem, and if it was a display problem the data was never in danger. When those two stories disagree — the digits came back, but they came back ending in a zero — believe the stored value.

4) How Two Customers Became One

Once the sixteenth digit is a zero on every row, two of the pairs in this file are no longer distinguishable:

Bewdley Heating     7100480000345671  →  7100480000345670
Dursley Mechanical  7100480000345672  →  7100480000345670      identical
Eccleshall Trade    7100480000345694  →  7100480000345690
Fishguard Heating   7100480000345695  →  7100480000345690      identical

The matching formula is the one everybody writes:

=VLOOKUP($B2, Ledger!$A:$D, 4, FALSE)

FALSE is doing its job perfectly. This is an exact match, it found an exact match, and it returned it. What no lookup function in any spreadsheet will ever tell you is that there were two exact matches and it handed you the first one — VLOOKUP, XLOOKUP and INDEX/MATCH all return the first hit by default and none of them has a "but there were others" channel to report on.

So Dursley Mechanical's 18,420.65 was credited to Bewdley Heating, and Fishguard Heating's 9,247.30 to Eccleshall Trade. Four accounts wrong from two collisions, and the arithmetic afterwards is flawless:

Every one of those differences nets to zero across the four accounts, which is precisely why the cash reconciled. The bank sent 145,922.40, the ledger received 145,922.40, and a reconciliation that compares two totals is structurally incapable of noticing that the money is on the wrong lines.

What happened next is the part that is not about spreadsheets. Dursley Mechanical was chased on day 12, chased again on day 26, and put on stop-supply on day 34 for 18,420.65 it had paid on day one — the remittance advice it sent as proof was searched for in the matching sheet and not found, because searching for 7100480000345672 in a column that now contains 7100480000345670 finds nothing. Fishguard was chased for 9,247.30 and produced the same proof to the same effect.

And Eccleshall Trade, whose account showed 9,247.30 of payment it had never made, looked like a customer in good standing with headroom. It was allowed that much more stock in month three and went into administration in month five, and the 9,247.30 that was written off is the same 9,247.30 that belonged to Fishguard in the first place.

🎯 Scenario: An exact-match lookup tells you it found something. It never tells you how many things it found. If a key column can possibly contain duplicates — and any key that has been through a General-formatted cell can — the count of matches is a separate question that has to be asked separately: =COUNTIF(Ledger!$A:$A,$B2) alongside the lookup, and anything other than 1 is a line nobody should be paying out on.

5) The Afternoon Spent on 0.00

While all that was in motion, the control cell at the bottom of the matching sheet was the thing that got escalated:

D20:  =SUM(D2:D17)                      145,922.40
F20:  =SUM(Ledger!F2:F17)               145,922.40
G20:  =D20-F20                          0.00
G21:  =IF(D20=F20,"OK","CHECK")         CHECK

A difference of zero and a test that says they differ, in adjacent cells, in the same workbook, on the same two numbers. Three hours went into that, including twenty minutes of somebody retyping the ledger figures by hand.

Both cells are telling the truth. G20 displays 0.00 because the real difference is about 1.5E-11 and you have asked for two decimal places; widen it to fifteen and the zero turns into something like 0.0000000000145. G21 is comparing the two doubles bit for bit, where they differ in the last place or two, and correctly reports that they are not the same number. Summing the same sixteen amounts in a different order is enough to produce that, and the two sheets were in a different order by design.

There is a wrinkle here worth knowing, because it is what makes the behaviour feel arbitrary. Excel applies a cosmetic cleanup to the final operation of a formula when that operation is a subtraction of two nearly equal numbers, and forces the result to zero. Microsoft documents it with this pair:

=0.5-0.4-0.1                            0                       ← cleanup applied
=1*(0.5-0.4-0.1)                        -2.77555756156289E-17   ← the same arithmetic, no cleanup

The multiplication is the last operation in the second formula, so the subtraction no longer qualifies and the residue survives into the result. That is the whole difference between the two lines, and it explains why a difference cell so often shows a beautiful clean zero while a test on the same two values says they are different: the subtraction gets the favour and the comparison does not.

So the fix is not to hunt for the missing hundred-billionth. It is to stop asking a question whose answer you do not care about. Money is equal when it is equal to the penny:

=IF(ROUND(D20-F20,2)=0,"OK","CHECK")                OK
=IF(ABS(D20-F20)<0.005,"OK","CHECK")                OK
=IF(TEXT(D20,"0.00")=TEXT(F20,"0.00"),"OK","CHECK") OK

The first is the one to write by default; the second is what you want when the tolerance is a business decision rather than a rounding artefact, and it is the honest place to write that decision down. The third works and is slower, but it has the advantage of being obviously correct to a reader who has never heard of floating point.

🎯 Scenario: A bare = between two computed currency values is a bug waiting for the right data, in every spreadsheet ever built. The rule is small enough to keep: numbers you typed may be compared with =; numbers Excel calculated must be compared with ROUND or ABS. And note what this cost — three hours on the one number in the workbook that was completely correct, while 27,667.95 sat on the wrong accounts and never raised a flag, because an error message is impossible to ignore and a correct total is impossible to question.

6) Where the Rounding Belongs

The instinct after an afternoon like that is to wrap ROUND around the comparison and move on. That works for the comparison, and it leaves the cause in place if the cause was upstream.

There are two different rounding jobs and they belong in different places:

Round at the point a value is created, when what you are creating is money. A line total is not qty * rate — it is money, and money has two decimal places by definition:

=ROUND(D2*E2, 2)                   the line total, as it will be invoiced

If you skip this, the invoice total is the sum of sixteen values carrying six or eight decimals of rate arithmetic each, and it will disagree with the sum of what the invoice actually printed by a penny or two. Nothing about that penny is a floating-point mystery — it is the difference between rounding sixteen numbers and rounding their sum, and it would happen identically with pencil and paper.

Round for display only, at the end, when what you have is a measurement, a rate or an average. Rounding a rate to two decimals and then multiplying by 10,000 units bakes the rounding error into the answer 10,000 times over.

The test for which you have: would a customer, an auditor or a bank ever see this exact number as a figure in its own right? If yes, round it where it is created, and let everything downstream add up already-rounded pennies. If no, leave it alone and format it.

=SUMPRODUCT(D2:D17, E2:E17)                  the mathematically pure total
=SUMPRODUCT(ROUND(D2:D17*E2:E17, 2))         the total of what you actually invoiced

Those two are different numbers and both are correct answers to different questions. The one that has to match the ledger is the second.

🎯 Scenario: When a total is out by a few pence and nobody can find where, the answer is almost never floating point — floating point errors are around 1E-16 relative, which on a 145,922.40 total is a hundred-billionth of a penny. Pence-sized differences are rounding-policy differences: somebody rounded the lines and somebody else rounded the sum. Find which, decide which is right, and write it once.

7) The Checkbox That Destroys Your Workbook

At some point in an afternoon like section 5's, somebody finds this in File ▸ Options ▸ Advanced ▸ When calculating this workbook and it sounds like exactly the right thing:

☐ Set precision as displayed

Do not tick it. It does not change how Excel compares, calculates or displays anything. What it does is permanently overwrite every stored value in the workbook with its displayed value, immediately, for every cell on every sheet. A cell holding 145,922.4046 formatted to two decimals becomes a cell holding 145,922.40. The remaining digits are destroyed, not hidden; unticking the box does not bring them back; there is no undo after a save.

The damage is worst where it is least visible. A rate column formatted to two decimals for tidiness — 0.0725 showing as 0.07 — loses 7% of its value to a checkbox nobody remembers ticking, and every calculation downstream is quietly wrong from then on. Whole models have been ruined this way by somebody trying to fix a one-penny discrepancy.

If you genuinely want stored values to match displayed values, round them: =ROUND(x,2), in the cells where it belongs, visibly, one column at a time, where the next person can see that you did it.

🎯 Scenario: Any setting whose description contains the word "precision" and whose scope is "this workbook" deserves ten minutes of reading before it is ticked. This one is the only option in Excel that silently rewrites data you already have, and it is two clicks from a menu people open looking for the calculation mode.

8) Holding an Identifier So It Survives

The fix for the reference column is not a formula. It is the format of the cells before anything arrives in them, because after arrival it is too late.

Typing or pasting into a sheet. Select the column, Format Cells ▸ Text, then paste. Text-formatted cells keep every character, keep leading zeros, and never get converted. A leading apostrophe — '7100480000345671 — does the same thing for a single cell; the apostrophe is not stored and not printed.

Importing a CSV. This is where most of these are created, and the difference between two ways of opening the same file is the whole story:

Power Query is the one to reach for if the import repeats, precisely because the column type is stored in the query rather than in somebody's memory of what they clicked last month.

Once it has happened. There is no repair inside the workbook. TEXT(B2,"0"), formatting as text after the fact, and retyping the visible digits all produce a sixteen-digit string ending in the zero Excel put there — a text version of the wrong number, which is worse than the wrong number because it now looks deliberate. The only recovery is to bring the column back from the source: the bank's original CSV, the ERP export, the system of record. If that source is gone, the digits are gone.

The thing to check before you trust a text column. Text and numbers do not match each other in any lookup:

=VLOOKUP("7100480000345671", A:B, 2, FALSE)      #N/A if column A holds numbers
=VLOOKUP(7100480000345671, A:B, 2, FALSE)        #N/A if column A holds text

Both sides of a join have to be the same type, and the loud #N/A you get when they are not is a kindness compared to everything else in this article. Fix it by making both sides text — =TEXT(A2,"0") on the numeric side, or better, by importing both as text in the first place.

🎯 Scenario: The decision about a column's type belongs to whoever designs the sheet, not to whoever happens to paste into it on a Tuesday. A blank template with the identifier columns already formatted as Text is a five-minute job that ends this class of problem permanently for everyone who ever uses it.

9) The Duplicate Check That Cannot See the Duplicate

Here is the part that catches the people who are being careful, and it is worth reading twice, because the obvious defence against everything above does not work.

Suppose you did it right. The references are text, all sixteen digits intact, no truncation anywhere. You now want to confirm there are no duplicates, so you write the formula everybody writes:

=COUNTIF($B$2:$B$17, B2)        → 2 for Bewdley, 2 for Dursley

Two. On a column where the two values are genuinely different. COUNTIF, COUNTIFS, SUMIF, SUMIFS and MATCH coerce numeric-looking text back to numbers before comparing, which puts them straight back into fifteen-digit territory — so as far as COUNTIF is concerned, "7100480000345671" and "7100480000345672" are the same criterion. It reports duplicates that do not exist, and on data that really does contain a duplicate it will merrily count a different reference as one.

The same coercion means SUMIF will add up the amounts belonging to two different customers under one reference, silently, on a column of perfectly intact text.

The function that does not do this is EXACT, which compares text character by character, case included, and never coerces:

=SUMPRODUCT(--EXACT($B$2:$B$17, B2))         → 1 on every row, as it should be
=SUMPRODUCT(--(COUNTIF($B$2:$B$17,$B$2:$B$17)>1))   → 4, which is a lie

For matching rather than counting, the same principle: =MATCH(TRUE, EXACT($A$2:$A$500,$B2), 0) is the collision-proof lookup on long identifiers, and XLOOKUP with EXACT inside it does the same job more readably. They are slower than COUNTIF. On a key that has to be right, that is not a consideration.

🎯 Scenario: This is the reason "we checked for duplicates" is not evidence on a long identifier. The check and the failure share the same blind spot — both stop reading at fifteen digits — so the check will agree with the corrupted data and disagree with the clean data. If a column holds anything longer than fifteen digits, EXACT is the only comparison in Excel that reads all of it.

10) Lookups on Numbers Excel Calculated

The collision in this article came from an identifier, but the same shape appears one step to the left, on keys that are computed:

=XLOOKUP(D2*1.2, Rates!$A:$A, Rates!$B:$B)       #N/A, sometimes, unpredictably

D2*1.2 produces a double that may sit one bit away from the typed value in the rates table, and an exact-match lookup on a double is the same bare = from section 5 wearing a different hat. It works for months and then fails on one row, which is the worst available failure schedule.

The fix is the same as everywhere else in this article — never make a floating-point value do a job that needs an identity:

=XLOOKUP(ROUND(D2*1.2, 2), Rates!$A:$A, Rates!$B:$B)     round the key
=XLOOKUP(TEXT(D2*1.2,"0.00"), Rates!$A:$A, Rates!$B:$B)  or make it text on both sides

Approximate-match lookups on banded values have a gentler version of the same problem: a boundary of exactly 1,000.00 computed as 999.9999999999999 falls in the band below. Round the value before the lookup, or set the band boundaries where no computed value can land on them exactly.

🎯 Scenario: Every lookup key in a workbook is either something a person typed, something a system issued, or something Excel calculated. The first two are safe to match exactly. The third needs rounding before it becomes a key, every time, without exception — and the reason to write the ROUND even when it currently works is that the row that breaks it has not arrived yet.

11) Five Checks

Run these on any sheet keyed on an identifier. The first two take a minute and would have caught everything in this article on the morning of the run.

1. Is the identifier held as text?

=SUMPRODUCT(--ISNUMBER($B$2:$B$17))               → 16

The answer you want is 0. Any identifier held as a number is one Excel is entitled to change, and one it will change without telling you the moment it exceeds fifteen digits.

2. Do they all end in the same digit?

=SUMPRODUCT(--(RIGHT($B$2:$B$17,1)="0"))          → 16 of 16

The fastest diagnosis in this article. Sixteen genuinely-random references sharing a final digit is a one-in-ten-quadrillion event; sixteen references sharing a final zero is Tuesday.

3. Are there as many distinct keys as there are things?

=SUMPRODUCT(1/COUNTIF($B$2:$B$17,$B$2:$B$17))     → 14 keys for 16 customers

Two missing keys is two collisions is at least two payments on the wrong account. On a column of intact text, =ROWS(UNIQUE($B$2:$B$17)) answers the same question without coercing anything — and note from section 9 that COUNTIF is only trustworthy in the formula above because these values really are numbers.

4. Does the cash agree by account, not just in total?

=SUMIF(matched_account, $A2, payment)  vs  =SUMIF(true_customer, $A2, payment)
=SUMPRODUCT(--(ROUND(col1-col2,2)<>0))            → 4 accounts

The only check in this list that catches the actual loss, because the totals are identical by construction. A reconciliation of two grand totals proves that nothing was lost. It proves nothing whatsoever about where anything went.

5. Is anything in this workbook compared with a bare equals sign?

Ctrl+F, Look in ▸ Formulas, and search for =IF( with = inside it on computed values. Every one of them is either safe because both sides were typed, or a bug that has not fired yet. Convert the second kind to ROUND(a-b,2)=0 while you are there.

12) Twelve Traps

1. Fifteen significant digits, and the sixteenth becomes a zero on entry. Not on save, not on calculation — at the moment you press Enter, before any formula sees the cell.
2. The digits are not recoverable from the file. No format, no width, no function, no undo after closing. The only copy is in the source system, and if the source is gone, so is the digit.
3. The corruption is invisible when it matters most. 7.1E+15 is loud and gets fixed; a sixteen-digit number ending in a zero looks exactly like a sixteen-digit number.
4. An exact-match lookup never tells you there were two matches. VLOOKUP, XLOOKUP and INDEX/MATCH return the first and say nothing. Pair every lookup on an untrusted key with a COUNTIF.
5. A reconciled total proves nothing about allocation. 145,922.40 in and 145,922.40 out was true on every single day that 27,667.95 was on the wrong accounts.
6. = between two calculated numbers is a bug waiting for data. Use ROUND(a-b,2)=0 or ABS(a-b)<0.005 on anything Excel computed.
7. A difference cell showing 0.00 does not mean the values are equal. Excel cleans up a final subtraction of two near-equal numbers; it grants the comparison no such favour, which is why the two cells disagree.
8. "Set precision as displayed" permanently destroys stored values, workbook-wide, in one click, with no undo after a save. Round in cells instead, where it is visible.
9. Round money where it is created, not where it is displayed — =ROUND(qty*rate,2) — and expect the sum of rounded lines to differ from the rounded sum, because it does, on paper too.
10. COUNTIF, SUMIF, COUNTIFS, SUMIFS and MATCH coerce numeric text to numbers, so they cannot tell two sixteen-digit references apart even when both are perfect text. EXACT can, and is the only thing that can.
11. Text and numbers never match each other in a lookup. The #N/A this produces is the friendliest error in this article — it is the one failure mode here that cannot hide.
12. Format the column as Text before the data arrives, and import through Power Query with the column typed as text. Every fix after arrival is cosmetic.

Nobody in this story was careless. The person who pasted the bank's reference column into the matching sheet did what that job has always required. The credit controller who chased Dursley Mechanical was reading a ledger that said, unambiguously, that Dursley had not paid — and Dursley's remittance advice, searched for in the sheet, genuinely was not there. The person who spent an afternoon on a control cell that said CHECK was doing exactly what a control cell is for: it was the only thing in the workbook asking to be looked at.

What made it expensive is that Excel's most consequential act on this data was performed in complete silence, at the moment of paste, on a column the user had not been asked a single question about. Every other data problem in a spreadsheet announces itself eventually — a broken reference goes #REF!, a text number refuses to sum, a bad lookup goes #N/A in colour. A truncated identifier produces a number that is the right length, the right shape, in the right column, matching cleanly against a ledger that also has it wrong, footing to a total that is correct to the penny.

So the discipline is boring and it is not about arithmetic. Decide what each column is before anything arrives in it, and give the identifier columns the Text format they need on the day the template is built. Ask, once, how many distinct keys a key column contains, and whether that is the number of things there are. Compare money with ROUND and never with =, so that the one cell in the workbook built to raise the alarm is not spending its life crying about a hundred-billionth of a penny. And when a reconciliation foots exactly, read it for what it is — a statement that nothing was lost, and a statement about nothing else at all.