How to Clear Contents in Excel Without Deleting Formatting

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Clear Contents in Excel Without Deleting Formatting: 4 Ways

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.

Sample Dataset to Clear Contents in Excel Without Deleting Formatting

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.

Press Del/Delete to Clear Contents in Excel Without Deleting Formatting

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.

Cleared Contents Except Formatting

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.

Read More: How to Clear Contents Without Deleting Formulas Using VBA in Excel


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.

Using Clear Contents Option to Clear Contents in Excel Without Deleting Formatting

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.

Clear Contents Option in the Context Menu


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.

Go To 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.

Go To Special Window

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

Press Del/Delete Key to Clear Contents in Excel Without Deleting Formatting

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.

Access the 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

Microsoft Visual Basic for Applications Window

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

Microsoft Excel 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.

Save the Excel as .xlsm File to Enable Macro

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

Access the Macros Tool

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

Run the Macro to Clear Contents in Excel without Formatting

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.

Cleared Contents in Excel Without Deleting Formatting

Read More: Excel VBA to Clear Contents of Range


Download Practice Workbook

You can download our practice workbook from here for free!


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.


Related Articles

Tanjim Reza
Tanjim Reza

Hello! I am Md. Tanjim Reza Tanim. I have just completed my B.Sc from Naval Architecture & Marine Engineering Department, BUET. Currently, I am working as an Excel & VBA content developer. I always had a great fascination with Microsoft Excel and its cool functions and formulas. Here, I am learning every day about new functions and formulas and working on applying MS Excel to the analysis of our real-life problems. I have great enthusiasm for learning any kind of new things, writing articles, and solving real-life problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo