How to Clear Contents in Excel without Deleting Formulas

Excel allows you to clear content in the worksheets selectively. So, you can clear the contents in all the cells except the ones that have formulas.

This can be useful if you use a template that has some formulas applied in it. You can delete all the cells with values and keep the formulas, so that the next time you copy paste data into it, formulas automatically calculate and you don’t have to create those again.

In this Excel tutorial, I will show you some simple methods to clear content in all the cells without deleting formulas.

So let’s get started!

Clear Cells Using Go To Special

Suppose you have a dataset as shown below, where Column D has formulas in it, and the rest of the cells have static values.

Below are the steps to clear the content from these cells while leaving the formulas untouched:

1. Select the entire dataset
2. Press the F5 key. This will open the ‘Go-To’ dialog box
3. Click on the ‘Special’ button. This will open the ‘Go-To Special’ dialog box.
4. Select the ‘Constant’ option
5. Click OK.

The above steps would select only those cells that have values in it. Any cell that has a formula will not be de-selected.

Now that we have all these cells with values selected, you can hit the DELETE key to remove the values from all these cells.

In case you want to completely clear the content (including values as well as the formatting), use the below steps:

1. With the cells (that only have the values) selected, click the Home tab
2. In the Editing group, click on the ‘Clear’ option.
3. In the options that show up, click on the ‘Clear All’ option

The above steps would instantly remove all the content as well as the formatting from these cells.

Also read: How To Remove Formulas In Excel But Keep Data

Clear Cells Using VBA

While the above method works great, it does have a few steps/clicks that need to be done to delete values while keeping the formulas.

If you’re okay with using VBA, you can speed up this process with a simple VBA code.

Below is the VBA code that will clear the content of all the cells (within the selection) while keeping the formulas intact:

Selection.SpecialCells(xlCellTypeConstants, 23).Clear

In case you want to do the same for the entire worksheet (and not just the selection), you can use the below code:

Activesheet.cells.SpecialCells(xlCellTypeConstants, 23).Clear

Below are the steps you use this VBA code in Excel:

1. Open the Excel workbook from where you want to clear the contents
2. Right-click on any worksheet tab
3. Click on the ‘View Code’ option. This will open the Visual Basic Editor
4. If you don’t already have the ‘Immediate window’ visible, click on the View option in the menu and then click on ‘Immediate Window’
5. Copy and paste the above code in the ‘Immediate Window’
6. Place the cursor at the end of the line
7. Hit the Enter key

The above steps would instantly clear the contents from the entire selection (expect the formulas).

In case you want to clear the contents from all the sheets (and keep the formulas intact), you can use the code below:

For Each ws In Worksheets: ws.Cells.SpecialCells(xlCellTypeConstants, 23).Clear: Next ws

The above code would go through each sheet in the workbook, clear the content from only those cells that have a constant value (while leaving the cells with formulas intact).

So these are two methods you can use to quickly clear the contents in Excel without deleting the formulas.

Hope you found this tutorial useful!

You may also like the following Excel tutorials:

1.  How to Delete a Comment in Excel (or Delete ALL Comments)
2. How to Unhide All Rows in Excel with VBA
3. How to Auto Format Formulas in Excel
4. Excel Showing Formula Instead of Result
5. How to Remove Apostrophe in Excel
6. How to Copy Formatting In Excel
7. Clear Contents in Excel (Shortcut)