Remove Table Formatting


Remove Formatting from Excel Tables | Remove Manual 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.

Excel Table

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).

Click First Style (None)

This removes the automatic table formatting but retains the table functionality!

Remove Table Formatting

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.

Convert to Range

4. Confirm the prompt to convert the table back to a regular range, which removes all table-specific features.

Regular Range

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.

Formatted Data Range

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.

Click Clear

3. Next, click Clear Formats.

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.

Remove Manual Table Formatting

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.