How to Copy and Paste Values
My partner called me from work this week, with an Excel problem that was making his life hard. Luckily, there was a very simple solution.
Someone had created an Excel spreadsheet with formulas in it. The formulas in this case were very simple – they took the number in the cell above and added one to the value. In this way they had created row numbers.
The problem was that they now wanted to get rid of the formulas, and just have the results.
The wrong way to fix this…
The author wanted him to go into each cell and delete the formula and enter the value instead. Would this have worked? Well, yes, but it would have taken a long time; there were over 700 rows of formulas in this particular spreadsheet.
Hint: If you click on the Show Formulas button in the Formula Auditing group on the Formulas tab you can see how formulas are being used in a document. Clicking on it again will return the document to its original state.
The right way to fix this…
Instead, I had him follow these simple steps, and he finished in minutes what might have taken hours.
- Click and release on the column header. In this example, it is column header D.
- Right-click anywhere on the selected column and choose Copy from the dropdown.
- Right-click again on the selected column and choose Paste Values from the dropdown. (There are six Paste Options listed in normal Excel installations. The Paste Values selection is the second from the left. The icon is a clipboard with “123” on top of it.)
That’s it! You’re done. If you turn on the Show Formulas button again you’ll see that the formulas have been replaced with the values.
You can press the Escape key to turn off the marching ants around the selection, and click in another cell to dismiss the selection.
