Excel Convert Formula to Value

Converting formulas into values is very helpful in improving performance through static data in case there are a lot of formulas or if the formulas themselves are complex. You can convert formulas into results on a cell-by-cell basis or by selecting an array.

When you convert a formula into the result from the calculation, Excel deletes the formula from that cell and only shows the numeric value from the formula.

For example, after you transfigure the referenced cell into a value, the reference doesn't show =SUM(); it only shows the number from which the sum was calculated.

The reasons why someone would want to convert formulas into values vary among different people. For example, some want to do it because they want to insert values into other workbooks/sheets; others want to prevent the result from modifying when references change, and so on.

In a competitive environment, it is best not to let your competitors know how you came to the result you calculated by transfiguring formulas into values. You can even prevent them from altering or editing values by changing the calculations into the results.

Throughout the article, we will show you how to convert formulas into values using various methods.

Examples of how to convert formula to value

In this section, we will look at three different approaches to transforming a formula into a value. For example, suppose a company has amounts listed for 12 months. They calculated all those numbers together and had a sum of $550.

To remove the formula while keeping the number, we must press the Ctrl + C keys to copy the reference.

Next, you'll have to go to the Paste section on the top-hand left corner, click the down arrow, paste values, and click on any of the three options in that category. In this example, we chose Values and Number Formatting in the middle.

Once we've clicked on one of the "past values" options, we should see the value without the formula attached to it. So, for example, cell C15 has the =SUM(C3:C14) inside, while cell C16 is just the number itself: 550.

There is a shorter way to do this. However, it doesn't give us the currency value, only the number itself. So, first, click cell C15, and press Ctrl and C on your keyboard to copy the reference.

Then, click on an empty cell, right-click, go to "Paste options," and click on the clipboard with the numbers on them, which are our "Values."

Once you've clicked "Paste Values," you get 550 without the calculation.

Now, let's look at another way to find the values. Suppose a company has its profits for each of the 12 months listed. We want to convert those profits into values.

So, what we need to do here is copy cells E3:E14.

Then, we need to go to the "Paste" section at the top, hit the bottom arrow, and go all the way down to "Paste Special."

Once we've pressed that button, it'll open the "Paste Special" window.

Next, we will change the paste selection from "All" to "Values."

Then we hit OK, which gives us the result without the attached calculations, which is just the number 550, without the currency symbols.

Researched and authored by Marcu Andrei Dumitrescu | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Basic Excel Formulas
• Excel Formulas Cheat Sheet
• Excel IF Statement
• Excel Modeling Best Practices
• How to Reduce Excel File Size