How to Reconcile a Bank Statement in Excel

To reconcile means to check that your records match your bank. You keep a list of money in and out. The bank sends a statement. If the two agree, you know your books are right. If they don't, you find out why before it becomes a big problem.

You can do this for free in Excel. The slow part is getting the statement into Excel. MyBankToExcel turns a PDF statement into a file Excel can open, so you can skip the typing.

What you need

Your bank statement for the month.
Your own list of money in and out for the same month. This might be a checkbook, a notebook or a spreadsheet.
Excel, Google Sheets or Numbers.

Get the statement into Excel

The free way: type each line from the statement into Excel. Use four columns: Date, Description, Amount and Balance. Put money out as a minus, like -45.00.

The quick way: use MyBankToExcel.

Choose your statement PDF.
Press Read my statement.
Download the file for Excel.

Money out comes out as a minus and money in as a plus. Your PDF never leaves your device. It is read in your browser, and only the words are sent to us to sort them into rows.

Step by step in Excel

These steps use the statement sheet and your own list side by side.

Open the statement in Excel. Put your own list on a second sheet in the same file.
Add a new column next to the statement called Found.
Go down the statement one line at a time. If the same amount is in your list, type Yes in the Found column. Mark it in your list too.
Look at the statement lines with no Yes. These are often bank fees, interest or payments you forgot to write down. Add them to your list.
Look at your list for lines with no mark. These are often checks that have not been cashed yet, or deposits still on the way.
Write down the end balance from the statement.
Take away the checks not cashed yet. Add the deposits still on the way.
Compare the result with the balance in your own list. If they match, you are done.

A quick balance check

You can also check that the statement adds up by itself.

In an empty cell, type the start balance from the statement.
Click an empty cell and type =SUM( then select the whole Amount column, type ) and press Enter.
Add the start balance and that total.
The answer should equal the end balance on the statement.

MyBankToExcel does this check for you before you download. If the numbers don't add up, it tells you in plain words.

If it still doesn't match

Look for a typo. Two numbers swapped, like 54 typed as 45, is a common mistake.
Look for a line entered twice. Sort your list by amount to spot doubles.
Check the dates. A payment on the last day of the month may show up on next month's statement.
Ask your bank if a line on the statement makes no sense to you.

Got other tables in PDFs?

Bills, price lists and reports can also come as PDFs. Sheetsnap puts PDF tables into Excel, with each column in its own place.

Frequently asked questions

What does reconcile mean?

It means checking that your own list of money in and out matches what the bank says. If they match, your records are right.

How often should I do it?

Once a month is a good habit, when the new statement comes. It is much easier to find a mistake from last month than from last year.

Why don't my numbers match?

Most often a check has not been cashed yet, a deposit is still on the way, or a bank fee is missing from your list. Sometimes a number was typed wrong.

Do I need to type the statement into Excel?

You can, but it is slow. MyBankToExcel turns the PDF statement into a file Excel can open, with the date, description, amount and balance in their own columns.