You may need to convert formulas to Values in Excel if you work with a lot of dynamic or volatile formulas. Show
For example, if you use Excel TODAY function to insert the current date in a cell, and you leave it as a formula, it will update itself if you open the workbook the next day. If this is not what you want, then you need to convert the formula into a static value. Convert Formulas to Values in ExcelIn this tutorial, I will show you various ways to convert formulas to values in Excel.
This is one of the easiest ways to convert formulas to values. A simple keyboard shortcut trick. Here is how to do this:
This would instantly convert all the formulas into static values. Caution: When you do this, you lose all the original formulas. If you think you may need the formulas at a later stage, make a copy of the worksheet as back-up. Using Paste SpecialIf you are not a keyboard shortcuts fan, you can use the paste special option that can be accessed from the ribbon in Excel. Here are the steps to convert formulas to values using Paste Special:
You can also access the same paste special option if you right click on the copied cell. Hover your mouse on the Paste Special option and you will all the options within it. When it comes to speed and ease, shortcut keys are the best and to turn formulas into values the easiest way is to use a shortcut key. All you need to do:
By using this shortcut key all the formulas will be replaced with their static result value. Note: If you don’t want to lose the original formulas then you should make a backup file. 2. Using Paste Special for Converting to ValuesIf you don’t like to use a shortcut key then you can simply use the paste special option to replace it.
This method just works like the shortcut key method to replace all the formulas with their static result values. 3. Using Right-Click MenuAnd if you don’t want to use paste special then you can use the right-click drag-drop method for this conversion. Here are the steps:
This will instantly convert all the formulas from those cells into static result values. 4. Convert Formulas into Values with a VBA CodeIf you want to automatically convert all the formulas into text or numeric values then you can use VBA for that. Here I have mentioned a simple VBA code where you just need to select the range of the cells and run this macro.
Related: Excel VBA Tutorial 5. Convert To Values Inside the FormulaLet’s think differently. You have a complex formula which is a combination of two or three functions. Here you have a long formula to clean a text value and then convert it into the proper case. Now if you want to convert the result of two substitute functions into static text values, here are the steps.
Now you just have a text value to convert into the proper text case. 6. Get Formula’s Result Value with Power QueryThere could be a situation when you need to get the result value of a formula in a separate cell instead of simply converting them. And in that case, you can use a power query. You need to convert it into values before sending it to your customer. But the thing is you don’t want to lose all the formulas. So here are the simple steps to get values from formulas using power query.
The best part is when you make any changes in your formula table you just need to refresh the power query table. By using this way you don’t need to copy-paste data every time. Related: Excel Power Query Tutorial ConclusionSometimes using complex and large formulas make your workbook heavy in size, so it’s better to convert them into absolute values before sharing them with others. And all the methods which we have discussed above can help you in different situations. If you ask me, my favorite one is the keyboard shortcut but I also like to use power query sometimes. I hope you found this tip useful, but now, tell me one thing. What’s your favorite one? Do you have any other method which you use while converting formulas into values? Please share with me in the comment section, I’d love to hear from you and don’t forget to share this tip with your friends, I’m sure they will appreciate it. How do I convert a formula to a text string in Excel?How To Convert Formula To Text String In Excel using Paste Special?. In a worksheet, select the cell range containing formulas you want to convert to text.. Then, go to the cell where you want to paste the converted formulas.. Right-click and choose Paste Special from the shortcut menu that appears.. How do I turn a formula into a text value?To copy the actual value instead of the formula from the cell to another worksheet or workbook, you can convert the formula in its cell to its value by doing the following: Press F2 to edit the cell. Press F9, and then press ENTER.
How do I copy a formula result in Excel as text?Alternatively, you can enter the editing mode and copy the formula in the cell as text:. Select a cell with the formula.. Press F2 (or double-click the cell) to enter the editing mode.. Select the formula in the cell using the mouse, and press Ctrl + C to copy it.. Select the destination cell, and press Ctl+V.. How do I convert a cell value to a string in Excel?Format numbers as text. Select the cell or range of cells that contains the numbers that you want to format as text. How to select cells or a range. ... . On the Home tab, in the Number group, click the arrow next to the Number Format box, and then click Text.. |