When we reference a range, and make the first cell absolute, this range becomes an expanding range.

=$A$2Įxpanding Range (Absolute Starting Cell Reference) Write this formula in E2 and copy E2 in rest of the range. It means when you copy formula, the formula will always refer to frozen cell. When we use $ sign before both, column letter and row number, this become absolute cell reference in excel. Syntax of Freeze Cell Only (Absolute Cell Reference) You will see that, only row 2’s data is copied in all the rows since row 2 is frozen using $ sign. This makes excel to freeze the row and keep columns relative. We use $ sign before row number and leave column number relative. This is because we freeze ($) on the column (A). When you copy towards adjacent right or left, column referencing is not changing. When you copy down the rows are changing. You can see that the only A columns data is being copied in all cells in E2:G6. Copy E2 in the rest of the cells in range E2:G6. This is also called mixed referencing since rows are relative and columns are absolute. When we only want to freeze columns only, we put $ sign before the column letter of the cell. The shortcut to make absolute references is F4 while selecting the range in formula. In range E2:G6 we will test above absolute referencing techniques of excel. So to illustrate the use of all above types of Absolute referencing, I have this 2D matrix in excel. Use the F4 key on the window to toggle between Relative and Absolute References. Or toggle between absolute reference and relative reference Syntax for Frozen Range (Absolute Starting Cell Reference) =$A$1:$A$10 Syntax of Collapsing Range (Absolute Ending Cell Reference) =A1:$A$10 Syntax of Expanding Range (Absolute Starting Cell Reference) =$A$1:A1

Syntax of Freeze Cell (Absolute Cell Reference) =$A$1 Syntax to Freeze Row Only (Absolute Row Reference) =A$1 Syntax to Freeze column only (Absolute Column Reference) In other words, we lock or say freeze the cell or range using absolute referencing. Well, when we don’t want cell references to change relatively while copying them in other cells, we use absolute references in excel. When we prefix the $ sign before a column letter and/or row number that row and/or column number becomes absolute. If not, you must have noticed the $ (dollar) sign before cell references ($A$1). You must have read the word absolute reference.

Absolute cell reference