How to Use Cell Styles in Excel to Save Time Formatting

Create, Apply and Modify Cell Styles to Save Time Formatting in Excel

by Avantix Learning Team | Updated April 8, 2022

Applies to: Microsoft^® Excel^® 2013, 2016, 2019, 2021 and 365 (Windows)

You can apply cell styles in Excel worksheets to quickly format cells in a consistent way and automate repetitive formatting. Excel includes several built-in styles (such as Heading 1, Heading 2 and so on) and you can modify styles before or after you apply them. If none of the built-in styles fit your needs, you can also create a new style.

A cell style includes a set of formats such as font, font size, number format, cell borders and cell shading. You can also lock cells using cell styles.

The default style in an Excel workbook is the Normal style. When you apply formatting to cells without using cell styles, you are using direct formatting and overriding a style.

It is important to note that styles are affected by themes so if theme colors or fonts are changed, cell styles may change as well. Also, if you modify styles or create new styles, the modifications and new styles are saved only in the current workbook.

You can also apply number formatting using cell styles. There are several built-in number format styles including a comma style with or without decimals, a currency style with or without decimals and a percent style. You can also add custom number format styles.

Note: Screenshots in this article are from Excel 365 but are similar in older versions of Excel.

Recommended article: How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)

Do you want to learn more about Microsoft Excel? Check out our virtual classroom or in-person classroom Excel courses >

Applying a cell style

To apply a cell style:

1. Select the cell or cells to which you want to apply a cell style.
2. Click the Home tab in the Ribbon.
3. In the Styles group, click Cell Styles. In some versions of Excel, click the More down arrow on the bottom right of the Cell Styles gallery.
4. Click the style you want to apply.
5. Repeat for other cells in the workbook as needed (cell styles can be applied on multiple worksheets in a workbook).

The Cell Styles gallery includes Excel's built-in styles:

Modifying a cell style

To modify a cell style:

1. Select a cell using the style you want to modify.
2. Click the Home tab in the Ribbon.
3. In the Styles group, click Cell Styles. In some versions of Excel, click the More down arrow on the bottom right of the Cell Styles gallery.
4. Right-click the style you want to modify. A drop-down menu appears.
5. Click Modify. A dialog box appears.
6. Check or select the elements you want to include in the style below Style includes. You can select all or some of the check boxes.
7. Click Format. The Format Cells dialog box appears.
8. Click a tab and select the formatting you want (for example, click the Font tab and select a font size).
9. Repeat for other tabs.
10. Click OK twice.

If you right-click a style in the Cell Styles gallery, a drop-down menu appears:

After you select Modify, a Style dialog box appears:

Creating a new cell style by duplicating a cell style

To create a new cell style by duplicating a cell style:

1. Select the cell or cells to which you want to apply a new cell style.
2. Click the Home tab in the Ribbon.
3. In the Styles group, click Cell Styles. In some versions of Excel, click the More down arrow on the bottom right of the Cell Styles gallery.
4. Right-click the style you want to duplicate. A drop-down menu appears.
5. Click Duplicate. A dialog box appears.
6. Enter a name for the new style in the Style Name box.
7. Check or select the elements you want to include in the style below Style includes. You can select all or some of the check boxes.
8. Click Format. The Format Cells dialog box appears.
9. Click a tab and select the formatting you want (for example, click the Font tab and select a font size).
10. Repeat for other tabs.
11. Click OK twice.

The new cell style will appear at the top of the Cell Styles gallery under Custom.

Creating a new custom cell style

To create a new custom cell style:

1. Select the cell or cells to which you want to apply a new cell style.
2. Click the Home tab in the Ribbon.
3. In the Styles group, click Cell Styles. In some versions of Excel, click the More down arrow on the bottom right of the Cell Styles gallery.
4. Click New Cell Style. A dialog box appears.
5. Enter a name for the new style in the Style Name box.
6. Check or select the elements you want to include in the style below Style includes. You can select all or some of the check boxes.
7. Click Format. The Format Cells dialog box appears.
8. Click a tab and select the formatting you want (for example, click the Font tab and select a font size).
9. Repeat for other tabs.
10. Click OK twice.

The new cell style will appear at the top of the Cell Styles gallery under Custom.

Removing a cell style

An easy way to remove a cell style from a cell is to simply apply the Normal style.

Copying cell styles between workbooks

To copy cell styles between workbooks:

1. Ensure the workbook you want to copy cell styles from is open.
2. Open or activate the workbook to which you want to copy styles.
3. Click the Home tab in the Ribbon.
4. In the Styles group, click Cell Styles. In some versions of Excel, click the More down arrow on the bottom right of the Cell Styles gallery.
5. Click Merge Styles. A dialog box appears.
6. Click the name of the workbook from which you want to copy styles below Merge styles from.
7. Click OK. A dialog box appears asking if you want to merge all styles with the same names.
8. Click Yes.

When  you select Merge Styles, a dialog box appears with a list of open workbooks:

Keep in mind that if you copy styles to a workbook that is using different theme fonts and colors, the cell styles may not display the way you want. It's best to apply the same themes in both workbooks. Themes appear on the Page Layout tab in Excel.

Cell styles have been available in Excel for a long time and are a fantastic timesaver.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Remove Duplicates in Excel (3 Easy Ways)

How to Merge Cells in Excel (4 Ways with Shortcuts)

How to Combine Cells in Excel using Concatenate (3 Ways)

Excel XLOOKUP Function (Overview, Syntax + Formula Examples)

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix^® Learning

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca