Remove Formulas in Excel and Keep the Values

Microsoft Excel is an amazing tool to handle large data without having issues. There are various Excel options that make the work easier, but many people are not familiar with these features. So one of these options is to remove the Excel formula of the sheet without losing the data. In case you are also finding a method to remove the Excel formulas quickly and that too without losing the data, then don't worry because there are multiple ways to do it easily.

In this tutorial, we are going to explain the techniques to remove formulas from the excel sheets and keeping the data(value) preserved by applying the following methods:

1. Remove formula using Paste Special
2. Remove formula using Mouse Trick
3. Remove formula using Paste Values
4. Remove formula using the Home Tab
5. Remove formula using Keyboard Shortcuts
6. Find & Remove formula using Keyboard Shortcuts
7. Remove Formula from Multiple Sheets (at the same time)

1) Using Paste Special Method

So here is the way to use Paste Special method in MS Excel for removing formulas:

1. Open the excel sheet to which you want to remove the formula.
2. Select and Copy all the cells which have the formulas that you want to remove.

1. Right-click and select the Special Paste option

1. Next, in the Paste Special box, select the Value option and click OK.

1. Now you see the formula has been removed from the cell, and the data will remain the same.

2) Remove Formula using Mouse Trick

Please follow the steps below to remove a formula through the mouse trick.

1. Open the excel sheet to which you want to remove the formula.
2. Select the cells to which have the formulas that you want to remove.

1. Take the cursor to the right edge of the column.

1. Hold the right key of your mouse, drag the cursor to a little right, and bring it back to the cell.
2. Now right click on the mouse and select 'Copy here as a value only.'

1. This 'Copy here as a value only' keeps the value and will remove the formula.

3)Remove formula using Paste Values

Please follow the steps below to remove a formula by the Paste Values option.

1. Open the excel sheet to which you want to remove the formula.
2. Select all the cells that have formulas that you want to remove.

1. Right-click on it and select Copy to copy all the selected cells.

1. After that, right-click again and go to the Paste Options and select 'Values.'

1. Now you will notice that only values are retained, and the formulas are gone!

4) Remove Formula Using the Home Tab

You can use the following steps for removing a formula through Home Tab.

1. Open the excel sheet to which you want to remove the formula.
2. Now select all the cells that have formulas that you want to remove.

1. Right-click on the selected cells and select Copy.

1. Now go to the Home tab and click the downward arrow below the paste option.

1. Next, click on the Paste Values.

1. Now you will notice all the formula from the cell has gone, and values have replaced it.

5) Remove Formula Using Keyboard Shortcut

In case you comfortable using the Paste as Value option keyboard shortcut, then please follow the steps below:

1. Open the excel sheet to which you want to remove the formula.
2. Select all the cells that have formulas that you want to remove.

1. Next, press Ctrl+C to copy all selected cells containing the formula.

1. Now press ALT + E + S + V + Enter to select Paste as Values option.

6) Find & Remove Formula Using Keyboard Shortcuts

In the above scenarios, you already knew which cells contain formulas. Let's go to an example where you have first to find the cells containing formulas and then remove formulas in Excel on each cell one by one using keyboard shortcuts. Please follow the steps below:

1. Open the excel sheet to which you want to remove the formula.
2. Press Ctrl + G on your keyboard to open a dialog box and then select Special.

1. Now select the Formulas option from the list and click OK. All the cells containing formulas will be selected.

1. Next, highlight all the cells containing formulas with color.

1. Now Press Ctrl + C to copy the one cell containing the formula.

1. Press Now press ALT + E + S + V + Enter to select Paste as Values option.
2. The formula has now been replaced with value.
3. Next, repeat these steps for all other highlighted cells.

7) Remove Formula from Multiple Sheets (at the same time)

Walking through the above methods, we learned the technique to remove the formulas from a single sheet at a time. But what if we want to perform the same operations on multiple numbers of sheets. This task will consume a lot of time for the person performing the task.

In this section, we will look at the technique that will teach us how to remove formulas and keep data in excel for more than one sheet at the same time.

To do this, we have first to group the sheets to which we want to apply the operations. Following is the way we can do so:

1. Click the Name tab of the first sheet
2. Hold the SHIFT button & simultaneously click on the Name tab of the last sheet

Note: You will notice that the color of all the sheet names will turn whiter because these sheets are now grouped. Now whatever operation you will perform on any one of the sheets will happen to all grouped sheet

1. Next, go to sheet one and select all the cells containing the formula.

1. Press Ctrl+C or Right-click and click on the Copy.

1. Now, right-click and go to the 'Paste' Option and select 'Values.'

Since all the sheets are grouped together, therefore, the operations performed on sheet 1 will be applied to all other grouped sheets, we can assume that formulas have been removed from all the sheets, and only values exist after that.

Note: Now after completing the task please turn off the grouping feature. Right-click on any sheet and click UNGROUP sheets.

Conclusion

Throughout this article, we discussed in detail different approaches to delete the formulas and preserve the values. We have also learned the way on how to apply the same kind of operations to multiple sheets at the same time.

So, this will help the person to save his time and allow him not to disclose the formulas to any third party or person.