How to Remove Table Formatting in Excel (2 Easy Methods)

In this article, I am going to show you “How to Remove Table Formatting in Excel”. Sometimes it is important to remove table formatting in Excel. People often face difficulties while doing that but actually, it`s not that difficult. All you need is some set of tasks which shall be done in a sequence for removing the table formatting in Excel. Here, I will be describing two methods for achieving the goal of the task.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Useful Methods to Remove Table Formatting in Excel

To illustrate the methods, first, let’s take a sample table like this below.

Sample Table

Now we will try to remove the Table formatting of this table using two easy methods. First, Let’s learn the first method.


1. Remove Table Formatting with Clear Formats Option

Let`s say your table contains formatting with filters. The easiest way to clear table formatting is to use the Clear Formats option. Moreover, after using that you need to remove the filter option also. We can clear the format by taking two different approaches. Let’s see the first approach.

1.1 Apply Clear Format Tool from Editing Group

  • First, select the entire table.

Remove Table Formatting with the "Clear Formats" option

  • After this press on to the Home tab and in the Editing group of Home tab look for the Clear option.

Remove Table Formatting with the "Clear Formats" Option

  • After selecting the Clear option, you will get a drop-down list. From there, select the Clear Formats option.

Remove Table Formatting with the "Clear Formats" option

  • After pressing the Clear Formats option, you will see all the colors from your table have gone and it will have black font color and white background color. But, you will still have the Filters which we need to remove.

Remove Table Formatting with the "Clear Formats" option

1.2 Apply Clear Table Format Tool from Table Design Tab

  • You can do the same task in an alternate way. Just select any Cell>Go to Table Design tab>Go to Table Styles Group>Click On the Down Arrow

Remove Table Formatting with the "Clear Formats" Option

  • Now, at the bottom of the menu, Select Clear Option.

Remove Table Formatting with the "Clear Formats" option

  • Here you will get the same result.
  • Now to disable the filters, select any cell which has the filter button with it and under the Home tab select the Sort & Filter option which is placed in the Editing. 
  • In the Sort & Filter drop-down list, you will find that the Filter command is active. To make it inactive, just click on it.

  • Alternatively, you can also disable the Filter option by selecting the Filter option under the Data tab.

  • After this, you will see all the filters in your worksheet will be gone.

Remove Table Formatting with the "Clear Formats" option

This is how you can remove table formatting in your Excel worksheets.

Note: In the above process, the table formatting and Filtering options of the table are removed but it will still work as a table.

Read More: How to Remove Format As Table in Excel


Similar Readings


2. Remove Table Formatting by Converting Table to a Range

Another process of removing table formatting is to convert the table to a normal range and then change the theme, font, and border color. Unfortunately, this process is much longer than the previous process. The process of this method is as follows.

  • First, select any cell from your table.

Remove Table Formatting by Converting Table to a Range

  • By selecting a cell from your table, you will get to see the Design Under the Design tab in the Tools section select the Convert to Range option.

  • After selecting Convert to Range option, you will get to see the below window. Click Yes there.

  • After doing this you will see that the filters from your table disappear. Now you have to change the font, theme, and border color.

Remove Table Formatting by Converting Table to a Range

  • To change the theme color, select all the cells which were in the table, and under the Font section of the Home tab, select the Fill Color option and press No Fill. By pressing it, you will see the orange color from the cells disappears leaving the white theme color.

Remove Table Formatting by Converting Table to a Range

  • Now for changing the font color, select all the cells of the table and press on to the Font Color option beside the Fill color option in the Home tab, and select the Automatic button which is actually the black color.

Remove Table Formatting by Converting Table to a Range

  • Now select the No Borders button under the All Borders option which is under the Home tab just beside the Fill color and Font Color options.

Remove Table Formatting by Converting Table to a Range

  • After doing all these you will see that the table formatting is removed from your worksheet. By doing all these the cells from the table will now look like the picture below.

Remove Table Formatting by Converting Table to a Range

Note: In this way, the converted range will function as a range, not as a table.

Read More: Excel VBA to Create Table from Range (6 Examples)


Conclusion

Removing table formatting from Excel is an interesting and important task. In this article, I described two methods of doing that. Hopefully, after reading this article, you will not face any difficulties while removing the table formatting in Excel. Stay connected and comment below to ask any questions regarding this article.


Related Articles

Tags:

Siam Hasan Khan

Siam Hasan Khan

Hello! Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I have completed my BSc in Electrical and Electronic Engineering from American International University-Bangladesh. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and an attitude to grow continuously. Continuous improvement and life-long learning are my mottoes.

4 Comments
  1. Hello,

    Thank you for your clear explanation.

    I was interested in this question because I have trouble with my table because of this functionality ‘filtered’.
    I pasted some new data to a table already filtered, and those new data are excluded from this filtered list :
    – They appear in white
    – When I apply a special filter it works only on the original data and leaves the rest (data paste afterward) unfiltered.

    I tried your method to remove the filtered format, but even after that the pasted data are still not included when I want to apply a global filter to my data…

    Have you any idea of what is happening and why the data added are not included?

    Thank you in advance for your answer,

    • Hi CLÉMENTINE! You have to select the newly added data from the filter list first (mark the Select All box for that). Then the filter command will work as usual. Thanks!

  2. THANKS. IT WAS OF A GREAT HELP.

  3. THANKS. IT WAS OF A GREAT HELP.

Leave a reply

ExcelDemy
Logo