How to Convert an Excel Formula into its Number Value Result

Excel is great for creating formulas that give you results as numbers. The problem is that when you look inside the cell, you see only the formula. Sometimes you want the number values instead. It's not really that hard to convert a formula into the numerical resulting value.

If you use Excel at all, you’ve probably created some formulas, even if only very simple ones like the total of a column. Sometimes you want to copy the resulting numbers and put them into some other application, or even into another area of your spreadsheet. Often pasting the formula itself will be either useless or will not give the outcome you’re seeking. The answer to this dilemma is Paste Special.

You might also want to use this method if you’re sending your spreadsheet to someone and you don’t really want them to know how you arrived at the numbers.

Paste Special includes many other functions, but I’m going to concentrate only on Values, which is the one I use most often.

Create a Formula: In order to demonstrate Paste Special Values, you need to have a formula in your spreadsheet. Even a simple formula will work, so if you have nothing else, enter “=7+3” into cell A1. The result 10 will appear in the cell, but if you copy cell A1 to cell B1, you’ll be copying the formula. If you drag it to copy it, you might even change the formula (not in this case, but in other situations).

Copy the formula: In order to copy you can use the menu: Edit, Copy or you can use the sequence Alt, E, C or you can use the keyboard combination Ctrl C. In any case get the contents of cell A1 into your clipboard. It’s still a formula.

Paste: For this demonstration, we will paste the result into cell B1 so you can retain the formula. Click your cursor into cell B1.

Note: if you use the standard paste function (Edit, Paste or Alt, E, P or Ctrl V) you will paste the formula into the cell. If it is a formula using cell addresses, the addresses will adjust to the new location. Since you want to see only the results of the formula you will not use simple paste.

In order to Paste Special, you have to use the menu Edit, Paste Special, or the sequence Alt, E, S. This will bring up a small window with some choices. You want to paste VALUES. This is found in the first section of the window, third from the top. Notice all the other options just under “Paste” although some of these you may never use, it’s handy to know they’re available. You’ll notice you could also paste “Values and number formats” which you might choose if you have special formatting on the cell you are copying. Once you have chosen “Values” (or Values and number formats”) click the OK button. The number 10 will now be in cell B1.

Obviously this is an extremely simple example, and it would be far easier to type the number 10 into the cell than to go through this process. However if you have a formula that draws from many other cells, and does some pretty esoteric calculations, you may be much happier copying and pasting-special.

Remember that you can copy an entire column (or row) of cells and paste special values into the next column. You can also paste it into the originating column, losing the formulae forever. Sometimes that’s what you want. Usually, I prefer to paste special into a new column and if I decide I don’t want the formulae, then I’ll delete that column, or hide it.

Although there isn’t a keyboard combination for Paste Special Values, you can remember the sequence “Alt, E, S, V, Enter” which doesn’t take too much effort. You can also create a toolbar button if you find you paste special values often. As in any toolbar customization, right click the toolbar area (or use the menu if you must: Tools, Customize or Alt, T, C). On the Commands Tab, click on Edit in the left side of the screen. Right after “Paste” on the right hand side, you’ll see “Paste Special” which is fine if you want to use the other functions as well. But if all you want is Paste Special Values, keep scrolling down a few. When you see what looks like a clipboard with a 12 on it, you’ve found the icon. Drag it to your toolbar (I suggest placing it near the scissors) and let go. Close the customization window. Now you have a Paste Values toolbar button.

• The least intuitive thing about this is realizing that you want to Paste the VALUES that result from your formulas.
• Memorize the keyboard SEQUENCE: Alt, E, S, V, Enter if you paste values often, or add a toolbar button.
• Paste Special includes a number of other interesting functions. Try them out.
• If you Paste the Values over your formulas and didn't mean to, CTRL Z will fix it back to the way it was. Better to make a new column to receive the values and then delete the column with formulas if you decide you're satisfied.