How to copy expressions without changing cell references in Excel

Microsoft Excel has a helpful behavior in regard to copying expressions. After entering a row or column of expressions, you can copy them, and Excel will update the cell references according. Otherwise, you’d have to enter all expressions manually and that would be tedious and open to error. However, it’s not uncommon to run up against a situation when you won’t want to update the references. There’s no easy built-in way to do this. In this article, I’ll show you two quick workarounds for copying expressions without updating the cell references.

SEE: 60 Excel tips every user should master (TechRepublic)

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

How copy works with expressions in Excel

When you copy an expression by using the fill handle, Excel updates relative cell references. For instance, 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, and copy =$B3/$C3. However, if you copy the expression down one row, Excel will update the row reference because it isn’t absolute: =$B4/$C4. The same behavior is in play whether you use Ctrl+C to copy the expression or move it. This behavior makes it difficult—impossible—to copy an expression when you don’t want to update the cell references unless all of those references are absolute: =$B$3/$C$3. 

SEE: TechRepublic Premium editorial calendar: IT policies, checklists, toolkits, and research for download (TechRepublic Premium)

The easy way handles a single expression in Excel

If you have only a few expressions to copy, retaining the original references, you can use the formula bar in edit mode. It’s quick, 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 data set to the right in Figure A, both expressions reference the same cells, B3 and C3. If you’re working in the browser, you’ll need to erase the ‘ character in the destination cell before it will evaluate the expression.

Figure A

This method is quick and easy but works with one or a few 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 a bit more work but works with lots of expressions simultaneously.

The hard way handles many expressions in Excel

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

• First, we’ll add a special character to the beginning of selected expressions using = as the Find string and # as the replace string Excel will treat the expressions as text 
• Then, we’ll copy the expressions to the new destination.
• Finally, we’ll replace the special character we used in step 1 with the = character.

SEE: How to expose expressions that return a defined 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 in the Editing group (on the Home tab)
3. Enter = in the Find what control.
4. Enter # in the Replace with control (Figure B). You don’t have to use #; you can use most any other character but use one that’s not in use or you might get unexpected results. That’s why I chose #. If # is in use, come up with 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 can see in Figure C, Excel treats all of the selected expressions as text.

Figure B

Figure C

Using your favorite 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 so, repeat the above instructions, but in step 3, enter # and in step 4, enter =, reversing the original search strings. When you’re done, you will have matching expressions in columns D and F (Figure D). You can select both sets by creating a non-contiguous selection or you can run the Replace task twice.

Figure D

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see