While using excel, there may be times when you want to keep the values same while copying formulas. This can be easily done by using certain cell references. When you are constantly using formulas in Excel, it becomes important that you know the differences between the cell references. There are 3 types of cell references: Relative Cell reference, Absolute Cell Reference and Mixed Cell Reference.
By default Excel uses the relative cell reference. In the following example, you can see that in cell D2 the reference of B2 and C2 are both relative.
When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (D9 in our case) and check the formula in formula bar. Cell D9 has the references as B9*C9. Similarly, if you check other cells you will notice that D3 references B3*C3, D4 references B3*B4 and so on.
So, when the cell reference is relative, it automatically changes when you copy it or move it. To simplify it, the cell reference is relative to its location.
When you want certain cell reference to remain unchanged or want to keep a value constant, that is the time you will have to use absolute cell reference. Like in the example below, to find the total, we require the price to be constant and thus we shall make the price cell (B1) absolute by adding a dollar ($) sign before the column name (B) and row name (1) that is $B$1.
When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (C12 in our case) and check the formula in formula bar. You will notice that cell C12 has references as B12*$B$1. The cell reference of quantity changed, whereas the cell reference of price remained unchanged as the cell reference was locked or absolute.
So, when the cell reference is absolute, the value remains unchanged. So when you want your value to remain unchanged then don’t forget to lock your cells.
As the name suggests mixed cell reference is a mix of Relative and Absolute cell references. It helps to keep one variable constant with other one changing. In a mixed cell reference either the row or the column remains unchanged. In the example below, we want the 2nd row to remain unchanged while applying the formula and thus we lock the row only and the not column.
When you press enter and drag the fill handle, you shall notice that the data has been filled automatically. Select any of the automatically filled cell (D9 in our case) and check the formula in formula bar. You will notice that cell D9 has references as B$2*C9. The price remains the same as we have locked the 2nd row.
Mixed cell reference is rarely used, but it plays an important part when you want to keep a single row or column unchanged while copying the formula.
Note: For changing the references you can also use the shortcut key F4.
If you found this article helpful, then share it with your friends and colleagues, and if you have any suggestions, let us know in the comments box below.
Global Association of Risk Professionals, Inc. (GARP®) does not endorse, promote, review or warrant the accuracy of the products or services offered by EduPristine for FRM® related information, nor does it endorse any pass rates claimed by the provider. Further, GARP® is not responsible for any fees or costs paid by the user to EduPristine nor is GARP® responsible for any fees or costs of any person or entity providing any services to EduPristine Study Program. FRM®, GARP® and Global Association of Risk Professionals®, are trademarks owned by the Global Association of Risk Professionals, Inc
CFA Institute does not endorse, promote, or warrant the accuracy or quality of the products or services offered by EduPristine. CFA Institute, CFA®, Claritas® and Chartered Financial Analyst® are trademarks owned by CFA Institute.
Utmost care has been taken to ensure that there is no copyright violation or infringement in any of our content. Still, in case you feel that there is any copyright violation of any kind please send a mail to firstname.lastname@example.org and we will rectify it.
2015 © Edupristine. ALL Rights Reserved.