Formulas are at the heart of what makes Excel so useful. But sometimes you need to replace a formula with the number that is the result of that formula. Maybe you’ve used VLOOKUP() to pull in data from another workbook, or used RIGHT() to extract the zip code from a city/state/zip column and now you just want that data, not a link to data in another location. Obviously, you could manually type the numbers in the cells. But that brings the opportunity for errors and isn’t efficient when changing more than a few cells. So here are 3 methods that are faster and more reliable.
Calculate using F9: If you only have a few cells that you want to convert from a formula to a value, you can use this simple method that probably goes back to the early days of Lotus 123. (Google it if you’re too young to know what that is.) Select the cell you want to change, press F2 to edit it. Then press F9 and you will see the formula change to a number. Instead of using F2, you could also double click the cell, or click in the formula bar. This method is quick and simple, can be used while entering a formula, and you can even highlight part of a formula to calculate just that portion of it. But it can only be used on one cell at a time.
Paste Special – Values is more useful when you need to convert multiple formulas to numbers. Just highlight the cells you want to convert, then right click on them and choose Copy (or whatever method you like for copying). Next, right click wherever you want to paste the numbers (right click on the already highlighted cells if you want to replace them) and choose Paste Special, then Values from the next menu. Excel will paste the result of the formula instead of the formula itself. Excel 2010 and later will give several choices for Paste Values, each with a different formatting option.
Copy Here As Values Only: The third method is very quick and easy if you don’t want to paste the numbers very far away from the original cells. Highlight the cells as in the last example, then right-drag the cells to where you want to paste them. If you just want to replace your formulas with numbers, you can drop the cells back on their original location instead of on a different cell. When you release the mouse button, a menu will pop up. Choose Copy Here As Values Only to copy/paste the amount calculated by the formula, instead of the formula itself. To “right-drag”, click with the right mouse button on the dark line surrounding the highlighted cells; continue holding the button down while moving your mouse pointer to the desired location.
This post was updated from a one originally made on 9/28/2011.
