How to Clear Contents in Excel Without Deleting Formulas

Get FREE Advanced Excel Exercises with Solutions!

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 Productionand Cost. In this scenario, we want to clear contents in Excel without deleting formulas. So without further delay, let’s dive right in!

Dataset showing unit production and per unit cost of widget production

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.

Pressing Show formulas button in the formulas tab

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

Displaying cells with and without formulas

  • 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.

Using Clear contents in the home ribbon

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

Cleared contents with clear contents option 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.

Applying Go To Special in the Find & Select tab

At this point, the Go To Special window appears.

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

Enabling constants option and unchecking text option

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

Selecting cells and pressing delete key

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

cleared contents using Go To special without affecting formulas

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.

Launching visual basic in the developer tab

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

Inserting 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

End Sub

VBA for clearing contents without deleting formulas

Code Breakdown

In the following section, we’ll explain the VBA code used to clear contents in Excel without deleting formulas.

  • In the first portion, name the sub-routine clear_content_keep_formula()
  • Next, define the variable val as Range.
  • Then use the Set statement to store the worksheet number (Sheet 6) and the range of cells (C5:D10).
  • Afterward, apply the ClearContents method to clear cells containing constant values.

📃 Note: Please change the sheet number and enter the appropriate cell range when using this VBA code.

VBA code explanation

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

Running VBA macro

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

Using VBA macro to clear contents in excel without deleting formulas

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.

Selecting multiple ranges with ctrl key and pressing delete

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

cleared contents without affecting formatting

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.

Practice Section for clear contents in excel without deleting formulas


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.

Prantick Bala

Prantick Bala

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF