How to Remove Table Formatting in Excel?

by

Excel Table is an amazing built-in feature that allows us to easily work with the data. 

When you convert a data set into an Excel Table, you’re able to use some additional features that are only available within Excel Table.

For example, instead of using cell references and ranges in your formulas, you can use structured references (which mix of formulas a lot easier to create and edit).

Excel Tables also make it quite easy to create Pivot Tables and use them in newer functionality such as Power Query 

However, when you convert your range into an Excel Table, Excel applies some predefined formatting to it that is not required in some cases and lots of users find it annoying and unnecessary. 

So how to remove this Excel Table formatting?

In this tutorial, I will show you how to remove table formatting in Excel. 

For demonstration purposes, I used a sample data set of the top 10 employees in a company that includes Employee ID, First Name, Last Name, Designation, and Salary as shown in the screenshot.

Dataset with some formatting

First of all, convert that range into an Excel Table by pressing CTRL + T from the keyboard

Data converted to an Excel table

As you can see in the screenshot above, Excel applied some formatting to the range when we converted the data into an Excel table. 

Now, I will show you the various method using which you can easily remove formatting in the table.

Method 1: Remove Only the Table Formatting (Not Custom Formatting)

You can remove predefined formatting by using the option in the ribbon. 

Here are the steps you need to follow:

  1. Select any cell inside the table
Select any cell in the Excel Table
  1. Now click on the Design tab in the ribbon
Click on the design tab
  1. Click on the ‘More Option’ icon in ‘Table Styles’ group
Click on More Option icon
  1. On the bottom, click on the Clear option 
click on the Clear option

The above steps will remove table styles and keep the filter option as shown below.

Table formatting has been removed
  1. Now if you want to remove the filter option as well, then under the Design tab uncheck the Filter Button option.
Uncheck filter option

It will remove all the filters from the table header as shown in the screenshot.

Filters have been removed

Note that the above steps would only remove the formatting that was applied when we converted the data into an Excel Table.

These steps would not remove any formatting that was applied to the data before converting it into an Excel table (such as the blue color header cells, the white font format, the borders. etc).

Also, we have only removed the formatting from the table, but we have not converted the table back into range.

So while you do not see the formatting in the table, you can still continue to use it just like any other Excel Table (in Power Query, data models, or in formulas using structured references)

Also read: Excel Table vs. Excel Range – What’s the Difference?

Method 2: Remove All Formatting from the Excel Table

If you would like to remove all the formatting from the table (including the formatting that is applied when a data range is converted into an excel table, as well as all the other formatting that was already applied to the data before), you can do that using the steps in this section.

  1.  Select any cell inside the table (from which you want to remove formatting) 
Select any cell inside the table
  1. Press CTRL + A twice from the keyboard to select the complete table including headers
Select the entire table
  1. Click on the Home tab in the ribbon
Click on the Home tab
  1. Then click on the Clear option as shown.
click on the Clear option
  1. In the drop-down menu that gets opened, select the ‘Clear Formats’ option
select the 'Clear Formats' option

This will remove all the formats from the table as shown in the screenshot

All formatting in removed
  1. Now if you want to remove the filter option as well then under the design tab uncheck the Filter Button option.
Uncheck Filter button option

Doing so will remove all the filters from the header as shown

Filers in header cells are removed

In this method, I have shown how you can remove all the table formatting in Excel. The resulting data looks like raw Excel data but it is still a table with no predefined or custom formatting. 

Also read: How to Remove Gridlines in Excel

Method 3: Remove Excel Table Formatting using VBA Macro

You can also use a simple via code to quickly remove the table formatting from an Excel Table

Below is the VBA code that you can use to remove the table formatting from an Excel Table

Sub RemoveTableFormating()
Sheet2.ListObjects("Table1").TableStyle = ""
End Sub

You’ll need to customize the below VBA code depending on what sheet has the table and the name of the Excel Table:

  • “Sheet2”‑ Sheet containing the Table: change this to the name of the sheet that has the
    Table.
  • “Table1”‑  Specify the name of your Table instead of Table1. You can check the name of your table by clicking on the design tab and looking for the Table name in the properties section.

Now run the script and it will remove the formatting from the Table as shown in the screenshot below.

Excel table formatting has been removed

If you’re wondering what to do with the VBA code and where to put it, next I have the steps on how to use this VBA code in Excel

Instruction to Run a VBA Script

  1. Open the VBA editor by pressing Alt + F11 from the keyboard in the case of Windows and Opt + F11 if you are using Mac.
Open the VBA editor
  1. Now here I have 2 sheets named sheet1 and sheet2. Sheet1 contains the source data while sheet 2 contains the Table.
sheet 2 contains the Excel Table
  1. To insert a new Module click on the Insert tab.
Click the insert tab
  1. Now click on Module
click on the module option
  1. This will insert a new Module where you can write your VBA script.
  2. Copy and Paste the above VBA code into the module code window
  3. To run the code, place the cursor anywhere in the code and then press the F5 key (or click on the green play button in the toolbar)

If you only want to remove the formatting from one or two Excel tables, doing it manually would be faster and more efficient.

However, if you’re anyway working with the VBA code, and as a part of it you want to remove the formatting from the table, you can use the VBA code mentioned above.

In this tutorial, I showed you various methods for removing table formatting in Excel. 

And if you want to remove all the formatting from the table (including the one that was applied to the table before it was converted into an Excel Table), use the second method.

In addition, you can also remove table formatting by employing the VBA script as discussed in Method 3. 

Other articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment