When you download an Excel file from the internet, the table formatting may not be suitable for your data table creation needs. If so, you can remove the table formatting in Excel.
When you download an Excel file from the internet, the table formatting may not suit your data table creation needs. If so, you can remove the table formatting in Excel and then set up a different format if necessary. There are many ways to remove table formatting in Excel, as guided in the article below.
How to remove Excel table formatting via the Design tab
Step 1:
Open the Excel spreadsheet you want to remove the formatting from. Next, highlight the area of the spreadsheet where you want to remove the formatting and then click on the "Design" option at the top.
Step 2:
Next, in the Table Styles section , click the arrow icon to display the menu of table styles. Then, click Clear to delete the table style.
You will then see that the table style has been removed, but the data formatting within the table has been retained.
How to completely remove Excel table formatting
When you completely remove the formatting from an Excel table, all table styles and data formatting within the table will also be completely removed.
We will press Ctrl + A to quickly select the entire table . Next, click the Clear icon in the corner as shown in the image. At this point, you will see several options to remove Excel table formatting, including:
- Clear Formats: Removes formatting without deleting data.
- Clear All: Removes all formatting, including deleting the data table.
Here, we will click on Clear Formats to completely remove the Excel table formatting.
Remove Excel table formatting by data range.
An Excel data range will help you remove some special formatting, such as data filter symbols, while still retaining the data in the table.
Step 1:
In the Excel spreadsheet interface, right-click on any cell in the table and select Table from the displayed list , then select Concert to Range to convert it to a data range.
Step 2:
A pop-up will appear asking if you are sure you want to switch to region mode; press Yes to proceed .
As a result, you will see that the filter icon is no longer displayed in the table.
Step 3:
Next, click the Clear icon and then select Clear Formats to remove the formatting from the Excel spreadsheet.
How to remove Excel table formatting using VBA
When you run a VBA command, all table customization operations are performed automatically, including removing table formatting.
Step 1:
We press the Alt + F11 key combination to open the VBA interface in Excel. Next, we right-click on the existing module, select Insert, and then click Module .
Step 2:
Display the interface for entering VBA code as shown below.
Sub RemoveFormatAsTable() Dim tbl As ListObject ' Check if the active cell is within a table If ActiveCell.ListObject Is Nothing Then MsgBox "Active cell is not within a table.", vbExclamation Exit Sub End If ' Set tbl to the table of the active cell Set tbl = ActiveCell.ListObject ' Remove the table format tbl.Unlist MsgBox "Format as Table removed successfully.", vbInformation End Sub
Continue pressing F5 to run the entered command. You will now see an interface like the image shown. In this interface , click on RemoveFormatAsTable and then click Run to execute the command to remove the table formatting.
How to remove Excel table formatting on your phone
You need to take a few extra steps to remove formatting in Excel on your smartphone or tablet. However, the process is still quite easy. Here are the steps you need to follow:
- Highlight the areas you want to remove formatting from.
- Click the three-dot icon on the toolbar at the bottom.
- Scroll down to Clear and select this option to display the list of options.
- Click on Clear Formats . The content in the table will then revert to its default settings.
After removing the Excel table formatting, you can easily rearrange the spreadsheet if needed. And if you decide not to remove the table formatting, you can always easily undo the process.