top of page

You are learning PivotTables in MS Excel

How to calculate running totals or subtotals in a PivotTable?

There are two ways to calculate running totals in a PivotTable:

1. Using the "Show Values As" Option:

1. Click on any of the value fields in your PivotTable that you want to show a running total for. This will select the entire column.
2. Right-click on the selected value field.
3. In the context menu, select "Value Field Settings."
4. A dialogue box titled "Value Field Settings" will appear.
5. Go to the "Show Values As" tab.
6. From the dropdown menu, choose "Running Total In" or "% Running Total In" depending on whether you want to see the absolute value or the percentage of the total.
7. Click "OK."

This will calculate the running total for each row or column group (depending on your PivotTable layout) and display it in the value field.

2. Using Calculated Fields (For More Complex Scenarios):

1. Go to the "PivotTable Analyze" tab. (This tab might be hidden. If so, right-click anywhere on the PivotTable and check "Show PivotTable Analyze Tab.")
2. In the "Calculations" group, click "Calculated Field."
3. In the "Formula" box, enter the formula for your running total calculation. This will depend on your specific data and desired outcome. A common formula for a running sum would be:

```
Previous Value + Sum([Your Value Field])
```

- Replace "[Your Value Field]" with the actual name of your value field in the PivotTable.
- "Previous Value" refers to the running total from the previous row or column group (depending on your layout). You can achieve this using a special function called EARLIER([Your Value Field]).

4. Click "Name" and give your calculated field a clear and descriptive name.
5. Click "OK."

Your new calculated field will appear in the PivotTable Fields list. You can then drag it to the desired location in your PivotTable to display the running totals.

Tips:

* Running totals typically work best when your PivotTable is sorted chronologically (by date, for example).
* You can also calculate subtotals in PivotTables using similar techniques. Instead of using a running total formula, you would simply sum the values for a specific group level.

bottom of page