How to Easily Paste Data Even When it is the Result of an Excel Formula

In several other posts we have discussed complex ways Excel can process data. These can include using formulas, data validation, VLOOKUPs, and much more. However, sometimes you just need the results of those functions without the actual function in the cell. This might be because you are copying the data to another sheet, or it might be because you want to remove some of the columns the formulas are dependent upon.

Whatever the reason, this post demonstrates how to easily paste data in Excel, even when that data is the result of a formula.

How to Easily Paste Data Even When it is the Result of an Excel Formula

If you have ever copied and pasted data from a cell or cells in Excel into a new sheet, you have probably run into the error where the data cannot be displayed. Instead you probably got a #REF! error which is basically Excel saying it cannot process the original formula because it is missing a reference.

Copying data from one sheet to another can be tricky if the data is a result of a formula, data validation, VLOOKUP or other function. However, there is an easy way to copy the data without losing any of the results, even if it is to another sheet entirely.

To easily paste data even when it is the result of a formula:

• Select all of the data you want to copy whether it is a single row or column, several of them or just a few cells.
• Right-click and select Copy from the pop-up menu OR use the Ctrl+C keyboard shortcut to copy the data.

• In the sheet where you want to copy the data, right click on the cell where you want the first cell to copy.
• In the pop-up menu, select the icon for "Values" under the "Paste Options:" menu.

This is the easiest way to copy data in Excel that is being processed through a formula, data validation, etc., and retain the actual value of the data rather than trying to copy the function behind the data. This can even be done in the same column, row or cells, meaning you can create formulas to get the data you need, copy the data and paste is as values if you no longer needed the formulas.

As always, knowing how to format the data the way you need it in Excel is key in it being a really effective application.