How to copy expressions without changing cell references in Excel

Microsoft Excel updates cell references when you copy an expression. Here are some solutions for those rare occasions when you don’t want to change cell references.

woman working on a spreadsheet

Photo: AndreyPopov, Getty Images/iStockphoto

Microsoft Excel has useful behavior regarding copying expressions. After you enter a row or column of expressions, you can copy them, and Excel will update the cell references accordingly. Otherwise, you will have to enter all the expressions manually and that will be tedious and open to error. However, it’s not uncommon to run into a situation where you don’t want to update references. There is no easy built-in way to do this. In this article, I’ll show you two quick solutions to copy expressions without updating cell references.

We see: 60 Excel Tips Every User Should Master (TechRepublic)

I’m using Microsoft 365 on a 64-bit Window, but this works on older versions. You can work with your own data or download the .xls demo file. Both methods work in the browser.

How does transcription work with expressions in Excel

When you copy an expression using the fill handle, Excel updates the relative cell references. For example, if you copy the simple expression =B3/C3 one cell to the right, Excel will adjust the column reference and enter the expression =C3/D3. If the column reference is absolute ($), Excel will change nothing, copy = $B3/$C3. However, if you copy the expression down one row, Excel updates the row reference because it is not absolute: =$B4/$C4. The same behavior is triggered whether you use Ctrl + C to copy or move the expression. This behavior makes it difficult—impossible—to copy an expression when you don’t want to update cell references unless all of those references are absolute: =$B$3/$C$3.

We see: TechRepublic Premium Editorial Calendar: IT Policies, Checklists, Toolkits and Research to Download (TechRepublic Premium)

The easy way to handle a single expression in Excel

If you have a few expressions to copy, but keep the original references, you can use the formula bar in edit mode. It’s fast, easy and gets the job done. We can illustrate this using the simple data set shown in Figure A As follows:

  1. Select D3.
  2. Go to the formula bar and highlight the entire expression.
  3. Press Ctrl + C.
  4. Press Esc.
  5. Select the destination cell F3 and press Ctrl + V.

As you can see in the dataset on the right in Figure A, both expressions refer to the same cells, B3 and C3. If you’re working in a browser, you’ll need to clear the ‘character’ in the destination cell before it evaluates the expression.

Figure A

excelcopyexp-a.jpg

Let’s copy the expressions in column D without updating the cell references.

This method is quick and easy but works with one or several expressions. If you have a lot of expressions, this method will take a long time. Now, let’s look at a second method that takes more work but works with a lot of expressions at once.

The hard way deals with many expressions in Excel

If you want to make the same change to many instances of the same content, what tool do you usually use? Replace – that’s right! This is the tool we will use to copy several expressions without changing the cell references at the same time:

  • First we will add a special character to the beginning of the specified expressions using = as the search string and # as the replacement string Excel will treat the expressions as text
  • Next, we will copy the expressions to the new destination.
  • Finally, we will replace the special character we used in step 1 with the = character.

We see: How to display expressions that return a specific error value in Microsoft Excel (TechRepublic)

Now, let’s run through a quick example using the expressions in column D:

  1. Select D3: D8.
  2. Press Ctrl + h or choose Replace from the Find & Select dropdown menu in the Editing group (on the Home tab)
  3. Enter = in the search for the control.
  4. Enter # in “Replace With” (Figure B). You don’t have to use #; You can use any other character mostly but use unused character or you may get unexpected results. That’s why I’m thinking #. If # is in use, choose another character or even a string of characters, such as AAA or ZZZ.
  5. Click Replace All.
  6. Click OK to confirm and close.

as you see in Figure CExcel treats all specified expressions as text.

Figure B

excelcopyexp-b.jpg

Enter search and replace characters.

Figure C

excelcopyexp-c.jpg

Excel displays expressions as text.

Using your preferred copy-and-paste method, copy the contents of D3:D8 to F3:F8. Now it’s time to replace the # character with the = character. To do this, repeat the above instructions, but in step 3 enter # and in step 4 enter =, inverting the original search strings. When you’re done, you’ll have identical expressions in columns D and F (figure d). You can select both groups by creating a non-contiguous selection, or you can run the Replace task twice.

figure d

excelcopyexp-d.jpg

With a few easy steps, I copied a bunch of expressions without changing the cell references.

see also