A Checkbook register is a very useful document for keeping track of your Bank balances for personal and business accounts.
The Checkbook Register is crucial document to remain on top of your financials. It helps you to look where the money is coming/going from and how much is left with you.
Many a time, it happens that bank officials commit some mistakes. If you maintain Checkbook Register, you can identify those errors and get them corrected.
Additionally, you can also prevent bouncing back of checks due to unavailability of balance. Thus, avoiding unnecessary fees charged on your account.
I have created an easy to use Checkbook Register Excel Template with preset formulas.
This template can be helpful to individuals, accounting professional and students. You can use this template to keep a track of all your income and expenditures.
Click here to Download the Checkbook Register Excel Template.
Additionally, you can download other accounting templates like Cost Sheet with COGS, Weekly Timesheet and Salary Sheet from here.
Let’s discuss the template contents in detail.
Content of Checkbook Register Template
This Template consists 2 worksheets:
- Datasheet and
- Checkbook Register
1. Datasheet
The Datasheet consists of lists of the type of transactions. This list is used for creating the dropdown menu in the Register sheet.
2. Checkbook Register
This sheet consists of the following sections:
a) Heading:
Heading of the sheet
b) Account Info
Account Type: Type of account like Checking, Current, Savings, Personal etc.
Account Number: Number for respective account.
From: Start date. The Formula given here is =A15, the first line of date column of data input section.
To: End date. The Formula given here is =MAX(A15:A41), the last date of entry of the date column.
c) Balance info
Opening: Opening balance of Bank and Cash as per the records.
Closing: Closing balance of Bank and Cash as per the records.
d) Summary
In this section, you don’t need to input any data. All the fields here are auto calculated and with preset formulas.
Opening Balance: This will reflect the opening balance from the Balance section.
Deposits: Total of Deposits column in the Data input section.
Withdrawal: Total of Withdrawals column in the data input section.
Closing Balance: This will reflect the closing balance from the Balance section. If the closing balance will not match the closing balance of data input section it will show a remark “Incorrect balance” next to this column.
Reconciliation Amount: This will reflect the opening balance from the Balance section. To easily reconcile the entries you need to put “R” next to the entries.
If any of the entries aren’t reconciled they will be displayed in the outstanding column. The total of the Outstanding amount is displayed in the cell.
Current Balance: This cell will display the ending Balance of account as per the data entered by the user.
Difference: This cell will display the difference if any. If there is a difference then it will display a message “Check Statement” next to the amount. Otherwise, it will display “Balanced” next to the cell.
e) Data Input section
In Data Input section, the user needs to enter the transaction details as and when occur during the period.
It contains the following column headings:
Date: Date of transaction
Transaction Type: Contains the Transaction types like deposits checks issued, checks receipt, ATM, NEFT etc. You can select the type of transaction from the drop down menu.
Trans. Ref: You can put any reference or check numbers in this column.
Description: Description of the payment or receipt
Withdrawals: All the withdrawal entries are to be entered in this column.
Deposits: All the deposit entries are to be entered in this column.
Balance: Balance will show you the balance after each transaction by adding or deducting the deposits and withdrawals.
Reconciliation: In this section, you need to enter “R” if you find the entries in the bank statement.
Balance and Outstanding: No entries are to be made. These cells are given predefined formulas. Balance column show the end balance and Outstanding column shows the non-reconciled entries.
We thank our readers for liking, sharing and following us on different social media platforms, especially Facebook.
If you have any queries or questions, share them in comments below. I will be more than happy to help you.
A very nice piece of work!
Is it possible to mark transactions as cleared, but not reconciled? Thanks.
Everything possible dear. You need to customize the format and insert the formulas and columns for the same.
Is it possible to easily change the units to dollars (to change the pounds sign to dollar signs) on this form? Thanks so much!
Yes, you can change to any currency you want. Dollars, Pounds, Yen any currency. To do this either right click and click on format cell and choose the currency format or by going to currency in the ribbon menu. Thanks
Balance and Outstanding: No entries are to be made. These cells are given predefined formulas. Balance column show the end balance and Outstanding column shows the non-reconciled entries. how we put formulas in chek register in =BALANCE……………Column is Empty till and show balance on ends….if we customize sheet work…thanks if you reply
Hi Luqman, couldn’t understand what you are saying or what you want. Please specify in details what exactly is your query.
Hi bro. m using your template as above for chek book register… here we some new banks accounts but in same excel file when we used copy option for next bank on new sheet the balance formula column shows same figure as we calculate in first sheet(template module). the new banks accounts sheet linked with first sheet only = Balance column???? meanwhile other columns work properly new posting….
my query is just this that how we put Balance column separate in new sheet work in proper i think you understand now better….thanks for reply…
Hi Luqman, I tried doing the same thing that you have mentioned in your query. But in my files, it changes the amount according to the differences. You might be doing a mistake. You might have not changed the opening balance as well as closing balance figures on the top right side of the sheet in the new sheet.
السلام علیکم محترم فہیم صاھب
میرے خیا ل میں آپ میری بات سمجھ گئے ہیں لیکن اظھار مشکل ہے۔ بات یہ ہے کہ میں چیک رجسٹر کو ایک ہی ورک بک میں استعمال کرنا چاہتا ہوں جس میں ہر شیٹ پر نیا بنک اکا ونٹ بنا نا ہے جس پر بیلنس کالم جو ریکینسائل کے بعد والا کالم ہےمیں بیلنس وہ فرسٹ شیٹ سے لیتا ہے اوپننگ بیلنس بھی ٹھیک ہیں اور کلوزنگ بھی جو
اوریجنل ہے دیپازٹ اور ود ڈرال کےبعد والا بیلنس بھی ٹھیک کام کرتا ہے لیکن میری خواہش یہ ہے کہ آپ مجھے یہ بتا دیں کہ اس کی سیٹنگ کیس ہوگی جبکہ اب میں ہر بنک کے لیے علیحدہ علیحدہ ورک بک بنا رہا ہوں کافی دوستوں سے پوچھا لیکن سمجھ نہیں آتا ۔۔۔۔۔ شکریہ
Hi Fahim Lashkaria,
Many thanks for your sharing and teaching. It definitely very useful.
I would like to double check the formula of J11 (The message “Check Statement” next to Difference)
The current formula of J11 is =IF(H11=0,”Balanced!”,IF(H8=0,”Check Statement Balance”,””))
It means that H11=0, then it should be “Balanced”;
However, I don’t understand why H8=0, then it should “Check Statement”
I think H8 is smaller or bigger than 0, then it should “Check Statement”
I cannot get the logic. Would you please further explain it?
Many thanks
Best Regards
Raj
Will check and revert.