Convert Excel Formulas and Functions to Values

Most Excel spreadsheets contain formulas. Sometimes you may want to force only the result of a formula to occupy a cell, instead of leaving the formula in place, where it will change if/when the data it references changes.

You can do this manually in a couple of ways, or you can use a macro that will make the job a breeze. Let's look at the manual methods first.

Using Paste Special

You can copy the formula results and still leave the original formulas in place using Excel's Paste Special tool. Assume you have formulas residing in cells A1:A100. Select this range, select Copy (you can do this from the Clipboard options on the Home tab or right-click), and then select the starting cell for the mirror results. Select Clipboard → Paste → Paste Values (orright-click and select Paste Special → Values) and click OK.

If you want to override the original formulas with their results, select the formula range and select Copy. With the formula range still selected, select Paste → Paste Values (or right-click and select Paste Special → Values) and then click OK.