How to Remove Table Formatting in Excel – Without Losing Data

You’d know about Excel tables and the great functionalities that they add to your Excel 💪

From a clean, pre-defined format to sorting and filtering of data – Excel tables will never fail to amaze you.

However, sometimes you might not want the heavy-duty table formatting but only table functionalities.

This means your table must not look like an Excel table but only work like it. The guide below covers various methods of how you can remove table formatting in Excel.

So jump right in and download our free sample workbook on your way down 📩

Table of Contents

How to remove table formatting in Excel with Table Styles

Here we have a table that shows the default table format of Excel.

Default Table style in Excel

To remove the table formatting of this table but retain its functionality, follow these steps:

  1. Select any cell from the table.
  2. Go to the Table Design tab > Table Styles Group.
Table tools
  1. Click on the first style by the name ‘None’ under the Light Design types.
Table formats

And there you go! Your table will be steered clear of any kind of all types of table formatting.

Remove formatting

Another way how you can do this is:

  1. Select any cell from the table.
  2. Again go to the Table Design tab > Table Styles Group.
  3. Launch the Table Styles drawer by clicking on the drop-down menu icon.
Custom table styles
  1. Scroll to the bottom of it to find the option Clear.
Option to Clear
  1. Click on it and all formatting from your table will be removed.
Remove formatting

If you had formatted the table manually in any way, such formatting would be retained even after you’ve followed the steps above. This method only removes the default table formatting of Excel 🧐

Kasper Langmann, Microsoft Office Specialist

Clear all table formatting

The next method on the list is to clear all table formatting in a single go 🚀

Pro Tip!

This method will remove all the formatting from your table. The Excel default table formatting as well as any other formatting applied to the table

To do so 💁‍♀️

  1. Select the entire table (or click any cell from the table and press the Control key + A).
  2. Go to the Home tab > Clear > Clear Formats
Clear Formats

Here comes your table in the rawest of forms (clean of all kinds of formatting).

All formatting removed from the table

Did you note? This time there’s nothing left back to the table. The font, alignment, format, color everything has been removed 👀

This method makes no distinction between the default formatting and any other custom formatting that you might have applied to the table yourself.

Turning the Excel table into a normal range of cells

If you don’t want to get rid of the table formatting but the table as a whole, this section is for you 💡

Under this method, you can convert any data table in Excel into a normal range of cells. Check it out here.

  1. Right-click on any cell of the table.
  2. From the context menu that then launches, click on Table > Convert to Range.
Option to convert to table
  1. You’ll be directed to this prompt – Click on Yes ✅
Prompt to remove table

The subject table will now be converted into a normal range of cells as shown here.

Normal range of cells

Pro Tip!

Did you note that this time the table format didn’t go anywhere? The banded rows are still the same 💭

However, there are no more filter buttons next to column headers. Including these, all the structured references, named range, and other in-built table features are gone.

Converting a table into a normal range of cells only takes away the table’s functionality. The table formatting is retained 🤟

That’s it – Now what?

The guide above discusses different ways how you can remove table-style formatting in Excel. You can do so by removing the table style, or the table format, or by simply converting the table to a normal cell range.

Without any doubt, Excel tables will make your Excel jobs a lot easier than ever. But do not underestimate other amazing tools, features, and functions offered by Excel 🔎

There are nearly unlimited of them. Want to explore them all? Get started with some core Excel functions like the VLOOKUP, SUMIF, and IF functions of Excel.

Click here to enroll in my 30-minute free email course that will take you through these (and many more) functions of Excel.

Other resources

You’re now good at deleting tables in Excel (whether that’s entirely deleting it or only removing the formatting from it).

But what about creating one? Click here to read our blog on how you can create tables in Excel.

Frequently asked questions

To make a table no more an Excel table:

  1. Right-click on any cell of the table.
  2. From the context menu that then launches, click on Table > Convert to Range.
  3. From the resulting prompt, click on Yes.

The table will be converted into a normal range of cells.

To remove table formatting in Microsoft Excel for Mac:

  1. Select any cell of the table.
  2. Go to the Table Tab > Table Styles.
  3. Click Clear.

Any default table formatting will be removed.