How to Remove Table Formatting in Excel?

The incredible Excel Table function makes working with data much simpler. Excel Tables have become even more crucial as more sophisticated tools like Power Query and Power Pivot now use Excel Tables as the data source.

Want to take advantage of Excel Table's outstanding features but do not want any Formatting in Excel? Don’t know How to Remove Table Formatting? Well, then this blog is for you!

Excel Tables come with a substantial number of key features by default, including predefined styles.

However, in some cases, you might want to retain the Table's other features intact while Removing the Formatting. So, wait no more and simplify your data with our blog. In this blog you will learn How to Remove Table Formatting in Excel along with changing the table formatting.

So, let's get going!

Become efficient in clear-cut, simple to use application. Check out our Microsoft Excel Training Courses today!

Table of Contents

1) Table Formatting in Excel

2) How to Remove Table Formatting in Excel?

3) How to change the Formatting of Excel Table?

4) How to Remove Table Formatting from a range in Excel?

5) Conclusion

Table Formatting in Excel

Tables in Excel help users in multiple ways. You need to follow the below steps to quickly create a table in Excel: 

1) Select a cell within the data 

2) Click on Home 

3) Then select Format as a Table option 

4) Now, choose a style for your table 

5) From the Create Table dialogue box, set the cell range 

6) Click on the OK button.

Tables not only stores data related to groups, but also eases the management of data. Users can add, modify and remove large amounts of data all at once. But while working on data you might require to remove the Table Formatting. It can be done easily. Let’s follow the blog to learn more.

How to Remove Table Formatting in Excel?  

Most of the time, the data we enter might be complex, and it can be difficult to find the exact number that we require. Thus, it is equally important to learn the process of Removing Table Formatting in Excel. Let’s assume we have the dataset shown below. Now, let’s understand How to Remove Table Formatting:

To convert this data into an Excel table, use the shortcut key Control + T. As a result, you will get the following: 

You can see that Excel formatted the table in some ways (apart from adding filters).

You might dislike the Formatting that Excel automatically uses and may want to personalise it. The Table's  Formatting can be completely removed or altered to look anyway you like.

Let's learn how to accomplish both. The procedures to eliminate Excel Table Formatting are listed below:

a) To remove the Formatting, you can choose any cell in the Excel Table.

b) Toggle to the design tab (this is a contextual tab and only appears when you click any cell in the table)

c) Now, select the "design" tab.

d) From the Table Styles, you can select the More icon (the one shown at the bottom of the small scrollbar)

e) Now, click on the Clear option. 
 

The aforementioned steps would preserve the Table's functionality while removing the Excel Table Formatting. Now, the Formatting is deleted, but the filters that are inserted automatically still appear.

Tips to follow while clear Formatting

The following are some mistakes to avoid while you clear Formatting of a Table in Excel:

a) Only the predefined Formatting that comes with a Table type is removed using the clear Formatting option.

b) This technique is useful when you want a Table's functionality but don't want to change the cell layout. So, to remove the Table style Formatting, convert your data to an Excel Table and then follow the preceding instructions. Your original fonts, colours, fills, borders, etc., will all be maintained.

c) Simply choose the desired style from the gallery to alter the Table's appearance.

Get expertise in using Excel and benefit from its various features, register in Microsoft Excel VBA And Macro Training!

Change the Formatting of the Excel Table 

You can change an Excel Table's default Formatting if you don't like it. You can select one of a few pre-sets. For instance, let's say you wish to change the Formatting of the Excel Table as shown above, the steps are listed below: 

a) Choose any cell in the Excel Table.

b) Toggle to the design tab (This contextual tab can only be accessed by clicking any table cell).

c) Click the more button in Table styles (it's the one at the bottom of the tiny scrollbar).

d) You can select any of the current designs or Formatting styles.
 

You may see an actual preview of how any design that is formatted in your Excel Table by placing your mouse over it. Simply click on it when you have finished creating the Formatting you like. 

If none of the Excel Table styles appeal to you, you can also design your own format by selecting "New Table Styles." By doing this, a dialogue box where the Formatting is controlled will open. 

Want to learn how to create advanced formulas and macros in MS Excel? Sign up for Microsoft Excel Expert Course!

How to Remove Table Formatting from a Range in Excel?  

Creating an Excel Table from tabular data and converting it back to a standard range are both simple processes. 

However, the Formatting is lost when you convert an Excel Table to a range, which might be a little problematic. The Excel Table Formatting must then be manually cleared. 

The following are the steps to change this Excel Table into a range are as follows: 

a) In the Excel Table, right-click any cell.

b) select the Table menu option.

c) Simply click on "Convert to Range"

d) To return to the standard range, select Convert to Range.
 

A prompt will appear asking if you actually want to convert the table to a normal range,  you can click on ‘Yes’ option. As a result, your data will still resemble a Table because the Table style Formatting is preserved. However, from a functional standpoint, it is just a typical range because all the Excel table's capabilities, like structural references, automatic expansion, built-in filters, etc., are lost.

Become efficient in clear-cut, simple to use application. Check out our Microsoft Excel Training today! 

Conclusion 

It is easy to design and customise a Table using one of the many predefined Table styles that Excel offers if the standard table styles don't suit your needs. We hope that from this blog,  you might have understood  How to Remove Table Formatting in Excel and the tips that you can use to easily Format these Tables in Excel.

For deeper understanding, sign up now and learn the top 10 Excel shortcuts with our Microsoft Excel Masterclass!