How to Remove Excels Table Formatting (Easy Guide)

The incredible Excel Table function makes working with data much simpler.

Excel tables will also become even more crucial since increasingly sophisticated tools like Power Query and Power Pivot now use Excel tables as the data source.

Excel VBA

However, the focus of this article is not on how amazing Excel Tables are, but rather on something that many people find annoying: the formatting.

When you create an Excel table from a date range, Excel automatically formats the data range (you can, of course, customize or remove the table formatting). Additionally, the Excel table layout is retained when you return an Excel table to a range.

In this article, I’ll demonstrate how to customize Excel and how to get rid of table layouts in Excel.

Then let's get going!  This instruction explains: 1. Excel Table Formatting Removal (while keeping the Table) 1.1 Formatting in the Excel Table should be removed 1.2 Format the Excel Table Differently 2. Remove the formatting and the Excel Table (Convert to Range) 3. Removing the Table

1. Excel Table Formatting Removal (while keeping the Table)

Let’s say I have the dataset depicted below.

I receive the results displayed below when I convert this data into an Excel table (keyboard shortcut Control + T).

You can see that Excel formatted the table by going ahead and doing so (apart from adding filters).

I typically don’t like the formatting that Excel applies automatically, therefore I need to adjust it.

Excel VBA

Now I have the option to entirely remove the formatting from the table or change it to appear the way I want.

I’ll demonstrate both for you.

1.1 Formatting in the Excel Table should be removed

The procedures to eliminate Excel table formatting are listed below:

Any cell in the Excel table can be chosen.

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

Click the More button in Table Styles (it’s the one at the bottom of the little scrollbar).

Select “Clear”

The aforementioned procedures would preserve the table’s integrity while removing the Excel Table formatting. The formatting has been deleted, but the filters that are inserted automatically still appear.

Now, if you like, you may format it manually.

1.2 Format the Excel Table Differently

You may also change an Excel Table’s default formatting by selecting one of many presets if you don’t like it.

Suppose you wish to change the formatting of the Excel table that is displayed below.

Excel VBA

The procedures are as follows:

Any cell in the Excel table can be chosen.
Toggle to the Design tab. (This contextual tab can only be accessed by clicking any table cell.)
Click the More button in Table Styles (it’s the one at the bottom of the little scrollbar).
Pick from any of the current designs.

You may get a real-time preview of how any design will be formatted in your Excel Table as you place your mouse over it. Simply click on it after you have finished creating the formatting you like.

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

Excel VBA

2. Remove the formatting and the Excel Table (Convert to Range)

You may get a real-time preview of how any design will be formatted in your Excel Table as you place your mouse over it. Simply click on it after you have finished creating the formatting you like.

Excel VBA

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

Assume you possess the Excel table depicted below:

The steps to change this Excel table into a range are as follows:

In the Excel table, right-click any cell.
select the Table menu item.
Simply choose “Convert to Range”

You will receive the outcome as displayed below (where the table has been deleted but the formatting remains).

Now you have the option to manually alter the formatting or completely remove it.

Excel VBA

The following actions should be followed to remove all formatting:

Select the whole formatting-containing range.
On the Home tab, click
Select Clear from the Editing group’s menu.
Select Clear Formats from the list of choices that appears.

The formatting would all be gone, leaving you with just the facts.

Another approach would be to delete the table after removing all the formatting from the Excel Table (technique outlined in the preceding section) (Convert to Range).