Table of Contents
Remove Table Formatting
This article will explain how to remove two types of table formatting in Excel. You’ll learn how to remove formatting from Excel tables and manually formatted data ranges.
Remove Formatting from Excel Tables
Automatic table formatting in Excel is applied when you create a table by clicking ‘Table’ on the Insert tab or ‘Format as Table’ on the Home tab.
This formatting includes color schemes banded rows and other style elements.
To quickly remove this table formatting follow these steps.
1. Click on any cell within the table to activate the Table Design tab.
2. Next on the Table Design tab in the Table Styles group click the first style (None).
This removes the automatic table formatting but retains the table functionality!
If you wish to remove the table functionality entirely and revert to a normal data range stay on the Table Design tab.
3. In the Tools group click Convert to Range.
4. Confirm the prompt to convert the table back to a regular range which removes all table-specific features.
Note: the headings in the first row were manually bolded before creating the table so this formatting will not be removed. Read on for more tricks.
Remove Manual Table Formatting
Manual table formatting is often applied to data ranges without built-in table features. This type of formatting includes changes made to font color borders etc.
To remove this formatting execute the following steps.
1. Select the range A1:D15.
2. On the Home tab in the Editing group click Clear.
3. Next click Clear Formats.
This action removes manual table formatting but also removes number formats such as the Currency format in column B which can be inconvenient.
Tip: to avoid losing number formats use Paste Special. First copy the range of cells (Ctrl+C) and paste it to another location by right-clicking selecting Paste Special and then choosing ‘Values and number formats’.
Next copy the newly pasted range return to the original location right-click select Paste Special and choose ‘Formats’. This will remove all manual formatting while keeping the number formats intact.
