How to Keep a Formula on the Same Cell in Excel?

Do you want to keep a formula in the same cell in Excel rather than having it automatically copied to other cells? There are a few different ways to accomplish this depending on your specific needs. In this article, we’ll explain how to lock a formula to a specific cell using absolute cell references, named ranges, the TABLE function, and other methods. By the end, you’ll know exactly how to keep a formula from moving in Excel when you copy it.

Understanding Relative vs. Absolute Cell References

By default, when you copy a formula in Excel to another cell, it adjusts the cell references relative to the new location. For example, if you have a formula in cell A1 that references cell B1, and you copy that formula to cell A2, the reference will change to B2. This is called a relative cell reference.

However, sometimes you want the reference to always point to the same cell, regardless of where you copy the formula. This is called an absolute cell reference. To create an absolute reference, put a dollar sign ($) before the column and/or row in the cell reference.

For example, $B$1 will always refer to cell B1, no matter where you copy the formula. $B1 will keep the column locked but allow the row to change relative to where you copy it. B$1 will keep the row locked but allow the column to change.

Here’s how it looks:

So to keep a formula referring to the same cell when you copy it, simply make the reference absolute by adding dollar signs.

Using Named Ranges to Keep a Formula Pointing to the same Cell

Another way to keep a formula pointing to the same cell(s) is to use a named range. This allows you to assign a name to a cell or range of cells, and then use that name in your formulas.

To define a named range:

1. Select the cell(s) you want to name
2. Go to the Formulas tab on the ribbon
3. Click Define Name in the Defined Names group
4. Enter a name for the range
5. Click OK

Now you can use that name in your formulas instead of cell references. For example, if you named cell B1 as “price”, you could use this formula:

=price

This will always refer to cell B1, regardless of where you copy the formula.

Named ranges are especially useful if you need to reference the same cell(s) in multiple formulas throughout your spreadsheet. If the location of the data changes, you only need to update the named range definition instead of changing many individual formulas.

Using The Table Function to Keep Formulas from Changing

If your data is in a table format, you can use structured references with the TABLE function to create formulas that don’t change when copied.

When you format your data as a table (Insert > Table), Excel automatically assigns names to each column based on the header row. You can then reference the column names in your formulas.

For example, if you had a table with columns named “Price” and “Quantity”, you could use this formula to multiply them:

=TABLE[[Price]:[Price]]*TABLE[[Quantity]:[Quantity]]

When you copy this formula within the table, the references will remain the same. The TABLE function specifies that you want to use the column names from the table the formula is currently in.

Note that you must use the special [column]:[column] syntax for the ranges inside the TABLE function, not just the column name by itself.

Using The INDIRECT Function to Prevent Formulas from Changing

The INDIRECT function allows you to specify a cell reference with a string. This can be useful for creating references that won’t change when copied.

The syntax is:

=INDIRECT(“cell_reference”)

Where “cell_reference” is a string specifying the cell you want to reference, in quotes.

For example:
=INDIRECT(“B1”)

This will always refer to cell B1, even if the formula is copied elsewhere.

You can also combine INDIRECT with other functions like ADDRESS to dynamically create the cell reference string. For example:

=INDIRECT(ADDRESS(1,2))

This is equivalent to =INDIRECT(“B1”).

The downside of INDIRECT is that it’s a volatile function, meaning it recalculates every time Excel recalculates, even if nothing that affects it has changed. This can slow down calculation time for large, complex spreadsheets. So use it sparingly.

Paste Special Options

Another way to control how formulas behave when copied is through Excel’s Paste Special options. Normally when you copy and paste cells, Excel pastes the formulas with relative references. But you can change this.

After copying your original cell(s), right-click on the destination and choose Paste Special. This brings up a dialog box with several options:

• Formulas – Pastes the formulas as-is, with relative references (default)
• Values – Pastes the current values from the copied cells, with no formulas
• Formats – Pastes only the formatting (fonts, colors, etc.), with no contents
• All – Equivalent to a regular paste (formulas, values, formatting)

There are also options to perform mathematical operations (+, -, *, /) on the values being pasted.

Under the Operation options is also a checkbox labeled “Skip blanks”. Checking this will tell Excel to not overwrite existing contents in the destination range with blank cells from the copied range.

Using the Values option allows you to copy the results of formulas without copying the formulas themselves. The pasted values won’t change if you modify the original data.

Referencing Other Sheets or Workbooks

Sometimes you may need to reference a cell in another worksheet or even a different workbook. Excel allows 3D references to handle this.

To reference a cell in another sheet in the same workbook, use the syntax:

=’SheetName’!CellAddress

For example, =SUM(‘Jan Sales’!B1:B10) will add up the values in cells B1 through B10 on the “Jan Sales” sheet.

To reference a cell in a different workbook, you also need to include the workbook name or path:

='[WorkbookName]SheetName’!CellAddress

For example, ='[Budget.xlsx]Summary’!D15 will refer to cell D15 on the “Summary” sheet in the “Budget.xlsx” workbook. The workbook name goes in square brackets, then the sheet name, an exclamation point, and the cell address.

Cells referenced this way will not change if the formula is copied within the current sheet. However, if you copy the formula to a different sheet or workbook, it will try to adjust the reference accordingly.

To prevent this, you can use the same absolute reference technique discussed earlier, putting dollar signs before each part of the reference you don’t want to change when copied:

='[WorkbookName]SheetName’!$CellAddress
='[WorkbookName]$SheetName’!CellAddress
=’$[WorkbookName]SheetName’!CellAddress

Final Thoughts

As you can see, there are several ways to control how cell references behave when copying formulas in Excel. Absolute cell references ($A$1), named ranges, table references and INDIRECT are the most common methods to keep a formula pointing to the same cell(s) no matter where it’s copied.

We recommend named ranges for important data points referenced in multiple places, the TABLE function for structured data tables, and absolute references in most other cases. INDIRECT is a bit more advanced and can slow down calculation, so reserve it for special cases.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.