How to Create Excel Checkbook Register with Reconciliation

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will demonstrate how to create an Excel checkbook register with Reconciliation. Bankers typically employ reconciliation in their daily work. The following article will help you create a reconciliation worksheet in Excel. We will use Microsoft 365 to do all these.


What Is Reconciliation?

Reconciliation is a technique in accounting that analyzes two sets of records to ensure that the numbers are accurate and consistent. Additionally, reconciliation verifies the consistency, accuracy, and completeness of the accounts in the general ledger.

Bank reconciliation, vendor reconciliation, business-specific reconciliation, intercompany reconciliation, and customer reconciliation are the five main categories of account reconciliation. However, in this article, we will focus mainly on Bank Reconciliation (Checkbook Register with Reconciliation).


How to Create Excel Checkbook Register with Reconciliation: Step-by-Step Procedures

In this article, we will show the bank account information of a person named “John Smith”. He created his bank account in September 2022 and the Excel sheet we will create will show all the transactions of John Smith in September 2022. Now, let’s jump into the steps for creating the checkbook with reconciliation.


Step 1: Creating New Excel File & Giving Title

In this step, we will give a title for the Excel sheet. To do that, we will follow the steps below.

  • In the very first step, we need to open a fresh Excel Workbook.
  • Next, we will take some cells from one of the top rows and merge those cells into one. We will take B2:I2 in this case and will Merge & Center.

Merge Cells to Create Title Row

  • Then, we will give a suitable title to it. I have given the name “Checkbook Register of A/C ID 058462”. Next we will modify the formatting of the cell to make it more attractive and elegant.

Giving Title to the Excel Checkbook Register

Read More: How to Create Sales and Purchase Ledger in Excel


Step 2: Inserting Information of Account Holder into Excel Checkbook Register

Since we will create the checkbook for John Smith, we will insert his information regarding the account.

  • To do that we will format the blank space for name as text and format the remaining as Currency. After that we will enter the information.

Details of the Checkbook Holder


Step 3: Creating Columns in Checkbook Register with Reconciliation

In this step, we will create 8 columns each with unique column headers and unique functions.

  • From B10 to I10 we will name the columns as follows.

Creating Columns for Data Entry

  • Secondly, we will insert rows according to the requirements.

Columns Created for Checkbook Register with Reconciliation


Step 4: Converting Range into Table

We will have to convert the range above into a table.

  • To do that, we will select all the cells and select the Tables ribbon command and then Table.

 Converting into Table

  • Then a dialog box appears in which we will select My Table has Headers and hit OK.

Dialog box of Creating Table

  • After doing that, we get our desired table.

Table Created Checkbook Register with Reconciliation


Step 5: Giving Input to Checkbook Register

In order to give the checkbook a genuine look, we will insert some random data into it.

Input Data into Table


Step 6: Inserting Formula into Balance Column

If we want to calculate balance, we will have to insert a formula in the balance column. We will follow the steps below to do that.

  • We will insert the following formula into
=IF(ISBLANK(B12),"",I11-F12+G12)

Inserting Formula

Final Result of Checkbook Register with Reconciliation

  Formula Breakdown

  • ISBLANK B12 returns FALSE since B12 has value.
  • The argument of IF is FALSE then
  • IF(ISBLANK(B12),””,I11-F12+G12) returns the value after doing the subtraction and addition operation.

Download Practice Workbook


Conclusion

We have discussed the ways to create an Excel checkbook register with reconciliation in the above sections. Hope this will be very helpful for you. Let us know your views and recommendations in the comment section. Have a good day.

<< Go Back to Excel Ledger Templates | Accounting Templates | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo