Formatting for Clarity

Chapter 1 warned that a cell's displayed value and its stored value can differ once formatting is applied — this chapter is where that idea gets properly explained, and where formatting stops being purely cosmetic. Good formatting makes a spreadsheet's story legible at a glance: which numbers are money, which cells need attention, which rows are already resolved. All three tools in this chapter — number formats, conditional formatting, and cell styles — exist to make a worksheet communicate, not just calculate.

Number Formats: Changing Display, Never the Stored Value

A number format controls how a value is shown. It never changes what's actually stored in the cell, and it never affects how that value behaves in a formula — this is the exact mechanism behind Chapter 1's warning box. Common built-in formats, all available from the Home tab's Number group:

Notice Percentage doesn't multiply the stored value by 100 in the cell itself — 0.256 is still stored as 0.256; the format simply moves the decimal and appends a % sign for display. A formula referencing that cell still receives 0.256, not 25.6.

Custom Format Codes: 0 vs. #

Beyond the built-in presets, Excel accepts custom format codes (Format Cells → Custom) built from placeholder characters. The two most common, 0 and #, both represent a digit, but differ in one important way:

0 means "always show a digit here, even if it's a padding zero." # means "show a digit here only if there actually is one." Use 0 when consistent decimal places matter (money should always show two decimals); use # when trailing zeros would just be visual clutter.

Conditional Formatting: Rules That React to the Data

Conditional formatting applies formatting automatically, based on a rule, and keeps re-evaluating that rule every time the underlying data changes — unlike manually colouring a cell once, which never updates itself. From Home → Conditional Formatting, the built-in categories cover most everyday needs:

• Highlight Cell Rules — greater than, less than, between, equal to, text containing, duplicate values.
• Top/Bottom Rules — top 10 items, top 10%, above/below average.
• Data Bars — an in-cell bar whose length scales with the value, letting a whole column be scanned visually without reading a single number.
• Color Scales — a gradient (e.g. red → yellow → green) applied across a range based on each value's relative position.
• Icon Sets — small icons (arrows, traffic lights, flags) assigned per cell based on value thresholds.

Formula-Based Conditional Formatting

The built-in rules above only look at the cell being formatted, in isolation. A custom formula rule (New Rule → "Use a formula to determine which cells to format") can react to a completely different cell — including highlighting an entire row based on one column's value.

This is Chapter 3's reference-locking rules in a new context: the formula is written as if it applies to the top-left cell of the selected range (here, A2), and Excel silently re-applies it to every other cell in the selection using the exact same relative/absolute logic as a normal filled formula. Locking the column with $C2 (column locked, row left relative) is what makes the rule check column C specifically for every row, while still letting the row number adjust so each row checks its own value.

Cell Styles: Reusable, Named Formatting

A Cell Style (Home → Cell Styles) is a saved, named bundle of formatting — font, colour, borders, number format — that can be applied in one click and updated everywhere at once by editing the style itself, rather than by re-formatting every cell individually. Built-in styles include semantic ones like Good (green), Bad (red), Neutral (amber), and structural ones like Input, Output, and heading levels.

The advantage over manually formatting each cell shows up the moment a design decision changes: update the "Input" cell style's fill colour once, and every cell that was ever tagged with that style updates automatically — versus having to hunt down and re-colour every individual cell that used to look that way.

Hands-On Exercises