Reconcile Bank Account: Balance Your Checkbook

Applies to Excel templates: "Georges Budget for Excel", "Georges Excel Checkbook Register", "Georges Excel Checkbook for Mac"

(back to All Support Topics)

 

Video - How to reconcile checking account in Excel checkbook registers

 

 

What is bank account reconciliation?

• You can reconcile your checking account by comparing your personal checking account register that you keep to record your transactions (whether its a paper checkbook register, Excel checkbook register, or personal finance software) to your banks monthly statements. Typically, paper bank statements are sent via the mail but you can also sign into online banking and download a copy of your bank statements into a PDF format. You want to make sure that all bank deposits, ATM transactions, checks written, debit card purchases, automatic payments, transfers, credits, bank fees, and all other checking account inflows and outflows have been properly recorded by you in your personal checkbook register and by the bank for a particular period of time, usually the statement period.
• In part, you are making sure that transactions recorded by the bank are correct and match with what you recorded in your personal checkbook register or money management software. That is why it is so important to keep your own checkbook register and record all of your transactions in the personal checkbook register so that you can verify if the bank recorded the transactions correctly as reflected by the bank statements or online banking. Its also important to keep your own registers because of outstanding items. Your register, if you recorded all of your transactions correctly, may show your balance differently because of outstanding items. Outstanding items are items that you have recording in your register but have not cleared the bank yet. So if you write a check and record it in your register, your bank account may not reflect the same balance because it takes several days for the check to clear the bank. The bank could make a mistake and post the incorrect amount and not post the item at all or you could have made a mistake in the registers so that is why you reconcile and compare the two and make any necessary changes to the checkbook register or by notifying the bank of any issues with their records of your account.
• You should reconcile your bank account at least every month or more if you can. With online banking, you can reconcile your checking account more often since you have access to your accounts posted transactions and balances online via online banking at any point in time and not just when the statement closes and the statements are printed and mailed to you.

Benefits of bank account reconciliation

• Help determine is fraudulent activity and identity theft happened in your account. Bank have systems in place to catch fraud and notify you but they may not catch all bank account fraud, so balancing your checkbook regularly may help catch fraud and then you can notify the bank to start the process to remove any fraudulent bank transactions. If you see a purchase or debit on your account in your bank statement that you did not authorize, then you can report it to the bank. Banks generally have a limited time frame that you can notify them of these fraudulent activities after they happen so that is another reason the it is import to regularly balance your bank account and notify your bank promptly of any issues. Balancing your checkbook and reconciling your bank account is more important now more than ever with identify theft on the rise and so many transactions happening over the internet.
• Help prevent overdrawn accounts and bank overdraft fees by maintaining your own checkbook register and reconciling it.
• Help catch bank errors. Banks make mistakes also. For example, maybe be bank charge you a monthly service fee, but since you met the balance requirement, you should not have been charged, so balancing your checkbook can help find these errors.
• Help catch any errors you may have made entering or downloading transactions into your personal checkbook register. For example, you may have entered the same transaction twice, or forget to enter a transaction such as an automatic payment or monthly service fee, or entered the wrong amount.

Tools to help reconcile bank account

For "Georges Budget for Excel":

• Use the Rec field in the Registers to mark items that have been reconciled (cleared) with an "R".
• If the transaction is outstanding (not cleared), leave the Rec field blank (empty). See "Split Transactions" help section for when to enter "Split" in the Rec field.
• The column titled Rec should only contain the 3 options below:

• If an item is a split transaction, mark the main split transaction with an "R" in the Rec field when it has been reconciled (cleared), and its related split itemizations will have "Split" in the Rec Field.
• The column titled Cleared will display a Green Check Mark indicator if the column titled Rec in the same row has an "R" in it.
• The column titled Cleared will display an Orange Exclamation Point indicator if the column titled Rec in the same row does not have an "R" or "Split" in it.
• The column titled Cleared will display "Split" if the column titled Rec in the same row has the word "Split" in it.
• Clicking the Filter: Show Outstanding button located at the top of the Registers will first remove any applied Filters in the Registers and then apply Filters that will create list of outstanding transactions and display the total dollar amount of those outstanding transactions.
• Clicking the Filter: Show Outstanding button will not apply any Filters if there are error(s) noted in the Balance column of the Register.
• Clicking the Filter: Show Outstanding button filters out (hides) any rows that have an "R" or "Split" in the Rec column and that have no date in the Date field, thus creating a list of outstanding transactions. It will also provide a total dollar amount outstanding in cell G4005.
• Clicking the Clear All Filters button will remove the Filters that were applied by clicking the Filter: Show Outstanding button. Clicking the Clear All Filters button will also remove any Filters that have been applied in the Register no matter how the Filters were applied.
• In each Register, Total Outstanding equals the sum of the amounts in the Amount column that have not been marked with an "R" or "Split" in the column titled Rec.
• In each Register, Total Cleared equals the sum of the amounts in the Amount column that have been marked with an "R" in the column titled Rec.
• In each Register, in cell G1, next to the words "Total Outstanding: " is a count of the total number of transactions Outstanding. The total number of transactions Outstanding is a count of the number of transactions in each register that have not been marked with an "R" or Split" in the column titled Rec.
• When you get done with your monthly bank account reconciliation in "Georges Budget for Excel", the "Total Cleared" near the top right of the Excel register should equal your ending bank statement balance. However, even though you may have completed your monthly checking account reconciliation, they may not equal if there were errors made in reconciliation process, such as you marked the wrong item as Reconciled (Cleared) in the Excel registers, or you marked an item as Reconciled (Cleared) in the Excel registers but the amounts were actually different, or there is an item on bank statement that is not in your Excel registers, or your register balance prior to doing the current reconciliation was not reconciled properly. After reconciling your bank account, if the "Total Cleared" near the top right of the Excel register does not equal your ending bank statement balance, I would go over my bank account reconciliation again and make sure that everything was matched properly, and that the Excel register balance prior to doing the current reconciliation was reconciled properly also.

 

 Picture below is example of "Georges Budget for Excel" 

 

 

Tools to help reconcile bank account

For "Georges Excel Checkbook Register" & "Georges Excel Checkbook for Mac":

• Use the Rec field in the Registers to mark items that have been reconciled (cleared) with an "R".
• The column titled Cleared will display a Green Check Mark indicator if the column titled Rec in the same row has an "R" in it. The Green Check Mark indicator in the column titled Cleared is represented by the number 10 in the AutoFilter list of numbers for the column titled Cleared.
• Clicking the Rec button located at the top of the Registers or located in the Accounts Summary will first remove any applied Filters in the Registers and then apply Filters that will create list of outstanding transactions and display the total dollar amount of those outstanding transactions.
• Clicking the Rec button will not apply any Filters if there are error(s) noted in the Balance column of the Register.
• Clicking the Rec button filters out (hides) any rows that have an "R" in the Rec column and that have no date in the Date field, thus creating a list of outstanding transactions. It will also provide a total dollar amount outstanding in cell G1505.
• Clicking the Clear Rec bottom will remove the Filters that were applied by clicking the Rec button. Clicking the Clear Rec button will also remove any Filters that have been applied in the Register no matter how the Filters were applied.
• In each Register, Total Outstanding equals the sum of the amounts in the Amount column that have not been marked with an "R" in the column titled Rec.
• In each Register, Total Cleared equals the sum of the amounts in the Amount column that have been marked with an "R" in the column titled Rec.
• After completing your monthly checking account reconciliation in "Georges Excel Checkbook Register" or "Georges Excel Checkbook for Mac", the "Total Cleared" near the top right of the Excel register should equal your ending bank statement balance. However, even though you may have completed your monthly checking account reconciliation, they may not equal if there were errors made in reconciliation process, such as you marked the wrong item as Reconciled (Cleared) in the Excel registers, or you marked an item as Reconciled (Cleared) in the Excel registers but the amounts were actually different, or there is an item on bank statement that is not in your Excel registers, or your register balance prior to doing the current reconciliation was not reconciled properly. After reconciling your checkbook account, if the "Total Cleared" near the top right of the Excel register does not equal your ending bank statement balance, I would go over my checkbook account reconciliation again and make sure that everything was matched properly, and that the Excel register balance prior to doing the current reconciliation was reconciled properly also.

 

Picture below is example of "Georges Excel Checkbook Register" and "Georges Excel Checkbook for Mac"

 

 

 

(back to All Support Topics)