The tutorial explains how you can quickly apply or change table styles and remove table formatting keeping all features of an Excel table.
After you have created a table in Excel, what's the first thing you would like to do with it? Make it look exactly the way you want!
Luckily, Microsoft Excel provides a variety of predefined table styles that let you apply or change the table formatting in a click. If none of the built-in styles meets your needs, you can quickly create your own table style. In addition, you can show or hide the main table elements, such as header row, banded rows, total row, and so on. This tutorial will show you how to leverage these useful features and where to get started.
Excel tables make it a lot easier to view and manage data by providing a handful of special features such as integrated filter and sort options, calculated columns, structured references, total row, etc.
By converting data to an Excel table, you also get a head start on the formatting. A newly inserted table comes already formatted with font and background colors, banded rows, borders, and so on. If you don't like the default table format, you can easily change it by selecting any of the inbuilt Table Styles on the Design tab.
The Design tab is the starting point to work with Excel table styles. It appears under the Table Tools contextual tab, as soon as you click any cell within a table.
As you can see on the screenshot above, the Table Styles gallery provides a collection of 50+ inbuilt styles grouped into Light, Medium, and Dark categories.
You can think of an Excel table style as a formatting template that automatically applies certain formats to table rows and columns, headers and totals row.
Apart from table formatting, you can use the Table Style Options to format the following table elements:
The following screenshot demonstrates the default Table Style options:
To create a table formatted with a specific style, do the following:
To apply a different style to an existing table, perform these steps:
Tip. If you have applied any formatting to the table manually, e.g. highlighed certain cells in bold or with a different font color, selecting another Excel style will keep the manually applied formats in place. To apply a new style and remove any existing formatting, right-click on the style, and then click Apply and Clear Formatting.
To set a new default table style for a given workbook, right-click that style in the Table Styles gallery and select Set As Default:
And now, whenever you click Table on the Insert tab or press the table shortcut Ctrl+T, a new table will be created with the selected default format.
If you are not quite happy with any of the built-in Excel table styles, you can create your own table style in this way:
To remove existing formatting, click the element, and then click the Clear button.
As soon as a custom style is created, it is automatically added to the Table Styles gallery:
To modify a custom table style, go to the Table Styles gallery, right-click on the style, and click Modify…
To delete a custom table style, right-click on it, and select Delete.
The built-in Excel table styles can neither be modified or deleted.
Tip. A custom table style is available only in the workbook where it is created. If you want to use it in another workbook, the fastest way is to copy the table with the custom style to that workbook. You can delete the copied table later and the custom style will remain in the Table Styles gallery.
If you want to quickly format the worksheet data with any of the inbuilt Excel table styles, but you don't want to convert a regular range to an Excel table, you can use the following workaround:
Or, right-click the table, point to Table, and click Convert to Range.
If you want to keep all features of an Excel table and remove only the formatting such as banded rows, shading and borders, you can clear the table format in this way:
Tip. To remove a table but keep data and formatting, go to the Design tab Tools group, and click Convert to Range. Or, right-click anywhere within the table, and select Table > Convert to Range.
For more information, please see How to remove table formatting in Excel.
That's how to manage table styles and formatting in Excel. I thank you for reading and hope to see you on our bog next week!
Table of contents