(Note: This guide on how to remove tables in Excel is suitable for all Excel versions including Office 365)
In Excel, when you have large amounts of consistent and uniform data sets, converting them to a table paves an easy way to enter, search, and retrieve the data.
When you create a table with the data, Excel automatically adds some formatting of its own. However, there are a variety of formatting options you can use to change the layout of the cell and perform any operations on them. This makes data handling a lot easier.
However, in some cases, you might need to remove the table formatting or even the whole table.
In this article, I will tell you how to remove table formatting in Excel. Additionally, you’ll also see how to remove tables in Excel using 3 easy ways.
Excel tables are a handy feature for organizing and analyzing related data sets. But sometimes tables lose their usefulness or just end up cluttering your spreadsheet.
In this guide, I’ll walk through several easy methods to remove Excel tables and their formatting. Whether you want to delete the entire table or just convert it back to a normal range, these steps will help you declutter your worksheets.
Follow these simple techniques to erase Excel tables:
Convert Table to Range
This retains the data but strips the table formatting
- Click inside the table to select it
- Go to Design tab and choose Tools > Convert to Range
- Click Yes on the prompt
The table styles and filters are now gone but values remain
Clear Table Formatting
To remove colors, borders and styling but keep the table object:
- Select the table cells
- Go to Design tab > Table Styles
- Click Clear to delete formatting
This leaves you with a plain table without any customized appearance.
Delete the Entire Table
To completely remove all data and the table:
- Click in the table and press Ctrl+A twice to select all
- Right click and choose Delete > Delete Table Rows
- Or use Home tab > Delete > Delete Table Rows
The table and its contents are now erased from the worksheet.
Copy Paste Values to Remove
You can also copy and paste just the values:
- Copy the table cells
- Right click and Paste Special > Values into new area
- Delete the original table range
This wipes the table but lets you paste the values elsewhere.
Use Clear Contents to Delete All
An easier way to remove values and table in one step:
- Select the entire table
- Go to Home > Clear > Clear Contents
- The table and data are deleted immediately
Employ VBA to Automate Deletion
For frequent large table deletions, use a macro like:
Sub DeleteTable() ActiveSheet.ListObjects("Table1").DeleteEnd SubThis VBA code will delete the specified table in seconds with a single click.
Bonus Tips for Table Removal
-
Double press Ctrl+A to select full table range including headers
-
Watch for #REF! errors if formulas reference deleted tables
-
Erase named ranges if linked to table columns
-
Adjust pivot tables, charts and conditional formatting referring to removed tables
So whether you need to erase formatting, convert to a range or completely delete the table, these 6 simple methods will help you remove Excel tables quickly and easily.
How to Remove Tables in Excel?
In the above-mentioned method, we saw how to remove the formatting in a table. However, in some cases, you might have to delete or remove the whole table.
Let us see 3 ways to remove tables in Excel.
Using Format As Table Option
This is one of the most common methods to remove table format in Excel.
- To remove the table formatting, first, select the table.
- Navigate to Table Design and under the Table Styles section, click on the More dropdown.
- Scroll down and click on Clear from the dropdown.
This only removes the formatting of the table, but the data, filters, and other elements remain in the table format.
- Now, navigate to Home. Under the Editing section, click on the dropdown from Clear and select Clear Formats.
This removes the dropdown from the headers and converts the table to normal range data.
How to Remove a Table from Excel
How to delete a table in Excel?
If your Excel worksheet has data in a table format and you no longer want the data and its formatting, here’s how you can remove the entire table. Select all the cells in the table, click Clear and pick Clear All. Tip: You can also select the table and press Delete.
How do I delete a row in Excel?
Click on the filter dropdown menu in the row heading and choose the ‘Filter by Selected Cell’s Value’ option. Select the ‘Delete Rows’ option and click ‘Ok’. It is important to note that using the AutoFilter feature to delete rows will permanently remove the data from your Excel sheet.
How do I undefine a table in Excel?
Now, if you want to keep all data and just “undefine” an Excel table, use the “convert to range” button on the Design tab of the ribbon. This command leaves all data and formatting in place, and removes only the table definition. To illustrate, here I have a table named “movies”.
