Financial Model Formatting: The Color Conventions

In a financial model, font colour records where a number came from, not how important it is. Blue text means a human typed the value in, so it is an assumption and you are allowed to change it. Black text means the cell is a formula that calculates inside the current sheet, so typing over it destroys a calculation. Green text means the cell is a formula pulling a value from another sheet in the same workbook, so the number is really owned somewhere else. Red is usually reserved for a link to a different workbook or an external data source, which is a warning that the value depends on a file that may not be attached. Everything else in the scheme is firm preference. Those three colours, blue, black and green, are close to universal, and they exist to answer one reviewer question fast: if I change this cell, what breaks?

This convention gets taught in analyst training, gets enforced by a VP with a red pen, and almost never gets written down anywhere a person outside finance can read it. So plenty of people open a model, see a wall of blue and black numbers, assume it is decoration, and type straight over a formula. It is not decoration. It is the closest thing a spreadsheet has to a permissions system.

What do the colours in a financial model mean?

The scheme is small, which is why it survives.

Blue is a hard-coded input. Somebody typed this number. Historical revenue pulled from a filing, a tax rate, a growth assumption, a headcount plan, a purchase price. If the model is wrong because an assumption is wrong, the fix lives in a blue cell.

Black is a formula that stays on this sheet. A gross margin calculated from the two rows above it. A subtotal. A year-over-year growth rate. Black cells are derived, and they should never be typed into.

Green is a formula that reaches another sheet in the same workbook. Revenue on the valuation tab pointing at revenue on the model tab. Green tells a reviewer two things at once: the number is calculated, and the calculation you want to inspect is not on the screen in front of you.

Red, in most house styles, is a link to a different workbook. Some teams use red for anything external, and some split it, red for another Excel file and a fourth colour for a live data feed. Whatever the shade, the meaning is the same. This number arrives from outside, and it can go stale or break when the source file moves.

One thing worth settling early: this is font colour, not fill colour. Fill is doing a different job in most models, marking header bands, section breaks and check rows. Mixing the two axes is how a model ends up looking like a parking ticket.

Why does colour mark editability rather than importance?

In an ordinary spreadsheet, colour marks importance. The total is bold and dark, the flagged rows are amber, the good numbers are green. That is a reader aid, and it is the right instinct for a report someone reads once.

A model is not read once. It gets handed to a colleague who has to change an assumption and rerun it, then to a reviewer who has to decide whether to trust the output. Both of those people are asking a question that has nothing to do with importance. They are asking which cells are safe to touch.

Structure is supposed to answer that. The ICAEW's twenty principles for good spreadsheet practice put it as principle 9: structure workbooks with a clear flow of inputs, processes, and outputs. When every assumption lives on one inputs tab and every calculation lives downstream, you barely need colour at all.

Real models leak. An assumption gets typed directly into the schedule where it is used, because moving it to the inputs tab meant three more clicks and it was 11pm. The colour convention is the per-cell backstop for exactly that leak. It marks provenance on the cell itself, so the structure can be imperfect and the model is still auditable.

That also explains why the convention is stricter than it looks. A blue cell sitting inside a black block is not a style choice, it is a flag saying an assumption is living in a calculation area, go and look at it. If you colour cells by importance instead, you lose that signal entirely.

The convention reference card

Keep this on the legend tab of your own model.

Cell type	Font colour	When it applies	The review question it answers
Hard-coded input	Blue	A human typed the value: assumptions, drivers, historicals entered by hand	"Is this number right, and may I change it?" Yes, change it.
Same-sheet formula	Black	Any calculation referencing only cells on this sheet	"Where does this come from?" From cells you can see. Do not type over it.
Cross-sheet formula	Green	A formula pulling from another tab in this workbook	"Which tab owns this?" Another one. Fix it there, not here.
External workbook link	Red	A formula referencing a separate .xlsx file	"Will this still be right tomorrow?" Only if the source file travels with it.
External data feed	Red or purple, house style	A live pull from a data provider or database	"Is this refreshed?" Check before you rely on the number.
Check or error flag	Black text, coloured fill or a conditional rule	Balance checks, circularity flags, tie-outs	"Did the model pass its own tests?"
Label or heading	Black, or the house header colour	Row labels, column headers, section titles	Not a value, so it sits outside the convention.

One rule keeps the card honest. If a cell contains anything a person typed, it is blue, even when the rest of its row is black. A single hardcode hiding in a formula row is the thing a reviewer is hunting for.

How do you set this up as reusable Cell Styles?

Recolouring by hand is how the convention decays. Excel's Cell Styles gallery makes it one click, and it is a feature most people have scrolled past for years.

Microsoft's current instructions for applying, creating, or removing a cell style are: on the Home tab, in the Styles group, click the Cell Styles dropdown arrow, then New Cell Style at the bottom of the gallery. Name it, click Format, set the font colour in the Format Cells dialog, and click OK.

The step that matters is the next one. Back in the Style dialog, under Style Includes (By Example), uncheck everything except Font. A style that carries number formats, borders, fills and alignment will overwrite formatting you already have, every time you apply it. A font-only style changes the colour and leaves the rest of the cell alone, which is what you want when you are tagging three hundred cells across a finished model.

Build four of them: Input (blue), Formula (black), Link (green), External (red). Then tag cells as you build, not afterwards.

Two things to know about where they live. Styles belong to the workbook, not to Excel, so a new file starts with the built-in set again. To carry yours across, open both files and use Home, then Styles, then the Cell Styles dropdown, then Merge Styles. Or save a styled empty workbook and start every model from it. Cell styles are also tied to the document theme, so switching the workbook theme can shift the colours your styles produce.

On Mac the path is shorter: Home, then Cell Styles, then New Cell Style. You can also Control-click an existing style and choose Duplicate to base a new one on it. Microsoft's article documents the desktop app rather than Excel for the web, so the practical route is to build the styles in the desktop app; the browser can apply what is already saved in the file. Ribbon layouts move between releases, so if a name here does not match your build, check the current Microsoft page rather than assuming the feature is gone.

Which conventions are universal and which are firm-specific?

Universal, in the sense that almost nobody will argue with you: blue for typed inputs, black for same-sheet formulas. If you adopt only two colours, adopt those two.

Widely used but not universal: green for cross-sheet links. Some teams treat every in-workbook formula as black, on the grounds that a tab is still the same file, and reserve colour for genuinely external references.

Firm-specific, and worth asking about on day one rather than guessing:

• Whether external workbook links are red or purple, and whether a live data feed gets a colour of its own
• Whether input cells also get a fill. Light yellow input blocks are common in corporate finance and planning teams, and rare in banking models, where fill is kept for headers
• Whether the convention applies to the number only, or to the whole row including its label
• Whether bold and italic carry meaning too, for example italic for a placeholder that still needs a real number
• Whether blue means "input" at all. In some planning and analysis teams blue means forecast and black means actuals, which is a completely different axis, and it produces confident misreadings when the two groups swap files

That last one is the argument for a legend. One small block on the first tab, listing each colour and what it means in this file, costs two minutes and settles every version of the question above.

What should a non-finance spreadsheet borrow from this?

You do not need four colours for a project tracker. You need one idea out of all this, and it is the useful half.

Colour the cells someone is allowed to type in, and leave everything else in plain black. One accent colour for editable cells, black for everything the sheet works out for itself.

It pays off anywhere a spreadsheet gets handed to another person:

• A quote or pricing sheet where the salesperson fills in quantity and discount, and the line totals calculate
• A budget where the plan column is typed and the variance column is derived. The column layout for that one is in how to format a budget spreadsheet people read
• A commission or bonus calculator where two inputs drive six outputs
• A timesheet where hours are typed and pay is computed

Add a one-line legend above the table, something like "type in the blue cells only", and you stop getting the file back with a formula overwritten by the number 4200.

Keep it to two colours. The reason a banking floor gets away with five is that everyone in the room learned the same five. Nobody else did.

Where does the convention break down?

The limits matter more than the rules do.

Colour on its own excludes some of your reviewers. WCAG success criterion 1.4.1, Use of Color, is a Level A requirement, and it reads: "Color is not used as the only visual means of conveying information, indicating an action, prompting a response, or distinguishing a visual element." A blue-versus-black distinction is precisely that failure, and blue against black is a hard pair for several types of colour vision deficiency. The fix is cheap. Pair the colour with a second cue: give input cells a light fill or a border as well as blue text, keep a legend tab, or say it in the row label. There is more on this in the accessible spreadsheets checklist, and on choosing colours that survive the pairing in the spreadsheet colour palette post.

Greyscale printing erases it. Blue and black print at close to the same density in mono. If the model goes into a printed pack, the convention stops working at the printer.

Paste-as-values destroys it silently. Copy a black formula range, paste it as values onto a report tab, and you now have hardcodes wearing formula colours. Nothing in Excel repaints them for you.

A hardcode inside a formula is invisible to it. =B12*1.03 looks like an ordinary black formula, and the 1.03 is an untracked assumption. That is why the ICAEW list carries principle 14, avoid using fixed values within formulas, as a separate rule. Colour cannot catch what it cannot see.

Conditional formatting overrides manual font colour. If a rule turns negative variances red, it will repaint cells whose colour was carrying meaning. Decide which of the two is in charge on a given sheet, and keep them apart.

And colour says nothing about whether a number is correct. A confidently blue cell holding a 40% growth assumption is still a 40% growth assumption. The convention makes a model auditable. It does not make it right.

Frequently asked questions

What does blue text mean in a financial model?

It means the value was typed in by a person rather than calculated. Blue cells are the model's assumptions, so they are the cells you are expected to change when you want a different scenario, and the cells a reviewer checks first when the output looks wrong.

Should hardcoded numbers ever appear inside a formula?

As a rule, no. A number buried inside a formula cannot be coloured, cannot be found by a reviewer scanning the sheet, and has to be hunted down one cell at a time when it changes. The ICAEW principles state it plainly as principle 14, avoid using fixed values within formulas. Put the number in its own blue input cell and reference it. The narrow exceptions are true constants that will never change, such as 12 for months in a year, and even those read better as a labelled input once a non-author has to maintain the file.

Do these conventions work in Google Sheets?

The colours do, because they are only font colours and Sheets sets those the same way. What does not carry over is the tooling. Google Sheets has no named cell style gallery equivalent to Excel's, so there is no one-click Input style to apply. The nearest built-in is Format, then Theme, which sets workbook-level fonts and colours and offers a Customize option, plus a saved row of custom colours in the text colour picker so your four shades are one click away. Most people end up using the paint format tool and a legend instead. If a file will move between Excel and Sheets, stick to plain font colours rather than relying on named styles surviving the round trip.

Is there a standard for negative numbers in models?

There is no single body-issued standard, but there are two live conventions, and mixing them inside one file is the actual mistake. Finance and accounting output leans toward parentheses, so a negative twelve hundred appears as (1,200), often in red, because a leading minus sign is easy to miss when you are scanning a column fast. Most other work uses the minus sign. Pick one per workbook, and set it with a number format rather than by typing brackets, so the values stay numeric and still sum. The four-section format code syntax that produces red parenthesised negatives is covered in the Excel number formatting guide.

Apply the formats, keep the judgement

Colour coding is a judgement call about your own model, and it should stay one. What does not need to be a judgement call is the mechanical layer underneath it: currency columns carrying a real number format, negatives showing as red parentheses instead of a stray minus sign, a totals row built from live SUM formulas, a frozen header, and columns wide enough to show their own contents.

That is the part SuperSheet does. Drop an .xlsx or .csv in, and it works out what each column holds and applies the matching format, including the accounting-style code that renders negatives in red parentheses. The Financial theme adds the presentation around them: a deep green header band, tight rules, soft banding and a green totals row. It does not colour your cells blue and black for you, because it has no way to know which of your numbers were typed and which were calculated. That call is yours, and the Cell Styles setup above turns it into a one-click job after the first time.

Nothing is uploaded. The file is processed in your browser, and you get the formatted .xlsx back.