How to Clear Contents in Excel Without Deleting Formatting

Method 1 – Using an Excel Keyboard Shortcut to Clear Content Without Deleting Formatting

We have a dataset containing 5 employees’ names, sales, salary, working hours, and nationality. Each column has a different color formatting. The Sales and Salary columns have Accounting data. We want to clear all the content in this Excel without deleting formatting.

Steps:

• Select the cells (B4:F9 here) that you want to clear.
• Press the Del / Delete key on your keyboard.

• All the content is cleared, but the formatting still exists.

• The data formats also remain exactly the same. Write something in the Sales column and you will see it will show a dollar sign automatically after putting the value.

Method 2 – Using the Clear Button from the Editing Option to Erase Content Without Changing the Cell Format

Steps:

• Select the cells B4:F9 that you want to clear.
• Go to the Home tab and the Editing group.
• Select the Clear tool and choose the Clear Contents option.

Method 3 – Using the Go To Special Tool to Clear Content in Excel Without Deleting Formatting

Steps:

• Press the F5 key on your keyboard.
• The Go To window will appear.
• Click on the Special… button in the window.

• The Go To Special window will appear.
• Check the Constants option from the Select group and click on the OK button.

• All the cells of the Excel file will be selected.
• Press the Del / Delete key on your keyboard.

Method 4 – Applying VBA Macro to Clear Content Without Deleting Formatting

Steps:

• Go to the Developer tab and select Visual Basic.

• The Microsoft Visual Basic for Applications window will appear.
• Select Sheet 5 from the VBAProject group.
• A code window will appear on the right side.
• Insert the following code and press Ctrl + S.

Sub ClearContentsExceptFormatting()
Range("B4:F9").ClearContents
End Sub

• A Microsoft Excel dialogue box will appear.
• Click on the No button in this dialogue box.

• The Save As window will appear.
• Choose the Save as type: option as .xlsm file and click on the Save button.

• Close the code window.
• Go to the Developer tab and click on Macros.

• The Macro window will appear.
• Choose the Sheet5.ClearContentsExceptFormatting macro and click on the Run button.

• Here are the results.

Download the Practice Workbook

Related Articles

• How to Clear Multiple Cells in Excel
• How to Clear Cells with Certain Value in Excel
• How to Clear Cell Contents Based on Condition in Excel
• How to Clear Recent Documents in Excel
• How to Clear Excel Temp Files
• How to Clear Contents in Excel Without Deleting Formulas

<< Go Back to Clear Contents in Excel | Learn Excel