Excel 2016 lets you create your own custom styles to add to the Tables Styles gallery and use in formatting your worksheet tables. Once created, a custom Table Style not only applies just the kind of formatting you want for your worksheet tables but can also be reused on tables of data in any worksheet you create or edit in the future.
You can even designate one of the custom styles you create as the new Table Style default for your workbook so that it's automatically applied when you later format a data table in its worksheets with the Tables option on the Quick Analysis toolbar.
To create a custom Table Style, you follow these steps:
Format the data in your worksheet as a table using one of the existing styles.
On the Design contextual tab of the Tables Tool tab, click the Table Styles More drop-down button and then select the New Table Style option near the bottom of the gallery.
The New Table Style dialog box shown in here appears.
Use the options in the New Table Style dialog box to create a new custom table style to add to the Table Styles gallery.Replace the generic, table style name, Table Style 1, with a more descriptive name in the Name text box.
Modify each of the individual table components in the Table Elements list box (from Whole Table through Last Total Cell) with the custom formatting you want included in your new custom table style.
To customize the formatting for a table element, select its name in the Table Element list box. After you select the element, click the Format button to open the Format Cells dialog box where you can change that element's font style and/or color on its Font tab, the border style and/or color on its Border tab, or the fill effect and/or color on its Fill tab.
Note that when customizing a First or Second Column or Row Stripe element (that controls the shading or banding of table's column or row), in addition to changing the fill for the banding on the Fill tab of the Format Cells dialog box, you can also increase how many columns or rows are banded by increasing the number in the Stripe Size drop-down list that appears when you select one of the Stripe elements.
As you assign new formatting to a particular table element, Excel displays a description of the formatting change below the Element Formatting heading of New Table Style dialog box, as long as that table element remains selected in the Table Element list box. When you add a new fill color to a particular element, this color appears in the Preview area of this dialog box regardless of which component is selected in the Table Element list box.
(Optional) If you want your new custom table style to become the default table style for all the data tables you format in your workbook, select the Set as Default Table Style for This Document check box.
Click the OK button to save the settings for your new custom table style and close the New Table Style dialog box.
As soon as you close the New Table Style dialog box, a thumbnail of the new custom table style you just created appears at the very beginning of the truncated Table Styles gallery that appears on the Design tab of the Table Tools font. You can then apply the newly created style to currently selected table of data (the sample table you formatting in order to create the new style) by clicking this thumbnail. When you next open the entire Table Styles gallery to format other tables of data that you add to your worksheets, the thumbnail of the custom style you created will appear in its own Custom section at the very top of the gallery.
If you made changes to the fill formatting for the First or Second Column Stripe element in the custom table style, don't forget to select the Banded Rows check box in Table Styles Options section on the Table Tools Design tab to make it appear in the formatted data table. So, too, if you made changes to the First or Second Row Stripe, you need to select the Banded Rows check box to make these formats appear.
To further modify, copy (in order to use its settings as the basis for a new custom style), delete, or add a custom style to the Quick Analysis toolbar, right-click its thumbnail image in the Table Styles gallery and then choose the Modify, Duplicate, Delete, or Add Gallery to Quick Access Toolbar option on its context menu.