In today’s tutorial, we’ll learn how to remove formulas from cells but keep the values intact. Let’s look at this dataset. We have the cost of items and a discount is offered on each one of them. We have already calculated the discounted price through a formula. We would like to remove the formula from the discounted price column but keep the results by following these steps.
There are several situations where it might be necessary to remove formulas from an Excel spreadsheet and keep the values. For example,
– If you are sending a copy of the spreadsheet to someone else and don’t want them to be able to see or modify the formulas, you can remove the formulas and leave the values behind.
– If you are using the spreadsheet to store data that will not change and don’t want to accidentally modify the formulas, you can remove the formulas to prevent accidental changes.
Step 1 – Select the cells with formulas
– Select all the cells with formulas, if the cells are adjacent then select the first cell and keep on dragging till all required cells are selected.
– If the cells are non-adjacent then select each one of them while holding down the CTRL key, as shown above.
Step 2 – Remove formulas keep values
– Now we’ll press CTRL+C to copy all the values. A dotted line will appear and keep on moving to show you that the values are copied.
– In the Home tab, click on the Paste action button’s dropdown menu in the Clipboard group.
– This will open up a dropdown menu and in the Paste Values section click on Values as shown above. This will paste the values only and remove all formulas from the cells.
Note: The same action can be completed by right clicking the selected cells and choosing keep values from the context menu.