An Excel Table has easy and impressive features; dynamic range, sorting and filtering, a big bunch of selectable styles, a fast track to adding a Total Row are among some. Which is why we would understand if you want to keep the Table for your dataset but not the look of the Table. Although the preset Table styles in Excel are not limited in variety, they may not match some exacting standards. In such a case it’s a good idea to let the range continue as an Excel Table but format it manually to choice.
Another scenario is when you want to keep the exterior of an Excel Table minus the functions of an Excel Table. Basically, you’d want to make your dataset a pretty range which is going into print and there is no need of a Table anymore. Or you are done with your work on your Table, need to pass the file around but don’t intend for the user to access the data as a Table. In those cases, you can retain the Table’s formatting as it is and delete the Excel Table.
This quick guide covers both the scenarios above and also a small section on how to clear all formatting from a Table down to default plain text.
Let’s get format sweeping!
Method #1 – Using Table Styles Feature
The steps below will remove the Excel Table formatting. Only the formatting will be removed; the Table stays with all its functions. This is done in a few clicks from the Table Styles menu in the Table Design tab. Don’t worry if you can’t find the Table Design tab when you look at your file; the tab appears only when the active cell is in a Table. Let’s use this elusive tab to remove Table formatting from an Excel Table:
- Select any cell in the Excel Table.
- Go to the Table Design tab and open the Table Styles menu by clicking on the dropdown arrow.
- Select the first option in the menu which is None.
- OR select the Clear option at the end of the menu.
The Table formatting will now be cleared but the Table functions still apply, as you can see from the filter buttons in the headers:
Method #2 – Using Convert to Range Feature
Here’s the situation opposite to the one above; you want to keep the look of an Excel Table but don’t want it to be an Excel Table. To achieve that, the Table will be converted to a range. When we say range here, we are talking about a regular group of cells. Converting a Table to a range leaves you with a nondynamic dataset but maintains the formatting of a Table. You will not be able to access any of the functions and features of an Excel Table.
We will convert an Excel Table to a range from the right-click context menu. The following are the steps to remove Excel Table formatting, converting the data to a range:
- Right-click any cell in the Excel Table. From the context menu, go to Table > Convert to Range.
- Select Yes in the confirmation window.
You will notice that the filters, the focal point of Excel Tables, has disappeared while the format has remained:
Method #3 – Using Clear Formats Option
Clearing all formatting removes all the formats, reverting the cells to their former condition, all in default text. All borders, color fill, alignment, font size, color, style, etc. from the selected cells will be cleared and reset to default.
For this action, we are using the little eraser on the Home tab, sitting ready to clear things away and is called the Clear button. This is how to find it and use it to clear all formatting in a Table:
- Select the cells to be cleared from their formatting.
- From the Home tab, click on the Clear button in the Editing
- Select Clear Formats from the list.
That’s all it takes to bare the Table and take away all the formatting while keeping the Excel Table’s functionality.
That was our short guide on removing Table formatting in Excel. We have elaborated on removing formatting while keeping the Table, how to remove the Table while keeping the formatting, and also how to clear all formatting from a Table. Apply one of the methods when you’re in need of a superficial table or superficial formatting, we won’t judge. While you’re formatting, removing, and clearing, we’re jotting down more hacks to help you hack your way through Excel field.
About Mehwish Javaid