How to Clear Contents in Excel Without Deleting Formatting

Clearing contents without deleting format means erasing cell data but without changing cell format. In this Excel tutorial, you will learn how to clear contents in Excel without deleting formatting using different features.

When working with Excel, there is a frequent need to keep the same formatting with different data. In this regard, sometimes, we need to clear cell contents without deleting formatting. If you are looking forward to the ways of doing this, you have landed in the right place.

Likewise, we have a dataset. Now, we are clearing dataset contents with the Clear button. After that, when we enter “Mili” and “1300” in the B5 and C5 cells respectively, we observe the same formatting as before.

The Excel tutorial will cover the use of the keyboard shortcut, Clear button, Go to Special tool, and VBA Macro tool to clear contents without deleting formatting.

1. Using Excel Keyboard Shortcut to Clear Contents Without Deleting Formatting

The easiest and quickest way to clear cell contents without deleting formatting is to use a keyboard shortcut. Say, we have a dataset containing 5 employees’ names, sales, salary, working hours, and nationality. Each column has a different color formatting. And, the Sales and Salary columns have Accounting formatted data. Now, we want to clear all the contents in this Excel without deleting formatting.

Follow the steps below to do this.

📌 Steps:

• First and foremost, select the cells (B4:F9 here) that you want to clear contents from.
• Subsequently, press the Del / Delete key on your keyboard.

• Thus, you can see all the contents are cleared, but the formatting still exists just as it was. And, the output should look like this.

• And, the data formats also remain exactly the same. To visualize this, write something in the Sales column and you will see it will show a dollar sign automatically after putting the value.

Read More: Difference Between Delete and Clear Contents in Excel

2. Using Clear Button from Editing Option to Erase Contents Without Changing Cell Format

Another quick way to clear contents in Excel without deleting formatting is to use the Excel Clear button. Go through the steps below to accomplish this.

📌 Steps:

• Select the cells B4:F9 that you want to clear.
• Subsequently, go to the Home tab >> Editing group >> Clear tool >> Clear Contents option.

Consequently, you will see that you have cleared all the contents from the selected range without deleting the formatting.

Read More: How to Remove Formatting in Excel Without Removing Contents

3. Using Go To Special Tool to Clear Contents in Excel Without Deleting Formatting

Besides, you can also use the Go To Special dialogue box to clear contents without deleting formatting. Follow the steps below to achieve your result using this method.

📌 Steps:

• At the very beginning, press the F5 key on your keyboard.
• As a result, the Go To window will appear.
• Click on the Special… button in the window.

• At this time, the Go To Special window will appear.
• Now, put the radio button on the Constants option from the Select group >> click on the OK button.

• As a result, all the cells of the Excel file will be selected automatically.
• Following, press the Del / Delete key on your keyboard.

Consequently, all the contents of the Excel file will be removed except for formatting.

4. Applying VBA Macro to Clear Contents Without Deleting Formatting

Moreover, you can apply a VBA code to clear cell contents without deleting formatting. Go through the steps below to achieve this target.

📌 Steps:

• First, go to the Developer tab >> Visual Basic tool.

• As a result, the Microsoft Visual Basic for Applications window will appear.
• Now, select Sheet 5 from the VBAProject group.
• Following, a code window will appear on the right side.
• Here, write the following code and press Ctrl + S.

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

• As a result, a Microsoft Excel dialogue box will appear.
• Afterward, click on the No button in this dialogue box.

• Consequently, the Save As window will appear.
• Following, choose the Save as type: option as .xlsm file here and click on the Save button.

• Now, close the code window.
• Afterward, go to the Developer tab >> Macros tool.

• At this time, the Macro window will appear.
• Now, choose Sheet5.ClearContentsExceptFormatting macro and click on the Run button.

• Thus, your target is acquired and you will see that the B4:F9 cell contents are gone, but the formatting still remains. For instance, the final outcome would look like this just the same as before.

Read More: How to Clear Formatting in Excel

Download Practice Workbook

You can download our practice workbook from here for free!

Conclusion

So, in this article, we have shown you effective tips and tricks to clear content in Excel without deleting formatting. You can use simply the keyboard shortcut or the Clear button to erase cell contents without changing the format. Besides, the Go to Special dialog box facilitates us to clear contents from Excel cells without deleting formatting. The VBA Macro is another useful tool to clear contents without formatting from multiple worksheets automatically.
If you have any further queries or recommendations, please feel free to leave a comment.

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 | Entering and Editing Data in Excel | Learn Excel