How to Clear Contents in Excel Without Deleting Formulas

The GIF below is an overview of clearing Excel values without deleting formulas. Here, a VBA code clears the contents without deleting formulas. When we run the code, it removes the contents without erasing the formulas.

How to Clear Contents in Excel Without Deleting Formulas: 3 Ways

We’ll use the following dataset in the range B4:E10, with values for Unit Productions and Per Unit Cost in columns C and D. We want to clear the contents of the table without deleting formulas.

Method 1 – Utilizing the Clear Contents Option

Steps:

• Go to the Formulas taband click the Show Formulas button.

• This displays all the cells containing formulas and the cells containing only values. (B5:D10 cells)

• Select the B5:D10 cells and go to the Home tab.
• Click the Clear drop-down and press Clear Contents to remove values without affecting formulas. You can also press the Delete key to erase the contents of the cells.

• The formulas will see the values as zeroes.

Method 2 – Clearing Contents Without Deleting Formulas Using the Go To Special Command

Steps:

• Click on the Find & Select option and choose Go To Special.

• The Go To Special window appears.
• Check the Constants radio button, uncheck the Text option, and hit OK.

• This will select the B5:D10 cells.
• Press the Delete key.

• Here are the results.

Read More: How to Clear Multiple Cells in Excel

Method 3 – Applying VBA Code to Erase Contents Without Deleting Formulas

Steps:

• Navigate to the Developer tab and click on Visual Basic.

• Go to the Insert tab and select Module.

• Copy the code from here and paste it into the window.

Sub clear_content_keep_formula()

    Dim val As Range

    Set val = Sheet6.Range("C5:D10").SpecialCells(xlCellTypeConstants)
    On Error Resume Next
    val.ClearContents

End Sub

• Click the Run button or press the F5 key to execute the macro.

• The results should look like the screenshot given below.

How to Clear All Content in Excel Without Deleting Formatting

Steps:

• Hold down the CTRL key and select the B5:E10 and C12:E12 ranges.
• Press the Delete key.

• Here’s the output.

Read More: How to Clear Contents in Excel Without Deleting Formatting

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice.

Download the Practice Workbook

Related Articles

• How to Clear Excel Temp Files
• How to Clear Recent Documents in Excel
• How to Clear Cells with Certain Value in Excel
• How to Clear Cell Contents Based on Condition in Excel

<< Go Back to Clear Contents in Excel | Learn Excel