How to Copy and Paste values without Formulas in Excel?

In Microsoft Excel, when you perform a normal copy and paste on a cell that has been calculated by a formula, the formula gets pasted as well. Sometimes, when you copy the contents of a cell, you want to paste just the value and not the underlying formula that is displayed in the formula bar like copy the resulting value of a formula to a cell on another worksheet. You can get an invalid cell reference error (#REF!) error.

You can avoid this error by pasting the resulting values of formulas without the formula in destination cells.

  1. Select the cells or ranges you wish to copy.
  2. On the Home tab, under Clipboard, select Copy.
  3. Select the cell you wish to paste your values to.
  4. Click the lower half of the large Paste button; from the extended menu that appears, select Values. Then click OK.

Or use a Shortcut Menu

  1. An even quicker way to paste values is by using a mouse shortcut:
  2. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
  3. Press the right mouse button, and drag the cells slightly to the right.
  4. Keep pressing the right mouse button, and drag the cells back to their original location.
  5. Release the right mouse button and a shortcut menu will appear.
  6. Click on Copy Here as Values Only.
  7. The formulas are replaced by the values of their results.


Related Posts with Thumbnails

Copyright © 2012. GS dot net - All Rights Reserved - Design by BTDesigner - Proudly powered by Blogger