Reconciling your bank account with a monthly statement can often be challenging and frustrating! After entering the start date and end date on the reconcile worksheet (example below), if the withdrawals and/or deposits fail to match with your bank statement, here are some common issues that users have encountered.
Common issue #1 – Bank statement start date includes transactions on that date
All bank statements will give a starting date and ending date, but some banks include transactions on the start date while other banks don’t. For example, Capital One statements will say “Statement Period: June 1 – 30, 2024” and their statement can include transactions that are dated on 06/01/2024. Other banks, like Truist, refer to the statement start date as “your previous balance as of 06/01/2024“, and their bank statements won’t have transactions on 06/01/2024 because they were included in the previous month’s statement. It’s important to look at your bank statement to see the dates of the earliest transactions. Are there transactions in the statement on the start date? If so, when entering the start date on the Reconcile screen, input the date as one day earlier, e.g., 5/31/2024 instead of 6/01/2024.
Common issue #2 – Incorrect dates or mismatched dates on transactions
Another common issue regards having a slightly different date for transactions in your spreadsheet vs. the bank statement at the beginning or end of the statement date range. For example, perhaps you entered a transaction with a date of 06/30/2024 but your bank posted it on 07/01/2024 and thus it is outside of the statement date range (assuming the statement period is 06/01/24 – 06/30/24).
Another issue is a mistyped date. For example:
It’s easy to overlook that the 3rd entry above was entered as 2025 instead of 2024, which means it wouldn’t be considered by the date range on the reconcile screen even though it seems like the entries are in date order.
Similarly, it can be easy to mistype a date entirely such that it’s not a date at all! Did you notice that last date in the screenshot above? It was entered as Oct 111, 2024 which is certainly an invalid date. One trick to help find invalid dates is to click the filter button on the date column. In the screenshot below, that bad date will stick out like a sore thumb.
Common issue #3 – A filter is active which is hiding rows
Another issue I’ve seen is when you have activated a filter on one of the columns, which will then hide rows, making it very difficult (or impossible!) to reconcile all of your transactions with your bank statement. How do you know if an Excel filter is active? The row-numbers will be blue instead of black, and you’ll likely notice that some row numbers appear to be skipped like in this example. You’ll need to look for the column heading that has a filter indicator, and clear (turn off) that filter.
Common issue #4 – Uncleared checks, or checks not recognized, and/or interest deposits
If you notice that the reconcile worksheet isn’t finding all of your checks, make sure you have put the check numbers in the Check # column. If you don’t enter a check number in that column, the reconcile worksheet won’t include it in the total checks found. Also, make sure that the dates you entered for the checks are a match with your bank statement.
Lastly, if you write a lot of checks, sometimes a check doesn’t get cleared and thus it won’t be on your bank statement but you have it in your spreadsheet. In those instances, you have to keep it in mind that the reconcile screen will show a different value for “checks found” and that’s okay.
If your checking or savings account earns interest each month, you’ll also need to remember to add an entry in your spreadsheet for that interest deposit too so that the “deposits/credits found” is correct.
For a walk-thru on using the reconciliation features in the Excel checkbook, please see the following videos.