How to use a mixed reference to create a running total

Another example of a situation where you may need a mixed reference is a running total.

Let's take a look.

With normal totals, you can just sum a range and be done. The SUM function adds together all the values in the range and reports the result. But what if you want to create a running total? Well, you could enter the first total as a custom formula, and then enter the rest of the formulas so that they add the current month to the previous total.

But another option is to set up one formula that you can copy down the entire table. That way, you have fewer unique formulas and less chance of making a mistake.

One way you can do this is to fix the first reference in the range and leave the second reference relative. Starting with the first cell, enter a range that consists of only the first cell that needs to be summed. To build a complete range, we need to add a colon, but both references are the same, initially.

Now we need to convert the first reference in the range to an absolute reference. With the first reference fixed, the formula can be copied down and correctly reports the running total for each month.

This basic approach is the same for horizontal running totals. Enter the range for the starting cell in the first formula, and lock the first reference in the range. Now the formula can be copied across the table and will show the correct running total.

Depending on your particular settings, Excel may add a warning symbol to the cells that contain running totals because they leave out other cells in the area.

To disable this warning, navigate to Options > Formulas

Then uncheck "Formulas that omit other cells in the region".

Although this example is based on the SUM function, you'll see this same approach in many advanced formulas that use a range that needs to expand as the formula is copied to other cells.