Setting up a running total in an Excel table is a little tricky because it’s not obvious how to use structured references. This is because structured references provide a notation for current row, but not for first row in a column. Let’s explore a few options, starting with formulas based 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 the SUM function instead. 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. To do this, I would enter a reference to the first cell twice, then lock the first reference with F4. 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. In pseudo-code, what we need is a range that starts with the first row in a column and ends at the current row. 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 the INDEX function. For array, we use the entire column. For row_number, I use 1. Now, the key to understanding this is to realize that INDEX is actually returning the address to cell first cell in the column, not the value. 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. The only downside to this formula is that it will be slow on very large data sets, since every formula has to generate a sum of all cells above. If performance becomes an issue, you may want to switch to the first formula we tried in the video, and manually update the formula, if you need to insert rows.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
