Do you want to remove the formatting from your Excel table?

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.

1. Select any cell inside 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.

2. Go to the Table Design tab.
3. Click on the More options button found in the lower right of the Table Styles section of the ribbon tab. This will expand the selection of preset styles.

4. Click on the Clear command.

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.

1. Select your table.
2. Go to the Table Design tab.
3. Click on the More options in the Table Style section.
4. Select the None format option found under the Light style section.

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.

1. Right-click anywhere on the table.
2. Select the Table option from the menu.
3. Select the Convert to Range option from the submenu.

Excel will show you a pop-up asking you if you really want to convert the table to a normal range.

4. Click on the Yes button.

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.

5. Select the entire range of data with the formatting. You can Press Ctrl + A with the active cell inside the data range to select the entire dataset.
6. Go to the Home tab.
7. Click on Clear in the Editing section of the ribbon.
8. Select Clear Formats from the options.

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.

Sub RemoveTableFormat()

Dim wsObj As Worksheet
Dim tableObj As ListObject

For Each wsObj In ActiveWorkbook.Worksheets
    For Each tableObj In wsObj.ListObjects
        tableObj.TableStyle = ""
    Next tableObj
Next wsObj

End Sub

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.

function main(workbook: ExcelScript.Workbook) {
    //get all tables in the workbook
    let tbls = workbook.getTables();
    //loop through each table in tbls and
    //set the style to null
    for (let i = 0; i < tbls.length; i++) {
        tbls[i].setPredefinedTableStyle(null);
    };
};

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!