Formatting: Numbers, Conditionals and Presentation
Formatting changes appearance, not value
A formatted cell still holds the underlying number. Typing 0.256 and applying a percentage format shows 26% but the value stays 0.256 for calculations, whereas =ROUND(A1,2) genuinely changes the stored value. Candidates must keep this distinction clear because questions often ask which approach makes a total "match to the penny".
Custom number formats
A custom format can have up to four sections separated by semicolons: positive; negative; zero; text. Within a section, 0 forces a digit, # shows an optional digit, , inserts a thousands separator, and % multiplies the displayed value by 100. So #,##0.00;[Red]-#,##0.00;"nil" shows negatives in red with a minus sign and the word nil for zero.
Dates and times
Dates are stored as serial numbers (1 = 1 January 1900 in the standard system) and times as fractions of a day (0.5 = 12:00). If a date is left-aligned it is text, not a real date, and sorting or date arithmetic will fail. Convert with DATEVALUE or Text to Columns.
Conditional formatting
Conditional formatting applies formats dynamically from rules. Understand rule priority, 'Stop If True', relative versus absolute references in formulas (the reference is relative to the top-left cell of the applied range), and how to clear rules from a whole sheet. Rules do not carry to a new workbook unless copied.
Efficiency tools
Copy formatting with Format Painter (double-click to lock it on), build reusable cell styles, and use Paste Special > Formats or Values to avoid dragging unwanted content. Merged cells break sorting and filtering, so prefer 'Centre Across Selection'.
Rule of thumb
If a cell looks wrong, ask two questions in order: is it text or a number, and is the format or the value the problem?