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

Step 1 – Create Title for Cash Book

  • Create a title for the cash book.
  • In the following sample, we have used the Merge&Center command for cell B2 to H2 and made a title.

Create Title for Cash Book in Excel


Step 2 – Insert Necessary Data

  • Create the cash book format and enter the daily debit and credit transactions. See the below image as an example.
  • We have put a row to calculate the Cash in Hand from the cash book.

Insert Data to create cash book in Excel


Step 3 – Enter and Calculate Debit

  • Insert all the debited or received money in the cash book as shown in the image

Enter and Calculate Debit to create a cash book in Excel

  • Go to cell D14 and input the SUM function to calculate the arithmetic sum of the received money.
=SUM(D6:D13)

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

SUM function

  • Press ENTER  to get the following output.


Step 4 – Estimate Credit

  • Enter the value under the credited or payment transactions as shown in the image below.

Estimate Credit to create a cash book in Excel

  • Select cell H14 to calculate the sum of the credited balance and enter the formula.
=SUM(H6:H13)

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

  • Press ENTER to get the result.


Step 5 – Evaluate Cash Book

  • Evaluate the remaining balance from the cash book.
  • Go to cell D16 and enter the formula.
=D14-H14

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

Evaluate Cash Book

  • Press ENTER to get the results.


How to Create Double Column Cash Book in Excel

How to Create Double Column Cash Book in Excel

  • For calculating Cash in Hand, go to cell D16 and insert the formula stated below.
=(D14+E14)-(I14+J14)

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.

Calculation to create double column cash book in excel

Press ENTER to get the result.

Read More: Excel Practice Test for Employment


Download Practice Workbook


Related Articles


<< Go Back to Excel for Accounting | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Fahim Shahriyar Dipto
Fahim Shahriyar Dipto

Fahim Shahriyar Dipto is a graduate of Mechanical Engineering at BUET. With over 1.5 years of experience at Exceldemy, he authored 70+ articles on ExcelDemy. He has expertise in designing worksheets at You’ve Got This Math. Currently, He is a Team Leader at Brainor. Dipto's passion extends to exploring various aspects of Excel. Beyond tech, he enjoys creating engaging kids' worksheets using Illustrator. A dedicated employee and innovative content developer, He incorporates a commitment to academic excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo