How to Clear Contents in Excel without Deleting Formulas (Also Using VBA)

You have a report template that works perfectly well, and now, you want to clear its contents to make way for a new set of data. So you wonder, is there a way to remove the contents of cells in one go without deleting the formulas (square root included)? There sure is.

Excel has this special feature that allows you to select cells based on their contents. Pretty cool, right? In this tutorial, I’ll show you how to utilize this feature to remove the existing data in one go while keeping the formulas.

Clear the Contents in Excel without Deleting the Formulas using the “Go To Special” Menu

1. Select all the data within your dataset (including those with formulas).

In the above example, all columns have formulas except those with orange headers.

2. From the Home menu, go to the Editing section.

Click the Find & Replace button and select Go To Special.

You can press F5 or CTRL + G for the keyboard shortcut.

3. The “Go To” menu will appear. Click the Special button.

4. The Go To Special menu will appear. From the list options, select Constants and click OK.

PRO TIP: If you want to remove only the numbers from the dataset (and keep the texts and formulas as is), you can do so by unchecking the Text checkbox (as shown below).

This tip is helpful if you want to keep the descriptive texts in between your data.

5. After pressing OK, notice that Excel now only selects cells in your sheet that don’t contain any formula.

6. Now, it’s time to remove the contents of these cells. Press the DEL key, and you’re all done!

Notice that the formulas are still intact and only get recalculated after removing the values.

Now you’re all set to enter a new set of data.

Clear the Contents in Excel without Deleting the Formulas Using VBA or Macros

If you need to regularly repeat this process at certain intervals (or whenever new data is available), it might be best to automate the process and have the macros do the work for you.

1. Press ALT + F11 to open the VBA Editor. Go to the Insert menu and select Module.

2. Copy the following code into the new module added.

Sub ClearContentsInSelectedRange()
'removes the constant values from the selected cells

    Dim rng As Range
    
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select the cells to remove the contents from:", Title:="Clear Contents", Type:=8)
    
    If Not rng Is Nothing Then
        rng.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
        rng.SpecialCells(xlCellTypeConstants, xlTextValues).ClearContents
    End If

    On Error GoTo 0
End Sub

Please note that the code above will remove all the texts and numbers in the selected cells. If you want to remove only the numbers from your dataset, use the following code instead:

Sub ClearContentsInSelectedRange_NumbersOnly()
'removes only the numbers from the selected cells

    Dim rng As Range
    
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Select the cells to remove the contents from:", Title:="Clear Contents", Type:=8)
    
    If Not rng Is Nothing Then rng.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents
    On Error GoTo 0

End Sub

3. Now, it’s time to add a button to our sheet, so we can click on it whenever we need to run the code. Close the VBA Editor.

4. Go to the Developer tab. Click the Insert button and select the Button Form Control (the first item on the list).

NOTE: If you can’t find the Developer tab in your ribbon, do the following:

• Go to File >> More >> Options.
• Under the Excel Options, select Customize Ribbon.
• Tick the Developer checkbox and click OK.

5. Click anywhere on the sheet where you want to add the button.

The Assign Macro menu will appear. Select the macro that we have recently added and click OK.

6. Let’s give the button a more meaningful name so that other users will know what the button is for.

Right-click on the button and select Edit Text.

I have named my button Reset Data.

7. Now, we’re all set to run the macro. Click the button.

A prompt will appear asking you to select the range which you would like to remove the contents from.

Drag your mouse over the sheet to select these cells. Once selected, click OK.

8. And that’s it! The macro will automatically remove all the contents from the selected cells while leaving the formulas as is.

IMPORTANT: Please note that once the macros run, the process cannot be undone. So please make sure you have a backup copy of the data before running it.

9. Remember to save the file in .xlsb or .xlsm format so you can rerun the macro next time.

But if you prefer to share the template without the formula in it, then learn how to save the data without the formula instead.

Conclusion

With Excel’s “Go To Special” feature, it’s now so easy to reset the contents of a report template in just one go. No need to meticulously go over each column and row to ensure that only the values (and not the formulas) are removed. You can even configure it to remove only the numbers (and not the texts).