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.

Applying VBA to Clear Contents without Deleting 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!

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

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.

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

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.

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.

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.

Practice Section for clear contents in excel without deleting formulas

Download Practice Workbook


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

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

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

  1. Reply
    Mongwongprue Marma Nov 10, 2023 at 11:55 PM

    Excellent in a word. However, Thanks a lot.

Leave a reply

Advanced Excel Exercises with Solutions PDF