If you wish to learn how to remove formulas in Excel, go through this Excel tutorial and follow along with the steps and illustrations.

Ever feel like your Excel file is working against you? Those formulas you once needed might now be extra clutter, like sticky notes you forgot to throw away.

Getting rid of them without messing up your data sounds harder than it is. Trust me, it’s not! In this guide, I’ll walk you through simple methods to clean up your worksheet and keep it running smoothly.

By the end, you’ll wonder why it ever seemed tricky. Let’s jump in and sort this out together!

Using the Clear Contents Command

When you don’t need a bunch of formulas in one or many cell ranges in an Excel worksheet, you can follow this method. Please note that you’ll lose the formula syntaxes along with the calculated value in the selected cells.

Go to the source worksheet and press Ctrl + G to bring up the Go To dialog box.

Click on the Special button.

The Go To Special dialog box will open.

There, select the Formulas option and click OK.

Excel will automatically highlight all the formula cells in the worksheet.

If you wish to delete all of those, click on the Clear drop-down menu in the Editing block of the Home tab.

Choose Clear Contents from the context menu.

That should remove the formula cells’s contents instantly.

However, if you wish to selectively remove formulas from a few cell ranges after highlighting all using the Go To Special tool, hit the Ctrl key on the keyboard.

Now, use the mouse to select only those cell ranges you wish to keep in the worksheet.

The remaining selected cell ranges are the ones you wish to delete to remove formulas.

Click on the Clear Contents command in Home > Editing > Clear menu.

Don’t forget to backup your workbook before performing the above steps. You can’t recover deleted formulas.

Using the Find and Replace Tool

Suppose, your Excel workbook is performing poorly due to redundant formulas. You have already extracted the calculated values from those formula cells in another worksheet of the same or to a different workbook and no longer need the values in the original worksheet.

However, you still wish to keep the formula syntaxes for reference purposes. In this case, you can use the Find and Replace tool to convert formulas into inactive mode. Find below the simple steps.

Go to the target worksheet and press Ctrl + F to bring up the Find and Replace tool.

Type an equal sign in the Find what field.

Go to the Replace tab and keep the Replace with field empty.

Hit the Replace All button.

Excel will remove the essential equal sign from all the formula syntaxes that render the rest of the formula arguments inactive.

In the future, you can simply add the equal sign to the left of the formula syntaxes to activate those.

Using the Paste Special Tool

So far, you’ve learned the methods to remove formulas without preserving the calculated values.

Now, let me show you the trick to keep the calculated values and delete the underlying formula.

Go to the source worksheet and select a cell range that contains the formula you wish to delete.

Press Ctrl + C to copy the cell range.

Now, press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select Values below the Paste section and click OK.

This action should replace the underlying formulas with hard-coded values.

Do opt for a backup of the workbook before trying this method because you won’t be able to recover the formulas again when you reopen the workbook later.

Using a Drag and Drop Movement

This method uses the Copy Here as Values Only command. However, calling the command is a bit tricky. Let me show you the easy steps below.

Firstly, select the cell range from which you wish to remove the formulas but keep the calculated values.

Hover the mouse cursor over the edge of the selection line and wait for the cursor to change into a four-headed arrow icon.

Now drag the cell range to the left or right by using a right-click move but don’t change the position.

Bring it back to its original position and let go of the right-click.

You should now see a new context menu with an option that says Copy Here as Values Only. Click on that.

Excel will keep the calculated values in General, Number, or Accounting formats and remove the formulas.

Using Power Query

Whenever you export Excel worksheet datasets to Power Query Editor only fixed values are exported. If the dataset contains formulas, then Excel only exports the calculated values, not the underlying formulas.

This method is highly suitable when importing datasets to an existing Excel worksheet from a different workbook, either from the local computer or a database server. While you can transform the incoming dataset, Power Query automatically removes the formula syntaxes leaving behind values.

Let me show you how in easy steps and illustrations below.

To import a dataset from an external Excel workbook, click on the Get Data drop-down menu in the Data tab.

Hover over the primary source of datasets, like From File, From Online Services, and From Other Sources.

Now, choose Excel Workbook or relevant data sources in the overflow menu.

Alternatively, to process datasets of the worksheet that you’ve already opened, select the cell range from which you wish to delete the formulas.

Click on the From Table/Range command in the Data tab.

Click OK on the Create Table dialog to export the dataset to Power Query.

You should now see the imported or exported dataset in the Power Query Editor interface.

Your dataset has already been transformed from formula cells to values cells.

Click on the File menu of Power Query and choose the Close & Load To option.

Select the Existing worksheet option in the Import Data dialog box and click on the destination cell in the active worksheet.

The transformed dataset will be exported back to the source worksheet.

Delete the old column.

Congratulations! You’ve successfully removed formulas in Excel using Power Query.

Conclusions

So far, you’ve seen how to remove formulas in Excel using various methods.

You should practice the techniques that involve the Excel user interface and shortcuts, like the Clear Contents command, the Find and Replace tool, the Paste Special shortcut, and the Copy Here as Values Only command. These methods are suitable for occasional to intermediate-level use cases.

However, if your dataset is too large or you’re bringing in a dataset from another workbook, try the Power Query-based method as it’s suitable to handle and process big datasets.

Did you learn a new Microsoft Excel skill today? Share your suggestions and feedback in the comment box.