Excel Convert Formula to Value
How to convert formulas into values in Excel?
What does 'Convert Formula to Value' mean in Excel?
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.
If you choose to freeze a part of the calculation, you can replace the part you don’t want to be recalculated.
Key Takeaways
- Convert Formula to Value is an incredibly easy task as you just have to select the references with the formulas in them and turn them into the results without the calculations by copying the cell/array and pasting them in an empty cell via “Paste Values.”
- When converting a formula to a value, Excel removes the formula from the cell and displays only the resulting numeric value. For instance, a formula like =SUM() will be replaced by the calculated number.
- When changing formulas into the result, you should keep a copy of the current workbook in case you choose to replace the values. You’ll have the original worksheet to return to.
- People convert formulas to values for different reasons, such as inserting values into other workbooks/sheets, ensuring result consistency despite changes in references, or maintaining confidentiality in competitive environments.
Convert Formulas to Values in Excel Example
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.
Excel Convert Formula to Value FAQs
To convert, follow these steps:
- Ctrl-C what you want to convert
- Go to Paste section in the top-hand left corner
- Click the down arrow
- Paste values
- Choose any of the three options in that category
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.
or Want to Sign up with your social account?