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.
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.
- Then select Short Date from the drop-down list.
- Next, follow the same steps for column C and select Accounting from the same drop-down list.
- 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.
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.
- 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.
- The pivot table will show up like this in the spreadsheet.
Read More: How to Create Account Statement in Excel (with Easy Steps)
Similar Readings
- How to Edit Bank Statement in Excel (with Easy Steps)
- How to Maintain Accounts in Excel Sheet Format (4 Templates)
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.
- 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.
- 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.
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.
Now all of the dates from only March will be available in the pivot table.
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.
- 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.
- 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.
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.
