Cell Styles in Excel: The Complete Guide

Have you ever seen some really cool Excel workbooks and thought, “I wish I could make mine look that professional”? It might seem like those skills are a long way off, or you think you don’t have the time to make worksheets “look pretty”. But in just a few minutes you can learn some hacks to make formatting cell styles in Excel a breeze, without having to be an Excel pro.

Excel has several built-in cell styles that help add consistency. That way, you don't have to decide on or manually apply the colors, fonts, sizes, and borders that would be considered appropriate for your data, and that goes with your overall visual theme. You can even create your own styles, modify styles, and import styles from other workbooks.

What is a cell style?

A cell style is a preset format that allows you to visually represent data with variations in color, cell borders, alignment, and number formats. One of the great advantages of using preset cell styles is that if you modify the formatting associated with that style, all the cells which had that style applied will be updated automatically instead of you having to go through and adjust them. This way you get an instant update of what your sheet would look like when all those cells are changed to the new format.

Cell Styles vs. Conditional Formatting

Cell styles are static. Using this command is a manual way of formatting cells quickly. It is quite different from Conditional Formatting, where formatting is dynamically applied to cells if the values inside the cell meet certain conditions you’ve defined. 

How to apply a cell style

Cell Styles are found on the Home tab of the Excel ribbon, within the Styles command group. 

 

The Cell Styles command expands to reveal a gallery of Excel-defined cell styles. In some versions of Excel, you have to click the More dropdown button, which is an arrow with a small line at the top.

In Windows systems, hovering over any of these styles will show a preview of the cell’s appearance in the active cell.

Good, Bad and Neutral

The Good, Bad and Neutral section is usually used to represent each respective data type based on how you want it to be interpreted. Sometimes you want to be able to quickly skim through a document to determine if the values that you are looking at represent a positive or negative outcome.

For example, a higher expense figure is usually considered less favorable, but a higher income figure is considered more favorable. It may be easy to lose track of what you’re looking at without a visual reminder. This section of the Cell Styles gallery accomplishes this.

Data and Model

The Data and Model section of the Cell Styles gallery indicates special types of values, including calculated results, cells where users should input values, cells that display output values, and warning cells. 

Titles and Headings

The Titles and Headings section is a quick and easy way to find formatting for title cell styles and different levels of headings according to their importance. A double-underlined Total cell style format is also available in this section.

Themed Cell Styles

The Themed Cell Styles section offers varying degrees of color accents that complement the overall theme of the workbook.

Number Format

In the Number Format section, there are five options for the display style of numbers in the active or selected cell(s) — comma-style with and without decimals, currency-style with and without decimals, and percent-style.

How to create a custom cell style

If you want to create your own cell style in Excel from scratch, click New Cell Style from the Cell Styles dropdown.

This will open up the Style dialog box, where you can overwrite the name in the Style name field to save your custom style with a name you like.

Excel uses the format of the active cell as a starting point for your new cell style. In the example above, our new style is assumed to be 11 point Calibri font with a General number format. To change any of the attributes of the style being created, click on the Format… button. This will open the Format Cells dialog box where you can apply and save Number, Alignment, Font, Border, Fill, and Protection settings. 

After making the desired changes, click OK from both windows and your custom style will be saved to the Custom section of the Cell Styles gallery, and can now be used anywhere within that workbook.

Create a cell style by modifying an existing cell style

There are two ways to modify an existing cell style.

Method 1 - Duplicate and modify

If there is an existing style that’s almost what you want but not quite, you can treat it as a template and modify it by taking the following steps:

1. Duplicate the existing style by right-clicking on the style from the Cell Styles gallery, then clicking Duplicate.
2. Rename the style in the Style name field
3. Click the Format button, select the elements to apply using the Format Cells window, and click OK.

This will be saved as a new custom style in the Custom section of the Cell Styles dropdown menu.

The original style will still exist in its original location when this method is used.

Method 2 - Modify and overwrite

You can overwrite the format settings on an existing cell style by doing the following: 

1. Right-click on the style name from the Cell Styles gallery and select Modify.
2. Click the Format button, change the elements to be modified using the Format Cells window, and click OK.

The original cell style will carry the same name, but will now carry the formatting features you have just created.

Note that Custom-built cell styles can be modified or renamed, while Excel built-in cell styles can be modified but cannot be renamed. 

Remove a cell style from data

To revert a cell to the default Excel style, select the relevant cell(s) and click the Normal style from the Good, Bad and Neutral section of the Cell Styles gallery.

Delete a predefined or custom cell style

To delete a cell style, right-click on the style name from the Cell Styles gallery and select Delete. You will not get a warning message before this action is taken! Excel will delete that style and will remove the formatting from all cells in that workbook where that style had been applied. 

Merge (import) a cell style from another workbook

You can import built-in or custom cell styles from another workbook by using the Merge Styles command. Both workbooks must be open.

1. Go to the Cell Styles command on the Home tab.
2. Click Merge Styles at the bottom of the dropdown menu
3. From the Merge Styles dialog box, select a workbook that has the style(s) you want to copy to the Destination workbook and click OK.
4. Custom styles from the Source workbook will appear in the Custom section of the Destination workbook, and Excel-defined styles will appear in their respective sections.

This method is also useful if you deleted a built-in cell style by mistake and want to restore it.

Cell Styles vs. Themes

Cell styles are different from the Themes available on the Page Layout tab. 

Each theme carries its own font, font size, and color scheme. The options offered in the cell style gallery will change to complement the selected theme and color scheme applied to the workbook. You can shrink the size of the Themes dropdown gallery by dragging on the lower right corner of the window. This will allow you to preview what your document will look like by hovering over the different themes in the gallery.

Liked learning about cell styles in Excel? Here are a few other cool tricks to make your already awesome work look like a masterpiece.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial