Why a converted bank statement won’t balance
Suppose a checking account statement closes at 940.00, while your spreadsheet calculates 960.00. The 20.00 difference gives you somewhere to start. Keep the original export intact and investigate in a working copy. Write down the difference with its direction: “sheet is 20.00 higher than the printed closing balance.” That one sentence makes it easier to tell whether a proposed correction moves the result toward the statement or further away.
In this guide
A useful first test is to compare money in and money out separately. Next, divide the statement into smaller groups until you can identify the first group that disagrees. Only then examine the individual transactions. This order helps when there are hundreds of entries and several purchases happen to equal the amount of the difference.
Match the period and balance type
Read the opening and closing balances from the same statement period. Use the account identifier, currency, and dates printed on the statement. An app screen showing today’s available balance may include later transactions or pending holds. A cover sheet may show the total across several accounts, while the following pages list just one checking account. Record the two balances and the printed account label in your working copy before changing any transaction values.
Establish which records belong in the calculation
Find the first posted transaction after the opening balance and the last posted transaction before the closing balance. Mark those two records in the PDF. An entry dated September 30 can legitimately appear on a statement that also shows an October 1 posting date. Use the membership of the printed activity list to set the boundary. Filtering your export by an assumed date field can hide an entry you need to reproduce the bank’s total.
Check that the statement is complete. Some banks number the cover page along with the transaction pages. Others restart the numbering for each account section. If your PDF contains page one of a three-page transaction list followed immediately by page three, a missing page can explain the difference. Download the original statement again before trying to infer the missing transactions from their combined value.
Calculate the difference in a small control table
For a conventional deposit account statement with separate positive amounts for deposits and withdrawals, opening balance plus deposits minus withdrawals should reproduce the printed closing balance, provided all posted activity is included. A credit card statement can use different conventions. Look at how a clearly identified transaction affects the printed balance before applying the deposit-account calculation to another statement type.
Work the 20.00 example
Enter opening balance 1,000.00, total deposits 200.00, and total withdrawals 240.00 into a scratch area of the sheet. The workbook result is 960.00. The statement gives the same opening balance and deposits, but shows withdrawals of 260.00. That narrows your investigation to 20.00 of missing or understated withdrawals. An incorrect opening value would have pointed you in a different direction even though both problems could produce the same closing difference.
Now change the example slightly. Suppose the statement shows deposits of 190.00 and withdrawals of 250.00. Its closing result is still 940.00. The workbook’s deposit total is 10.00 too high and its withdrawal total is 10.00 too low. Moving a 10.00 withdrawal from the deposits column back to the withdrawals column would change the closing result by 20.00. The separate totals make that explanation plausible. You still need the corresponding printed entry before making the correction.
Check what the total actually sums
Read the cell range used in each totals formula. A subtotal formula that stops at row 78 misses a final transaction pasted into row 79. A short Excel selection that excludes the last line can produce the same discrepancy as a lost transaction. Inspect the last included cells and confirm that headers, opening balances, and the statement’s own total rows are outside the activity range.
Locate the first group that disagrees
If the statement prints running balances, compare the calculated balance immediately after a transaction with the printed balance immediately after the same transaction. Use the original printed sequence. Sorting by date alone can rearrange transactions sharing a posting date. Your calculation can appear wrong for several lines and then agree again when all transactions from that day have been included.
Compare boundaries before inspecting every row
For a twelve-page statement, try the last transaction on page six. If its printed and calculated balances agree, check the end of page nine and then a smaller interval. If the end of page six already differs, start closer to the beginning. This approach is most effective when investigating a single difference. When discrepancies cancel, agreement at a boundary does not prove all earlier rows are perfect. It tells you where to look first to explain the particular ending difference.
For pages without a running balance, calculate a subtotal of the transactions you can identify on each page. Record the first transaction, last transaction, deposit total, and withdrawal total. Compare those four values with the corresponding spreadsheet group. If the first and last entries match while a subtotal differs, inspect the group’s middle rows. If a boundary entry does not match, fix your grouping before treating the subtotal as evidence of a missing transaction.
Examine the suspect row and its neighbors
A PDF stores text at positions on a page. A converter must infer which pieces of text belong together. A long merchant description can wrap below its amount. A printed minus sign can sit apart from the digits. A heading that includes a number can resemble a transaction. Look at the original row, the preceding row, and the following row together, especially when the difference first appears at a page break.
Distinguish a real charge from a carried-forward balance
Imagine a page ends with a withdrawal of 20.00 and a resulting balance of 940.00. The following page begins with “Balance brought forward 940.00.” That second 940.00 reports an account state. It is not another payment. If it was placed in an activity column, set it aside in your working copy and compare the remaining transactions with the printed ones. Preserve the original text in a note so someone reviewing the workbook can understand why it was excluded.
A visually similar issue occurs when a page header repeats the final transaction description from the previous page. Compare references and the change in the running balance. Two identical amounts at the same merchant may represent two purchases. If the balance steps down twice and the PDF lists two transactions, keep both. Removing a genuine transaction simply because a spreadsheet labels it a duplicate can create a fresh discrepancy.
Inspect values that look numeric
An extracted “20.00” may be stored as text, particularly if it contains a currency symbol, a space, or a separate sign. In Excel, try an ISNUMBER check on the specific amount cell. Also inspect what appears in the formula bar. An identifier such as 00020 can look like a small amount but belongs in a reference column. Convert only the cells whose printed meaning you have verified. Formatting an entire sheet as numbers can damage identifiers without fixing the total.
If the statement uses a comma as a decimal mark, compare one known value in the PDF, the exported file, and the spreadsheet import preview. A printed 20,50 must represent twenty and a half currency units in the intended locale. If Excel interprets it as two fields during CSV import, change the import settings and preview again. Repeated manual fixes within a malformed import make the working copy harder to reproduce.
Make one documented correction and rerun the checks
Record the page, transaction description, original spreadsheet value, corrected value, and reason. For a missing withdrawal, a note might read, “Page 4, Oct 16, service fee: 20.00 printed in withdrawals, no corresponding export row; added in working copy.” If the row exists with the wrong sign, note the edit instead. These are different corrections even when the same closing total results.
Recalculate the deposits total, withdrawals total, and closing balance after that single change. Then compare the next printed running balance or page boundary with the corrected value. In the missing-withdrawal example, the sheet should move from 960.00 to 940.00 while the deposits total stays at 200.00. If deposits also changed, inspect the formula ranges or reference cells before accepting the correction. A matching closing balance by itself cannot tell you whether money entered the correct columns.
If the discrepancy remains unexplained
Keep the unresolved difference visible. Avoid adding an invented adjustment line merely to reach the expected closing value. Instead, write a short handoff note with the mismatch amount, the last agreeing transaction, the first disagreeing transaction, and the checks already performed. A colleague can investigate a five-row window more efficiently than an entire monthly statement. If the source PDF itself appears incomplete or internally inconsistent, obtain another official copy or seek clarification from the issuer.
Repeat the process on a clean export
When an import setting or layout issue caused the discrepancy, repeat the conversion on an untouched copy and verify that the same correction works. That test separates a reproducible remedy from a workbook that happens to balance after several edits. Save the original statement, the raw export, and the reviewed workbook with distinguishable filenames. A later reviewer should be able to identify which values came from the bank and which cells received a manual correction.
Use the free bank statement to Excel converter to create a fresh workbook from a digital PDF. If you are preparing the spreadsheet for the first time, the statement-to-Excel walkthrough covers the extraction stage. For a table imported from a CSV file, start with the PDF table to CSV guide and check the column boundaries before comparing totals.