Convert Formulas to Values in Excel to solidify your data and prevent any unintended alterations, especially when sharing your spreadsheets with others or finalizing reports. This essential skill is invaluable for financial analysts, project managers, and anyone who needs to preserve the integrity of their data calculations. Whether you’re preparing a budget, finalizing a project plan, or compiling analytical reports, this guide will walk you through the process of converting formulas to static values in Excel. By mastering this technique, you’ll ensure your data remains accurate and consistent, making your spreadsheets more reliable and user-friendly for all stakeholders.
This Content Covers:
- Why Should We Convert Formulas to Values?
- How to Convert Formulas to Values?
- Using a Keyboard Shortcut
- Using Mouse Trick
- Using Paste as Values Option from Right Click
- With a VBA Code
- How to Convert to Values Inside the Formula?
- How to get Formula’s Result Value with Power Query?
1. Why Should We Convert Formulas to Values?
- It is preferable to turn all the formulae into static values (paste values) if you provide a report to someone without its source data.
- This will prevent anyone from making any changes to the final calculation in a workbook.
- Some functions and values will recalculate when you update the worksheet. So, it is better to convert the formulas into values.
- If someone wants to use the values from your worksheet, they can easily do that. But if the values have a formula wrapped, copy and paste them becomes much more complex.
2. How to Convert Formulas to Values in Excel Using Multiple Options?
This datasheet contains data or values that were produced using a formula for column D (Profit). We now want to convert formulas to values. We can remove the formulae by converting these formulas into values using a few simple techniques.
2.1 Excel Shortcuts in Keyboard by using Paste Special
Step 1: Select the range that contains formulas and copy them using CTRL + C.
Step 2: Press ALT+E+S+V to enable the Paste special dialogue box. You can also open this dialogue box by clicking the right button of your mouse and selecting Paste Special. From this dialogue box select Values and click OK.
Step 3: All the formulas are now converted or changed into values.
2.2 Using Mouse Trick to Replace a Formula to Value
Step 1: Select and copy the range by CTRL+C command and take your mouse cursor at the right edge of this selection. You will see an icon pointing toward all four directions.
Step 2: Click and hold the right button of your mouse and drag the selection towards right and bring it back to its original position.
Step3: When you bring it back to its original position and leave the right button, an option menu will come up on your screen. Select Copy Here as Values Only.
Step 4: All the Excel formulas are converted into values.
2.3 Using Paste as Values Option from Right Click
Step 1: Select the range and copy using CTRL+C. Right click on your mouse. Select Values (V) under Paste Options.
Step 2: The formulas are changed into values.
2.4 With a VBA Code to Replace Formulas with Values
Step 1: Right-click on your worksheet and select View Code.
Step 2: Paste this VBA Code inside the VBA Module and click on RUN.
Sub ConvertToValues()
With ActiveSheet.UsedRange
.Value = .Value
End With
MsgBox (“All Formulas are converted to values”)
End Sub
Step 3: This dialogue box will pop up saying “All Formulas are converted to values”, click OK and the formulas in that worksheet will convert into values.
3. How to Convert to Values Inside the Formula?
Step 1: Select cell D2 and click F2 button from your keyboard to enable the edit mode.
Step 2: Select the portion of the formula that you want to change into value and click F9 key. You can do this change in the formula bar also.
Step 3: Once you press F9 key then click on ENTER button the formula will convert into value. Now do this for each of the cells containing formulas separately.
4. Converting Formulas to Values using Power Query?
Step 1: Select any cell from the table and go to Data>>From Table.
Step 2: Select Close & Load from Power Query Editor.
Step 3: This will open a new worksheet for you with the same table but the formulas from column D will be converted into Values.
Application of Converting Formulas to Values in Excel:
- Data Sharing: Convert formulas to calculated values to prevent recipients from altering calculations or seeing proprietary formulas when sharing Excel files.
- Report Finalization: Ensure that the values in reports remain static and unaffected by any future changes in the source data or formulas.
- Performance Improvement: Enhance spreadsheet performance by converting complex formulas to values, reducing the processing load, especially in large files.
- Preventing Errors: Minimize the risk of errors in data by converting formulas to values, safeguarding against accidental deletions or modifications of critical formulas.
- Archiving Data: Preserve the state of your data at a specific point in time by converting formulas to values, creating an accurate historical record.
- Simplifying Templates: Simplify templates for users who may not be familiar with Excel functions by providing them with straightforward values instead of complex formulas.
For ready-to-use Dashboard Templates:
