Mixed Reference Running Total in Excel

This guide shows you how to build a running total in Excel using a mixed reference—a cell reference that locks either the column or the row (like $A1 or A$1). By anchoring the start of your range while letting the end move, you create a formula that adds each new value to everything above it. You’ll need a column of numbers and a blank column for the totals.

What You’ll Need

• Microsoft Excel (2010 or later, including Microsoft 365) or Google Sheets
• A column of numeric values (e.g., sales, expenses, scores)
• A blank column where the running total will appear

Step-by-Step

1. Open your spreadsheet and locate the column with the values you want to total. For this example, assume values are in column A, rows 2 through 10 (row 1 has headers).
2. Click the cell where you want the first running total to appear—say, cell B2.
3. Enter the formula =SUM($A$2:A2). The dollar signs lock the first cell reference ($A$2) so it won’t change when you copy the formula down. The second reference (A2) is relative, so it will adjust as you fill down.
4. Press Enter to see the first total. For row 2, it equals the value in A2 (e.g., 100).
5. Copy the formula down by dragging the fill handle (the small square at the cell’s bottom-right corner) from B2 down to B10. Alternatively, double-click the fill handle to auto-fill if adjacent columns have data.
6. Check the results: In B3, the formula becomes =SUM($A$2:A3), which sums A2 and A3. In B4, it sums A2:A4, and so on. Each cell shows the cumulative total up to that row.
7. Format the totals as needed—select column B, right-click, choose Format Cells, and pick a number format (e.g., Number, Currency, or Accounting).

Common Mistakes

• Using absolute references for both ends: =SUM($A$2:$A$2) will always return just the first value, not a running total.
• Using relative references for both ends: =SUM(A2:A2) will only sum the current row, not the cumulative range.
• Starting the range at the wrong row: If your data starts in row 2 but you use $A$1, you might include a header or an empty cell, causing errors or wrong totals.
• Copying the formula with Ctrl+C and pasting into a non-adjacent column can shift the relative reference unexpectedly—always paste into the same column.
• Forgetting to lock the column letter if you plan to copy the formula horizontally as well (e.g., for multiple value columns). Use $A2 to lock the column but allow the row to change.

Tips

• Use a table (Insert > Table) to make the formula auto-fill as you add new rows. The mixed reference will still work, but you’ll need to adjust the range to the table column name if you use structured references.
• For a cleaner look, consider using =SUM($A$2:A2) and then hiding the intermediate columns if you only need the final total.
• If your data has blanks, the running total will still work—blank cells are treated as 0.
• To reset the running total by category, use a formula like =IF(A2="Category", SUM($A$2:A2), SUM($A$2:A2)-SUMIF($A$2:A2, "Previous", $B$1:B1))—but that’s more complex. For most cases, a simple mixed reference is enough.
• Use the keyboard shortcut Ctrl+D to fill down after selecting the range from the first cell to the last.

Troubleshooting

• #VALUE! error: This usually means your range includes non-numeric text. Check that all values in column A are numbers. If you have a header, start the range at row 2.
• #NAME? error: This appears if you mistyped the function name (e.g., SUMM). Re-type the formula as =SUM($A$2:A2).
• Running total doesn’t update when you change a value: This can happen if calculation is set to manual. Go to Formulas > Calculation Options and select Automatic.
• Totals are off by one row: Check that the first formula is in the row right below your first value. If your data starts in row 2, the first total should be in B2, not B1.
• Dragging the fill handle doesn’t copy the formula: Sometimes Excel interprets the drag as a fill series. Hold the Ctrl key while dragging to force copy.
• In Google Sheets, the same formula works, but if you get a circular reference warning, ensure the formula range doesn’t include the cell itself (e.g., don’t put the formula in column A).

Frequently Asked Questions

What is a mixed reference exactly?

A mixed reference locks either the column or the row with a dollar sign. $A2 locks column A but allows the row to change; A$2 locks row 2 but allows the column to change. In a running total, we lock the starting row and column ($A$2) but let the ending reference move down.

Can I use this for a running total across columns instead of rows?

Yes. If your values are in row 2, columns A through Z, you can put the first total in cell A3 and use =SUM($A$2:A2)—but note that the relative reference will move horizontally. To run across columns, use =SUM($A$2:A2) in B3, then copy to the right. The $A$2 stays fixed, while A2 becomes B2, C2, etc.

Why does my running total show the same number in every row?

This happens if you used an absolute reference for both ends, like =SUM($A$2:$A$2). That always sums just the first cell. Change the second reference to be relative (no dollar signs) so it moves with each row.

How do I make the running total stop at a certain row?

Simply don’t copy the formula below that row. If you’re using a table, you can delete the formula from rows beyond your data. For a dynamic range, consider using =SUM($A$2:A2) and then filter or hide rows—the total will still calculate for visible rows only if you use SUBTOTAL instead, but that’s a different method.

Does this work in Google Sheets?

Yes, the syntax is identical. Enter =SUM($A$2:A2) in the first cell and drag down. Google Sheets also supports the same mixed reference logic.

Can I use a running total with filtered data?

The simple SUM formula ignores filters—it sums all rows, even hidden ones. To get a running total that respects filters, use =SUBTOTAL(9, $A$2:A2) instead. The 9 tells SUBTOTAL to sum, and it will ignore rows hidden by a filter.

Related

• Cheat Engine Guide: How to Use It
• 2XKO Sidekick Fuse: How It Works & When to Use
• Why AI Agents Must Show Their Work to Earn Trust