How to Remove Table Formatting in Excel

One of the best ways to manage data in Excel is to use the Excel table. When you apply an Excel table, it converts data into an Excel table and applies formatting to it.

But maybe you don’t want to have that formatting on the Table and want to remove it. This can be useful when you want a minimalist view of the data to make the user or anyone read it.

Now, there are two ways to remove formatting from a table. The first is to change the formatting to “None” formatting style, which has no formatting, and the second is to convert that Table back into a normal range.

Read Also – How to Rename a Table in Excel / Extend an Excel Table (Increase Range)

Remove Table formatting by using the “None” Format

If you have an Excel table, it’s easy to change its formatting style to “None,” which gives you clean formatting and helps you remove all the formatting from the data.

1. First, click anywhere inside the Table to select it. This will activate the “Table Design” tab (or “Design” in some versions of Excel) on the ribbon.
   
2. Look for the “Table Styles” group in the “Table Design” tab. A drop-down gallery displays a variety of style options.
   
3. You will find the option “Clear” at the bottom of the drop-down. So, all you need to do is click on the option to apply to the selected Table.
   
4. Selecting this option clears the formatting from the Table without removing the values or even without removing the Excel table functionalities.
   

Apart from this, you can also apply the “None” table, which doesn’t have table formatting and works exactly as removing formatting from the Table.

But when you apply “None” formatting to remove the formatting from the Table, you have some formatting in the Table that you have applied other than table formatting, and that will be there. You need to remove it using a different method.

All you need to do is select the entire Table or only that part of the Table, go to the Home Tab, click on the “Clear” drop-down, and click on the “Clear Formats” option.

You can also use the keyboard shortcuts Alt, H, E, F.

So, as I said, when you change the table style to none, it only removes the Table’s formatting, such as alternate row colors and heading row colors. However, it will not change the table’s functionalities. Your Table will still be an Excel table that you can use.

Read Also – Clear Formatting in Excel

If you have more than one Excel table in your Worksheet or the workbook, you need to go to each Table and change its color theme to none to remove the formatting.

The other way is to use a VBA code. If you are familiar with VBA, you can use the code I have shared below, which loops through the entire workbook and applies the table style “None” to remove the formatting from all the tables.

Sub ApplyNoneStyleToTables()

    Dim ws As Worksheet
    Dim tbl As ListObject

    ' Loop through each Worksheet in the workbook
    For Each ws In ThisWorkbook.Worksheets

        ' Loop through each Table in the Worksheet
        For Each tbl In ws.ListObjects

            ' Apply the "None" style to remove all formatting
            tbl.TableStyle = ""

        Next tbl

    Next ws

End Sub

Read Also – Run a Macro in Excel

Convert to Table and then Remove the Formatting

As I said, removing formatting from the Table doesn’t remove the Excel table from the data. You can also convert an Excel table back to the range and remove its formatting.

You also click on any of the cells from the Table and then go to the >Table > Convert to Range.

Both methods convert the Table from an Excel table to a normal range. After that, you only need to select the Table using control + A and then the keyboard shortcut Alt, H, E, F.

Or you can go to the Home > Editing > Clear > Clear All.

Remove Table Formatting from a Normal Data Table

There might be a change. You have a data table you formatted manually instead of using a pre-built Excel table. In that case, you need to use the same “Clear Format” option I showed you in this tutorial’s first part.

The above Table that I have in my workbook is formatted with the “AutoFormat” option, and I want to remove the formatting from this Table. All you need to do is to select the Table and use the keyboard shortcuts Alt, H, E, F.

Or you can go to the Home Tab > Editing > Clear > Clear Formats.

Now, one thing you need to take care of is that removing formatting removes formatting entirely. So when sharing that with someone else, re-apply formatting, like dates and alignment to the data.

Copy and Paste the Excel Table without Formatting

If you don’t want to change the original Table, you can copy and paste it into the new Worksheet without formatting it.

All you need to do is Select the Table> Copy the Table using Ctrl + C, then go to the new Worksheet or a new range of cells in the same Worksheet, and then use the keyboard shortcut Alt, H, V, V.

This way, you will only get the data you have in the Table without formatting it.

In the End

Removing formatting from an Excel table helps you apply custom styles or conditional formatting. When a table is converted back to a regular range or cleared, you’re no longer constrained by the pre-set table styles that Excel imposes.

This gives you complete control over how your data looks, allowing you to apply specific text or cell color, fonts, and other formatting options that better suit your data.