How to lock a cell reference in Microsoft Excel
Copying and pasting in Microsoft Excel can be a bit of a minefield if you don’t know what you’re doing. If you use the cut command, you’ll bring a whole new dimension to this discussion.
When copying and pasting a formula in Excel, make sure you know exactly what your new destination cell is referring to. If you don’t lock down or fix your cell reference, the referenced cell will move by exactly the same number of columns and rows as your destination cell from your original cell.
For example, if the copied formula is in cell A1, and your destination cell (the cell you are pasting the copied formula into) is in cell B2, then your new reference cell will also move by one row and one column.
If you don’t want the reference cell to move, Excel provides a simple way for you to lock down either the specific cell or the specific row or column.
Enter your formula in the original cell
Type a simple formula into a cell that references another cell. For example, in cell D1, enter =A1+A2. Cells A1 and A2 are the referenced cells for cell D1.
Lock a specific cell reference
To lock or fix a reference to a particular cell, simply add dollar signs ($) next to the cell reference. For example, if you want to lock the reference to cell A1, you’ll have to type $A$1. Alternatively, move your cursor to the cell reference and press F4 once.
Copy and paste the original cell into another cell
Copy cell D1, the original cell, into cell D3, the destination cell. You will notice that the formula in D3 is =$A$1 + A4. The reference cell A1 remains the same because it is locked down while the original reference cell A2 now becomes A4 as the destination cell is two rows down from the original cell.
Lock a row or column
If you want to fix only the row or the column of the cell reference, simply put the dollar signs next to either the row or the column reference. For example, if you only want to lock the row of the reference cell, type A$1 or press F4 twice. If you only want to lock the column of the reference cell, type $A1 or press F4 three times.
- How to add the activity indicator to the Firefox navigation toolbar
- How to delete your browser’s history when using Internet Explorer
- How to use SocialOomph for Twitter
- How to check the amount of memory your computer has
- How to capture a screen shot in Mac OS X
- How to customize the bookmarks toolbar on the Firefox web browser
- How to freeze top row and first column in Excel
- How to turn on the spell checker in Firefox