Column Harbor
Module 02: Make formulas work for you
LESSON 08 / Beginner · ABOUT 20 MIN

Turn clear rules into IF, AND, and OR formulas

Label orders with explicit conditions, including boundaries and missing inputs.

Before you start

Windows desktop: Excel 2016, 2019, 2021, 2024, and Microsoft 365. Mac/web menus and shortcuts differ. No dynamic arrays required.

Useful first: Build your first formulas from cell references · Copy formulas while keeping shared inputs fixed

By the end, you can…
  • Return an understandable label with IF.
  • Distinguish conditions that must all hold from conditions where either is enough.
  • Test the exact threshold and a missing-input case.
YOUR PRACTICE FILE

Small data. A result you can check.

Data only. Paste at A1 on a blank sheet. Add the sixth, missing-amount example manually after testing the complete records.

Download practice data
View the raw practice data
Order	Amount	Paid
O-101	120	Yes
O-102	80	Yes
O-103	140	No
O-104	100	Yes
O-105	60	No

01Write the decision rule before the formula

Create a Decisions sheet and paste at A1. The invented rule is Ready when Amount is at least 100 and Paid is Yes; other complete records are Review.

Read row 2 first: O-101 has amount 120 and Paid Yes, so it should be Ready. Row 3 has paid but is below the amount threshold. Row 4 meets the amount threshold but has not paid. Neither should be Ready.

OrderAmountPaid
O-101120Yes
O-10280Yes
O-103140No
O-104100Yes
O-10560No

02IF returns a label; AND combines requirements

Enter Ready check in D1 and the formula below in D2; fill through D6. AND requires both comparisons. IF chooses a text label from the result. Use straight double quotation marks.

The expected D2:D6 labels are Ready, Review, Review, Ready, Review. The >= comparison deliberately includes exactly 100, so O-104 passes. Changing it to > would encode a different rule and would make that boundary record fail.

=IF(AND(B2>=100,C2="Yes"),"Ready","Review")

Fill D2:D6 using numeric amounts. English functions and commas are shown; some locales need semicolons.

03OR asks whether either condition is enough

Add Priority check in E1. Enter =IF(OR(B2>=100,C2="Yes"),"Priority","Standard") in E2 and fill through E6. This separate rule accepts either a qualifying amount or Paid Yes.

E2:E6 read Priority, Priority, Priority, Priority, Standard. O-102 passes on payment; O-103 passes on amount. O-105 fails both. Replacing AND with OR changes which records qualify.

04Make missing inputs visible instead of inventing a decision

Add O-106 in A7, leave B7 empty, and enter Yes in C7. A missing amount is a different situation from a confirmed amount below 100. For this row, use =IF(B7="","Missing amount",IF(AND(B7>=100,C7="Yes"),"Ready","Review")) in D7.

The outer IF detects missing amounts, not every invalid input. Arbitrary text, errors, unexpected status spellings, and trailing spaces still need review. Assume numeric amounts and the choices Yes/No; a label does not prove source accuracy.

PUT IT INTO PRACTICE

Implement both rules and verify that boundaries and missing inputs have intended outcomes.

  1. Paste the five-row sample and fill the AND-based IF formula in D2:D6.
  2. Fill the separate OR-based IF formula in E2:E6.
  3. Confirm that the amount exactly 100 qualifies under >=.
  4. Add the specified sixth record and the blank-aware formula in D7.
Show the worked answer

AND labels: Ready, Review, Review, Ready, Review.

OR labels: Priority, Priority, Priority, Priority, Standard.

O-104 is Ready because 100 meets the inclusive threshold and Paid is Yes.

D7 is Missing amount; no amount was assumed for O-106.

Check your work
  • The two rules remain in separate result columns.
  • The boundary value 100 is tested explicitly.
  • The missing-input label is distinct from a failed complete record.
CHECK YOUR UNDERSTANDING

One question before you move on.

Under the Ready rule, an order has amount 100 and Paid Yes. What should it return?

Ready for the next step?

Mark this lesson when you can explain the idea and reproduce the practice result.

Your checklist stays on this browser.
NEXT LESSONTurn an order range into a growing Excel table

Keep the skill close.

FIELD GUIDECompare Two Lists in Excel: Find Missing IDs and Duplicate CountsFIELD GUIDEHighlight Duplicate Rows in Excel When Both Columns Must MatchWORKSPACE TOOLCSV import checker

Reference notes

These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.

Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.