Excel guide · 4 min read
How to use cell styles in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B |
|---|---|---|
| 1 | Pricing model | |
| 2 | ||
| 3 | Assumptions | |
| 4 | Unit price | 24.5 |
| 5 | Units per month | 1800 |
| 6 | Discount | 0.08 |
| 7 | ||
| 8 | Calculations | |
| 9 | Gross revenue per month | 44100 |
| 10 | Discount given | 3528 |
| 11 | Net revenue per month | 40572 |
| 12 | ||
| 13 | Output | |
| 14 | Net revenue per year | 486864 |
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.
- Select the cells.
- Go to Home > Cell Styles (in the Styles group; on a narrow window it's behind the Styles button).
- 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.
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.
- Format one cell exactly as you want inputs to look, for example a light yellow fill, blue font and a thin border.
- With that cell selected, go to Home > Cell Styles > New Cell Style.
- Type a name such as Assumption. The ticked parts are taken from the selected cell.
- 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.
- Open both workbooks.
- In the workbook that needs the styles, go to Home > Cell Styles > Merge Styles.
- 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
- Accounting vs Currency format in Excel: what's the difference?
- How to change the default font in Excel
- How to use Format Painter in Excel
- How to add borders in Excel
- Excel scroll bar too small: how to make it bigger
- How to enable macros in Excel
- How to turn on dark mode in Excel
Updated