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.
To remove the table formatting of this table but retain its functionality, follow these steps:
- Select any cell from the table.
- Go to the Table Design tab > Table Styles Group.
- Click on the first style by the name ‘None’ under the Light Design types.
And there you go! Your table will be steered clear of any kind of all types of table formatting.
Another way how you can do this is:
- Select any cell from the table.
- Again go to the Table Design tab > Table Styles Group.
- Launch the Table Styles drawer by clicking on the drop-down menu icon.
- Scroll to the bottom of it to find the option Clear.
- Click on it and all formatting from your table will be removed.
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 🧐
Clear all table formatting
The next method on the list is to clear all table formatting in a single go 🚀
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 💁♀️
- Select the entire table (or click any cell from the table and press the Control key + A).
- Go to the Home tab > Clear > Clear Formats
Here comes your table in the rawest of forms (clean of all kinds of formatting).
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.
- Right-click on any cell of the table.
- From the context menu that then launches, click on Table > Convert to Range.
- You’ll be directed to this prompt – Click on Yes ✅
The subject table will now be converted into a normal range of cells as shown here.
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.
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.