How to Clear Contents in Excel Without Deleting Formulas

Oftentimes, we may want to remove values from our Excel spreadsheet. Now, what if we need to erase only the contents without affecting the formulas? Sounds tricky, right? Fortunately, in this article, we’ll discuss 3 ways how to clear contents in Excel without deleting formulas. Moreover, we’ll also learn to clear contents while keeping the formatting intact.

The GIF below is an overview of this article which represents the clearing of contents in Excel without deleting formulas.

In the following sections, we’ll learn more about the dataset and observe each method individually with the necessary details.

Download Practice Workbook

3 Ways to Clear Contents in Excel Without Deleting Formulas

First and foremost, let’s assume the Cost Breakdown of Widget Production dataset shown in the cells that contain the “Week”, “Unit Production”, “Per Unit Cost”, and “Total Cost” columns. In this dataset, we get the total cost in column D by multiplying “Unit Production” (column B) and “Per Unit Cost” (column C) and we’ll apply the SUM function to obtain the “Total” for the “Unit Production” and “Cost”. In this scenario, we want to clear contents in Excel without deleting formulas. So without further delay, let’s dive right in!

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Utilizing Clear Contents Option

In the first place, we’ll utilize the Clear Contents option to erase contents without affecting the formulas. Now allow us to demonstrate the process in the steps below.

📌 Steps:

• In the first place, go to the Formulas tab >> click the Show Formulas button.

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

• Next, select the B5:D10 cells >> move to Home Ribbon >> click the Clear drop-down >> press Clear Contents to remove values without affecting formulas.

As a note, you can also press the DELETE key to erase the contents of the cells.

Boom! That is how simple it is to remove contents in Excel without deleting formulas.

2. Clearing Contents Without Deleting Formulas Using Go To Special Command

Alternatively, we can use the Go To Special feature to find the cells that contain only values, which means we can remove all of them at once and keep the cells containing formulas.

📌 Steps:

• Initially, press the Find & Select option >> choose Go To Special.

At this point, the Go To Special window appears.

• Later, enable the Constants radio button>> uncheck the Text option >> hit OK.

• Not long after this, select the B5:D10 cells >> press the DELETE key.

Finally, the results should look like the image shown below.

3. Applying VBA Code to Erase Contents Without Deleting Formulas

For one thing, VBA Macros in Excel help to automate repetitive tasks. Now, clearing contents is one such task that we can automate, therefore, follow along.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button.

• Second, go to the Insert tab >> select Module.

For ease of reference, copy the code from here and paste it into the window as shown below.

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

• Third, click the Run button or press the F5 key to execute the macro.

Consequently, the results should look like the screenshot given below.

How to Clear Contents in Excel Without Deleting Formatting

Lastly, we can also preserve the Excel formatting when clearing contents. In fact, the process is quite simple, hence let’s see it in action.

📌 Steps:

• To begin with, hold down the CTRL key and select the B5:E10 and C12:E12 ranges >> press the DELETE key.

Lastly, the final output should resemble the screenshot shown below.

Admittedly, we’ve skipped some of the relevant examples of clearing contents without deleting formatting which you may explore if you wish.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Conclusion

In short, this tutorial explores all the ins and outs of how to clear contents in Excel without deleting formulas. Now, we hope all the methods mentioned above will prompt you to apply them in your Excel spreadsheets more effectively. Furthermore, if you have any questions or feedback, please let me know in the comment section.