To calculate the running total, we need to combine the simple SUM function with absolute and relative cell references. (By using the $ sign). So in our example, the cell that contains the SUM formula will always contain an absolute cell reference, which anchors the first cell that Excel will sum. In this case, it is $B$2, as the first part of the formula will not change as your copy or drag it down the rest of the rows of data. The second reference in the formula is relative and adjusts as it is dragged or copied down the rows of data expanding the range.

In the Column example, the formula begins with =SUM($B$8:B8), and it can be easily dragged across columns to calculate the running total as easily as the running totals of the rows. The first reference of the formula again contains the absolute reference to anchor the first part of the formula, and the second part being relative.

You can see how the formula progresses in each of the cells as it is dragged when I show the formulas in the cells not the results of the formulas below.

The default and simplest type of cell references in Excel are relative. They are named relative due to the fact that Excel adjusts and changes the cell reference when copied or when using AutoFill and it is RELATIVE to the original cell reference.

How to create and copy an Excel formula using relative cell references.

First of all, we need to write our Excel formula in our first cell, which in this example is C1. If we copy the formula =A1+B1 down from row one to row two then Excel automatically adjusts the formula automatically for us. These, as you can see, are really useful when you need to repeat the same formula across many rows or columns.

 

To copy the formula down just grab the fill handle (the black cross). As an alternative, double-click on the cell that contains your formula to fill down the column of data.

 

Absolute Excel References.

There may be times when you do not want your cell reference to change. For example when you are coping or filling formulas down or across your Excel worksheet. It is in this scenario that you need to know how to use an absolute cell reference. This enables you to keep the row, the column or both constant.

You can change a relative reference to an absolute one by the use of the $ as an addition to the formula.

Here is a summary of where the $ should sit within your cell reference to achieve your constant or absolute cell reference.

The most commonly used absolute cell reference is that which anchors both the column and the row $A$1. Use F4 on your keyboard to make the cell reference absolute. As you will find this is both the quickest and easiest way.

How to create and copy an Excel formula using relative cell references.

Let’s go back to our worksheet we used previously, and add a value in the worksheet which adds 12% Sales Tax to our values in Column C. The Sales Tax value is stored in cell G3. Next, we will need to use the absolute cell reference $G$3 in our formula as each of the formulas will use the same tax rate. Subsequently, want the cell reference it to remain constant when the formula is copied or and filled to other cells in Column C.

To copy the formula down just grab the fill handle (the black cross) over the cells you want to copy the formula to. As an alternative, double-click on the cell that contains your formula to fill down the column of data.

Toggling between relative and absolute cell referencing.

When creating absolute cell references, we do not need to manually type the $ sign. You can hit the F4 key to make a cell reference absolute. Hit the F4 once the whole cell is made constant like above with our Sales Tax. Hit the F4 to make the column constant. With a final click of F4 will make the Row only constant. If you want to convert an existing formula to absolute then hit F2. This will change Excel into Edit mode. Place your cursor next to or in the reference you want to amend.

The sequence of toggling through the absolute cell reference is as follows.

C1 –> $C$1 –> C$1– > $C1– > C1