Mixed Reference in Excel
A cell reference or a cell address is a way to define a cell on a worksheet, it is formed by combining a column letter and a row number. To refer any if the cell on a worksheet (in any formula/ to copy the cell) we use cell reference.
Example: To refer to the cell present in row 2 & column D, cell reference is: D2, where D is column letter & 2 is the row number.
Relative Cell Reference :
By default, every cell is a relative reference. Example: If you refer to cell D2 in cell F2, you refer to a cell that is 2 columns to the left (F-D = 3) in the same row number 2.
The relative relation for that location will be updated automatically when it is copied to other positions in a worksheet. (Because a relative cell reference defines an offset to another cell – say, 2 columns left within the same row in the example mentioned above).
If you copy F2 to F4, then F4 = D4 (as F2 = D2, i.e., 2 columns left in the same row), So D4 is two columns left to F4 in the same row 4.
Absolute Cell Reference :
There are times when the cell reference must stay the same when copying or using AutoFill. Using dollar signs, a column and/or row reference can be kept constant. So, to get an absolute reference from a relative, we can use the dollar sign ($) characters.
We use an absolute reference to refer to an actual fixed location on a worksheet whenever copying is done. The reference here is locked such that rows and columns do not shift when copied.
Dollar ($) before row fix the row & before the column fixes the column.
Example: Fixing/Locking both – row & column – Say if we want to lock row 2 & column D, we will use $D$2 as:
F2 = $D$2, when copied to F4, F4 also becomes = $D$2.
Excel Mixed Cell Reference :
A mixed cell reference is a mixture of absolute & relative reference, i.e., an absolute column and relative row, or an absolute row and relative column. To get an absolute column or absolute row, you can put the $ before the column letter or before the row number.
Example: $D2 is relative for row 2 but absolute for column D, and D$2 is absolute for row 2 but relative for column D.
Here Dollar ($) before row number fixes/locks the row & before the column name fixes/locks the column.
Example 1: When we fix the only row: If we have F2 =$D2 then :
We used $ before the column number, so we are locking the only column here.
When F2 is copied to H4, then H4 = $D4 because the column is the same here, i.e., column letter D But the row is relative (i.e. the same row in which copying is done).
Example 2: When we fix /lock the only row : If we have E2 = C2*A$2 then :
When E2 is copied to E3, E3 = C3*A$2 (not C3*A3) because the row (number 2) has been locked already.
I hope you now know the relative and absolute cell references, how to use $ sign and how to use mixed cell references.
Attention reader! Don’t stop learning now. If you are an Excel beginner (or an intermediate) and want to learn Excel, Geeksforgeeks brings the perfect course for you to start, Diving Into Excel