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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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?