Let’s explore a few options, starting withformulasbased on regular references.
One common approach is to simply add the row above to the value in the current row…
But this will throw an error because the column header contains text.
An easy workaround in this case is to use theSUM functioninstead.
Because SUM automatically treats text as zero.
And, this formula works.
But, there is a problem.
If I add a new row, the formula correctly expands.
If I insert a row in the middle of the table, the formula doesn’t work properly.
So, with this formula, I’ll need to fix the formula manually, if I insert rows.
Another common approach for running totals is to use an expanding reference inside SUM.
The result is a reference that expands as it travels down the table.
At first, this seems to work fine.
If I insert a row, the formula adjusts correctly.
However, notice if I add a new row, the formula becomes corrupted.
As before, I’ll need to fix this manually if I add new rows.
Finally, I’ll create an expanding range using structured references.
Getting the current row is easy.
If I click into the column, Excel create’s the reference with the @ notation.
But what about the first row?
The key is to use theINDEX function.
Forarray, we use the entire column.
Forrow_number, I use 1.
In other words, the range resolves to the same formula we used earlier.
But this time, the formula can handle both inserted rows and new rows.
