by Want to leverage awesome features of an Excel table but do not want any formatting in it? This tutorial will teach you how to quickly undo table format in Excel.
By default, Excel tables are equipped with many great features including predefined styles. In some situations, however, you may want to remove formatting keeping all other functionality of a table. This tutorial will teach you how to quickly do that.
How to remove table formatting in Excel
To remove formatting but retain all other features of an Excel table, this is what you need to do:
- Select any cell in your table.
- On the Table Design tab, in the Table Styles group, pick the very first style in Light group, which is called None.
Or click the More button
in the Table Styles group, and then click Clear underneath the table styles.
As the result, you will still have a fully functional Excel table but with no formatting:
Tips and notes:
- Both these options remove only predefined formatting included with a table style, but not custom formatting that you've applied manually.
- This method comes in handy when you want to get the functionality of a table but keep the existing cell formatting. Simply, convert your data to Excel table, and then perform the above steps to clear the table style formatting. All your original fonts, colors, filling, borders, etc. will be preserved.
- To change a table style, just pick any style you want from the gallery.
How to clear all formatting in a table
If the above-mentioned approach has not removed all the formatting in your table, that means the remaining part is your custom format that was done manually.
To clear absolutely all formatting (both predefined and your own one), carry out these steps:
- Click any cell within a table, and then press Ctrl + A twice to select the whole table including the headers.
- On the Home tab, in the Editing group, click Clear > Clear Formats.
As the result, all formatting in a table is removed:
Note. Please keep in mind that this method clears all formatting entirely, including number formats, alignment, etc.
How to remove format as table in Excel (convert table to range)
In case you no longer need data formatted as a table, here's how you can remove a table format and turn it into a regular range of cells:
- Right-click any cell in your table.
- In the context menu, click Table > Convert to Range.
A prompt will show up asking if you really want to convert the table to a normal range, and you click Yes.
In the result, your data will still look very much like a table because the table style formatting is preserved. From the functional viewpoint, however, it is a usual range - all features of Excel table such as structured references, automatic expansion, inbuilt filters, etc. are gone.
For more details, please see How to undo a table in Excel.
That's how to clear table formatting in Excel. Hopefully, you've found these small tips helpful. I thank you for reading and look forward to seeing you on our blog next week!
6 comments
Thanks issue resolved.
Thanks, this realy help
This didn't work for me as I am using a pre-formatted table. What I want to do is remove the "comments" that pop up in a column or cell. Because it's pre-formatted from an existing table, I can't seem to delete those. Any ideas?
It must be so easy to have a short-cut "No Format" right there under "Format as Table" where the other formats are available. Some times designers don't think realistically. Nobody has patience to go to another tab and then click 2 other options and then "Clear" just to remove the existing format. Insane!!
thank you very much
I was so frustrated that I couldn't "undo" the table and just have it as a range and I didn't know how to word that. Thank you so much for this info! You saved me redoing hours of work!