How to Use Pivot Table to Calculate Running Total by Date in Excel

The article will show some ways to calculate running total by date in Excel using the Pivot Table. Suppose you have data where you have sales on individual dates. But you want to see the cumulative sum of sales on each date. That’s where you need the Excel Pivot Table to see the running total by date.

In the picture below, you will see the sales and profits of a company on different dates.

How to Use Pivot Table to Calculate Running Total by Date in Excel: 3 Quick Ways

1. Using Value Field Settings to Create Running Total by Date

Let’s say we want to see the running totals of sales on individual dates. We can follow the description below.

Steps:

• First, select the range B4:D12 and go to Insert >> Pivot Table.
• You will see a dialog box showing up, just click Ok.

• Now, a new sheet will open, where you can see the Pivot Table fields and Areas at the right side of it.

• Drag the Dates field into the Rows.

• You will see a Months section show up in the Rows are automatically. As we want to see running totals by date, we removed the Months

• Now drag the Sales field two times in the Values area consecutively. You will see two sections- Sum of Sales and Sum of Sales2 in that area.

• After that, select Sum of Sales2 and go to Value Field Settings.

• You will see the Value Field Settings Give your running total column a name. In this case, I wrote the name Running Total of Sales in the Custom Name section.
• Choose Show Values As after that.
• Click on the arrow of the Show Values As section and then select Running Total In.

• Now choose the Base Field as Date and click Ok.

• You will now see the individual sales and corresponding cumulative sales in the Pivot Table.

Thus you can calculate running totals by date using the Pivot Table feature.

2. Grouping Dates into Month in Pivot Table to Create Running Total

We can also calculate running totals by grouping dates into months. We want to see the running totals of sales by this method. Let’s discuss the process below.

Steps:

• Create a Pivot Table for running totals of Sales and Dates, please go through Section 1.
• Select cells A4 to C4 in the Pivot Table and go to PivotTable Analyze >> Group >> Group Selection 

• Set the date in the Grouping dialog box in the ‘Auto’ section and choose Month from the ‘By’
• Click OK.

• After that, go to Running Total of Sales in the ‘Values’ area and select Value Field Settings…

• Then choose Show Values As, select Running Total In from the Show Values As section and set the Base field to Dates.
• Click OK.

• Now you will see the running totals of sales in individual dates grouped into months.

Thus you can calculate the running total by Pivot Table.

3. Using Excel Pivot Table and DAX to Calculate a Running Total by Date

Another way to calculate the running total by date in the Pivot Table is to use DAX. We want to see the running totals of profits at this time. The description of the process is given below.

Steps:

• First, select the cells B4:D12 and go to Insert >> Pivot Table

• A dialog box will appear, then select Add this data to Data Model and click OK.

• You will see Pivot Table fields and areas at the right side of the sheet.
• Here the Table name is Range. Right-click on it. You will then select Add Measure.

• A window will appear.
• Give a name in the Measure Name section (In this case its Running Total of Profits)
• Type the following Formula.

• Set the Number Format to Currency and choose as many decimal points as you want.
• Click OK.

Here we calculate the running total of profits by comparing the dates and their corresponding profits. We use the FILTER function to filter the dates.

• Now drag the Date Field to the Area of Rows.

• Select Profit and fx Running Total of Profits from Pivot Table Fields.

You will now see the running total of the profits that the company made on the corresponding dates.

By following this method, you can effectively use the Excel Pivot Table to calculate the running total by date.

Practice Section

In this section, I gave you the dataset that we used to explain these methods so that you can practice on your own.

Download Practice Workbook

Conclusion

The article explains how to calculate the running total in Excel by date using the Pivot Table. If you have any better methods or ideas or any feedback, please leave them in the comment box. This will help me enrich my upcoming articles.

<< Go Back to Pivot Table Running Total | Pivot Table Calculations | Pivot Table in Excel | Learn Excel