Fully Automated Cashbook in Excel [PART 2]

Welcome to PART 2 of creating a fully automated cashbook in Excel. Before we proceed to the further steps, we recommend you to read PART 1 of this guide.

Creating a Fully Automated Cashbook in Excel [PART 1]

Let’s get started with the second part of creating a search box the cashbook and more.

The next step is to create the search box.

• First, add two additional rows to start working.
• Merge and give borders to the cells as in the screenshot below.

• Now, enter Total Debit on Date and Total Credit on Date headings in both the tables.
• Type Cash, Bank in the adjacent cells in both tables.

Let us add some colors to the background of these tables at the top.

• Press and hold CTRL and select all blank cells between and beside the tables.

• Choose a Fill color from the home tab.

Let’s insert the search icon near the search box.

• Go to the Insert tab.
• Click on Illustrations.
• Click Icons.
• Search for magnifying tool and click Insert.

• Change the color of the icon from the Home tab by changing fill colors.

The next step is to apply the SUM formula in the table above display the total debit and credit transactions by cash and bank transfers.

• In the Debit in Cash cell, type =SUM( and select the entries in the Cash column on the Debit side. Close the bracket and hit ENTER.

• In the Debit in Bank cell, type =SUM( and select the entries in the Bank column on the Debit side. Close the bracket and hit ENTER.
• In the Debit Subtotal column, type =SUM( select the entry Debit in Cash, put + and select the entry in Debit in Bank. Or, use the SUM formula here.

Repeat the steps on the credit side as well. Add the cash and bank amounts on the credit side of the cashbook on the right side of the summary above.

Creating a search box

Now, it’s time to make the search bar functional. The search bar will allow us to search for a date that will display the total cash and bank transactions that occurred on that date.

Let’s started with creating the search bar.

We will begin from the debit side of the cashbook.

• We will use the SUMIF formula.
• In the Cash column, type =SUMIF( and select the dates in the Date column in the cashbook.
• Put a comma and select the cell in the search box where you will enter the date.
• Put a comma and select the cash entries from the Cash column in the cashbook.
• Close the brackets and press ENTER.

The selections in your formula should look like this.

Try searching for a date with cash entries on it. The search box should have the total cash entries for that date in the box.

You can see all cash entries are summed for 1^st January 2021.

• In the Bank column, type =SUMIF( and select the dates in the Date column in the cashbook.
• Put a comma and select the cell in the search box where you will enter the date.
• Put a comma and select the cash entries from the Bank column in the cashbook.
• Close the brackets and press ENTER.

Repeat the same steps for the search box on the credit side.

Freezing the summary board

To freeze the summary board with the search box above the cashbook, follow these steps.

• Select the empty row right below the board or between the board and the cashbook.
• Go to the View tab.
• Pull down on Freeze panes.
• Click Freeze Panes.

Conclusion

This article was a PART 2 guide to make a fully automated cashbook in Excel with a fully functional search box. Stay tuned for more informative and unique tutorials like this, on QuickExcel.