Fully Automated Cashbook in Excel [PART 1]

This tutorial is a detailed guide to creating a fully automated cashbook in Excel. If you are an Accountant or any kind of financial accounts handler whose job is to prepare daybooks or cashbooks, then this tutorial is for you!

This guide collectively teaches you to create a fully automated cashbook in Excel that allows you to effortlessly enter the financial debit or credit entries and total the two transactions by cash and bank transfer.

Creating a fully automated cashbook in Excel

Let’s get started with creating your first fully automated cashbook from scratch. Follow the steps below to learn how.

Create a new blank workbook.
Click on a cell in the 10^th or 12^th row of the sheet.
Enter the headings of a cashbook in 5 cells. SR.NO, Date, Particulars, Cash, and Bank.
Now, select the cells above the Date, Particulars, Cash, and Bank headings and click Merge & Center in the home tab.
Type Debit in the merged cell.
Now, copy this entire table, leave one cell to the right, and paste the table in that cell. Rename Debit to Credit.
Insert a new column between the SR.NO and Date heading.
Reduce the column size from the top.

Now, press and hold on CTRL and select the cells in the tables.
Add borders, bold the text, and choose to fill color from the home tab.
Now, enter a dummy entry in the Debit table.

Select the entry area in both tables by pressing and holding CTRL.
Now, select the two tables except the Debit and Credit titles. You DON’T have to hold CTRL this time, the selection can include empty cells too.
Press CTRL+T on your keyboard.
Enable My table has headers.
Click OK. Don’t click away yet!

Your data is converted to a tabular format now.

Without clicking away, go to the Table Design tab.
Select a table style here. These styles will reflect only in the area where you have not applied colors manually.
Go to the Data tab and click on Filter to remove all filters from the table.

For the headers named Column1 and Column2.
Click on the cell with the heading, and press SPACEBAR to make the cell empty.
If a value still shows in the cell, enter double or triple spaces in it to make it blank.
Do the same for duplicate headers below. For example, put a space after Date in the cell displaying Date3.

We will change the date formats for the date entries.

Select the two date entries and right-click on one of them.
Click Format Cells.
Go to the Custom tab.
Select the dd-mmm-yy format from the list. The date will be displayed in 01-Jan-21 format.
Click OK.

Now, irrespective of the format you enter the date in, the cell will display the date only in this applied format.

Notice that any new entry you make in a blank cell below the last entry acquires the table formatting automatically.

The next step is to format the Sr. no column to automatically add the next number in the series for a new entry.

Follow the steps carefully here.

Click on the first cell under the SR. NO heading.
Remove all contents (if any).

We will use the IF and ROWS functions to achieve this.

Type =IF( and click on the first date entry cell and type =””,””,

After the comma, type ROWS( and click on the left adjacent cell to the current cell.
Put a colon and that cell address will repeat itself.
Now, select the first cell address in the ROWS function, and press F4 or Fn+F4 to lock the cell address.
Close the bracket twice and hit ENTER.

Try adding a new date in the table and check if the serial numbers display automatically.

It is time to create the upper automated portion of the cashbook where we can search entries in the cashbook and display the total debit and credit entries in cash and bank transfers. Let’s begin now!

Go to the top of the workbook.
Select a few cells in the second row and click Merge & Center in the home tab.
Type your company’s name in this cell.
Select 3 cells below the merged cell and merge and center these cells as well.

Type Month: January 2021 in this cell.
Merge the cells beside this cell and type Cashbook in it.
Leave one row below the Month cell.
And Merge Across three cells vertically.
Select these three cells and then three more cells from the adjacent column and apply borders to it.

Type Debit Subtotal, Debit in Cash, Debit in Bank in the three cells on the left.

Repeat the same steps for the Credit side of the cashbook.

Type Credit Subtotal, Credit in Cash, Credit in Bank in the three cells on the left side.

Select the cells in the middle and Merge & Center them. Type Summary in this cell. Apply the formatting according to your preferences.

The further steps are to create a search button for the Debit and Credit entries to search them in the table below in detail.

Conclusion

This tutorial was everything about creating a fully automated cashbook in Excel that allows you to effortlessly enter the financial debit or credit entries and total the two transactions by cash and bank transfer.

Part 2 is about creating a search option to view the total debt or credit entries for a specific date. Stay tuned!