Tables have a lot of great features and are perfect for working with and storing your tabular data. But one feature that many users aren’t keen on is the default table formatting that’s automatically applied when you convert your range to a table. After creating a table, you might want to get rid of this default format and opt for a less distracting look. This post will show you how to remove the table formatting in your Excel table.
Remove Table Format from the Design Tab
When you create an Excel table with your tabular dataset, Excel will automatically apply a default table format. If you don’t like the default format that’s applied, you can remove it! Here’s how to remove the format from your table. When you select any cell inside your table, with will cause a new tab to appear in the ribbon. The Table Design tab is a contextual tab that only appears when you have a table selected. This will clear out all the table formatting but leave the data and the table object intact. Any other additional formatting that has been manually applied to the table such as number formats on a column will remain as well. You will know it is still a table but without the formatting, because you can still see the table resize handle in the lower right of the table as well as the Table Design tab remains visible with the table selected.
Change the Table Format to Simple
Another option to get a similar effect to removing the format is to select the most simple table style from the default options. This will also remove the formatting but leave everything else intact with the table.
Remove the Table and Format with Convert to Range
It might be the case that you want to remove the table and also the formatting it comes with. Removing the table can be done fairly easily with the Convert to Range command, but while this will remove the table it will leave the formatting behind. Here’s how you can remove the table and then remove the formatting as well. Excel will show you a pop-up asking you if you really want to convert the table to a normal range. Excel removes the table and you can see this because the resize handle in the lower right corner is gone and no Table Designs tab will appear when the active cell is inside the dataset. But the range looks the exact same otherwise removing the table doesn’t remove the default format. You will need to select this range and clear all the formatting manually. This will remove all the table formatting, but it will also remove any other formatting such as number and date formats that have been applied to the columns.
Remove Table Format in All Tables with VBA
Suppose you have a workbook with many tables and you want to remove the table format from all of them. This would mean you need to select each table and then clear the format from the Table Design tab one at a time. This is something that can be automated with a VBA macro! Press Alt + F11 on your keyboard to open up the visual basic editor. Then go to the Insert menu in the editor and select the Module option. This will add a new module where you can add your VBA code. Paste the above code into the new module, and you will be able to run this code to remove table formatting from each table in the workbook. The code loops through each table in the workbook and then sets the TableStyle property to nothing which will clear the format.
Remove Table Format in All Tables with Office Scripts
You can also use Office Scripts to automate the table format removal across all your tables. Go to the Automate tab and click on the New Script option to open the Office Script editor. Paste the above code into the Code Editor and press the Save script button. When you press the Run button, the code will loop through all the tables in the workbook and set the table style to null which clears out any table format styles.
Conclusions
The default table format styles can be quite ugly, so removing them will help make your spreadsheets look good! Removing the formats is a painless process and can be done a few ways while still keeping the table objects. You can even batch the format removal for all your tables across the workbook using a VBA macro or an Office Script to loop through all your table and clear the formats. Do you remove the default table formats when you make a table? Do you know any other ways to get the job done? Let me know in the comments!
