📌 Stage 4 · Follow-Up Discipline · Sales Pipeline CRM · Overdue Alerts · 35–45 min

A nested IF plus conditional formatting - no macros

Learning goals

  • Write a three-branch nested IF that classifies every activity
  • Add text-match conditional formatting so overdue rows turn red without macros
  • Explain the fixed aging date and why TODAY() would make the lesson uncheckable

Concepts

The status formula

Each activity earns one of four labels. Done: the flag reads Y. No date: no due date was promised. Overdue: not done AND the due date fell before the aging date. Scheduled: not done, due date still in the future. Nested IF reads inside-out and stops at the first branch that matches - order matters, which is why the Done test comes first.

=IF($I12="Y","Done",IF($H12="","No date",IF($H12<Settings!$C$6,"Overdue","Scheduled")))

ACT-508: Done flag is N, due date Jun 5, 2026 is earlier than the aging date Jun 30, 2026 in Settings!C6 - so the third test fires and the cell reads Overdue.

💡 Tips:

  • Excel nests IFs up to 64 deep; humans stay sane at about three. When you need a fourth branch, that is usually a sign the data wants another column.

Color without code

Formulas classify; conditional formatting shouts. In the downloadable workbook, two rules target column J: cells equal to "Overdue" get red text on a rose fill, cells equal to "Done" get green. The rules are text matches on values the formula already produced - live, automatic, and completely macro-free. Home > Conditional Formatting > Highlight Cell Rules > Text that Contains is the two-minute version.

This pairing - a formula computes a label, conditional formatting colors the label - is the single most reusable no-macro pattern in this course. It reappears on Invoices (Paid / Overdue) and could wrap any status column you ever build.

💡 Tips:

  • Keep highlight rules simple (equal-to-text). Color scales and icon sets look impressive and communicate nothing on status columns.

The aging date, again

Every comparison uses Settings!C6 (Jun 30, 2026), never TODAY(). With TODAY() this lesson's answer would change daily - today ACT-510 (due Jul 10) would read Scheduled, next month Overdue - and no check figure could ever be verified. The fixed date makes the two overdue rows a stable fact: ACT-508, a site visit at Foxglove promised for Jun 5, and ACT-509, the patio cold-brew demo due Jun 18. Both belong to open deals. That is the pattern that kills pipelines: the visit happens, the follow-up never gets booked, and a quarter later the deal has quietly gone cold.

💡 Tips:

  • In your own copy, set Settings!C6 to your month-end close date and the whole workbook ages on your reporting rhythm - still without TODAY().

Practice

Fixed case (Jan-Jun 2026, aging date Jun 30, 2026). Pale-green cells are formulas; white cells are typed entries. This lesson opens on Activities.

  1. Type the status formula — J12 is blank. Click it and type =IF($I12="Y","Done",IF($H12="","No date",IF($H12<Settings!$C$6,"Overdue","Scheduled"))) then Enter. It returns Overdue.
  2. Find the second overdue — Scan column J: J13 (ACT-509, patio demo due Jun 18, 2026) is the other Overdue. Exactly two - the Dashboard will count them in Lesson 20.
  3. Check a future promise — J14 (ACT-510, due Jul 10, 2026) reads Scheduled - after the aging date, so still honest. And J18 (ACT-514, due Jul 6) also reads Scheduled.
  4. See the color version — Open the downloadable workbook (last lesson's card) and look at Activities column J in desktop Excel: the two Overdue cells are red, Done cells green - conditional formatting, zero macros.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ J12 returned Overdue via the three-branch nested IF
  • ☐ Located both overdue activities: ACT-508 (due Jun 5, 2026) and ACT-509 (due Jun 18, 2026)
  • ☐ Can explain the formula-plus-conditional-formatting pattern and why the aging date is fixed