How to Create Daily Bank Balance Report Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

Financial statements are important for any organization, both for them and individuals. Excel, being the excellent data storing and analysis software it is, we can easily create bank balance reports along with other types of trackers in Excel. This article will focus on creating a daily bank balance report format in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


Step-by-Step Procedure to Create Daily Bank Balance Report Format in Excel

The main idea to create a daily bank balance report in Excel is to utilize the pivot table feature. Generally, it is common to store data in a spreadsheet along with dates. But not all dates are included in the chart as a dataset. So to track every transaction and balance at the end of each day, we are going to use a pivot table. Also, it alleviates the problem of multiple transactions in a single day.

For the demonstration, we will be going through the very beginning and keep on going to the very last. You can find the required steps from the table above if the starting steps are too basic for you or if you want to start from a particular one.


Step 1: Prepare Headers for Report

First, we need to create headers to identify data types later on. This helps further data entry in the sheet and other users to understand the dataset.

These are the headers for our basic dataset.

headers for daily bank balance report format in excel


Step 2: Format Cells for Dataset

Next, we will format all of the cells in the columns that will contain our dataset. In this case, these are columns B, C, and D.

  • First, select column B by clicking on the header on top of the spreadsheet.

  • Then go to the Home tab on your ribbon.
  • After that, click on the drop-down icon beside General from the Number group.

formatting cells for daily bank balance report format in excel

  • Then select Short Date from the drop-down list.

short dates format in daily bank balance report format in excel

  • Next, follow the same steps for column C and select Accounting from the same drop-down list.

accounting format in daily bank balance report format in excel

  • Column D doesn’t particularly need any formatting as we will be using text here.

Step 3: Insert Data

Once you have formatted the cells described in the previous step, you can easily just enter data in the spreadsheet. Excel will automatically record it and create a daily bank balance dataset that we will use to create a report format.

So insert all of the data at this point in the dataset. We have chosen these sample data for the demonstration.

dataset for daily bank balance report format in excel


Step 4: Create Pivot Table

As we have mentioned earlier, we are going to use the pivot table feature in Excel to create the daily bank balance report format. To create a pivot table from the dataset from the previous step, follow these steps.

  • First, select any cell from the dataset.
  • Then go to the Insert tab on your ribbon.
  • After that select PivotTable from the Tables group.

inserting pivot table

  • Once you have done that, another box will pop up. Excel will select the whole table/range will be automatically here. Just select the destination of the pivot table you want from this box and click on OK.

  • As we have selected a new worksheet for the pivot table, it will create a new sheet and the pivot table options will be available there.
  • Now click and drag the Date field to the Rows area and the Inclusive Amount to the Values area.

pivot tables for daily bank balance report format in excel

  • The pivot table will show up like this in the spreadsheet.

initial pivot table

Read More: How to Create Account Statement in Excel (with Easy Steps)


Similar Readings


Step 5: Group Dates in Pivot Table

Now we will group all the dates along with the month for the daily bank balance report format in the Excel spreadsheet containing the pivot table.

  • First, right-click on any of the dates.
  • Second, select Group from the context menu.

  • Then select the particulars of the Grouping box as shown in the figure below.

grouping options for daily bank balance report format in excel

  • After that, click on OK.

The final result after that will look like the below. We have just added months as the group.


Step 6: Display Blank Values Within Month

We want all of the dates in the daily bank balance report format in the Excel pivot table. But clearly, it wasn’t like that in the results from the previous step. We will make some modifications to this.

  • First, right-click on any of the dates on the pivot table.
  • Then select Field Settings from the context menu.

selecting field settings in daily bank balance report format in excel

  • As a result, the Field Settings box will pop up.
  • Now go to the Layout & Print tab in the box.
  • Then check the Show items with no data under the Layout options.

  • After clicking on OK, it will look something like this.

all dates in daily bank balance report format in excel

Read More: Monthly Bank Reconciliation Statement Format in Excel


Step 7: Remove Unwanted Months

As we can see from the result in the previous step, all of the dates in the year are included in the pivot table. But our dataset only contains values from the month of march.

So we need to hide all of the dates that belong to the other months.

  • For that, click on the filter button with the downward-facing arrow beside the column heading of the dates in the pivot table. It is better to click on it while any of the month cells is selected in the column.
  • Then uncheck all of the undesired months and dates from the selection.

removing unwanted fields

Now all of the dates from only March will be available in the pivot table.

required fields only in daily bank balance report format in excel


Step 8: Add Running Total Balance

Now let’s add a running total column beside the pivot table. This will help track the amount available in the account after each day.

  • First, click and drag the Inclusive Amount field to the Values area again.

adding running totals in daily bank balance report format in excel

  • Now the pivot table will look like this.

  • Next, right-click on any of the cells in the third column.
  • Then hover your mouse cursor over the Show Value As option from the context menu.
  • After that, select Running Total In.

formatting running total for daily bank balance report format in excel

  • In the next box, select Date and click on OK.

Finally, it will look like this now.

After some modifications, the final daily bank balance report format in Excel will look like this now.

daily bank balance report format in excel


Conclusion

And that is how we can easily create a daily bank balance report format in Excel. Hopefully, you have grasped the idea of using the pivot table for daily balance report formats and can use it to create your own formats now. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo