Excel Formatting Styles

Sheets of figures some with color highlights
nigelcarse / Getty Images

Learn to use Excel's pre-set formatting styles to add color to your worksheet. Doing so not only gives your data a more polished look, but it can also make it easier to read and interpret the data. Follow these step-by-step examples to take your spreadsheet designs to the next level.

01
of 05

Cell Styles Overview

Screenshot of Excel showing the Cell Style Gallery

A cell style in Excel is a combination of formatting options, such as font sizes and color, number formats, and cell borders, and shading, that is named and saved as part of the worksheet.

Excel has many built-in cell styles that can be applied as is to a worksheet or modified as desired. These built-in styles can also serve as the basis for custom cell styles that can be saved and shared between workbooks.

One advantage to using styles is that if a cell style is modified after it has been applied in a worksheet, all cells using the style will automatically update to reflect the changes.

Cell styles are based on the document theme that is applied to an entire workbook. Different themes contain different formatting options so if a document's theme is changed, the cell styles for that document also change.

Further, cell styles can incorporate Excel's lock cells feature that can be used to prevent unauthorized changes to specific cells, entire worksheets, or entire workbooks.

02
of 05

Applying and Creating Cell Styles

Screenshot of Excel showing the Cell Styles button

To apply one of the built-in formatting styles in Excel:

  1. Select the range of cells to be formatted.
  2. On the Home tab of the ribbon, click on the Cell Styles icon to open the gallery of available styles.
  3. Click on the desired cell style to apply it.

To create a custom cell style:

  1. Select a single worksheet cell.
  2. Apply all desired formatting options to this cell — a built-in style can be used as a starting point.
  3. Click the Home tab on the ribbon.
  4. Click on the Cell Styles option on the ribbon to open the Cell Styles Gallery.
  5. Click on the New cell styles option at the bottom of the gallery.
  6. Type a name for the new style in the Style name box.
  7. The formatting options already applied to the selected cell will be listed in the dialog box.
Screenshot of Excel showing creation of a custom cell

To make additional formatting options or modify current choices:

  1. Click on the Format button in the Style dialog box to open the Format Cells dialog box.
  2. Click on a tab in the dialog box to view the available options.
  3. Apply all desired changes.
  4. Click OK to return to the Style dialog box.
  5. In the Style dialog box, under the section entitled Style Includes (By Example), clear the check boxes for any formatting that is not wanted.
  6. Click OK to close the dialog box and return to the worksheet.

The new style's name is added to the top of the Cell Styles gallery under the Custom heading. To apply the new style to cells in a worksheet, follow the steps list above for applying a built-in style.

03
of 05

Modifying an Existing Cell Style

Screenshot of Excel showing how to modify a cell

For Excel's built-in styles, it is usually best to modify a duplicate of the style rather than a style itself, but both built-in and custom styles can be modified using the following steps:

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Modify to open the Style dialog box.
  3. In the Style dialog box, click on the Format button to open in the Format Cells dialog box.
  4. Click on the various tabs to view the available options and apply all desired changes,
  5. Click OK to return to the Style dialog box.
  6. In the Style dialog box, under the section entitled Style Includes (By Example), clear the check boxes for any formatting that is not wanted.
  7. Click OK to close the dialog box and return to the worksheet.

At this point, the modified cell style will be updated to reflect the changes.

04
of 05

Copying and Duplicating Cell Styles

Screenshot of Excel showing how to merge styles

To copy a custom cell style for use in a different workbook:

  1. Open the first workbook containing the custom style to be copied.
  2. Open the second workbook that the style is being copied to.
  3. In the second workbook, click the Home tab on the ribbon.
  4. Click on the Cell Styles icon on the ribbon to open the Cell Styles gallery.
  5. Click on the Merge Styles option at the bottom of the gallery to open the Merge Styles dialog box.
  6. Click on the name of the workbook containing the style to be copied.
  7. Click OK to close the dialog box.

At this point, an alert box will appear asking if you want to merge styles with the same name. Unless you have custom styles with the same name but different formatting options in both workbooks, click the Yes button to complete the transfer of the style into the destination workbook.

Screenshot of Excel showing duplication of styles

Create a duplicate of a built-in style or a custom style using the following steps:

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Duplicate to open the Style dialog box.
  3. In the Style dialog box, type in a name for the new style.
  4. At this point, the new style can be changed using the steps listed above for modifying an existing style.
  5. Click OK to close the dialog box and return to the worksheet.

The new style's name is added to the top of the Cell Styles gallery under the Custom heading.

05
of 05

Removing and Deleting Cell Style Formatting

Screenshot of Excel showing how to remove styles

To remove a cell style's formatting from cells of data without deleting the cell style:

  1. Select the cells that are formatted with the cell style that you want to remove.
  2. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  3. In the Good, Bad, and Neutral section near the top of the gallery, click on the Normal option to remove all applied formatting.

The above steps can also be used to remove formatting that has been applied manually to worksheet cells.

To completely delete a cell style:

With the exception of the Normal style, which cannot be removed, all other built-in and custom cell style can be deleted from the Cell Styles gallery.

If the deleted style had been applied to any cells in the worksheet, all formatting options associated with the deleted style will be removed from the affected cells.

  1. On the Home tab of the ribbon, click on the Cell Styles icon to open the Cell Styles gallery.
  2. Right-click on a cell style to open the context menu and choose Delete — the cell style is immediately removed from the gallery.