How to Use Excel to Reconcile GL Accounts

Monthly reconciliation of balance sheet accounts is the only way to ensure that the financial data for a company is properly recorded on the accounting books. Balance sheet account reconciliation protects a company from reporting errors on the company financial statements, which can lead to errors when preparing the company’s federal tax return. Many accountants create Microsoft Excel spreadsheets to assist with reconciling general ledger (GL) accounts to external documentation.

Young businesswoman eating lunch at desk
credit: Siri Stafford/Digital Vision/Getty Images

Step

Type the name of the general ledger account and the general ledger account number at the top of the Excel spreadsheet. You can type both names and numbers directly into the cells in Excel and the software will correctly format the entries.

Step

Enter the month and year to be reconciled below the GL name and number.

Step

Type “Ending Balance per GL” in the first column of the spreadsheet a couple of lines below the heading.

Step

Enter the ending amount contained in the general ledger account in the second column of the spreadsheet on the same line as the “Ending Balance per GL” label.

Step

Type “Ending Balance per (source document)” in the first column of the spreadsheet below the row that contains “Ending Balance per GL.” Examples of source documents include accounts receivable reports, accounts payable reports and asset listings.

Step

Enter the ending balance per the source document for that general ledger account in the second column of the spreadsheet on the same line as the “Ending Balance per (source document)” label.

Step

Type “Difference” in the next line in the first column.

Step

Create a calculation in the second column next to the “Difference” label that subtracts the source document total from the ending general ledger balance. This is the amount that needs to be reconciled.

Step

Create a section below the difference line called “Reconciling Items.”

Step

List each item creating a reconciling difference in the reconciling items section. Include an explanation of each item and the amount of the item. An example of a reconciling item may be a payment on account that was posted to the bank account but not posted to the customer’s account.

Step

Create a calculation at the end of the Reconciling Items section that totals the amount of reconciling items. When the Reconciling Items total equals the “Difference” total, then you have properly reconciled the account.