Even though table formatting gives a clean and professional look to your tables in Excel, there may be times when you’d like to remove these default table formatting. Removing table formatting in Excel is a simple yet effective process that can be done in just a few steps.
To remove table formatting, you can follow these simple steps:
- Click on any cell within the Excel table.
- Go to the Table Design tab.
- Expand Table Styles.
- Select Clear.
In this article, you’ll learn various methods (visually) to remove table formatting from your data while retaining the information itself.
With this knowledge, you’ll be better equipped to customize your spreadsheets and derive valuable insights from your data.
Let’s get started!
Understanding Table Formatting in Excel
Microsoft Excel is a powerful software that allows users to organize, analyze, and visualize data in various ways. One such feature is Excel Tables.
Tables in Excel provide a convenient way to manage and analyze a group of related data. With table formatting, you can apply consistent formatting to a set of data, making it easier to understand and interpret.
Table formatting in Microsoft Excel is a built-in feature that allows you to apply various styles and formatting options to your dataset. These options include borders, shading, font styles, and colors.
When you convert a dataset into an Excel table, the default table format is applied, which includes alternate row shading, header formatting, and an automatic filter added to each column.
In addition to making your dataset visually appealing, table formatting offers several benefits.
Among them is the ability to sort and filter data, create calculated columns, and extract summarized information using functions like SUMIFS, COUNTIFS, and more, all while maintaining consistent formatting.
However, there might be instances where you want to remove table formatting without deleting the data.
Whether you need to present the data differently, or you want to use parts of a table outside its original structure, Excel provides several methods to remove table formatting.
Note: Removing table formatting will not delete the dataset. It only clears the applied styles and returns the data to its original state.
The data will still be intact within the Excel worksheet, ready for further analysis or presentation.
Next, let’s go over the various ways you can remove table formatting in Excel.
How to Remove Table Formatting in Excel
Removing table formatting in Excel allows you to clean up your data presentation while retaining the original information.
There are several methods that you can apply to remove Excel table formatting:
- Use Clear Table Style to remove Table format
- Use Clear Formats to remove the formatting of Excel Tables
- Use a VBA code to clear the formats of Tables in Excel.
Let’s learn each method step-by-step.
1. Use Clear Table Style to Remove Table Format
There is an Excel Table option to clear Table Formatting.
You can use this method by following the steps below.
Step 1: First, select any cell within the Excel table you want to remove the formatting.
Step 2: Next, click on the Table Design tab which will appear as a contextual tab (hidden tab) once you have selected a cell within the table.
Step 3: In the Table Styles section, locate and click on the More icon (represented by a small scrollbar with an arrow and a line at the top).
Step 4: Finally, click on the Clear to clear Table Style.
The table formats are removed, but the actual data remains within the table.
In the above steps, you’ll remove only the formatting. Still, the whole table functions as an Excel table.
How to Convert the Excel Table to a Normal Range?
If you want to convert the Excel table to a normal range, you can follow these additional steps:
1. In the Excel Ribbon, go to Table Design.
2. Go to the Tools section and click Convert to Range.
3. Click Yes on the prompt to confirm the conversion.
This process will remove the Table Design tab options, leaving you with a standard range of cells that no longer have the table formatting features including structured references, banded rows, etc.
Remember using this method, you can only remove the table style. Number formats and alignments still remain in the table.
2. Use Clear Formats to Remove Formatting of Excel Tables
To efficiently remove all formatting from an Excel table, follow these simple steps:
Step 1: First, select the entire table. You can do it by clicking on any cell within the table, and then pressing Ctrl+A (Windows) or Cmd+A (Mac) twice.
If you haven’t selected the entire range of the Excel table including the headers, Excel will not remove the table formatting.
Step 2: Go to the Home tab on the Excel Ribbon.
Step 3: Locate the Editing group and click the Clear option.
Step 4: Click Clear Formats from the drop-down.
By following these steps, you’ll effectively remove all formats from the table, including number, alignment, and table-style formatting.
This will leave you with a plain-looking table by removing all the formatting. Then, you can manually format it according to your needs.
However, keep in mind that these steps don’t convert the data back into a regular range of cells, but instead clear the formatting while retaining the Table structure.
If you wish to revert the table to a regular range of cells, remember to right-click the table to open the Context Menu and select Table, followed by Convert to Range.
This will remove the table formatting and structures, transforming it into a standard Excel data range.
3. Use VBA Code to Clear Formats of Excel Tables
You can also use Excel VBA code to remove table formatting. In this section, we’ll show you the steps to use VBA to remove table formats in Excel.
Step 1: To begin, you’ll need to access the VBA Editor by pressing Alt + F11 on your keyboard.
Step 2: Go to the Insert tab and select Module. The Module is where you’ll write your VBA code.
Step 3: Copy the below VBA code in the Module.
Sub Remove_Table_Formatting()
ActiveSheet.ListObjects("TableName").TableStyle = ""
End Sub
In this macro, you have to replace the “TableName” with the actual Table name of your Excel file.
When you Run the above macro, your table formatting will be removed instantly.
In conclusion, VBA provides a robust and flexible solution to remove table and cell formatting in Excel. By understanding and implementing these VBA macros, you can create more streamlined and organized spreadsheets.
Final Thoughts
Removing Table formats in Excel is a straightforward process that allows users to convert stylized tables back to plain, regular cells.
As you master these table format removal techniques, you’ll find it easier to tailor your spreadsheets to your specific needs. Keep in mind that practicing these methods with various Excel features will further enhance your proficiency and confidence in dealing with data management and manipulation tasks.
Would you like to learn how to create unique table templates for your Power BI reports? Watch the below video:
Frequently Asked Questions
How do I delete table formatting without losing data?
To remove table formatting without losing data, first, select any cell in your Excel table. Then, click the “Table Design” tab and click on the “More” icon in the Table Styles group. Choose the “Clear” option to remove formatting while retaining data and functionality.
What is the method to unformat a table in Excel?
To unformat a table in Excel, go to the Home tab and click the “Clear” options in the Editing group, then choose “Clear Formats.
This method will remove all formatting, including number formats and alignment, but won’t affect the underlying data.
Can I convert a table to a range in Excel?
Yes, you can convert a table to a range in Excel. Select any cell in your table, then click the “Table Design” tab. In the “Tools” group, click on the “Convert to Range” option.
You’ll be asked to confirm the conversion, and once you do, your table will become a regular range.
How do I remove table styles while keeping formulas?
To remove table styles while preserving formulas, you can clear the table format as previously described and then apply your desired format individually to cells with formulas.
This ensures that the contents of the cells, including formulas, remain unchanged.
How can I clear table formatting in Excel 2016?
Clearing table formatting in Excel 2016 is similar to newer versions. Select a cell within your table, navigate to the “Design” tab, then click on the “More” icon in the Table Styles group. Choose the “Clear” option to remove table formatting.
