For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=ARTICLES

Create a simple formatting system for Excel workbooks

FMT.A1 BLUE = INPUT · BLACK = FORMULA
Create a simple formatting system for Excel workbooks — Excel example preview

A consistent visual convention — blue font for hardcoded inputs, black for same-sheet formulas, green for cross-sheet links — makes any workbook self-documenting.

=YOUTUBE

Watch alongside this article

Public YouTube resources matched to this article's topic, functions, and practice goal.

video / Intermediate

Mastering Number Formats in Excel: Currency, Percentage, and Date Formatting Tutorial

KnowHow

Open on YouTube (opens in a new tab)
KnowHow

Loading preview…

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/2026 means two different days on two sides of the Atlantic; 12 Jan 2026 means 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.

=PRACTICE

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

  1. Define a small style system for headers, inputs, formulas, and outputs.
  2. Align columns and row heights so sections scan consistently.
  3. Use borders sparingly to separate sections rather than decorate every cell.
  4. 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.