How to Remove Table Formatting in Excel

Today’s guide will be on how to remove table formatting in Excel when the table style doesn’t meet your needs.

Excel Tables play an important role if you want to create ranges and write easy-to-read formulas. The problem is that if you convert a normal range of cells into a table, Excel will apply custom formatting. Unfortunately, the custom style sometimes does not meet your requirements. The vice versa case: Some unnecessary styles remain when you convert a Table to a normal range. This post will explain the best practices regarding tables.

How to Remove Excel Table Formatting (keep data)

When you import data into Excel from various sources, Excel automatically formats data and arranges all records into an Excel table. Furthermore, you can create a table using the Ctrl + T keyboard shortcut.

In another case, right-click and select the ‘Convert to range’ command to convert your table into a normal range back.

Remove Formatting from the Excel Table

To clear formatting from an Excel Table but keep the data untouched, follow the steps below:

1. Select any cell in your table
2. Click the Design Tab on the ribbon
3. Under the Table Styles Group, click the down arrow
4. Select the Clear option to display the default table format

#1 – Select any cell in the table

Selecting the entire table to clear the actual formatting style is unnecessary; it is enough to select any cell in the table. In the example, we select cell C5.

#2 – Click on the Design Tab

Locate the ribbon and select the Table Design Tab. Good to know that the Table Design Tab is hidden by default. You can activate it by clicking any cell in the table.

Note: The Design tab is a contextual tab that appears when selecting any cell in the table.

#3 – Click the down arrow under the Table Styles Group

The Table Design tab contains two groups, Table Style Options and Table Styles. Locate the down arrow at the right of the Table Styles Group and click on it.

#4 – Use the Clear command

After clicking the down arrow, you will see the available styles in a grid layout. Choose the Clear command to remove the Table formatting.

Now you have a table, headers, filters, and data that remain untouched.

Shortcut to remove table formatting in Excel

In Excel, you can use a time-saving solution when you want to remove table formatting using shortcuts quickly.

1. Select a cell inside the table and press the Ctrl + A + A keys to select data and headers
2. Use the Alt, H, E, F key sequence to clear formatting

If you are unfamiliar with Excel shortcuts, select the Home tab and click Clear. Finally, apply the Clear Formats command in the Editing group.

How to change the Formatting of the Excel Table

Excel provides various built-in presets if you want to format a Table. To quickly change the visual style of your table, follow the steps below:

1. Click inside the table to select any cell.
2. Select the Design tab.
3. In the Table Styles Group, locate the scrollbar and click on the ‘More’ icon.
4. Choose your preferred style from the existing built-in presets.

Each style uses a unique combination of borders and shading to change the appearance of your table. Hover over a style in the gallery to preview it in your spreadsheet.

Remove Excel Table and Formatting (Convert to Range)

As mentioned above, converting a tabular format into an Excel table is not rocket science using a keyboard shortcut. However, sometimes we need to convert the Excel table back to a normal range; this section will show you how to do that.

You may be disappointed with the results after the conversion: the range keeps the original Table style format, and you need to clear the format manually.

Here are the steps to convert a table to a normal range:

1. Right-click inside the table on any cell
2. Select the Table option from the menu
3. Choose ‘Convert to Range’

Now you have a normal range (which does not contain auto filters).

To remove all the formatting, use the Clear command:

1. Select the range and the headers
2. Click the Home tab and choose Editing group
3. Click on Clear
4. Choose the Clear Formats command

Note: The Clear Formats command “reset” the format, and you will lose your font style and size settings. In the example I’m using Segoe UI, the Clear Format command will restore the default settings and change the font to Calibri.