How to How to Create Running Total in Pivot Table in Excel in Excel - Practical Guide

Learn to create running totals in Excel pivot tables to display cumulative values across rows or columns. This technique transforms raw data into progressive sums, enabling quick analysis of trends, sales growth, and performance metrics. Running totals provide dynamic insights without manual formula entry, making data interpretation faster and more accurate.

Why This Matters

Running totals are essential for financial analysis, inventory tracking, and performance monitoring, enabling stakeholders to identify trends and growth patterns instantly. This skill eliminates tedious manual calculations and enhances data-driven decision-making.

Prerequisites

•Basic understanding of Excel pivot tables and their structure
•Data source with chronological or sequential arrangement
•Knowledge of pivot table layout (rows, columns, values)

Step-by-Step Instructions

Create or open your pivot table

Go to Insert > Pivot Table (or use existing pivot table). Ensure your data includes date/period fields and numeric values to sum.

Add fields to appropriate areas

Drag date/period field to Rows, product/category to Columns, and numeric values to Values area in the PivotTable Field List.

Right-click the Values field for running total

Right-click any value cell in the pivot table, select Summarize Values By > Sum to confirm sum calculation is active.

Access Show Values As option

Right-click the same value cell again and select Show Values As > Running Total in > [select your row/date field] to enable cumulative calculation.

Verify and format results

Check pivot table displays cumulative values increasing down rows. Format numbers via Design > Format > Accounting or percentage as needed.

Alternative Methods

Using calculated fields with SUBTOTAL

Create a calculated field in the pivot table that uses SUBTOTAL functions to compute running totals without using Show Values As option.

Manual column formula approach

Insert helper columns outside the pivot table using SUM with absolute and relative references to create running totals independently.

Tips & Tricks

✓Sort your data chronologically before creating the pivot table to ensure running totals display logically from earliest to latest.
✓Use percentage running totals (Show Values As > % Running Total) to see what portion of the total each period represents.
✓Refresh pivot table data regularly to update running totals when source data changes (right-click > Refresh).

Pro Tips

★Combine running totals with slicers to dynamically filter data while maintaining cumulative calculations across filtered subsets.
★Use Timeline slicers with running total pivot tables for interactive date-based analysis that updates instantly.
★Create multiple running total columns for different measures to compare growth patterns across KPIs simultaneously.

Troubleshooting

Running Total option is greyed out in Show Values As menu

Ensure the Values field contains numeric data and is set to Sum aggregation. Check that your data includes at least two rows in the pivot table.

Running totals reset at subtotal rows

Running totals naturally reset at group level boundaries; if this is unwanted, use helper columns outside the pivot table with absolute SUM references instead.

Values show as cumulative but don't match expected totals

Verify the base field is correctly selected in Show Values As dialog. Check for hidden rows or filters affecting the calculation base range.

Related Excel Formulas

SUM SUBTOTAL SUMIF CUMSUM

Frequently Asked Questions

Can I create a running total for multiple fields simultaneously?

Yes, you can add multiple value fields to the Values area and apply Show Values As > Running Total to each independently. Each field will display its own cumulative progression.

Will running totals update automatically when I refresh the pivot table?

Yes, running totals recalculate automatically when you refresh the pivot table source data via right-click > Refresh All. The cumulative calculations maintain their logic based on the updated values.

How do I convert a running total back to regular sum view?

Right-click any value cell, select Show Values As > Normal to revert running totals to standard sum display.

Can I use running totals with grouped dates?

Yes, running totals work with grouped dates (daily, monthly, yearly). The cumulative calculation respects the grouping level you've applied to your date field.