How to lock a cell reference in Microsoft Excel

Make copying and pasting formulas easier by locking cell references

photo by

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.

Instructions

Step 1

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.

Step 2

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.

Step 3

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.

Step 4

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.

Things Needed

Microsoft Excel

An Excel formula that references another cell

Tips & Warnings

Pressing the keyboard shortcut, F2, will allow you to edit the cell or show you the formula in the cell.

Always double check to make sure your formulas are referring to the right cell.

	Excel 2007 All-In-One Desk Reference For Dummies by: Greg Harvey Amazon Price: $16.16 Used Price: $10.99
	Microsoft Excel 2010 In Depth by: Bill Jelen Amazon Price: $23.51 Used Price: $23.19