How to copy expressions without changing cell references in Excel

  • Technology
  • How to copy expressions without changing cell references in Excel

Microsoft Excel updates cell references when you copy an expression. Here are a couple of workarounds for those rare occasions when you don’t want to change the cell references.

Image: AndreyPopov, Getty Images/iStockphoto

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

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 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

excelcopyexp-b.jpg

  Enter the find and replace characters.

Figure C

excelcopyexp-c.jpg

  Excel displays the expressions as text.

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

excelcopyexp-d.jpg

  With a few easy steps, you copied a selection of expressions without changing the cell references.

How to copy expressions without changing cell references in Excel

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

Did you like this article? You can read it and many others @ Tech Republic!

From The S3 News Community

Check out posts from our own content writers

FRESHEST

Here's the latest news

Want to watch the premiere of The Real Housewives of Salt Lake City early? Well you're in luck, Bravo fans! E! News can exclusively announce that Bravo will be hosting advanced drive-in screening events for RHOSLC in Los Angeles (Thursday, Nov. 5)...

How to Watch The Real Housewives of Salt Lake City Early

When the pandemic hit in March, a JBS meatpacking plant in Greeley, Colo., began providing paid leave to workers at high risk of serious illness.But last month, shortly after the plant was cited by the federal Occupational Safety and Health...

OSHA Under Fire Over Regulation of Meatpacking in Pandemic

The Big Ten this week will become the fourth Power 5 conference to return to the football field in 2020. The conference on Aug. 11 postponed its season till the spring amid the COVID-19 pandemic, later reversing its decision and...

Big Ten football schedule: Full Week 8 TV coverage as conference returns to play

When you set out to create new music, what does your mindset look like? Are you curious and ready to explore ideas wherever they take you, or are you bogged down with the baggage of expectations? The truth is that...

Why Having No Expectations Is Best For Your Songwriting Process

Check out our selection of stocking fillers and gift ideas for the Raspberry Pi fan in your life for the coming festive season. The Raspberry Pi has been a runaway success since its launch in 2012, and each year since...

12 great gift ideas for Raspberry Pi fans in 2020
Load More
Share via
Copy link
Powered by Social Snap