Running Total and Percent of Total in One View

Excel Formulas › Dashboards & Reporting

Two columns turn a plain list of numbers into a report: a running total that accumulates down the rows, and each row's percent of the grand total.

Functions used (tap for the full reference guide):

The example

Monthly sales that we want to accumulate and express as a share of the total.

The formula

The trick is the mixed reference: lock the start of the range but let the end grow as you fill down:

How it works

How each column works:

1. In the running-total column, SUM($B$2:B2) sums from the fixed first cell down to the current row.
2. As you fill down, the range expands: row 3 sums B2:B3, row 4 sums B2:B4, and so on.
3. For percent of total, divide each row by the grand total with a fully locked range: =B2/SUM($B$2:$B$8).
4. Format the share column as Percentage; the values add up to 100%.

Sort the list descending first and the running-total percent becomes a Pareto (80/20) view.

Try it: interactive demo

Variations

Running total that resets each year

Use SUMIFS keyed to the year so the total restarts.

Percent of a category subtotal

Divide by a SUMIF for the row's group instead of the grand total.

Pitfalls & errors

Practice workbook

Frequently asked questions

Function references: SUM