Excel table styles and formatting: how to apply, change and remove
Excel table styles
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
Total row - add the totals row at the end of the table with a list of functions for each total row cell.
Banded rows and banded columns - alternate row or column shading, respectively.
First column and last column - apply special formatting for the first and last column of the table.
Filter button - display or hide the filter arrows in the header row.
How to choose a table style when creating a table
To create a table formatted with a specific style, do the following:
Select the range of cells that you want to convert to a table.
On the Home tab, in the Styles group, click Format as Table.
In the Table Styles gallery, click the style that you want to apply. Done!
How to change table style in Excel
To apply a different style to an existing table, perform these steps:
Click any cell within the table whose style you want to change.
On the Design tab, in the Table Styles group, click the More button
to show all available Excel Table styles.
Hover your mouse over the style you want to apply, and Excel will show you a life preview. To apply the new style, just click on it.
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.
How to change the default table style in Excel
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.
How to create a custom table style
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:
On the Home tab, in the Styles group, click Format as Table. Or, select an existing table to display the Design tab, and click the More button
.
Underneath the predefined styles, click New Table Style.
In the New Table Style window, type a name for your custom table style in the Name box.
Under Table Elements, select the element you want to format and click the Format button. The Format Cells dialog will open, and you select the desired formatting options on the Font, Border, and Fill tabs.
To remove existing formatting, click the element, and then click the Clear button.
Tips:
The formatted table elements are highlighted in bold in the Table Element box.
The formatting changes are shown in the Preview section on the right.
To use the newly created table style as the default style in the current workbook, select the Set as default table quick style for this document box.
Click OK to save your custom table style.
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.
PRE ORDER THE COMPLETE EXCEL MANUAL ( DIGITAL) TO READ FURTHER
DIRECT MESSAGE TO PRE ORDER AND ENJOY THE COMPLETE A-Z MANUAL TO BECOME EXCEL EXPERT IN NO TIME