How To Remove Table Formatting In Excel

Unsure how table formatting works in Microsoft Excel and need an easy guide?

How To Remove Table Formatting In Excel

Last Updated on

Excel has a cool feature in which you can quickly format a table, which comes in many different designs. To find this feature you need to go to the Insert tab, and then click the table. Here you will find the templates for your table.

This differs from a regular table in Excel, as formatting can give you more options such as filtering and sorting, as well as formulas for your data. However, sometimes you want to remove these features.

1

How To Remove Formatting

Follow the step by step guid on how to remove formatting below:

How To Remove Formatting

Step

1

Removing The Filtering And Sorting Options

First, we will look at removing the filtering and sorting options, if your table has this and you want them to be removed.

Step

2

Convert To Range

Click on your table, anywhere within the borders. Next, head to Table Design which is located at the top of the page (note that some versions of Excel will be labeled as Design), and then click Convert To Range.

Confirm that you want to convert it to a normal range. This will remove all of the sorting and filtering that comes with the table formatting.

Step

3

Select The Entire Table

To remove the formatting entirely, select the entire table by dragging the mouse and holding over every cell.

Step

4

Remove All Formats

Next, go to the Home tab and go to Editing. This can be represented by a small eraser icon.

Click this button and then select Remove All Formats. Now all the formats in your table will be removed and you should be left with just a table with your numerical or written data.

One way to test if this has been carried out successfully is to click one of the cells in your table and can spot if the formulas have been converted to the normal ways they are referenced. To understand this, your formula should have references to cells in the sheet, i.e. “=sheet1$D2/sheet1$C2”.

2

How To Revert Back To A Table

Below are the step by step on how to revert back to a table.

Step

1

Selecting Table

If you have decided to change your mind and want the table back, you can do this by going to the Insert tab and selecting table.

 

Step

2

Click Ok

Before you click ok, make sure you confirm that the cells have been highlighted correctly and that Excel has not missed out on any cells of your desired table.

Click Ok, and the table will automatically format, although something to bear in mind is that the table might not be in the exact same format as before. To check whether this is the case, you can follow the steps we laid out before and click one of the cells to determine the formula.

Step

3

Table Not Formatted Correctly

If the table has not been formatted correctly, then there are two options to rectify this.

The first is to manually input the formula back into the desired cells. The downside to this method is that it will take longer, especially if you have a lot of data and many cells to change.

The second option is to click on the Table Design tab and choose from the predetermined list of table formats.

You can choose the basic table which will be the first option available. This will allow you to have a table with all of the features, minus the formatting.

Important Points

Please note that once you have created your table, there are custom table styles available in a gallery under the custom section of Excel.

However, if you want a custom style table, they are only available in the current workbook, and will not be available in other workbooks.

Final Thoughts

Formatting a table is an easy process, and removing table formatting is just as easy. Using the tips above you will have much more control over your Excel tables without the confusion.