Clear Excel Cell Contents While Keeping Formulas and Formatting
Question details
Clear entered values from specific Excel cells while preserving formulas and formatting.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to clear constants in selected ranges without affecting formulas or formatting.
- Observed behavior
- Expanding blank cells to entire rows can affect cells outside the intended range, including cells containing formulas.
Identify the worksheet and ranges whose entered values you want to clear. Check that the target cells are editable and that you want to remove all constants in those ranges, including text and numbers.
Use VBA to clear constants in the target range
Use SpecialCells to clear entered values while leaving formulas and formatting intact.
SpecialCells(xlCellTypeConstants) selects cells containing constants, not formulas. ClearContents removes their contents without clearing cell formatting. Avoid EntireRow unless you intend to affect the full worksheet rows.
In desktop Excel, open the workbook and select Developer > Visual Basic. If the Developer tab is hidden, enable it in Excel's ribbon settings.
In the VBA editor, select Insert > Module and paste this code: Sub ClearEnteredValues() Dim target As Range Set target = ActiveSheet.Range("A9:J9") Dim constants As Range On Error Resume Next Set constants = target.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If Not constants Is Nothing Then constants.ClearContents End Sub
Replace A9:J9 with the range you want to clear. To process other ranges, repeat the selection and clearing logic for each range, resetting constants to Nothing before each SpecialCells call.
Activate the intended worksheet, then select Developer > Macros, choose ClearEnteredValues, and select Run.

Clear constants with Excel Go To Special
Use this method to clear a selected range without writing a macro.
Clear entered values while keeping formulas in WPS Spreadsheets
Use Go To Special in WPS Spreadsheets to select constants in a range, then clear their contents without removing formulas or formatting.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to choose your Excel workbook.
- 2. Select the range: On the worksheet, select the cells whose entered values you want to clear.
- 3. Find constants: Open Go To Special from the Find or Go To menu, select Constants, and confirm the selection.
- 4. Clear contents: Press Delete or use the Clear Contents command for the selected cells, then check that the formulas remain.

Frequently Asked Questions
Does ClearContents remove cell formatting?
No. In Excel VBA, ClearContents removes cell contents but leaves formatting intact. Use it instead of Clear when you want to preserve formatting.
Will formulas that return an empty string be cleared?
No. SpecialCells(xlCellTypeConstants) does not select formula cells, even if a formula displays an empty string.
Why does SpecialCells return an error?
SpecialCells raises an error if it finds no cells matching the requested type. Handle that case before calling ClearContents, as shown in the VBA example.
Can I clear only numbers and keep text constants?
Yes. In Excel, select the range and choose Home > Find & Select > Go To Special > Constants. Clear the other constant-type checkboxes, leave Numbers selected, and then clear the selected contents.




