How to Clear Contents in Excel Without Deleting Formatting

Excel is extremely useful for handling large datasets. When working with Excel, it 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. In this article, I will show you 4 quick ways to clear contents in Excel without deleting formatting.

Table of Contents hide

Download Practice Workbook

4 Ways to Clear Contents in Excel Without Deleting Formatting

1. Use Keyboard Shortcut to Clear Contents Without Deleting Formatting

2. Use Clear Button from Editing Option

3. Use Go To Special Tool

4. Apply a VBA Code to Clear Contents Without Deleting Formatting

Conclusion

Download Practice Workbook

You can download our practice workbook from here for free!

Clear Contents Except Formatting.xlsm

4 Ways to Clear Contents in Excel Without Deleting Formatting

Say, we have a dataset containing 5 employees’ names, sales, salary, working hours, and nationality. Each column has 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. You can use any of the 4 effective ways given below to accomplish this.

In this article, we have used the Office 365 version of Microsoft Excel. But, you can use these ways in any version of Excel that is available to you. If you face any problems regarding versions when using these ways, please leave us a comment below.

1. Use 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. 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 would see it would show a dollar sign automatically after putting the value.

2. Use Clear Button from Editing Option

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.

Note:

You can also find the Clear Contents option in the context menu when right-clicking on your mouse over a range of data.

3. Use Go To Special Tool

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. Apply a VBA Code 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 Sheet5 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 would 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.

Conclusion

So, in this article, I have shown you the 4 quick and effective ways to clear content in Excel without deleting formatting. I suggest you go through the full article carefully to understand it better and apply its knowledge afterward according to your needs. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to leave a comment. And, visit ExcelDemy for many more articles like this.