How to Remove Table Formatting in Excel?

Do you find the automatic styles that Excel applies to your tables a bit overwhelming or just plain unsuitable? You're not alone. Many users prefer a clean, unformatted look that allows for easier data manipulation and analysis.  So, grab your virtual toolbox because we’re about to learn How to Remove Table Formatting in Excel like seasoned pros!   

Are you ready to customise your Excel tables to better suit your needs? This blog provides step-by-step instructions on How to Remove Table Formatting in Excel effectively and easily. We'll explore how to change table styles to match your preferences, giving you complete control over your data's appearance. So, let’s dive in and simplify your Excel experience!

Table of Contents

1) Table Formatting in Excel

2) How to Remove Table Formatting in Excel?

3) 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 offer various benefits, making data management easier. Follow these steps to quickly create a table in Excel:

1) Select a cell within the data 

2) Click on the ‘Home’ tab

3) Select the ‘Format as Table’ option

4) Now, choose a style for your table 

5) A "Format as Table" dialogue box will appear

6) Click the ‘OK’ button

Tables not only store grouped data but also simplify data management. Users can easily add, modify, and remove large amounts of data. However, you might need to remove the table formatting while working on data. This can be done easily. Follow the blog to learn more.



 

How to Remove Table Formatting in Excel?  

Most of the time, the data we enter can be complex, making it challenging to find the exact information we need. Therefore, it is equally important to learn How to Remove 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 Ctrl + T. This will result in the following:

You can see that Excel has formatted the table in several ways (in addition to adding filters)

You might dislike the formatting that Excel automatically applies and may want to personalise it. The table's formatting can be completely removed or altered to suit your preferences.

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

 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) Once the cell is selected, go to the Table Design tab on the ribbon (this may also be labelled as Table Design or Table Tools, depending on your version of Excel).

c) In the Table Styles group, click the drop-down arrow next to Table Styles.

d) Select Clear from the bottom of the style options. This will remove all formatting from the table while retaining the data.

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 automatically inserted filters still appear.

Sign up for the Microsoft Excel Course today and become an Excel expert quickly!

Change the Formatting of the Excel Table 

If you don't like the default formatting of an Excel table, you can easily change it by selecting one of several pre-set styles. Here's how you can change the formatting of the Excel table as shown above:

a) Select any cell within the Excel table.

b) Switch to the ‘Design’ tab (this contextual tab can only be accessed by clicking on a table cell).

c) Click the ‘More’ button in the Table Styles group (located at the bottom of the small scrollbar).

d) You can choose any of the available designs or formatting styles.

By hovering your mouse over a style, you can see a live preview of how it will look in your Excel table. Once you find a style you like, simply click on it to apply the formatting.

If none of the Excel table styles appeal to you, you can create your own format by selecting "New Table Style." This will open a dialogue box where you can customise the formatting to your liking.

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

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, when you convert an Excel table to a range, the formatting is retained, which might be a little problematic. You may need to manually clear the Excel table formatting afterwards. 

Here are the steps to convert an Excel table into a standard range:

a) In the Excel table, right-click any cell

b) Choose the ‘Table’ option from the context menu

c) Click on ‘Convert To Range’

d) A prompt will appear asking if you want to convert the table to a normal range. Click ‘Yes’

 As a result, your data will still look like 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, and built-in filters, are lost.

Join our Excel Training With Gantt Charts Course and enhance your Excel skills!

Conclusion 

Mastering How to Remove Table Formatting in Excel empowers you to present your data with clarity and finesse.  Whether you’re a data wizard, a financial maestro, or just someone who loves a clean slate, these techniques will be your trusty companions. So go forth, my spreadsheet-savvy friend, and let your data shine!

Join our Microsoft Excel VBA and Macro Training to boost your productivity!