Remove Table Formatting in Excel

Table formatting is an awesome feature in Excel that allows you to sort and treat a range of data as a whole table. However, it does add some complications when you are trying to preform certain actions in Excel. So in this tutorial I am going to show you how to remove table formatting in excel. Lets first take a look at table and no table formatting.

There are two tables below one has table formatting while the other one does not have table table formatting. You can quickly add table formatting in Excel with just highlighting the information and pressing Control + T

How to Detect Table Formatting?

When you select the cells in the table you will be able to see the table name in the top corner. You can see the table name in the table name box. Also under drop down section with cell names. Also you will be given the Table Design heading in the top ribbon Don’t rely on the appearance of filters, borders, and coloring to indicate something is a table. These features can be added to a range of data.

Table Formatting vs Cell Formatting

Cell formatting includes text size, color, borders, and other variables. This format lets you customize spreadsheet cells.

Table formatting involves preparing a table of data. Excel applies a table style, which includes color-coding, borders, and other formatting options, to a range of data. The table format allows sorting, filtering, structural referencing, and data validation criteria. Adding rows automatically expands tables, making it easier to manage vast volumes of data.

Quickly Remove Table Formatting in Excel

You will find the methods below will quickly help you remove table formatting in Excel by converting tables to ranges. A range is simply a collection of data as opposed to a table.

Convert Table to Range to Remove Table Formats

You can quickly convert the table to range using the steps below. Important to note this remove table formatting not all the cell formatting. Remember a range of data is table-lized data or a group of cells that make up a rectangular selection of cells in a worksheet

1. Select the table by highlight all the cells in the table, or pressing Control A in the table.
2. Navigate to the to Ribbon and Choose Table Design
3. Under the Tools Section in Table Design, choose Convert to Range

All the filtering and table features will be removed. If you don’t like the colors you can simply use clear all cell formats

If you want to clear the cell coloring press Ctrl + Shift + Alt + F5 then clear cell formats

Note that this shortcut will clear all formatting, not just cell color. If you only want to remove cell color and not other formatting, you can manually change the cell color back to the default color. To do this, select the cells that you want to change and go to the “Home” tab in the “Font” section. Click the “Fill Color” button and select “No Fill”. This will remove the cell color and restore the default cell background color

Use the Table Styles to Remove Table Formatting

Now that you are aware of how to find the Table Design table in Excel. You can easily just remove the table formatting by navigating over to table styles removing the the table formatting. Here are the steps

1. Select your Table
2. Click Table Design, under the Table Styles > click the down arrow > Clear

Simply navigating back down to the clear section will allow you to remove the table formatting.

This will again leave you with some of the cell formats and filtering options that are common to a table. You can simply use clear clear formats to remove these features.