How to Format Double Column Cash Book in Excel

Looking for a way to format a double column cash book in Excel? Then, this is the right place for you. Here, you will find a step-by-step different way to format a double column cash book in Excel.


Download Practice Workbook

You can download the workbook to practice yourself.


What Is Cash Book?

Cash Book is a kind of ledger account that records regular cash and bank transactions in chronological order of a company. There are 4 types of Cash Books.

1. Single Column

2. Double Column with Discount

3. Double Column with Tax

4. Triple Column with Discount and Tax


What Is Double Column Cash Book?

Double Column Cash Book is used to record cash and bank transactions with two columns on each side. It is also known as two column cash book.


Step by Step Procedures to Format Double Column Cash Book in Excel

Here, we will show you how you can format a double column cash book in Excel by going through the following 8 steps.


Step 1: Insert Headers

In the first step, you will have to insert headers to create a double column cash book and then format them in Excel.

To do that, follow the steps given below.

Steps:

  • To begin with, type Serial No, Date, Description, Cash and Bank to create headers.

Insert Headers to Format Double Column Cash Book in Excel

  • Then, select cell range C5:F5 and click on Merge & Center from the Home tab.

  • Now, type Receipts as a header.

  • Similarly, merge & center cell range H5:K5 and type Payments.

  • Again, add another header as Cash Book for January 2022 by merging & centering cell range B4:K4.

  • After that, select the 3 headers below pressing Ctrl on your keyboard.
  • Next, click on the Bold button and set 14 as the Font Size from the Home tab.

Formatting Headers to Format Double Column Cash Book in Excel

  • Further, select the header Cash Book for January 2022 and click on Fill Color from the Home tab.

  • Now, select any color of your own choice. Here, we will select Blue, Accent 1, Darker 50%.

  • Click on Font Color and select any color of your choice. Here, we will select White, Background 1.

  • Similarly, you can format the other headers according to your wish.


Step 2: Create Table to Format in Excel

Next, we will create a table to format a double column cash book in Excel. Go through the steps given below to do it on your own.

Steps:

  • Firstly, select cell range B6:K7.
  • Secondly, go to the Home tab >> click on Borders.

Creating Table to Format Double Column Cash Book in Excel

  • Then, select All Borders.

  • After that, press the keyboard shortcut Ctrl + T to create a table.

Using Keyboard Shortcut to Create Table to Format Double Column Cash Book in Excel

  • Now, the Create Table box will open.
  • Here, you will see that the cell range has already been selected.
  • Afterward, turn on My table has header option.
  • Finally, click on OK.

Opening Create Table Box to Format Double Column Cash Book in Excel

  • Next, go to the Table Design tab >> click on Quick Style >> select Clear.

  • Further, press Ctrl + Shift +L.

  • Thus, a table will be created.

  • Here, to remove the header in Cell G6, select the Cell.

  • After selecting, remove the text by pressing Backspace using your keyboard.
  • In the end, insert a space and then press Enter.

  • Similarly, you can edit the other headers by inserting a space and then pressing Enter.

Read More: How to Create Petty Cash Book Format in Excel (With Easy Steps)


Step 3: Use IF & ROWS Functions to Update Serial Number

Now, we will show you how you can update the serial number using the IF and ROWS function in Excel to format a double column cash book.

Steps:

  • In the beginning, select Cell B7 and insert the following formula.
=IF([@Date]="","",ROWS($A$7:A7))

Use IF & ROWS Functions to Update Serial Number to Format Double Column Cash Book in Excel

  • Then, press Enter.

πŸ”Ž How does the Formula Work?

  • ROWS($A$7:A7)—–> The ROWS function returns the count value of the rows given in the function.

Output: {1}

  • IF([@Date]=””,””,ROWS($A$7:A7))—–> The IF function returns a value if the given condition is TRUE and returns a different value if the condition is FALSE.

IF([@Date]=””,””,1)—–> turns into

Output: {1}

  • Now, if you insert a date in the Date column the Serial No will automatically get updated.


Step 4: Enter Data into Double Column Cash Book

Next, enter your data into the double column cash book you have prepared till now.

Enter Data into Double Column Cash Book in Excel


Step 5: Change Number Format to Format Double Column Cash Book

In the fifth step, we will show you how you can change the Number Format in Excel to format a double column cash book.

Steps:

  • Firstly, select the cell ranges which need a change in the Number Format. Here, we will select cell ranges E7:F11 and J7:K11 to change the Number Format to Currency.
  • After that, go to the Home tab >> click on Number >> click on Number Format.

Change Number Format to Format Double Column Cash Book in Excel

  • Next, select Currency.

  • Thus, you can change Number Format in Excel.


Step 6: Calculate Total Cash & Bank for Receipts in Cash Book in Excel

Now, we will show you how to calculate Total Cash and Bank for Receipts in a double column cash book.

Calculate Total Cash & Bank for Receipts to Format Double Column Cash Book in Excel

Go through the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell N6 and insert the following formula.
=SUM(Table4[Cash])

Calculate Total Cash to Format Double Column Cash Book in Excel

  • Next, press Enter and get the value of Total Cash for Receipts.

Here, in the SUM function, we added all the values of Column 4 named Cash in the table.
  • After that, select Cell N7 and insert the following formula.
=SUM(Table4[Bank])

  • Then, press Enter.

Here, in the SUM function, we added all the values of Column 5 named Bank in the table.
  • Now, select Cell N8 and insert the following formula.
=SUM(N6:N7)

  • Finally, press Enter.

In the SUM function, we added the values of cell range N6:N7.
  • Now, if you add, edit or delete any data in the table for Receipts, the resultant values for Receipts will automatically update.

Step 7: Determine Total Cash and Bank & Payments in Double Column Cash Book

Similarly, you can determine the Total Cash and Bank for payments in a double column cash book following the steps given below.

Determine Total Cash and Bank & Payments in Double Column Cash Book in Excel

Steps:

  • Firstly, select Cell N11 and insert the following formula.
=SUM(Table4[[Cash ]])

Determining Total Cash for Payments to Format Double Column Cash Book in Excel

  • Then, press Enter.

Here, in the SUM function, we added all the values of Column 9 named Cash in the table.
  • Secondly, select Cell N12 and insert the following formula.
=SUM(Table4[[Bank ]])

Determining Total Bank Amount for Payments to Format Double Column Cash Book in Excel

  • Now, press Enter to get the value of Total Bank amount for Payments.

Here, in the SUM function, we added all the values of Column 10 named Bank in the table.
  • After that, select Cell N12 and insert the following formula.
=SUM(N11:N12)

  • Finally, press Enter to get the value of Grand Total for Payments.

In the SUM function, we added the values of cell range N11:N12.
  • Next, if you add, edit or delete any data in the table for Payments, the resultant values for Payments will automatically update.

Step 8: Figure Out Closing Balance of Cash Book in Excel

In the last step, we will figure out the Closing Balance of a double column cash book in Excel.

Steps:

  • To start with, select Cell N15 and insert the formula given below.
=N8-N13

Figure Out Closing Balance to Format Double Column Cash Book in Excel

  • Then, press Enter and get the value of Closing Balance.

In the Formula, we subtracted the Grand Total for Receipts in Cell N8 by the Grand Total for Payments in Cell N13 to get the value of the Closing Balance.

Now, to get a clear vision of the cask book you can turn off the Gridlines from your Excel worksheet.

  • To do that, go to the View tab >> turn off Gridlines.

Turning Off Gridlines to Format Double Column Cash Book in Excel

  • That’s it!! Thus, you can format a double column cash book in Excel.
  • Now, if you add, edit or delete any data in the table the whole dataset will change accordingly.

Double Column Cash Book Format in Excel


Things to Remember

  • All the Cash and Bank received are considered Debits and payments are Credits.
  • Closing Balance is figured out after a specific period of time calculating both total cash and bank amounts for receipts and payments.

Conclusion

So, in this article, we have shown you ways to format a double column cash book in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo