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.
First of all, convert that range into an Excel Table by pressing CTRL + T from the keyboard
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:
- Select any cell inside the table
- Now click on the Design tab in the ribbon
- Click on the ‘More Option’ icon in ‘Table Styles’ group
- On the bottom, click on the Clear option
The above steps will remove table styles and keep the filter option as shown below.
- Now if you want to remove the filter option as well, then under the Design tab uncheck the Filter Button option.
It will remove all the filters from the table header as shown in the screenshot.
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)
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.
- Select any cell inside the table (from which you want to remove formatting)
- Press CTRL + A twice from the keyboard to select the complete table including headers
- Click on the Home tab in the ribbon
- Then click on the Clear option as shown.
- In the drop-down menu that gets opened, select the ‘Clear Formats’ option
This will remove all the formats from the table as shown in the screenshot
- Now if you want to remove the filter option as well then under the design tab uncheck the Filter Button option.
Doing so will remove all the filters from the header as shown
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.
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
- “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.
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
- Open the VBA editor by pressing Alt + F11 from the keyboard in the case of Windows and Opt + F11 if you are using Mac.
- Now here I have 2 sheets named sheet1 and sheet2. Sheet1 contains the source data while sheet 2 contains the Table.
- To insert a new Module click on the Insert tab.
- Now click on Module
- This will insert a new Module where you can write your VBA script.
- Copy and Paste the above VBA code into the module code window
- 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:
- How to Remove Conditional Formatting in Excel? (5 Easy Ways)
- How to Paste without Formatting in Excel (Shortcuts)
- How to Copy Formatting In Excel (4 Easy Ways)
- How to Highlight Every Other Row in Excel (Conditional Formatting & VBA)
- How to Remove Gridlines in Excel (Shortcut + VBA)
- How to Flip Data in Excel (Columns, Rows, Tables)?
- How to Remove Watermark in Excel (Image and Text)?
- How to Rename a Table in Excel?
- How to Delete Defined Name in Excel? (Name Manager + VBA)