To show a running total in an Excel PivotTable, select a value in the Values area, right-click it, and choose Show Values As > Running Total In. Select the field that defines the sequence, such as Month or Date, as the base field; Excel will display each item’s cumulative value rather than its individual value.
What a PivotTable running total shows
A running total adds each value to the values that came before it in a chosen order. If monthly sales are $12,000 in January, $15,000 in February, and $9,000 in March, the running totals are $12,000, $27,000, and $36,000. The February figure includes January and February; March includes all three months.
In a PivotTable, this is a display calculation applied to a value field. It does not change the source data or replace the underlying Sum, Count, or other aggregation. You can still choose a different calculation later, or add another copy of the same value field to show both the original amounts and the cumulative amounts side by side.
A running total is not the same as a grand total. A grand total combines the values in the report, while each running-total row shows the cumulative amount up to that point. The grand total for a running-total column may equal the final cumulative figure, but it is usually not meaningful to add the running-total rows together: that would count earlier values more than once.
Set up a running total in Excel
These steps apply to Excel for Microsoft 365 on Windows. First create a PivotTable with the sequence field in Rows and the numeric field in Values. For a monthly sales report, put Month in Rows and Sales in Values. Excel should summarize Sales by Sum; if it displays Count instead, check that the source sales cells contain numbers rather than text.
- Click a numeric cell in the PivotTable’s value column. Selecting a value cell helps Excel identify which value field to change.
- Right-click and choose Show Values As > Running Total In. You can also open Value Field Settings, select the Show Values As tab, and choose Running Total In from the calculation list.
- In the Base field list, select the row or column field that establishes the sequence. For monthly sales, select Month. For a daily report, select Date.
- Select OK. Check the first few results against the source amounts to confirm the order and accumulation are what you intended.
The base field is essential: it tells Excel which items to accumulate through. It is not the value field being added. If the PivotTable has both Region and Month in Rows, for example, selecting Month as the base field makes the calculation progress through the month items within the report’s layout. With nested fields, inspect the results to confirm that the sequence and any group boundaries match your reporting requirement.
Worked example: monthly sales
Suppose a source table has one row per transaction, with columns for Date, Region, and Sales. The PivotTable groups the dates into months and sums Sales. Before applying the running-total display calculation, the monthly amounts might be January $12,000, February $15,000, and March $9,000. After setting Running Total In with Month as the base field, the displayed values should be $12,000, $27,000, and $36,000.
To retain both views, drag Sales into the Values area a second time. Leave the first instance as Sum and set the second instance to Running Total In. Rename the fields to something clear, such as Monthly Sales and Cumulative Sales, using Value Field Settings or by editing the displayed heading. This makes it easier to compare a single month’s performance with progress to date.
For a report with multiple years, decide whether you want one uninterrupted sequence or a separate year-to-date sequence for each year. A Year field above Month in Rows gives the PivotTable a hierarchy, but the exact display depends on the base field and layout. Check the first month of each year: if it should restart at that year’s amount, confirm that the grouping and base-field setup produce that result. If the requirement is specifically year-to-date, separate the years clearly rather than assuming a monthly running total has the intended reset behavior.
Use dates and month groupings correctly
For reliable date-based totals, the source Date column should contain real Excel dates, not text that merely looks like a date. In a PivotTable, right-click a date item and choose Group to group dates by Months, Years, or another available interval. If the data covers more than one year, include Years as well as Months when grouping; otherwise, January from different years can appear as one combined January item.
Make sure the displayed order is chronological. A true date field usually sorts by date, but month names stored as text can sort alphabetically—for example, April before February—instead of in calendar order. A running total follows the item order in the PivotTable, so an incorrect sort produces a cumulative sequence that is mathematically consistent with the displayed order but wrong for a timeline. Use real dates and date grouping where possible, then verify the order from the first period to the last.
Grouping can fail when the source date field contains blanks, text values, or invalid dates mixed with valid dates. Inspect the source column and correct those entries before grouping. After fixing the source, refresh the PivotTable and try grouping again. If you need daily rather than monthly accumulation, use the individual dates as the base field and ensure the PivotTable is not combining them into months.
Choose the right field and report layout
Pick a base field whose items represent the order in which accumulation should occur. Common choices include Date, Month, Week, or a sequential period number. A category such as Product or Salesperson is usually not a meaningful base field unless you intentionally want a cumulative total through an ordered list of categories. Sorting that list differently will change the apparent progression.
Row and column placement affects how people read the result. A date field in Rows creates a conventional vertical time series; a date field in Columns creates a horizontal progression. The base field must be available as a field in the PivotTable layout. If the field you expect is missing from the Base field list, add it to Rows or Columns, then configure the calculation again.
Filters and slicers can make a report easier to explore, but be clear about the scope of the figures. A cumulative total in a filtered report should be interpreted in the context of the items currently shown and the PivotTable’s setup. For a published report, label the value column and state the selected period or filters so a reader does not mistake a filtered cumulative amount for an all-data total.
Common problems and how to fix them
- The numbers still show monthly amounts. Confirm that you selected a value cell and applied Running Total In to the intended value field. If there are two copies of Sales in Values, you may have changed the other one.
- The Base field list does not show the expected field. Add the sequence field to Rows or Columns, then reopen Value Field Settings. Choose the field that contains the ordered items, not the numeric measure.
- The total jumps or follows an odd order. Check the sort order and look for text-based month labels, missing periods, or unexpected categories. Running totals use the PivotTable’s item sequence.
- Dates will not group. Check the source for blanks, text dates, and invalid entries. Correct them, refresh, and group the dates again.
- New source rows are missing. Use PivotTable Analyze > Refresh. If the new rows fall outside a fixed source range, update it through PivotTable Analyze > Change Data Source. Turning the source range into an Excel Table before creating the PivotTable helps it expand as rows are added, though the PivotTable still needs a refresh.
- The displayed total seems too large. Do not sum the running-total rows. Each row already includes earlier periods. Read the last period as the cumulative total for the full displayed sequence.
Running total versus formulas and other PivotTable calculations
Use the PivotTable’s Running Total In option when you want a cumulative view that stays connected to a PivotTable and can respond to its layout, filters, and refreshed source. It is often simpler than adding a cumulative formula beside a report, especially when the number of periods changes. The trade-off is that the result depends on the PivotTable’s base field and item order, so those settings need to be understood and checked.
A worksheet formula can be a better fit when you need a custom rule—for example, a cumulative calculation that resets under a specific condition, includes only selected transaction types, or combines data from several sources in a way the PivotTable display calculation does not express clearly. Formulas also make the calculation visible cell by cell, but they can require extra maintenance when a report expands or changes shape.
Other Show Values As calculations answer different questions. % Running Total In expresses cumulative progress as a percentage of the total for the chosen base field. % of Grand Total shows each item’s share of the report-wide total, not cumulative progress. A standard Running Total In displays cumulative amounts, so choose the option that matches whether the reader needs a value or a percentage.
Frequently Asked Questions
Can I show the monthly amount and running total together?
Yes. Add the same numeric field to the Values area twice. Keep one instance as Sum and set the other to Show Values As > Running Total In. Give each displayed value a distinct heading.
Why does my running total start at zero or show a blank?
The first item’s cumulative value should normally equal that item’s value. A blank or unexpected first result can indicate blank source values, a different aggregation such as Count, a different base field, or a layout that is not in the expected order. Check the source and the Value Field Settings.
Can a PivotTable running total reset every year?
A year-to-date report needs a clearly defined year boundary. Group dates by both Years and Months, place the fields in a suitable hierarchy, and verify the first month in each year. Do not assume that choosing Month alone will create every desired reset rule; if precise reset behavior is critical, test the layout or use a separate calculation designed for that rule.
Does a running total change my source data?
No. It changes how the PivotTable displays a value field. The original transaction amounts remain unchanged, and you can switch the field back to No Calculation in Value Field Settings.
Can I create a running total in Excel for Mac?
Yes. Excel for Mac also supports PivotTable value display calculations. Select a value in the PivotTable and use its value-field settings to choose Running Total In and a base field. Ribbon placement and menu wording can vary by Mac release, so look for Value Field Settings and its Show Values As options.
Related
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
- Power Query Remove Leading Zeros Without Losing IDs
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Field List Not Showing? Fixes in ExcelIf the PivotTable Field List is missing in Excel for Microsoft 365 on Windows, click any cell inside…
- Excel Gantt Chart Project Plan: Build and Manage ItTo make an Excel Gantt chart project plan, list each task with a start date and duration, put…
- Best Excel Gantt Chart Templates for Every ProjectFor most projects, the Vertex42 Gantt Chart Template is the best place to start: it gives you a…
- How to Create a Sunburst Chart in Excel Step by StepTo generate a Sunburst chart in Excel, arrange your data in columns from the highest level of the…
- Link an Excel Chart to PowerPoint Without Losing UpdatesTo link an Excel chart to PowerPoint in Microsoft 365 on Windows, select the chart in Excel and…
- Excel Chart Horizontal Line: Add a Constant or TargetTo add a horizontal target or constant line to an Excel chart, add a new data series whose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.