Skip to main content

How to keep formula cell reference constant in Excel?

Author: Siluvia Last Modified: 2024-11-20

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 screenshot below shows two different types of cell references in the formulas for calculating the total cost of items:
  • 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

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.

  1. Click on the cell that contains the formula.
  2. In the formula bar, place the cursor within the cell reference you want to make constant. Here I choose the reference F3.
  3. 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.

keep the discount rate linked to cell

Note: Each press of the F4 key cycles through different reference states: absolute ($A$1), mixed absolute-column and relative-row ($A1), mixed relative-column and absolute-row (A$1), and back to relative (A1).

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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:

  1. Select a range or multiple ranges containing formulas that you want to make all cell references constant.
  2. Choose the To absolute option and then click OK to start converting.
    Choose the To absolute option in the dialog box

Then all cell references for formulas in the selected range are immediately changed to constant references.

Note: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Demo: Keep formula cell reference constant with Kutools for Excel

 

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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!