Create a simple formatting system for Excel workbooks

A consistent visual convention — blue font for hardcoded inputs, black for same-sheet formulas, green for cross-sheet links — makes any workbook self-documenting.
Watch alongside this article
Public YouTube resources matched to this article's topic, functions, and practice goal.

Do's & Don'ts of Excel Spreadsheet Design (with Examples)
Leila Gharani
Open on YouTube (opens in a new tab)

Mastering Number Formats in Excel: Currency, Percentage, and Date Formatting Tutorial
KnowHow
Open on YouTube (opens in a new tab)Good formatting is quiet. It helps people understand a workbook without drawing attention to itself, and the test is simple: hand the file to a colleague and see whether they can find the input cells without asking.
Most workbooks fail that test not because they are ugly but because they are inconsistent. Blue means “input” on one sheet and “heading” on the next. Three sheets show thousands and one shows full amounts. The fix is a small set of conventions applied everywhere — and, crucially, saved as Cell Styles so they are one click rather than a remembered recipe.
Give colour a job
Use colour to mean something, and use only as many colours as you have meanings. The convention most finance and modelling teams converge on is some version of this:
| Role | Convention | Why |
|---|---|---|
| Input | Blue font, light fill | Type here. The oldest convention in spreadsheet modelling, and the one an outsider is most likely to recognise. |
| Formula | Black font, no fill | Calculated. Don’t touch. |
| Link to another sheet | Green font | The value comes from elsewhere in this workbook. |
| External link | Red font | Depends on another file — the thing most likely to break. |
| Check | Light grey fill | A control total or validation cell, not part of the report. |
| Warning | Amber fill, dark text | Something needs attention right now. |
You do not have to use these exact colours. You do have to use the same ones on every sheet, and you have to say somewhere what they mean — a small legend block on the first sheet is enough, and it costs four cells.
The rule that matters most: do not give each section its own colour. A workbook where Marketing is purple and Operations is teal looks organised and reads as noise, because the colour is repeating something you already know from the heading while hiding the thing you don’t know — whether the cell is safe to type in.
Save the conventions as Cell Styles
This is the step that makes the system stick. A convention you have to remember lasts about two weeks.
On the Home tab, open the Cell Styles gallery, right-click any style,
choose Duplicate, and define your own: Input, Formula, Link,
External, Check, Warning. Give each one its font colour, fill, border, and
number format.
Two things then become true. Applying the convention is one click, so people actually do it. And changing it later is one edit — modify the style and every cell using it updates across the workbook. Hand-applied formatting has to be hunted down one sheet at a time.
Styles travel with the file. To reuse them in a new workbook, open both and use
Cell Styles → Merge Styles — or better, save the styled empty workbook as a
template (.xltx) and start from it. If you are building something others will
fill in repeatedly, that template is the deliverable; see
designing a reusable budget template.
Standardise number formats
Decide these once and apply them through the styles above:
- Currency. Thousands separator, and a fixed decision on decimals. Whole units for management reporting, two decimals for anything that has to reconcile to a ledger. Mixing them within one report makes columns impossible to scan.
- Negatives. Pick one representation — parentheses
(1,234)or a minus sign — and use it everywhere. Red-on-negative is fine in addition, never instead, for the same reason colour never carries meaning alone. - Percentages. Set decimal places to match what the number can actually
support. A rate derived from 12 observations should not display as
8.3333%. - Dates. One format, unambiguous about day versus month.
12/01/2026means two different days on two sides of the Atlantic;12 Jan 2026means one. - Scale. If a sheet shows thousands, say so in the column header —
Revenue (000)— not in a note somewhere else. Scale errors are the most expensive formatting mistake there is.
Where units vary by row, put the unit in its own column rather than inside the
number format. A cell containing 12 kg as text cannot be summed.
Name sheets so they tell you where to go
Sheet names like Data, Inputs, Calcs, Checks, and Report are boring in
the best way. They describe the sheet’s role, which is what a new reader needs,
rather than its content, which they can see once they are there.
Order them left to right in the direction the work flows: inputs first, calculations next, outputs last, data and checks at the far right. Someone opening the file for the first time can then read the tab strip as a table of contents.
Two things to avoid. Spaces and punctuation in sheet names force quoting in every
formula that references them — ='Q3 Sales'!A1 — which makes formulas longer and
harder to read. And dates in sheet names on a recurring file all but guarantee
the workbook gets rebuilt each period instead of refreshed.
Skip the decoration
Borders, heavy fills, and merged cells make a workbook harder to maintain and rarely make it clearer. Reach for these first:
- White space. A blank row between sections separates them more cleanly than a border does, and it costs nothing to maintain.
- Alignment. Numbers right, text left, headers matching their column. Centred numbers are harder to compare because the digits stop lining up by place value.
- Indentation (Home → Increase Indent) for hierarchy, instead of merged cells. It survives sorting, filtering, and copying; merged cells break all three.
- Freeze panes on the header row of any list longer than a screen.
Merged cells deserve a specific warning: they break sorting, they break
filtering, they break Ctrl+Arrow navigation, and they confuse every tool that
reads the sheet as data — see
preparing Excel data for Power BI.
Where you want a label centred across several columns, use Format Cells →
Alignment → Center Across Selection instead. It looks identical and breaks
nothing.
Check it reads, not just looks
Formatting choices that work on your monitor can fail elsewhere. Before you treat the system as finished, check the contrast on a cheap screen, check it in greyscale (print preview will show you), and check it against the colour-vision question — red and green flags that look obvious to you are the same grey to roughly one man in twelve. That, and the rest of the readability pass, is in making a workbook easier to read and use.
And print it. A layout that is fine on screen often breaks across pages, which is a separate set of settings covered in preparing a dashboard for PDF export.
FREE EXCEL UPDATES
Get practical Excel tips in your inbox
New tutorials, tools, templates, and worked examples a few times a month. Unsubscribe anytime.
Go deeper with this skill
Create workbooks that look professional because structure, spacing, and labels are consistent. For this article, the goal is to practice: A colour convention, a number-format standard, and a sheet-naming scheme that make a workbook readable to someone who has never opened it — saved as Cell Styles so it actually sticks.
Practice workbook setup
Use a workbook with raw data, calculations, and a final output sheet.
Practice workflow
- Define a small style system for headers, inputs, formulas, and outputs.
- Align columns and row heights so sections scan consistently.
- Use borders sparingly to separate sections rather than decorate every cell.
- Add a short cover or contents area for multi-sheet workbooks.
Quality checks
- Similar objects look similar across sheets.
- Important numbers stand out without excessive color.
- Readers can identify where to edit and where not to edit.
Common mistakes
- Using too many fonts, fills, borders, or number formats.
- Making reports pretty before fixing structure.
- Using formatting to hide unclear formulas or missing labels.
Next actions
- Create a reusable formatting checklist.
- Apply the same design system to one old workbook and compare readability.
Formula focus: even if this workflow is not formula-heavy, add one check cell that confirms the final output still matches the source data.





