How to clear table formatting style without losing table data in Excel?

In this article, we will learn how to remove the table formatting in an excel file without losing the data. Sometimes the table formatting may hide some parameters based on some conditions and that may get lost while copy-pasting the data from one sheet to another. Let’s have a look on the steps to clear table formatting.

The formatting of a table can be removed using the following options −

• By using Convert to Range under Table Tools

• By using Clear Table Design under Table Design

• By using Clear Formats under Editing

Convert to Range feature

This feature removes the table features and formatting and keep the design as is along with the table data. After applying Convert to Range, the table will not have any options and filters.

Step 1− Select the table range to remove the formatting, now the Table Tools tab will get activated.

Step 2− Go to Table Tools > Tools > Convert to Range.

Step 3− However, you can right-click on any cell of the table to get the Convert to Range option.

Step 4− Now, click Yes in the below prompt to apply the changes.

Step 5− Here you can see that the table formatting has been removed and table’s color and style theme still persists.

Clear Table Design

This feature will remove all the formatting and table color but keep the features of the table without losing the table data. After clearing all the table formatting, you will still be able to perform table operations like filtering, data validations etc.

Step 1− After selecting the table range, go to Table Tools > Table Styles > Clear.

Step 2− Click Clear and all the formatting of the selected table will be removed.

Note− The above two methods remove only predefined formatting not the custom formatting applied by a user.

Clear Formats under Editing

This method is used to clear all formatting of a table, both predefined and custom. Follow the below steps to apply the changes.

Step 1− Select the table range and go to Home > Editing > Clear > Clear Formats.

Step 2− The final result will be as following without any table features and formatting.

Note− Please note that this method clears all formatting completely, including number formats, date format, etc.

Conclusion

Hence, we have learnt 3 methods to remove the formatting of table in excel file. Carefully use the last methods as this will remove all the field specific formatting as well and can display all hidden data as well, if any. Try to apply the changes on a snippet of table format first before applying on the whole data.