Excel Tip – Add Cumulatively In Excel
[ad_1]
Let's look at creating cumulative total in Excel, also sometimes referred as a running total. Here is a step by step guide.
First, let's set up an example. We can set up a data table with Deposit Values in Column C beginning at C4 through to C9 Ie C4: C9
Deposit Values
15th
16.99
56
89
90
50
We then need a running total which will begin in column D with cell references of D4: D9.
So, we want Excel to begin with a total of 15 in our first cell D4, then add 15 to 16.99 in cell D5 to total 31.99, then add 15, 16.99 and 56 in D6 to total 87.99 and so on down the columns of values .
The formula for this is fairly simple in Excel.
In cell D4 use the keyboard shortcut ALT = for a quick AUTOSUM or, alternatively you can type directly into the cell or the formula bar
= SUM (D4: D4), what we need to do is make the first reference to cell D4 absolute or lock it in place by surrounding the reference in dollar signs like this $ D $ 4. So the final formula for the first cell should look like this-
= SUM ($ D $ 4: D4)
So, when we copy the formula down column D, the reference $ D $ 4 will not move, and the second cell reference in the formula D4 does not have the absolute reference or dollar signs so this is a relative reference and will adjust as it is copied down the column.
All you need to do is drag the formula down the column from D4 to D9 and Excel will populate the cells with the cumulative totals.
By locking the first cell reference we have made an expanding range of cells from D4 to D9. You can view this expanding range if you put your cursor in the middle of the formula in the formula bar at cell D5, you will see that in the formula Excel has included both D4 and D5 in the formula. They will be surrounded in a blue box or highlighted in some way depending on your version of Excel move your cursor down the cell formulas in D5 to D9 to see the range expand.
You total cumulative should equal 316.99 in cell D9. Well done you have set Excel to add cumulatively. Just remember to lock or make your first cell reference absolute in your formula to enable your sum range to expand as you drag or double click your formula down the cumulative totals column.
[ad_2]
Source by BJ Johnston
