How to Define Custom Cell Styles in Excel 2016

By Greg Harvey

You don’t have to live with just the predefined styles that Excel 2016 gives you on the Cell Styles gallery because you can readily create custom cell styles of your own.

By far the easiest way to create a new custom cell style is by example. When you create a cell style by example, you choose a cell that already displays all the formatting attributes (applied separately using the techniques discussed previously in this chapter) that you want included in the new cell style. Then, you follow these simple steps to create the new style by using the formatting in the sample cell:

  1. Position the cell pointer in the cell with the formatting that you want in the new style.

  2. Click the New Cell Style option at the bottom of the Cell Styles drop-down gallery (opened by clicking the Cell Styles button in the Styles group on the Ribbon’s Home tab).

    This action opens the Style dialog box with a generic style name (Style 1, Style 2, and so on), and the formatting attributes applied to the cell are listed in the Style Includes (By Example) section of the dialog box.

  3. Type the name for the new style in the Style Name text box (replacing the Style 1, Style 2, generic style name).

  4. Click OK to close the Style dialog box.

When defining a style by example, select only one cell that you know contains all the formatting characteristics that you want in the new style. This way, you avoid the potential problem of selecting cells that don’t share the same formatting. If you select cells that use different formatting when defining a style by example, the new style will contain only the formatting that all cells share in common.

After you close the Style dialog box, Excel adds a thumbnail for the new style to a Custom section at the top of the Cell Styles gallery. To apply this new custom cell style to other cell selections in the worksheet, all you have to do is click its thumbnail in the Custom section of the gallery.

You can also create a custom cell style from scratch by defining each of its formatting characteristics in the Style dialog box as follows:

  1. Position the cell pointer in a cell that doesn’t have only the Excel default formatting applied to it and then click the New Cell Style option at the bottom of the Cell Styles drop-down gallery (opened by clicking the Cell Styles button in the Styles group on the Ribbon’s Home tab).

    This action opens the Style dialog box with a generic style name (Style 1, Style 2, and so on), and with the attributes for the Normal style listed in the Style Includes (By Example) section of the dialog box.

  2. Type a name for the new style that you are defining in the Style Name text box (replacing Style 1, Style 2, generic style name).

    Now you need to select the formatting settings for the new style.

  3. (Optional) Remove the check mark from the check box for any attribute (Number, Alignment, Font, Border, Fill, or Protection) that you don’t want included in the new style.

    They are all selected by default.

  4. Click the Format button in the Style dialog box.

    This action opens the standard Format Cells dialog box, where you can use the options on its six tabs (Number, Alignment, Font, Border, Fill, and Protection) to select all the formatting attributes that you do want used when you apply the new style to a cell selection.

  5. After you finish assigning the formatting attributes that you want in the new style in the Format Cells dialog box, click OK to return to the Style dialog box.

    The Style Includes (By Example) section now lists all the attributes that you assigned in the Format Cells dialog box.

  6. Click OK to close the Style dialog box.

As soon as you click OK, Excel applies the formatting in your newly defined custom style to the current cell and adds the new style to the Custom section of the Cell Styles gallery. To apply this new custom cell style to other cell selections in the worksheet, all you have to do is click its thumbnail in the Custom section of the gallery.

To remove a custom style from the Cell Styles gallery that you’ve defined by example or from scratch, you have to right-click its thumbnail in the gallery and then click Delete on its shortcut menu.