Although Excel may not be used as heavily in the home as it is in the business, learning this fundamental tip about cell references in formulas will help you avoid mistakes when creating Excel formulas.
Absolute & Relative References are important to know if you copy and paste cells that contain formulas, which is what a lot of us do in the business world to save time. Surprisingly enough, a lot of users are not aware of what we are going to show you, which is why they take longer to create an Excel spreadsheet, or worse, decisions are made using data with errors in the formulas. The best way to teach this is with this example:
.png)
Open a new Excel sheet and type the number 5 in cell A1. In cell B1, enter this formula: =A1*10. The result should be 50. Now copy cell B1, and paste it in cell B2 – just below it. Cell B2 should display a 0. Click on cell B2 and observe the formula. You will notice that the formula no longer refers to cell A1, but instead is pointing to cell A2.
.png)
This is because by default formulas treat cell references as relative to the position of the cell with the formula – in this case, the original formula pointed to the cell to the left of the cell with the formula, and when you copy and paste this cell, it will always refer to the cell to the left, and in most cases, this behaviour is ok.
.png)
For example, if we created a sum formula at the bottom of a list and then copied it across multiple columns, the formulas update to give us a total of each list. If you need to create a formula that refers to a particular cell regardless of where you copy it to, then you need to use an absolute reference. Go back to cell B1 and edit the formula so it looks like this: =$A$1*10.
.png)
Now copy and paste the formula anywhere you like, and again, observe the formula of the pasted cell. You will notice it still points to cell A1, it is an absolute reference, because it has the dollar signs in it. You can use just one $ sign, such as $A1, which will keep the column reference fixed, but the row reference will still be relative to the copied cell.
