Daily Bank Balance with a PivotTable

When you download banking activity, you typically get the transactions, but not the daily bank balance. To compute the daily bank balance, you need to summarize the transactions by day and display a running total for all days, even those without any activity. This post demonstrates how to build such a report using a PivotTable.

Overview

Before we dig into the technical details, let’s be clear about our objective. We have downloaded some banking activity and manually entered the beginning bank balance as of 1/1/2017. This is illustrated below.

20161020c

We’d like to build a report that displays one row for each day of the month, even if there are no transactions for that day. Plus, the report should show a running total that represents the daily bank balance. Essentially, we’d like to build a report that looks like this.

20161020b

Let’s get to it.

Details

Building such a report can be accomplished with a PivotTable. I’ll walk through each step below. But, if you are already familiar with PivotTables, the short answer is this: group by month and day, show items with no data, and show values as a running total.

We’ll build this report using the following steps.

  • Basic PivotTable
  • Display all days
  • Running total
  • Cosmetics

Basic PivotTable

Let’s get the basic PivotTable set up. Select any cell in the data source range, and Insert > PivotTable. We place the report on a new worksheet and click OK.

In the PivotTable Fields panel, we check the Date and Amount checkboxes. At this point, we have a basic PivotTable, as shown below.

20161020d

Note: Excel 2016 for Windows automatically groups date fields, and if you are using a version prior to Excel 2016 for Windows, your report may look like this instead:

20161020g

If it does, no worries, the steps below will work just fine 🙂

The biggest problem with our report is that it only displays days with activity. But, our report needs to include one row for every day of the month, even those without any transactions. So, the next task is to display all days.

Display all days

We want to display all days, even those without any data transactions. To do this, the date field needs to be grouped. We want our report grouped by month and day. So, we, right-click any date cell in the report, and select Group.

This displays the Grouping dialog, where we pick Months and Days, as shown below.

20161020h

We click OK and our report now looks like this:

20161020f

Now, we open the Field Settings dialog for the Date field (not the Months field). An easy way to do this is to right-click any day in the report, and select Field Settings. In the resulting Field Settings dialog, we head to the Layout & Print tab. There, we find a checkbox named Show items with no data. We check it, as shown below.

20161020i

We click OK and wow! We now see one report row for each day, as shown below.

20161020j

Note: if you group the date field by month, then you can show months with no data. If you group by day, then you can show days with no data. That is, the Show items with no data checkbox can be applied to the desired date group field.

We are getting closer! At this point, we need to add a running total column.

Running Total

Adding a running total column is not too bad. We begin by inserting the Amount column into the Values layout area again. An easy way to do this is to click-and-drag the Amount field into the Values area.

20161020-a

Now we have two Amount columns in the report. We’ll leave the first one as is, and we’ll convert the second Amount column into the running total. To do this, we right-click any value cell in the second Amount column, and select Show Values As > Running Total In. In the resulting Show Values As dialog, we accept the default base field (Date) and click OK.

Bam, our report now includes a running total column, as shown below.

20161020k

Our basic report is done, now we just need to clean it up and make it pretty.

Cosmetics

I’ll go ahead and summarize the steps I used to format the report, but, formatting is personal preference so feel free to skip these or do any other type of formatting you prefer.

  • First, we filter out any undesired report rows. To do this, we select the filter control on the Row Labels header, and uncheck all but Jan.
  • Next, we change the report style to Tabular by using the PivotTable Tools > Report Layout > Show in Tabular Form command icon.
  • Then, we update the style to one we like by using the PivotTable Tools Style gallery (I went with Pivot Style Medium 13).
  • Update the number formatting by right-clicking any value cell and selecting Number Format. Do that once for each data column.
  • Update the report headers from Sum of Amount to Daily Total, and from Sum of Amount2 to Running Total by selecting each label cell and typing in the new label.

And, the resulting report is shown below.

20161020b

We did it…our running total reflects the daily bank balance!

If you have any other fun PivotTable ideas, please share by posting a comment below.

Additional Resources

 

This article was written by Jeff Lenning

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.