Special copying values and formulas

We all know how to copy the values and formulas in Excel, but sometimes we need either copy value as result of formula or same formula without moving references and ranges. Lets look how to do that.

Copying values

To copy a value not a formula we need copy cell and then use Paste Special and select Values:

Copying formulas

To copy the formula without moving references we can copy formula not through the cell but in Formula Bar.

Then insert it into another cell. The formula will be refer to the same cells and ranges without any fixing of rows and columns inside.

But such approach doesnt work if we want to copy many formulas. In such case we can use one trick.

So:

  • - Select all cells that we want to copy;
  • - Choose menu Find and Replace (Ctrl+F), select Options Look in Formulas and replace sign = at any another special symbol (#, @, ?...);
  • - Copy modified formulas as ordinary text and pastes them into another place (another sheet, workbook etc.);
  • - Replace special symbol at sign =.
  • .