Excel Tables offer multiple inbuilt features to organize data. But what if we don’t need to use any table or want to use features of it? In that case, we have three options; Table without any formatting, Only the data as range, and No table at all. In this article, we use some of the Excel features such as Clear, Delete, Table Style, and Convert to Range as well as VBA Macro Code to remove table formatting or the whole table or show the table as a range in Excel.
Let’s say, we have a Table of Product Sale similar to the picture below.
Dataset for Download
5 Easy Ways to Remove Table from Excel
Method 1: Using Clear Feature
In the Home Tab Editing section, Excel offers Clear features with the option Clear All which includes alternatives such as Clear Formats, Clear Contents, Clear Comments, and Clear Hyperlinks.
We can use Clear Formats to remove table formatting, still able to use Table’s inbuilt features. On the contrary, we can use Clear All to remove the table as a whole.
1.1. Removing Table Formatting
Step 1: Select the whole Table. Then Go to Home Tab > Click on the drop-down menu beside Clear (in Editing section) > Select Clear Formats (from the drop-down menu options).
Table formatting goes away leaving Table’s in-built features still in function. The outcomes depict an image something similar to the following image.
1.2. Removing the Whole Table
This case will be effective if you want to remove the whole as the Table seems redundant to you.
Step 1: Follow Step 1 from Removing Table Formatting but in the Editing Section and Clear drop-down options, select Clear All.
It removes the whole Table. Nothing in that Table exists now.
1.3. Using Keyboard Shortcuts
We can execute the Clear Format and Clear All feature using Keyboard Shortcuts.
Step 1: Select the entire Table. Then Press ALT+H altogether. It enables Keyboard Command to hover among the Home Tab features.
Step 2: Hit the key E to open up the options from the Editing section.
Now, you can Hit either the key F to execute the command Clear Formats or A to execute the command Clear All. By doing so, you’ll get similar consequences as we showed earlier in methods Removing Table Formatting or Removing the Whole Table respectively.
Method 2: Using Delete Feature (Removing Data and Formatting)
Excel’s Delete feature works like the Clear All option we used earlier. It removes the whole Table along with the data.
Step 1: Select the entire Table after that Go to Home Tab > Select Delete (in Cells section).
In a moment, Table formatting and contents get removed and you’ll see a result similar to the image below.
Method 3: Using Table Styles Feature (Removing Table Formatting)
In this case, you want to use Table’s in-built features but don’t want to keep the formatting. We can use the Table Styles feature from the Table Design tab.
Step 1: Click on any cell inside the Table. It makes the Table Design Tab appear beside other tabs. Click on Quick Styles (in Table Styles section) > Select Clear.
It removes the formatting of the Table but keeps all the features to work with.
Method 4: Using Convert to Range Feature
Let’s say, you want the Table as a Range with none of its features let alone the format. We can achieve it by using the Convert to Range feature.
Step 1: Click on any cell inside the Table. Table Design Tab will automatically appear beside other Tabs. Select Convert to Range (in Tools section).
Step 2: A confirmation pop-up window appears. Click OK.
The range keeps the formatting of the Table but transforms the Table into a simple Range containing all the contents of the Table.
This Convert to Range feature is also accessible via Context Menu.
Let’s undo what we have done and do the job using the Context Menu option.
Step 1: Right Click on any cell inside the Table. The Context Menu appears. It offers multiple options to go with. Click on Table > Select Convert to Range.
Then a confirmation window appears as appeared earlier in this method. Click OK. You’ll get the same outcome as it is in the below image.
The outcomes are similar to those we achieved via the Table Design Tab option. Convert to Range keeps the cell’s formatting along with the data.
Method 5: Using VBA Macro Code
VBA Macro Code is an efficient way to achieve anything in Excel if you are comfortable with it. For this instance, suppose we name our Table as ProductSale_Table from the Properties section in the Table Design Tab.
Step 1: Hit ALT+F11 altogether. Afterward, the Microsoft Visual Basic window appears. From the Toolbar, Click on Insert > Select Module.
Step 2: In the Module, Paste the following code.
Sub ProductSale_Table_Delete() Dim sTableName As String Dim sSheetName As String sSheetName = "VBAMacro" sTableName = "ProductSale_Table" Sheets(sSheetName).ListObjects(sTableName).Delete End Sub
The code declares the Table Name from the specific Sheet Name and executes a command to delete them.
Step 3: Tab on the F5 key to run the macro code. Then back to the worksheet, you’ll see the entire Table has been removed.
In this article, in order to remove Table in Excel, we have three options to go with. The first one is keeping the built-in features of a Table only the formatting is to remove. The second one is removing the entire Table along with data. The last one is to convert the table into a Range.
Clear Formats and Table Styles provide us with the first option. Clear All, Delete, and VBA Macro Code result in the second option. Only Convert to Range offers the outcomes as the last option. Hope above discussed methods do your job. Comment, if you have further inquiries or have anything to add.