Tutorial Excel : Paste a Formula’s Result

You can control the output that a copied formula displays by pasting the formula’s result rather than the actual formula. After you copy a formula that uses relative cell references, when you paste the formula, Excel automatically adjusts the cell references. For example, if the destination cell is one row down from the original cell, Excel adds 1 to the value of each row reference in the formula. This is usually welcome behavior because it helps you to repeat similar formulas without having to retype them.

However, this automatic adjustment of cell references means that you always end up with a different formula after you paste the original. One way to avoid this is to use absolute cell references (see the section, “Use Absolute Cell References in a Formula”). Alternatively, if you are only interested in the formula result, you can paste the copied formula as a value.

How to Paste Formula Result:

1. Select the cell containing the formula you want to copy.
Note: This task uses a single cell, but the technique also works for arange of cells. 

2. Click the Home tab.
3. Click the Copy icon.
Note: You can also copy the selected cell by pressing Ctrl+C. 

4. Click the cell where you want to paste the formula value.
5. Click the Paste drop-down arrow.
6. Click a Paste Values option.

● Excel pastes just the value of the formula, not the actual formula.

More Options!

If the copied cell has a number format applied, or any other cell formatting, when you paste the result using the Paste Values command, Excel does not copy the formatting to the destination cell. To include the original number format in the pasted cell, click the Paste drop-down arrow and click Values & Number Formatting, instead; to transfer all of the original cell formatting, click Values & Source Formatting.

Try This! 

If you are interested in displaying a formula result in a particular cell, you can paste just the value of that formula, but that pasted value will be incorrect if the inputs to the formula change in the future. To ensure that a particular cell always displays the current formula result, select the destination cell, press the equals key (=), click the original cell, and then press Enter. This simple formula tells Excel to always display the value of the original cell’s formula.

Categories: Excel 2010