Remove Table Formatting in Excel

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.

If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Excel Functions and Data Analysis in Excel.