How to Create a Daily Bank Balance Report Format in Excel (Step-by-Step Procedure)

The primary approach for creating a daily bank balance report in Excel involves utilizing the Pivot Table feature. Typically, data is stored in a spreadsheet alongside dates. However, not all dates are included in the chart as part of the dataset. To track every transaction and balance at the end of each day, we’ll use a pivot table. This approach also addresses the issue of handling multiple transactions within a single day.

Step 1 – Prepare Headers for the Report

Create headers to identify data types. This facilitates data entry and helps other users understand the dataset. Here are the basic dataset headers.

Step 2 – Format Cells for the Dataset

Format the cells in columns B, C, and D (which will contain our dataset).
For column B, select the header and go to the Home tab.

Click the drop-down icon next to General in the Number group.

Choose Short Date from the list.

Repeat the same steps for column C, selecting Accounting from the drop-down list.

Column D doesn’t require specific formatting since it will contain text.

Step 3 – Insert Data

Enter data into the spreadsheet. Excel will automatically record it, creating a daily bank balance dataset for the report.
Use sample data for demonstration purposes.

Step 4 – Create a Pivot Table

Select any cell within the dataset.
Go to the Insert tab on the ribbon.
Choose PivotTable from the Tables group.

In the pop-up box, select the desired destination for the pivot table and click OK.

The pivot table options will be available on a new sheet.
Drag the Date field to the Rows area and the Inclusive Amount to the Values area.

Step 5 – Group Dates in the Pivot Table

Right-click on any date in the pivot table.
Select Group from the context menu.

Configure the Grouping settings (e.g., grouping by month).

Click OK.

Step 6 – Display Blank Values Within the Month

Right-click on any date in the pivot table.
Choose Field Settings from the context menu.

In the Field Settings box, go to the Layout & Print tab.
Check Show items with no data under Layout options.

The final result will display months as groups after clicking OK.

Step 7 – Remove Unwanted Months

In the previous step, we noticed that all dates for the entire year are included in the pivot table. However, our dataset only contains values from the month of March. To address this, we’ll hide dates corresponding to other months.

Click on the filter button (the downward-facing arrow) next to the column heading of the dates in the pivot table.
While any of the month cells is selected in the column, uncheck the undesired months and dates from the selection.