How to Format Excel Spreadsheets Using Cell Styles

Printed spreadsheets with color highlights

nigelcarse/Getty Images

Formatting your Excel spreadsheets gives them a more polished look, and can also make it easier to read and interpret the data, and thus better suited for meetings and presentations. Excel has a collection of pre-set formatting styles to add color to your worksheet that can take it to the next level.

These instructions apply to Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

What is a Cell Style?

A cell style in Excel is a combination of formatting options, including font sizes and color, number formats, cell borders, and shading that you can name and save as part of the worksheet.

Screenshot of Excel showing the Cell Style Gallery

Excel has many built-in cell styles that you can apply as is to a worksheet or modify as desired. These built-in styles can also serve as the basis for custom cell styles you can save and share between workbooks.

  1. Select the range of cells you want to format.

  2. On the Home tab of the ribbon, click the Cell Styles icon, in the Styles section, to open the gallery of available styles.

  3. Click the desired cell style to apply it.

    The Cell Styles menu icon in Excel.

One advantage to using styles is that if you modify any cell style after applying it in a worksheet, all cells using that style automatically update to reflect the changes.

Further, you can incorporate Excel's lock cells feature into cell styles to prevent unauthorized changes to specific cells, worksheets, or workbooks.

You can also customize cell styles either from scratch or using a built-in style as a starting point.

  1. Select a worksheet cell.

  2. Apply all desired formatting options to this cell.

  3. Click the Home tab on the ribbon.

  4. Click Cell Styles on the ribbon to open the Cell Styles Gallery.

  5. Click New cell styles at the bottom of the gallery.

  6. Type a name for the new style in the Style name box.

  7. For more options, click the Format button in the Style dialog box to open the Format Cells dialog box.

  8. Click a tab in the dialog box to view the available options.

  9. Apply all desired changes.

  10. Click OK to return to the Style dialog box.

  11. Underneath the name is a list of the formatting options that you selected. Clear the check boxes for any unwanted formatting.

  12. Click OK to close the dialog box and return to the worksheet.

    New cell style dialog box in Excel.

The new style's name will now appear at the top of the Cell Styles Gallery under the Custom heading. To apply your style to cells in a worksheet, follow the steps above for using a built-in style.

To edit cell formatting, launch the Cell Styles Gallery and right-click on a cell style and choose Modify > Format. The right-click menu also includes a Duplicate option.

Copying a Cell Style to Another Workbook

When you create a custom cell style in a workbook, it's not available across Excel. You can easily copy custom styles to other workbooks, though.

  1. Open the first workbook containing the custom style you want to copy.

  2. Open the second workbook.

  3. In the second workbook, click the Home tab on the ribbon.

  4. Click Cell Styles on the ribbon to open the Cell Styles gallery.

  5. Click Merge Styles at the bottom of the gallery to open the Merge Styles dialog box.

  6. Click the name of the first workbook.

  7. Click OK to close the dialog box.

    Cell Styles dialog box with merge styles selected in Excel.

An alert box will pop up 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 into the destination workbook.

Removing Cell Style Formatting

Finally, you can remove any formatting you apply to a cell without deleting the data or the saved cell style. You can also delete a cell style if you no longer want to use it.

  1. Select the cells that are using the cell style that you want to remove.

  2. On the Home tab of the ribbon, click Cell Styles to open the Cell Styles gallery.

  3. In the Good, Bad, and Neutral section near the top of the gallery, click Normal to remove all applied formatting.

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

You can delete any built-in and custom cell styles from the Cell Styles gallery except for Normal, which is the default. When you delete a style, any cell that was using it will lose all associated formatting.

  1. On the Home tab of the ribbon, click Cell Styles 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.