In the previous article, you learned how to create a table in Excel with headers. Now we will show you how to format a table in Excel, which is almost the same. In this tutorial, you will learn how to remove table formatting in Excel 2016 or later. If you remove a table format in Excel, it will remove all the styles and colors but keep the features. You will also understand how to change the table style in Excel.
Table of contents
How to format a table in Excel 2016
It is effortless to format a table in Excel because it is the same as creating a table. The only difference is the steps you need to follow to convert the data list. When you choose a table style in Excel, it will automatically format the table with features.
Before you start, you need to have a data list ready with rows and columns. Read the previous article to understand how to organize your data. We are going to use the same data, as you can see in the below picture.
- Start by selecting a cell in your data list. In the example below, we have selected cell F1 (Quantity). You can also select the entire data range (A1:E10)
- Then go to the Home tab and click on Format as Table in the ribbon.
- Now a large list appears, where you can choose a Table Style. Click on any style you like to have.
After you click on the table style, a box called Format as Table appears on your sheet. It automatically selects the entire data list, as you can see by the green dotted line. If your table has headers, you should tick on a check in front of My table has headers. Then click OK to confirm.
Now you have successfully formatted a table with headers in Excel. You have chosen a specific table style, but it automatically includes features. These features could be useful, such as data sort and filter.
Remove the table formatting in Excel
Luckily you can remove a table format and style in Excel, but keep the features such as data sort and filter. It is easy to follow the steps, but you will be left with a white table with functions.
There are two ways to clear the formatting of an Excel table. We will explain both options to give you a better understanding.
Option 1 – How to remove a table style and formatting
These steps below will remove the table format and style, such as the colors. It will keep all the table features.
- Start by selecting the entire table.
- You have to be in the Home tab > go to the Editing group in the ribbon.
- Click on Clear > Select Clear Formats.
Once you click on Clear Formats, the table becomes all white. All the table formatting is removed, but you will still have the filter buttons at each header.
Option 2 – How to clear a table style
You could also remove the table formatting in another way. This option is very easy to follow.
- You start by clicking on any cell in your table. It will open Table Tools at the top of your document.
- Then click on the Design tab under Table Tools.
- Now go to the Table Styles group and click on the More drop-down button.
Once you click the drop-down arrow, it will open all the available table styles. Underneath, you should click on Clear. It will clear the table style, and you will be left with a white table with features.
Remove table features (Convert to range)
If you only want a table with the same format and style but without any features or functions, such as data sort and filter, you should follow these steps. Microsoft Excel has shown how to convert an Excel table to a range of data. In this case, you will remove the Excel table functions but keep the same table format and style.
Follow the steps below if you want to keep the data and table style but remove the features.
- Click on a cell in the table to open the Table Tools at the top of your document.
- Click on the Design tab that appears under Table Tools.
- Go to the Tools group in the ribbon > Then click on Convert to Range.
You could also right-click on a cell in the table. Choose Table > Then click on Convert to Range. This will also give the same outcome.
Now a box shows up that asks you if you want to convert the table to normal range. Here you should click on the Yes button.
It removes all the features and functions in the table. As you can see, the table style is still the same.
Change the table style and format
Now we are going to show you how to change the table style in Excel. If you don’t like the table format, you can change this in a few steps.
- First, you have to click on a cell in your table. This will open the Table Tools at the top of the document.
- Now click on the Design tab under Table Tools.
- Go to Table Styles in the far right of the ribbon > Click on the More drop-down arrow.
- This will open a list of different table styles > There are Light, Medium, and Dark styles > Go with your mouse cursor on each style to see the preview.
- Click on the table style you liked most. It will change the table formatting immediately.
If you want to remove a table format in Excel, you can do it in two different ways. When you remove table formatting in Excel 2016, you will clear the table style and color. It means that you will end up with a white Excel table with features. You have also learned how to change table style in Excel. Luckily there is also an option to remove the table but keep the data and formatting.