Sometimes you need to create a running total.
The most versatile solution I know is using the SUM function and a dynamic range which in our case is $B$2:B2. However, the most important fact is that we have to lock (F4 key) the first part of this range (Fig. 1).
=SUM($B$2:B2)
When we copy our formula down, we can see that the first part of the range will always refer to cell B2, but the second one will go down as we drag the formula. This way, we have a range that expands as we go down. It’s the most versatile solution (Fig. 2).
=SUM($B$2:B4)
This solution has problems with Excel tables. When we sum from F2 to F2 cells and press F4 key to lock it, everything looks fine. However, when we add new data to our table, the last cell will expand because it refers to the whole column July -> August (Fig. 3).
In this case, we have to modify our range. Instead of cell F2 we can use the table nomenclature. When we click cell F2, Excel will refer to this table row, where @ means this table row, and Income means the column we are referring to (Fig. 4).
=SUM($F$2:[@Income])
We have to overwrite all cells (Fig. 5).
This way we got proper results. We can check if it works properly by adding some new rows. After we added two rows, the results are still correct (Fig. 6).
The only drawback of this formula is that we won’t see the whole range because Excel won’t select the whole range. Theoretically, the last cell should include the whole column but Excel selects only the first and the last row (Fig. 7).
=SUM($F$2:[@Income])
We won’t see it, but Excel will. We have to remember that Excel will create a proper reference and we can create a proper running total, even in Excel tables.