How to remove all formula references but keep values in cells in Excel?
In many Excel workbooks, cells often contain formulas that reference other cells, sheets, or even external data sources. However, there are scenarios where you might want to retain just the calculated values and clear out all the underlying formula references. For example, before sharing data with others, or to preserve the current calculation results while preventing accidental changes due to future data edits, it can be essential to convert all formulas to their resulting values. Fortunately, Excel provides several effective methods to remove formula references but keep the actual values intact. This article will guide you through multiple practical approaches to achieve this, helping you select the method best suited for your particular needs or workflow.
Remove all formula references but keep values in cells by copying and pasting
Remove all formula references but keep values in cells with only one click
VBA Code - Use a macro to replace all formulas in selected cells with their calculated values automatically
Remove all formula references but keep values in cells by copying and pasting
One of the most straightforward and universally compatible ways to remove formula references while keeping the actual calculated values in your worksheet is by using the Copy and Paste Special feature in Excel. This method is especially useful for quick tasks, does not require any add-ins, and works in all modern versions of Excel. However, it should be noted that once the formulas are replaced by their values, you will lose the automatic calculation and linkages that the formulas provided, so this method is best applied when you are certain that you no longer need dynamic updates in those cells.
Please do as follows to remove all formula references but keep values in the specified cells in Excel:
1. Select the cells containing formulas that you want to convert to values. Next, press Ctrl + C to copy these cells. While the selection is still highlighted, right-click anywhere within the highlighted area to bring up the context menu, then select Values under the Paste Options section. (If using the Ribbon interface, you can also go to the Home tab, click the drop-down under Paste, and choose Paste Values.) See screenshot:
After performing this operation, all formulas in the selected cells will be replaced by their current values, as shown in the screenshot below. The cell values will remain unchanged visually, but the formulas themselves are removed, leaving only the fixed numbers or text.
Applicable Scenarios: This solution is ideal for small to medium-sized data ranges or when you want full control over which areas to convert. If you frequently need to repeat this process, using keyboard shortcuts (Ctrl + Alt + V, then V) can make it faster.
Pros: No add-ins or coding required; instant results; works in all versions of Excel.
Cons: Cannot be undone once changes are saved and the workbook is closed; only applies to the currently selected range each time.
Tips & Cautions: Double-check your selection before pasting values, especially if you have formulas referencing each other. If you need to keep a backup of your formulas, consider copying the original sheet to a new worksheet before applying this method.
Remove all formula references but keep values in cells with only one click
If you are looking for a quicker, one-click approach – especially for large datasets or routine tasks – you can take advantage of the To Actual feature provided by Kutools for Excel. This utility streamlines the process, letting you instantly convert all formulas within your selection into static values, without extra copy-paste steps. This is particularly useful when working with large reports, shared templates, or confidential financial data that needs to remain static.
1. Choose the cells or the range that contains the formulas you wish to convert. Then, simply click Kutools > To Actual in your Excel toolbar.
All formulas in your chosen cells are immediately replaced with their resulting values, while the numbers and text in the cells remain exactly as displayed. See screenshot below for confirmation:
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy permanently free AI features! Download now!
Scenario Advice: Kutools for Excel is suited for users who require efficient, repetitive operations or who regularly work with large workbooks full of formulas. It is especially beneficial for those who want to minimize repetitive manual steps and reduce the risk of error.
Pros: Extremely fast; works with large and multiple ranges; saves time for frequent tasks.
Cons: Requires installation of the Kutools for Excel add-in (a paid utility, though trial versions are available).
If you encounter issues where not all formulas are converted, check to ensure you have selected the full intended range before using the utility. It is recommended to save your workbook before performing batch operations, in case you need to revert your changes.
VBA Code - Use a macro to replace all formulas in selected cells with their calculated values automatically
For more advanced users or those looking to automate the process, using a custom VBA macro is another effective solution. This method is particularly useful when you have repetitive needs or want to convert formulas to values across multiple, non-contiguous ranges at once. With VBA, you can execute the conversion with a single command, saving both time and effort for recurring tasks.
Note: This approach is suited for anyone comfortable with running VBA macros in Excel. If your workplace policy restricts macros, check with your administrator before use.
1. Open the VBA editor by clicking Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy and paste the following code into the new module:
Sub ConvertFormulasToValues()
Dim rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Please select the range where you want to remove formulas and keep values:", xTitleId, WorkRng.Address, Type:=8)
If WorkRng Is Nothing Then Exit Sub
For Each rng In WorkRng
If rng.HasFormula Then
rng.Value = rng.Value
End If
Next
End Sub2. To run the macro, click the 
Run button in the VBA editor. You will see a dialog box prompting you to select the range where you want to replace formulas with their values. After selecting your target range and confirming, the macro will automatically convert all formulas in that range to their calculated results, removing the formula references but keeping the displayed values unchanged.
Applicable Scenarios: This solution is highly flexible and can be used for batch-processing large or irregular cell ranges. It is especially valuable when you want to automate the process as part of a larger workflow or macro routine.
Pros: Automates repetitive tasks; can easily be extended or combined with other automation steps; works well for complex selection scenarios.
Cons: Requires enabling macros; not recommended for users unfamiliar with VBA; macro security settings may restrict use in some organizations.
Troubleshooting: If you encounter a runtime error, ensure you have selected a valid range. Also, make sure macros are enabled in your Excel instance. If you wish to undo the operation, you can use Excel's Undo feature (Ctrl + Z) immediately, but macro operations cannot be undone once the workbook is saved and closed.
Related articles:
- How to reference format and value from another cell in Excel?
- How to reference or link a value in an unopened/closed Excel workbook file?
- How to reference a worksheet by index number instead of name in Excel?
- How to keep a formula cell reference constant in Excel?
- How to reference a tab name in a cell in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in