This concise guide demonstrates how to efficiently calculate running totals (cumulative sums) in Excel using a SUM formula with strategically placed absolute and relative cell references. A running total displays the accumulated sum of a dataset as it grows, updating with each new addition. This is useful for tracking scores, sales, bank balances, and more.

Calculating Running Totals in Excel

To calculate a running total for values in column B, starting from B2, enter this formula in C2 and copy it down:

=SUM($B$2:B2)

The $B$2 portion (absolute reference) remains constant, always referencing cell B2. The B2 (relative reference) adjusts as the formula is copied, expanding the summation range. Thus, in C3, the formula becomes =SUM($B$2:B3), and so on.

This method also works for tracking bank balances (positive for deposits, negative for withdrawals). Enter values in column C, and use this formula in column D:

=SUM($C$2:C2)

Handling Blank Cells

To prevent displaying the last calculated total in rows below the last data entry, use this improved formula:

=IF(C2="","",SUM($C$2:C2))

This checks if C2 is empty; if so, it returns a blank cell; otherwise, it calculates the running total.

Creating a Cumulative Chart

Once the running total is calculated:

1. Select your data (including the running total column).
2. Insert a 2-D clustered column chart (via the Insert tab).
3. Select the running total data series.
4. Right-click and choose "Change Series Chart Type...".
5. Select "Combo" chart type and choose a line chart for the running total series (e.g., "Line with Markers").
6. Click "OK". Add data labels (optional) by right-clicking the line and selecting "Add Data Labels".

Customize chart titles, legends, and styles as needed. This creates a visual representation of your cumulative data.

The above is the detailed content of How to do a running total in Excel (Cumulative Sum formula). For more information, please follow other related articles on the PHP Chinese website!