Who this is for
- Aspiring and junior Data Analysts who work in spreadsheets daily.
- Anyone preparing dashboards or QA checks on CSV/Excel data.
- Analysts who want fast visual cues for outliers, trends, and data quality issues.
Prerequisites
- Comfort entering data, sorting/filtering, and basic formulas (SUM, AVERAGE, TODAY).
- Understanding of absolute vs. relative references ($A$1 vs A1).
- Basic dataset concepts: rows = records, columns = fields.
Why this matters
Conditional formatting turns raw numbers into instant signals. As a Data Analyst you will:
- Spot anomalies (e.g., negative margins, missing values) at a glance.
- Prioritize work (late shipments, high-risk accounts) without extra reports.
- Create presentation-ready sheets where insights pop automatically.
- Build robust QA checks that flag data issues on import.
Real tasks where you will use it
- Highlight revenue below target by month to guide stakeholder discussion.
- Color overdue invoices to speed up finance follow-ups.
- Mark out-of-range sensor readings for an operations team.
- Heatmap churn risk scores to focus retention efforts.
Concept explained simply
Conditional formatting applies visual styles (colors, icons, data bars) when cells meet rules. You choose a range and define what "counts" as important. The spreadsheet checks each cell or row and paints it accordingly.
Mental model
- Think "IF this is true, THEN paint it".
- Preset rules = fast templates (greater than, top 10%, duplicates, color scales).
- Custom formula rules = full control (AND/OR across columns, dates, text checks).
- Rule order matters. If two rules hit the same cell, the order plus "Stop if true" decides which format you see.
Worked examples
Example 1: Heatmap sales performance with a 3‑color scale
- Select the numeric range (e.g., C2:C101 for Sales).
- Apply a 3‑color scale so low values are red, mid are yellow, high are green.
- Optionally set min/mid/max to Percentile 10/50/90 for resilient scaling.
Result: A quick heatmap that reveals weak and top‑performing items.
Example 2: Flag overdue and unpaid invoices (custom formula)
- Range: select all invoice rows (e.g., A2:F500).
- Create a custom formula rule like:
=AND($D2<TODAY(),$E2="No")where D = Due Date, E = Paid?. - Set fill to light red, bold text.
Result: Any row where the due date is before today and Paid? equals No is highlighted.
Example 3: Highlight rows where profit margin < 5% (cross‑column rule)
- Assume Revenue in B, Cost in C. Select rows A2:D200.
- Add a custom formula:
=IFERROR(($B2-$C2)/$B2,0)<0.05 - Format with amber fill and dark text.
Result: Low‑margin rows surface immediately.
Example 4: Top 10% performers with icons
- Select a metric column (e.g., Score in F2:F200).
- Apply an icon set and adjust thresholds so the top 10% show a green up icon.
- Reduce icon size or hide values if you need a compact view.
Result: Fast spotlight on the top performers.
How to do it step by step
- Select the data range carefully (include the rows/columns you want to evaluate and color).
- Choose rule type: preset (greater than, top/bottom, duplicates, color scale, data bars, icons) or custom formula.
- Set the condition. For formulas, reference the first row (e.g., $D2) and use $ to lock columns/rows appropriately.
- Pick a clear format: minimal colors, readable contrast.
- Order rules and use "Stop if true" if needed to avoid conflicting styles.
- Test on a few values to confirm the rule behaves as expected.
- Checklist before applying to the whole sheet
- Right range selected?
- Formula references anchored correctly with $?
- Numeric columns truly numeric (not text)?
- Rule order reviewed and simplified?
Exercises (do these in your spreadsheet)
These mirror the graded exercises below. Try first, then compare with the solutions.
Exercise 1: Overdue and Unpaid
Columns: A=Invoice ID, B=Client, C=Amount, D=Due Date, E=Paid? (Yes/No). Select A2:E200 and highlight rows where Due Date < today AND Paid? = "No" in red fill.
Exercise 2: Sales Heatmap + Top Signals
Columns: A=Product, B=Category, C=Sales. Apply a 3‑color scale to C2:C500. Then add an icon set for the top 10% in C to show a green icon. Ensure the icons do not overwrite the color scale for the same cells by ordering rules sensibly.
Common mistakes and self‑check
- Wrong apply range: Formatting only first 20 rows by accident. Self‑check: open rule manager and confirm the full range (e.g., A2:E200).
- Broken formula references: Using D$2 or $D$2 incorrectly. Self‑check: In a custom rule for rows, lock the column (e.g., $D2) so it moves down rows but stays in column D.
- Text numbers: Values look numeric but are stored as text, so rules fail. Self‑check: Align right and convert numbers; remove stray spaces; use VALUE() if needed.
- Conflicting rules: Multiple formats on the same cell. Self‑check: Reorder rules and use "Stop if true" for the highest‑priority rule.
- Too many colors: Heatmaps that confuse stakeholders. Self‑check: Limit to one strong signal per view; pick accessible colors.
Learning path
- Master presets: greater than, top/bottom, duplicates, color scales.
- Row‑level logic with custom formulas (AND/OR, dates, text checks).
- Rule management: order, stop if true, apply range, copy/paste rules.
- Edge cases: blanks, errors, mixed data types.
- Performance: use focused ranges (A2:A5000) and sensible thresholds.
Practical projects
- Quarterly KPI Sheet: Heatmap KPIs by red/yellow/green with thresholds tied to targets.
- Collections Tracker: Red rows for overdue & unpaid; amber for due in 7 days.
- Data Quality Monitor: Highlight blanks, duplicates, and out‑of‑range values across imported tables.
Mini challenge
Create a rule that highlights any row where:
- Country = "US"
- Signup Date is within the last 30 days
- Spend >= 500
Hint formula pattern: =AND($B2="US",$C2>=TODAY()-30,$D2>=500) applied to the full table.
Show a crisp solution approach
- Select the entire table (header excluded).
- Custom formula referencing the first data row only, with $ on columns.
- Readable fill + bold to make rows stand out.
- Test with a few sample rows and adjust as needed.
Next steps
- Refine custom formulas for multi‑column logic.
- Combine conditional formatting with filters and pivot tables for faster analysis.
- Document your rules so teammates know what each color means.
Quick Test
The quick test is available to everyone. If you log in, your progress and score will be saved; otherwise, you can still take it for practice.