Cell Styles in Excel: Format Inputs, Totals and Headings Fast

Cell styles in Excel let you format a cell with one click: pick "Input", "Total" or "Heading 1" and Excel applies the font, fill, border and number format saved in that style. Change the style later and every cell that uses it updates. This guide is for anyone who builds models, budgets or reports that other people read and edit, and wants them to look consistent without formatting every cell by hand.

What are cell styles in Excel?#

A cell style is a named bundle of formats saved in the workbook. Each style can control up to six things:

The difference from normal formatting is the link. When you format a cell by hand, Excel forgets how you did it. When you apply a style, the cell remembers the style's name, so you can restyle a whole workbook by editing one style.

How to apply a cell style#

To apply a style, select the cells, then choose it from the gallery:

1. Select the cells, for example the column headings in row 1.
2. Go to Home > Cell Styles (in the Styles group).
3. Hover over a style to preview it, then click to apply it.

On a narrow window, the Styles group may shrink into one Styles button; the gallery is inside it. Excel for Mac has the same Cell Styles button on the Home tab. On Windows, Alt+' (apostrophe) opens the Style dialog for the selected cell.

The Comma Style and Percent Style buttons in the Number group apply the built-in Comma and Percent styles. For one-off number formats, the keyboard is faster: Ctrl+Shift+$ for currency and Ctrl+Shift+% for percent (Mac: ⌃⇧$ and ⌃⇧%). The guide to formatting numbers as currency and percent covers those formats in detail.

Which built-in cell styles should you use?#

Excel groups its built-in styles into five sets. The exact colours depend on the workbook theme:

• Good, Bad and Neutral: Normal, Good (green), Bad (red) and Neutral (yellow). Use them to mark results by hand.
• Data and Model: Input, Calculation, Output, Check Cell, Linked Cell, Note, Warning Text and Explanatory Text.
• Titles and Headings: Title, Heading 1 to Heading 4, and Total.
• Themed Cell Styles: tinted fills such as "20% - Accent1" and solid "Accent1" to "Accent6".
• Number Format: Comma, Comma [0], Currency, Currency [0] and Percent.

The Data and Model set is the most useful one for job work. It gives a model a visual language that readers learn quickly:

How to format inputs, totals and headings in one pass#

Here is a quick routine for a small budget sheet:

1. Select the title cell and apply Title.
2. Select the column headings and apply Heading 3 or Heading 4.
3. Select the typed amounts and apply Input.
4. Select the total row and apply Total.
5. Add a check cell next to the total and apply Check Cell.

A check cell holds a formula that confirms the numbers add up. With costs in B2:B5 and a typed total in B6:

=IF(SUM(B2:B5)=B6,"OK","Check")

The four costs add up to 7,000, so the result is OK. If someone changes Rent to 1,300 without updating the total, it shows Check. Styles make the purpose of each cell obvious, and the formula makes sure the purpose is met.

Styles don't replace good layout. For long labels, see how to wrap text in a cell, and for extra lines, see how to add borders in Excel.

How to create your own cell style#

Make a custom style when the built-in ones don't match your company's look:

1. Format one cell exactly the way you want: font, fill, border, number format.
2. With that cell selected, choose Home > Cell Styles > New Cell Style.
3. Type a name, such as "Company input".
4. Untick any parts the style should leave alone. For example, untick Number if the style should keep each cell's own number format.
5. Click OK. The style appears under Custom at the top of the gallery.

To change a style, right-click it in the gallery and choose Modify, then click Format. Every cell that uses the style updates at once. To copy styles from another open workbook, use Cell Styles > Merge Styles. Styles are saved in the workbook, so a template with your custom styles keeps them for every file built from it.

Cell styles vs Format Painter vs table styles#

These three tools all copy a look, but they behave differently:

• Format Painter copies the formatting of one cell to another, once. There is no link afterwards, so later changes must be repeated by hand. The guide to Format Painter shows how to use it many times in a row.
• Cell styles keep a link, so one edit restyles the whole workbook.
• Table styles (from Format as Table) format a whole table: banded rows, header row and total row. They are a separate gallery and work only on Excel tables.

A common mix is a table style for the data, plus Input, Total and Check Cell styles for the model around it.

Common problems with cell styles#

Most surprises come from styles that include more parts than you expected:

1. A style wiped my number format. The style includes the Number part. Modify it and untick Number, or apply the number format after the style.
2. Decimals look wrong after applying Comma or Currency. These styles set two decimal places. Adjust them with the Increase and Decrease Decimal buttons; see how to change decimal places.
3. The colours changed when I changed the theme. Themed styles follow the theme colours. That is by design.
4. I can't delete Normal. Normal is the base style for every cell and can't be deleted. Modifying it changes the default look of the whole workbook, so do that with care.
5. The workbook has hundreds of odd styles. Copying sheets between files can bring styles along. Delete unused custom ones from the gallery with right-click > Delete.

Practise it#

Formatting is quick to learn but easy to forget under pressure in a test. Practise cell formatting in a live spreadsheet, then build speed with the formatting shortcuts lesson, where you format numbers, borders and headings from the keyboard and get instant feedback. You can also try the check-cell exercise on this page first.