After converting a range to a table, Excel automatically applies its formatting. This includes different colors for headers and a theme for the remaining cells. Although some people don’t mind Excel’s formatting to others it’s annoying and I would like to get rid of it.
In this tutorial, I will show you how you can remove formatting in the Excel table or change it.
By using Table styles
Removing the Excel table formatting is pretty straightforward. All you need to do is follow these easy steps.
1. Select any cell in the table that you wish to remove formatting
2. Navigate to Table Design >Table Styles then click on the drop-down arrow at the bottom right corner
It is important to note that the Table design Tab only appears if you have selected a cell.
3. A list with different styles will appear but we are only interested in the clear option located at the bottom of the list. Click on it to remove formatting from your table.
As you can see from the image above the formatting has been removed yet the data has been retained.
Clear formats option
Another quick and easy way to remove table formatting is by using the clear formats option.
1. First highlight the whole table including the headers
2. Place your cursor on one of the cells
3. Navigate to the home tab and click on the clear drop-down button under the editing group
4. Click on clear formats
5. That’s it the table will remain as a table but other formatting will be removed.
By Converting the table to a range
Another easy way to remove table formatting is by converting it to a normal range. Here are the steps.
1. Right-click on any cell located within the table
2. Click on Table > convert to range from the context menu that appears.
3. You will get a prompt to confirm that you want to convert the table to a range. Once you confirm, the table will be converted to a normal range.
Now you can add your styles manually.
Use VBA code to remove all table formats in your worksheet
1. Press Alt + F11 to open the VBA Editor in Excel.
2. Go to Insert > Module to create a new module.
3. Copy and paste the code into the module window.
Sub RemoveTableFormatting()
Dim tbl As ListObject
' Loop through each table in the active sheet
For Each tbl In ActiveSheet.ListObjects
' Convert each table to a range
tbl.Unlist
Next tbl
End Sub
4. Close the VBA Editor.
5. To run the macro, press Alt + F8, select RemoveTableFormatting, and click Run.
This code will iterate through all tables in the active worksheet and convert them back to regular ranges, removing the table formatting
How to change the formatting of an Excel Table
You can easily change to another style from a huge library of Excel Table preset styles. This gives you a chance to choose a style that matches.
1. First you need to click/select any cell within the table
2. Click on the design tab (Note that you have to click inside the table for this option to appear)
3. Click on Table Styles drop-down and select any style that you like or click on more to view other preset table styles.
You can hover the mouse over various styles to preview how your table would look before applying the style.
Use light theme (None)
The light theme looks exactly like the plain Excel cells. It overrides any other themes making it the best way to remove table formatting
1. First click on any cell on the table,
2. Click on the design tab that appears in the top right corner
3. Click on table styles and select the first option which is a light theme
As you can see the light theme has automatically removed table formatting on the table located to the left
How to delete Excel Table
Deleting the Excel table also deletes the formatting. However, note that this method also deletes the data.
1. First select the data on the table
2. Navigate to the home tab, under the cells group click on delete then select delete cells or rows.
Final Thoughts
Removing table formatting is easy with the methods that we have discussed in this article. However, if you don’t like the style you can browse other styles from the table styles option. If you are working on many tables at once then it will be easier to remove formatting on all of the at once using VBA code.
Read More: How to Move a Table in Excel
