How to Create a Cash Book in Excel (With Easy Steps)

Microsoft Excel contains a plethora of powerful calculation formulas that allow us to estimate and evaluate the basic accounting needs in our daily lives. A cash book is one kind of accounting chore where we put each transaction through cash or the bank. By doing this, we can easily track debits and credits and eventually determine the remaining balance. When it comes to creating a cash book, Excel shines with its enormous features. In this article, we’re gonna demonstrate the steps to create a cash book in Excel. We think it might be helpful for doing your daily work with ease. So, let’s get started with it.

Introduction to Cash Book

The cash book is a financial record book where you can record your daily cash in and out. It is a subsidiary accounting ledger used to track cash transactions. The general ledger is updated by the accountant with the cashbook entries. Besides, the cash book contains debit and credit sides where you can input your cash receipts and your cash payment. There are several types of cash books. We have discussed some types here.

Single Column Cash Book

A single-column cash book contains only one cash column, which means you have to enter one cash column for debit or receipts and another cash column for credit or payment. Thus, you can simply calculate the cash inward and outward from the single column and maintain your cashbook.

Double Column Cash Book

A Two-column cash book or a double-column cash book has an extra column i.e. bank transactions to the single-column cash book. This type of cash book has an extra benefit as it shows your cash transactions along with bank transactions when the period has ended.

Triple Column Cash Book with Tax and Discounts

A cash book with discounts and VAT is designed for people whose everyday cash transactions involve providing and receiving discounts as well as collecting or paying taxes. For discounts and taxes, there are additional columns in this kind of book.

Petty Cash Book

Whenever someone needs to record his small/little amount of cash he may use a petty cash book. A little cash may not be ideal to go with the cheques. So, you can keep frequent, quick, and small payments and receipts in the petty cash book.

How to Create a Daily Cash Book in Excel: 5 Steps

In this section, you’ll explore making a daily cash book in Excel. Then you’ll see the process in the case of a double-column cash book. To create the cash book, you have to follow some simple steps. We have demonstrated to you the steps here for your better visualization.

Step 1: Create Title for Cash Book

Firstly, you need to create a title for your cash book.
Here, we have used the Merge&Center command for cell B2 to H2 and made a title. We also put the month of September 2022 as we want to make a cash book for September.

Step 2: Insert Necessary Data

In this step, you need to create your cash book format as your preference and then enter your daily debit and credit transactions there. See our below image as an example.
Furthermore, we have put a row to calculate the Cash in Hand from our cash book.

Step 3: Enter and Calculate Debit

Initially, insert all your debited or received money in your cash book, just like the image shown below.

Secondly, go to cell D14 and input the SUM function to calculate the arithmetic sum of your received money.

=SUM(D6:D13)

It will add all the values from your D6:D13 cell.

Press ENTER and you will get the following output.

Step 4: Estimate Credit

In the very beginning, we entered the value under our credited or payment transactions. You will add yours just like the image below.

Secondly, select cell H14 to calculate the sum of your credited balance and enter the formula.

=SUM(H6:H13)

This function will calculate the total of H6:H13 cells.

Press ENTER and get the result.

Step 5: Evaluate Cash Book

Lastly, we need to evaluate the remaining balance from our cash book.
Thus, we will go to cell D16 and enter the formula.

=D14-H14

It will deduct the H14 value from the D14 debited total.

Consequently, you will get the result after pressing ENTER.

How to Create Double Column Cash Book in Excel

As we said earlier, a double-column cash book contains two cash columns on both sides of debit and credit. You can calculate your transactions side by side here. It helps you to keep your cash record pretty organized. Here we have discussed and shown the double-column cash book where you can calculate your cash in hand. Follow the below steps.

Initially, do the Step 1-4 as mentioned while creating the daily cash book. Then add a new column, namely Bank as shown in the below image.

For calculating Cash in Hand, go to cell D16 and insert the formula stated below.

=(D14+E14)-(I14+J14)

Here, D14, E14, I14, and J14 cells represent the arithmetic sum of the Cash and Bank columns of debit and credit, respectively.

It will add both the two columns of debit and credit. Then, subtract them to get the final output.

Finally, you will get the result.

Benefits of Using Cash Book in Excel

Basically, a cash book helps you track your financial transactions such as income, cash flow, expenditure, etc. Both individuals and companies must maintain a cash book to track their business’s financial statements. Furthermore, we can do diverse calculations using the created cashbook as Excel offers a lot of tools. For example, we can use a Progress Pie Chart to show the Categories of Debit. Look at the image shown below.

The image above depicts a percentage breakdown of your debited transactions. It will help you figure out the cash flow most smoothly. As a result, Excel shines with its feature that improves the accuracy and precision of your business statements.

Read More: Excel Practice Test for Employment

Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.

Creating a Cash Book.xlsx

Conclusion

That’s all about today’s session. These are some easy steps to create a cash book in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding, please download the practice sheet. Thanks for your patience in reading this article.

<< Go Back to Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!