Using the Excel table feature in your report will make your data presentable in a better way. If you are a regular user of Excel, you must have an idea about advanced tools Power Query and Power Pivot. Both of these tools are dependent on Excel tables as the data source.
One thing that Excel users often tend to ask is how they can perform the Excel remove table formatting operation. When it comes to formatting, you have to be aware of all the basic as well as advanced parameters involve in the operation. Formatting is something that most people usually get frustrated about, that’s why it needs to be acknowledged every time you work on it.
You must have noticed that Excel applies some formatting by default when your data range turns into an Excel table. Though it is not a big deal to remove formatting, however when you convert the table back to its original form, the formatting does not go away. This is where users get annoyed and tend to find multiple tricks that can sort out this problem.
Let’s dive in to see how Excel removes table formatting quickly by using multiple methods.
How to Clear All Formatting in a Table
Sometimes the custom formatting does not go away easily. Custom formatting is done manually that’s why changes stay there even when you perform certain functions. In case, if you need to clear all formatting you can follow the steps given below:
Choose a cell from the table and press CTRL + A twice. It will help you select the entire table including headers.
Choose the Clear option and then Clear Formats from the Editing menu given in the Home tab.
Ultimately, you will see all the formatting is cleared.
One thing you must keep in mind is that when you apply this procedure, it clearly means that all formatting will be removed including alignment, number formats, etc.
How to Clear Formatting from an Excel Table
As you know, the Excel table feature is the most amazing way to organize data according to the needs. When the data is converted into tabular form, usually the built-in table styles are applied. When the data is imported, or you link it to a database, the data will appear by default as a table. On the other hand, when they make a table from the data range, the data will be formatted in Excel.
In case, if you need to make a new table from the given data range, you have to highlight the range. Press CTRL + T and follow the steps:
Click on the table from which you need to remove the formatting when you want to clear formatting from a table.
Choose Table Design and then Table Styles from the Ribbon menu. Now, click on the down arrow that appears at the lower right-hand side of the menu.
Remember that the Table Design option in the Ribbon menu is a contextual tab that will be visible only when you click on an Excel data table.
Choose the Clear option given at the end of the list.
You will see the formatting is not there but the data gets back as a table form. Apart from that the Table Design tab will also be there given in the Ribbon.
How to Remove Table Format in All Tables with VBA
Let’s assume you are having a workbook that contains lots of tables and you need to remove the table formatting from each table. For this task, you have to choose a table to clear the format using the Table Design option one by one. Though it will take time, however with VBA macro you can do it instantly.
- Open the Visual Basic editor and for this press ALT + F11 key from the keyboard.
- Now, open the Insert menu and choose the Module option.
- You will notice a new module is added so that you can add the VBA code.
Sub RemoveTableFormat()
Dim wsObj As Worksheet
Dim tableObj As ListObject
For Each wsObj In ActiveWorkbook.Worksheets
For Each tableObj In wsObj.ListObjects
tableObj.TableStyle = “”
Next tableObj
Next wsObj
End Sub
The above-mentioned code will be pasted in the new module added just now.
This code will be used to remove table formatting from all tables in the workbook.
Final Thoughts
Excel users do not prefer most of the time, built-in table styles because they are too simple and basic without any attraction. Excel remove table formatting is quite a tricky thing because you may get tired of removing formatting while keeping the data.
