In Microsoft Excel there is a formula which can be used to provide a consistent reference point to a particular cell, even if this formula is copied or moved around elsewhere throughout the Excel worksheet. To do this, you need to create an absolute cell reference.
An absolute cell reference in Excel can be described as a “cell’s address” which contains a $ sign in the row or column, or both. When this cell reference is entered as part of a formula, Microsoft Excel automatically assumes that it is a relative reference unless you manually change it to an absolute reference.
So, if you want part of a formula to stay as a relative reference then all you need to do is delete the $ sign which appears before a row number or column letter.
Creating an Absolute Reference in Microsoft Excel
It is very simple to do this on the worksheet and it’s perhaps one of the easiest formulas in the entire Microsoft Excel program.
1. Click on an empty cell where you want to insert a formula.
2. To begin the formula, type an equals sign.
3. With your cell selected, type an arithmetic operator (plus +, minus -, divide /, or multiply *.)
4. Select another cell and hit the F4 key to turn that cell’s reference into an absolute reference. There are lots of different reference types and you can keep pressing F4 to cycle through them all.
5. If you need to, keep entering the cell reference formula and then hit the ENTER key.
That’s it.
Absolute references are ideal when you are working with lots of information for a dataset, with a good example being stocktaking and inventory counts for shops which sell a physical product. Some of the data in the dataset here would include price, quantity and total price.
Changing an Absolute Reference Back to a Relative Reference
You can easily change an absolute reference back to its original form as a relative reference. To do this, click on the cell where you have inserted an absolute reference and just keep pressing F4 until all $ signs disappear from the reference.
Like we said above, it is pressing F4 which cycles through all of Microsoft Excel’s different reference types. If, for example, you inserted your formula in B1 you would press F4 for $B$1, again for B$1, again for $B1 and again to get back to B1.
Other Reference Types: Relative and Mixed
Cell references means the cell to which another cell is pointing or referring to. If you put =A1 in cell D7 then cell D7 is referring to A1. These make more sense when we create formulas with them. If, for example, you want to make cell A3 display the sum of the values in cells A1 and A2 you would put the formula =A1+A2 in cell A3.
There are three main types of reference. Absolute we have already covered, but there are relative and mixed reference types, too.
Relative references are the default reference type. When relative reference types are copied to another cell or multiple cells then they will change based on where they are copied to. The above example of A1+A2 is an example of a very simple relative reference type. Relative references are very useful when you need to repeat the same thing, such as a calculation, across several columns or rows.
Absolute references, as above, are ones which don’t change when they are copied to another cell or range of cells.
Mixed references are either an absolute column or relative row, or relative column and absolute row. When you add in the $ symbol before a column’s letter you are creating an absolute column, and when you add it after the letter but before the number, you are creating an absolute row.
