If you don’t require the value to be tied to any other cells and only want to show the value, or if the formula contains confidential data that you don’t want to share, removing formulae and keeping the data in Excel may be important. In this tutorial, we’ll demonstrate to you how to remove formula in Excel and keep values with some quick examples.
How to Remove a Formula While Keeping Values in Excel (5 Ways)
We’ve shown a data set for calculating the yearly change in revenue percentage (%) in the screenshot below, but we do not wish to reveal the reference formula we used.
Method 1 – Right-Click to Remove a Formula While Keeping Values in Excel
- Select the cell that contains the formula you want to remove.
- Press Ctrl + C to copy.
- Right-Click on the cell.
- Select Paste Values.
- The formulas have been removed from the formula barwhile the values remain.
Method 2 – Use Home Tab Options to Remove a Formula While Keeping Values in Excel
Select the cell containing the formula you want to remove.
- Press Ctrl + C to copy.
- Go to the Home tab and select Paste.
- Select the first option from Paste Values.
- You will see that the formula is not shown in the formula bar.
Read More: How to Remove Formulas from Entire Excel Workbook
Method 3 – Apply a Keyboard Shortcut to Remove a Formula while Keeping Values in Excel
- Select the cells that contain the formula you want to remove.
- Press Ctrl + C to copy the cells.
- To open the dialog box, press Ctrl + Alt + V keys.
- Select Values and press Enter.
You will see cells that contain values but no formulas.
Method 4 – Apply Drag to Remove a Formula While Keeping Values in Excel
- Select the cells containing the formula you wish to remove.
- Hold the Right-Click button and drag to another cell.
- Drag back to the previous position and release the Right-Click.
- Click on Copy Here as Values Only.
- The formula has been removed from the formula bar while the values remain.
Read More: How to Remove Automatic Formula in Excel
Method 5 – Customize the Quick Access Toolbar to Remove a Formula While Keeping Values in Excel.
- From above the Ribbon, click on the Quick Access Toolbar and choose More Commands.
- Choose the All Commands option to display all commands available.
- Scroll down to find the Paste option.
- Select and click Add.
- Press Enter.
- Go back to the data set and copy the cells.
- A new icon for the Paste option will appear. Click the icon and select Paste Values.
- The formulas have been removed from the formula bar while the values remain.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Related Articles
- How to Remove Formula When Filtered in Excel
- How to Remove Hidden Formulas in Excel
- VBA to Remove Formulas in Excel Keeping Values and Formatting
<< Go Back to Remove Formulas in Excel | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
What if “copy here as values only” is greyed out?
Greetings Emma,
Let’s us know some info at first.
1. Can you access the other methods to paste values only?
2. Can you replace data in a non-blank cell?
We have gone through every possible solution but couldn’t match the issue exactly. If you kindly share your excel file with us, we will send it back with the solution.
Email: [email protected]
Our dedicated ExcelDemy Team is always there to help.