Hello Excellers. I have decided to go Back To Basics today for today’s #formulafriday blog post and take a look at relative and absolute references in Excel. Why would I do that?. Well, a colleague of mine asked me a question last week. It was not anything special or help to do anything wizardry in Excel. Just a good solid question that once they know the answer saved them a tonne of time. It probably relieved a stress headache or two.
They wanted to know how to use relative and absolute references in Excel. He didn’t call them that. But after he explained he wanted ‘one value on one cell not to move when he filled in the rest of the column with the formula’. To me that sounded like he needed some tips on cell referencing with Excel.
Relative cell references.
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) 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.
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. All you need to do is hit 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. Once you hit the F4 once the whole cell is made constant like above with our Sales Tax. By hitting the F4 again the Column only is made 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
That’s it. The two types of cell references that Excel has.
If you want more Excel and VBA tips then sign up to my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series. Click on the link below
