How to keep formula cell reference constant in Excel?
In this tutorial, we will demonstrate how to change a cell reference in a formula to a constant (absolute) reference to prevent it from changing when the formula is copied elsewhere.
- The formula on the left incorrectly shifts the discount rate reference down each row. When copying from D3 to D4 and D5, it references F4 and F5 instead of the fixed discount rate in F3, leading to incorrect calculations in D4 and D5.
- On the right side of the screenshot, $F$3 is a constant (absolute) reference. It keeps the discount rate linked to cell F3, no matter where the formula is copied within the workbook. Therefore, copying the formula down the column keeps the discount rate consistent for all items.
Keep formula cell reference constant with the F4 key
To maintain a constant cell reference in a formula, simply add the $ symbol before the column letter and row number by pressing the F4 key. Here’s how to do it.
- Click on the cell that contains the formula.
- In the formula bar, place the cursor within the cell reference you want to make constant. Here I choose the reference F3.
- Press the F4 key to toggle through the reference type until you reach the absolute reference, which adds a dollar sign ($) before the column letter and the row number. Here F3 will be changed to $F$3. Then press Enter to confirm the change.
Now, it keeps the discount rate linked to cell F3, no matter where the formula is copied within the workbook.
A few clicks to make all cell references absolute in ranges
The above method helps to handle cells one by one. If you want to apply absolute reference to multiple cells at once, here highly recommend the Convert Refers feature of Kutools for Excel. With this feature, you can easily make all cell references in a cell range or multiple cell ranges absolute in a few clicks.
After installing Kutools for Excel, go to the Kutools tab, select More > Convert Refers to open the Convert Formula References dialog box. Then you need to:
- Select a range or multiple ranges containing formulas that you want to make all cell references constant.
- Choose the To absolute option and then click OK to start converting.
Then all cell references for formulas in the selected range are immediately changed to constant references.
Demo: Keep formula cell reference constant with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!