Let’s say we have an Excel spreadsheet with sales data for different months of the year. We have information about the number of products we sold in each individual month. But in the next column, we would like to have a running total – a sum of all the products sold up to that month in a year.
Sadly, there isn’t a straighforward function that we could use, so we need to be a little creative with our references. As you probably know, Excel can make absolute references to cells, which means that a formula will reference that specific cell no matter where we copy that formula to. But it can also make mixed references, which means that when we copy the formula elsewhere, either the column or the row number reference will stay the same, while the other will change.
Since we want to add up the values from column B, we will naturally be using the SUM function in column C. Since we always want to start adding up at the value in B2, we will make an absolute reference to that cell, meaning we will type the reference like this – $B$2. You can type the dollar signs manually or you can press the F4 key after clicking on the cell you want to reference.
Now you want the SUM function to add the values up until the appropriate month. For example, cell C8 should only contain the sum of sold products from January to July. Therefore, the second argument of the SUM function needs to “float around” a little bit, so we use something called mixed referencing. The formula will look like this:
What does $B2 mean? It means that no matter where we put the formula, it will always reference column B (that’s what $B means), but the number does not have a dollar sign, which means that the row number reference will change when we copy the formula upwards or downwards – which is exactly what we want.
If we confirm this formula and copy it downwards until the last month in our little spreadsheet, we will notice something interesting:
We can see that every next cell accurately sums up the values up until that month. That is because the second part of the SUM function contains a mixed, not an absolute reference, which in this case adds every next cell we copy the formula to, to the adding up range.