How to lock cells in Excel? What’s the Excel dollar sign shortcut? How to switch between relative and absolute references?
In this tutorial, we’ll answer all your questions about the famous dollar sign in cells!
First, we need to clarify what an absolute reference is in Excel.
What’s an absolute reference in Excel?
In Excel, an absolute reference is a cell reference that does not change when you copy-paste a formula in another cell. The formula stays the same because you locked it with a dollar sign ($).
For example, if your formula is =$A$1+$B$1 in cell C1, when you copy the formula to C2 it will stay =$A$1+$B$1 and will not become =A2+B2.
Note that you will end with the same result if your formula is =A$1+B$1 in this particular example.
You can mix relative and absolute references within a cell (for example, =$A$1+B1 in C1 will become =$A$1+B2 in C2).
What’s a relative reference in Excel?
By default, a cell reference is relative in Excel. This means the reference logic between two cells will be used the same way for other cells.
If you refer C1 from A1, you refer to a cell that is 2 columns to the left and within the same row. The system keeps the same logic regarding cells reference. If you copy the formula from C1 to C2, the referred cell will be A2.
For example, if your formula is =A1+B1 in cell C1, when you copy the formula to C2 it will change to =A2+B2.
To switch from a relative reference to an absolute one in Microsoft Excel, add a dollar sign before the cell reference.
How to lock cells in Excel?
To lock a cell in Excel (to switch from a relative to an absolute reference) add a dollar sign ($) before cells references.
To do so, just add the $ symbol directly in the formula.
Note that a cell reference contains a column and a row references, if you add $ only before A1 ($A1), it may still become A2 if you paste the formula in another cell. To keep A1 reference in any other formulas change it to $A$1.
Take 30 sec. to watch the video on the right to learn how to do it.
In this example, the goal for the students is to end with the lowest score. We want to give a bonus (-2 points) to the students who score less than 10 points.
Our formula is =IF(score<10,(score-E3),score) with E3=2. This formula works fine for row 2 but if we apply the same formula to rows below, we’ll encounter an issue: E3 will become E4, then E5, etc.
We need E3 to be an absolute reference: let add a dollar sign $ before E and 3. Our formula is now =IF(score<10,(score-$E$3),score) and we can now use our IF function without any issues.
Check this page if you are not familiar with the IF function in Excel!
How did we add dollar signs simultaneously, you ask? What’s the shortcut to lock cells in Excel? It’s fn+F4!
Excel: what’s the dollar sign shortcut?
To lock a cell in Excel, press fn and F4.
We do recommend you to press fn first to avoid any unwanted system actions like closing the window, opening the search window, etc.
Now, you know how to lock cells in MS Excel and how to use the F4 shortcut!
Learn more handy shortcuts and formulas with our Online Course. Join now and receive a free Spreadsheet Cheat Sheet!
