Special copying of values and formulas

Description

We all know how to copy values and formulas in Excel, but sometimes you need to copy the value that the formula returns or the formula itself without shifting references and ranges. Let's see how to do it.

Copy values

To copy a value instead of a formula, you need to copy the cell and then use A special insert , which will help us insert the values themselves. It looks something like this:

Copying formulas

To copy the formula without shifting the links, we can copy the formula not through the cell, but in the formula ribbon itself.

Then paste it into any cell, and the formula will refer to the same cells again, even without first fixing the rows and columns in the formula.

However, this method will not work if we want to copy many formulas without shifting the references. In this case, one trick will help us. So:

• select all the formulas that we want to copy;
• call up the menu Search and replace ( Ctrl+ F ), we choose Options - Search area: formulas and replace the "equals" sign (=) to any special character (#, @, ? ...);
• copy the converted formulas as plain text and paste them in another place (another sheet, a book);
• we do the reverse replacement of the special character with the "equals" sign.