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 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. Here, a VBA code is developed to clear the contents without deleting formulas. When we run the code, it removes the contents without erasing the formulas.

This Excel tutorial covers:

• Clearing contents without deleting formulas utilizing the Clear Contents option.
• Removing contents without deleting formulas by applying the Go To Special command.
• Applying VBA to erase contents without deleting formulas.
• Clearing contents without deleting formatting in Excel.

📃 Note: We have employed Microsoft 365 while preparing the dataset for this tutorial. You can apply the mentioned methods in versions from Excel 2007 onwards.

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

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.

Read More: Difference Between Delete and Clear Contents in Excel

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.

Read More: How to Clear Multiple Cells in Excel

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 and, 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.

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 yourself. Please make sure to do it by yourself.

Download Practice Workbook

Conclusion

In short, this tutorial explores all the ins and outs of how to clear contents in Excel without deleting formulas. You can clear contents without deleting formulas in Excel by using the Clear Contents option, Go To Special command, and also by applying VBA. 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.

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 Remove Formatting in Excel Without Removing Contents
• How to Clear Cell Contents Based on Condition in Excel
• How to Clear Formatting in Excel

<< Go Back to Clear Contents in Excel | Entering and Editing Data in Excel | Learn Excel