WandaVision episode 3 Easter eggs and Marvel references on Disney Plus – CNET
More secrets emerge in episode 3, streaming now. Warning: Spoilers ahead! Source link
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.
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)
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:
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
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.
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:
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:
As you can see in Figure C, Excel treats all of the selected expressions as text.
Figure B
Enter the find and replace characters.
Figure C
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
With a few easy steps, you copied a selection of expressions without changing the cell references.