Cellwise Start free

Excel guide · 4 min read

How to use cell styles in Excel

Quick answerA cell style is a named bundle of formatting (number format, font, fill, borders, alignment, protection) that you apply from Home > Cell Styles. Change the style once with right-click > Modify and every cell that uses it updates. Use the Data and Model styles to mark inputs, calculations and outputs, and make your own with New Cell Style.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowAB
1Pricing model
2
3Assumptions
4Unit price24.5
5Units per month1800
6Discount0.08
7
8Calculations
9Gross revenue per month44100
10Discount given3528
11Net revenue per month40572
12
13Output
14Net revenue per year486864

Download this sheet as an Excel practice file (.xlsx)

Apply a built-in style

The gallery groups the built-in styles:

  • Good, Bad and Neutral: Normal plus green, red and yellow fills for quick flags.
  • Data and Model: Input, Calculation, Output, Linked Cell, Check Cell, Note, Explanatory Text and Warning Text.
  • Titles and Headings: Title, Heading 1 to 4 and Total (a top border with a double line under it).
  • Themed Cell Styles: accent fills that follow the workbook theme.
  • Number Format: Comma, Comma [0], Currency, Currency [0] and Percent.
  1. Select the cells.
  2. Go to Home > Cell Styles (in the Styles group; on a narrow window it's behind the Styles button).
  3. Hover over a style to preview it on the sheet, then click to apply it.

Mark inputs, calculations and outputs in a model

In the sample pricing model, B4:B6 are the numbers someone is allowed to change, B9:B11 are formulas and B14 is the answer people read. Apply Input to B4:B6, Calculation to B9:B11, Output to B14, Title to A1 and Heading 2 to A3, A8 and A13. Anyone opening the file can now see which cells to type in, without a word of explanation.

This is the main reason to use styles instead of picking colours by hand: the colour means something, and it means the same thing on every sheet. Change the discount in B6 from 8% to 10% and only the Input cell is touched; the Calculation and Output cells follow.

=B14

Result: 486864

486864: net revenue per year from 1,800 units a month at 24.50 with an 8% discount.

Modify vs Duplicate

Right-click a style in the gallery and choose Modify to change it everywhere. The Style dialog shows what the style includes, with a tick box for each part: Number, Alignment, Font, Border, Fill and Protection. Click Format to change them. Every cell that uses the style in this workbook updates at once, which is how you restyle a 20-sheet model in a minute.

A part with its box unticked isn't part of the style, so applying the style leaves that formatting alone. For example, untick Number and the style changes colours without wiping your number formats.

Choose Duplicate instead when you want a variation and the original kept. Excel proposes a name like Input 2; rename it, change the format and click OK. Changes to a built-in style only affect the current workbook, never Excel itself.

Create a custom style for assumptions

The Locked trick is the useful part. Cells are locked by default but it only matters once you use Review > Protect Sheet. With Locked switched off in the style, every Assumption cell stays editable on a protected sheet while formulas stay safe, without setting protection cell by cell. Custom styles appear in a Custom section at the top of the gallery.

  1. Format one cell exactly as you want inputs to look, for example a light yellow fill, blue font and a thin border.
  2. With that cell selected, go to Home > Cell Styles > New Cell Style.
  3. Type a name such as Assumption. The ticked parts are taken from the selected cell.
  4. Click Format, open the Protection tab and untick Locked, then click OK twice.

Copy styles to another workbook, and remove them

To remove a style from cells, select them and apply Normal from the gallery, or use Home > Clear > Clear Formats. Either one resets all formatting, number formats included. To get rid of a style completely, right-click it and choose Delete; cells that used it go back to Normal. Normal itself can't be deleted, because it's the base every cell starts from. Changing Normal changes the default font for the workbook, covered in change the default font in Excel.

Styles travel when you copy cells between workbooks, so files that are often pasted into slowly fill with junk styles. If the gallery is cluttered with names you never made, that's where they came from; deleting them one by one is the built-in fix.

  1. Open both workbooks.
  2. In the workbook that needs the styles, go to Home > Cell Styles > Merge Styles.
  3. Pick the workbook to copy from and click OK. If both have styles with the same name, Excel asks whether to merge them: Yes replaces yours with the incoming ones, No keeps yours.

Common mistakes

Colouring inputs by hand on each sheet

Use one Input or custom style everywhere, so a single Modify updates the whole workbook.

Expecting a modified style in every new workbook

Styles live in the workbook. Use Merge Styles to copy them, or save a workbook with your styles as a template.

Applying Normal to remove a fill and losing number formats

Normal resets everything. To remove only a fill, use Home > Fill Color > No Fill instead.

A style that wipes number formats

Modify it and untick Number, so the style changes appearance but leaves formats alone.

FAQ

Where are cell styles in Excel?

On the Home tab, in the Styles group, under Cell Styles. If the window is narrow, click Styles first to see the gallery.

How do I create my own cell style in Excel?

Format a cell the way you want, select it, then choose Home > Cell Styles > New Cell Style and give it a name. It appears under Custom at the top of the gallery.

How do I copy cell styles to another workbook?

Open both files, go to the workbook that needs them and use Home > Cell Styles > Merge Styles, then pick the source workbook.

How do I remove a cell style in Excel?

Select the cells and apply Normal, or use Home > Clear > Clear Formats. To delete the style itself, right-click it in the gallery and choose Delete.

Related guides

Updated