top of page

Absolute and Relative Cell Reference in Excel | Microsoft Excel

Using a function or writing a formula in excel is a manual activity unless it is optimized. Functions are one of the things that make excel spreadsheets a more powerful tool. Each time user writes a formula, excel stores that formula as relative reference unless they are made absolute explicitly by the user. The practical use and importance of relative cell referencing and absolute cell referencing needs to be understood if one want to be more efficient in excel.


Whenever user writes a formula in excel and want to copy and paste the formula to other cells (formulas which contains reference to other cell/range) instead of writing formula again and again for every item, one thing has to be kept in mind is that- whether he/she want to move the reference cell or range contained in the formula to other cell/range? --If not, then we have to fix the cells (i.e., change the relative cell references to absolute cell reference) so as to copy and paste the formulas to other cells.


For example, we have a formula in cell A3 which looks like-

=A1+A2

If we copy the cell A3 to B3, then the resultant formula in B3 would look like-

=B1+B2

Excel actually thinks of these cells ( A1 & A2 ) are relative to cell A3 and therefore, when user copy and paste the formula from cell A3 to B3, excel applies the same logic and the relative cells for B3 becomes ( B1 & B2 ). This logic applied moving to the right. Similarly, the logic is true when user copy and paste to the other 3 sides too ( B2 & B3 if the formula is copy and pasted to cell B4 )


Sometimes, user don't want to move the relative cells referred in the formula while copy and pasting the formula from any particular cell (say 'Sales Price' which would be same for different 'Quantity'). In such cases, one has to change those relative cell references to absolute cell reference by including Dollar($) sign (i.e., for 'Sales Price" cell).



Basic Rule for Absolute and Relative Cell Referencing are-


To fix the column - put Dollar ($) sign before the Column Alphabet. [ $A1 ]:

For example, user has sales price in cell A1 (same for all customers) and sales quantity in A2, B2, C2 and so on (different quantity for different customers). If the formula in cell A3 is =A1*A2 and user copy and paste the formula to the right in cell B3, C3 and so on, the resultant formula would be =B1*B2 in cell B3, =C1*C2 in cell C3 which is incorrect and we don't want to refer cells B1 and C1 as the Sales Price is available in cell A1 applicable to all the customers.

Therefore, we change the relative cell reference for A1 to absolute by including a Dollar($) sign- $A1 and the formula looks like- $A1*A2. Now if we copy and paste the formula to the right, the resultant formula will look like-

=$A1*B2 and =$A1*C2, respectively.


To fix the row - put Dollar ($) sign before the Row Number. [ A$1 ]:

For example, user has sales price in cell A1 (same for all customers) and sales quantity in B1, B2, B3 and so on (different quantity for different customers). If the formula in cell C1 is =A1*B1 and user copy and paste the formula to the down in cell C2, C3 and so on, the resultant formula would be =A2*B2 in cell C2, =A3*B3 in cell C3 which is incorrect and we don't want to refer cells A2 and A3 as the Sales Price is available in cell A1 applicable to all the customers.

Therefore, we change the relative cell reference for A1 to absolute by including a Dollar($) sign- A$1 and the formula looks like- A$1*B1. Now if we copy and paste the formula to the right, the resultant formula will look like-

=A$1*B2 and =A$1*B3, respectively.


To fix both column and row - put Dollar ($) sign before both Column Alphabet & Row Number. [ say $A$1 ]:

For example, user has sales price in cell A1 (same for all customers) and sales quantity in B1, B2, B3, E1, E2, E3 and so on in different columns of excel spreadsheet (different quantity for different customers). If the formula in cell C1 is =A1*B1 and user copy and paste the formula to the down in cell C2, C3 and right in cel F1, F2, F3 so on, the resultant formula would be =A2*B2 in cell C2, =A3*B3 in cell C3, =D1*E1 in cell F1, =D2*E2 in cell F2 and =D3*E3 in cell F3 which is incorrect and we don't want to refer cells A2, A3, D1, D2 and D3 as the Sales Price is available in cell A1 applicable to all the customers.

Therefore, we change the relative cell reference for A1 to absolute by including a Dollar($) sign- $A$1 and the formula looks like- $A$1*B1. Now if we copy and paste the formula to the right, the resultant formula will look like-

=$A$1*B2, =$A$1*B3, =$A$1*E1, =$A$1*E2, =$A$1*E3, respectively.


Note: When a user cut and paste a formula from one cell to another cell, absolute and relative cell references with in the formula behaves the same i.e., they won't change.

For example, we have a formula in cell A3 which looks like-

=A1+A2

If we cut and paste the cell from A3 to B3, then the resultant formula in B3 will be the same-

=A1+A2

 

Disclaimer: We, in our example, only cover how to perform such functions in excel and less focusing on the type of example we take. Our motive is to deliver the exact solution to the queries on "How To" in the most simplest way. Of course, the application of these function can be seen in our advanced modules with more complex examples and datasets.

 

Feel free to share your views and ask for models in the #comment section. If you like our content, please hit a #like button.

46 views0 comments
bottom of page