Exceljet

Quick, clean, and to the point

Pivot table running total

Pivot tables have a built-in feature to calculate running totals. In the example shown, a pivot table is used group data by month and show both the monthly total and running total over a 6-month period.

Fields

The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Sales.

Field list for running total pivot table

The Date field has been added as a Row field, then grouped by Months:

Date grouping for running total pivot table

The Sales field has been added twice as a Value field. The first instance is a simple sum, and has been renamed "Total":

Total field settings

The second instance is renamed "Running" and set to calculate a running total based on the Date field:

Running total field settings

Helper column alternative

This example uses automatic date grouping. As an alternative, you can add a helper column to the source data, and use a formula to extract the month name. Then add the Month field to the pivot table directly.

Steps to make this pivot table

  1. Create a pivot table
  2. Add Date field to Rows area, group by Months
  3. Add Sales field Values area
    1. Rename to "Total"
    2. Summarize by Sum
  4. Add Sales field Values area
    1. Rename to "Running"
    2. Show value as running total
    3. Set base field to Date

Pivot Table Training

If you use Excel, but don't know how to use Pivot Tables, you're missing out...wasting time trying to do things that a Pivot Table can do for you automatically. Core Pivot is a step-by-step Excel video course that will teach you everything you need to know to use this powerful tool. With a small investment, Pivot Tables will pay you back again and again. See details here.