How to drag or copy formula and lock the cell value in Excel?
Normally, when selecting a formula cell and dragging it down to fill the cells below, the cell references in the formula will be changed automatically. If you want to make the formula constant without changing with the cell, you need to convert the cell references to absolute. This article can help you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
For locking the cell reference of a single formula cell, the F4 key can help you easily.
Select the formula cell, click on one of the cell reference in the Formula Bar, and press the F4 key. Then the selected cell reference is locked.
Go ahead to lock the other cell references of current formula with the same step above.
The Convert Refers utility of Kutools for Excel can not only convert all cell references of a single cell to absolute at once, but also convert all cell references of a range of formula cells to absolute at once. Please do as follows.
1. Select the formula cell (or a range of formula cells) which you need to lock the value, then click Kutools > Convert Refers. See screenshot:
2. In the Convert Formula References dialog box, select the To absolute option and then click the OK button.
Then values of selected formula cells are locked, and they won’t be changed with dragging or copying to another cells.
The Exact Copy utility of Kutools for Excel helps you copy all formulas of selected cells to another cells without adjusting the relative cell references. Please do as follows.
1. Select the formula cells you need to copy to another cell without adjusting the relative cell references, then click Kutools > Exact Copy. See screenshot:
2. In the Exact Formula Copy dialog box, check the Copy formatting box as you need, and then click the OK button.
3. In the second Exact Formula Copy dialog box, select a blank cell for pasting the copied formula cells, and then click the OK button. See screenshot:
Then the selected formula cells are pasted to a new range without changing any relative cell reference. See screenshot:
Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 60 days. Download the free trial now!
- How to lock all cell references in formulas at once in Excel?
- How to lock or protect cells after data entry or input in Excel?
- How to lock or unlock cells based on values in another cell in Excel?
- How to lock picture/image to or inside cell in Excel?