Written by Allen Wyatt (last updated October 12, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
Roni wants to clear everything in a worksheet except for cells which may contain formulas. This task can be completed either manually or through the use of a macro.
If you want to do the clearing manually, you can follow these steps:
Figure 1. The Go To dialog box.
Figure 2. The Go To Special dialog box.
This works great if you only need to clear out the non-formula contents of a worksheet once in a while. If you need to do it more often, then you can simply use the macro recorder to record the above steps. Or, if you prefer, you can create your own macro from scratch, such as the following one:
Sub ClearAllButFormulas() Dim wks As Worksheet 'Ignore errors in case there are only formulas On Error Resume Next For Each wks In Worksheets wks.Cells.SpecialCells(xlCellTypeConstants).ClearContents Next On Error GoTo 0 Set wks = Nothing End Sub
This macro is particularly useful if you need to clear out all the non-formula cells in an entire workbook. The reason is because it does the clearing on every worksheet in the entire workbook, without you needing to do the clearing manually.
You should use caution when using this macro. Make sure you really do want to clear out everything except for cells that contain formulas in the worksheet or workbook before you run this macro. It would be a shame to accidentally get rid of the contents of cells that you really needed to keep. If you are a bit squeamish about such a possibility, you might want to use the following version of the macro:
Sub ClearAllButFormulas2() Dim wks As Worksheet Dim sTemp As String Dim iCheck As Integer sTemp = "This macro deletes everything in the current " sTemp = sTemp & "workbook except formulas. Once done, " sTemp = sTemp & "it cannot be undone." & vbCrLf & vbCrLf sTemp = sTemp & "Are you sure you want to continue?" iCheck = MsgBox(sTemp, vbYesNo + vbExclamation, "Warning!") If iCheck = vbYes Then 'Ignore errors in case there are only formulas On Error Resume Next For Each wks In Worksheets wks.Cells.SpecialCells(xlCellTypeConstants).ClearContents Next On Error GoTo 0 Else MsgBox "Operation cancelled." End If End Sub
This version displays a message box asking if you are sure you want to do the clearing. The upshot is that you have less of a chance of messing up your workbook by accident.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9097) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Clearing Everything Except Formulas.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
Insert a symbol into a cell, and it should stay there, right? What if the symbol changes to another character, such as a ...
Discover MoreWhen entering many negative values in a worksheet, you could save time if you didn't need to enter the minus sign for ...
Discover MoreSeparating text values in one cell into a group of other cells is a common need when dealing with text. Excel provides a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-10-13 08:39:24
Alex Blakenburg
A possible issue might be, that to provide a bit of an audit trail people often add multiple figures together in a cell. Since this involves using an "=" sign it will be regarded as a Formula and not a constant and will not be deleted.
2024-10-12 05:31:58
Dave Bonin
There’s something to be said for adding an intermediate check to perhaps visibly highlight all formula cells. Perhaps with some unusual highlighting such as orange with yellow spots. Something that would be easy to apply and to remove using a macro.
This technique could be used as intermediate checks for other forms of mass change with a macro.
Alternately, a macro could automatically create a backup copy of your file before proceeding just to protect you from our own oversight.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments