Yes, you can create a dummy variable in Excel with a single formula. But if you assign 1s and 0s manually—or worse, copy-paste them across rows—you’ll introduce silent errors that wreck your regression output.
Quick Answer
Use =--(A2="Yes") or =IF(A2="Approved",1,0) in an adjacent column, then copy down. That’s it — no add-ins, no VBA, no Power Query needed. Just make sure your category labels are spelled *exactly* the same in every row (case doesn’t matter for =--(), but extra spaces do).
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
Double Unary (=--(A2="Value")) |
Type formula in B2, press Enter, drag down | Binary categories (Yes/No, Male/Female) | Fails silently if source cell is blank or has trailing spaces |
| IF with exact match | Enter =IF(A2="Acme Corp",1,0), drag down |
Named entities (companies, regions, product lines) | Hard to scale beyond 2–3 categories without nesting |
| XLOOKUP + predefined table | Build lookup table (E2:F5), use =XLOOKUP(A2,$E$2:$E$5,$F$2:$F$5,0) |
Multi-category coding (e.g., Region → 1=APAC, 2=EMEA, 3=Americas) | Requires Excel 365 or 2021; fails if lookup value isn’t in table |
| Paste Special → Multiply | Paste 1s/0s as values, select range, type 1, copy, paste special → Multiply |
Converting pre-made text labels ("Y","N") to numbers fast | Destructive — overwrites original values unless you duplicate first |
Method 1 Deep Dive
Let’s say your data lives in A2:A11 and looks like this:
| A | B |
|---|---|
| Status | Dummy (Approved?) |
| Approved | |
| Pending | |
| Rejected | |
| Approved | |
| Pending | |
| Approved | |
| Rejected | |
| Approved | |
| Pending |
In B2, type =--(A2="Approved"). Press Enter. You’ll see 1. Drag that cell down to B11. Done. The double unary (--) converts TRUE/FALSE into 1/0 — faster than IF, less verbose than XLOOKUP.
But here’s what most people miss: if A4 contains "Approved " (with a trailing space), the formula returns 0. Not an error — just wrong. So before you build dummies, clean your data. Select A2:A11, press Alt+H+F+W (Home → Find & Select → Replace), type a space in "Find what", leave "Replace with" blank, click Replace All. Then re-run the formula.
Method 2 Deep Dive
Now imagine you’re coding job titles for a salary analysis: Analyst, Manager, Director, VP. You want numeric codes (1–4) — not just binary. That’s where XLOOKUP shines.
First, build your code table in E1:F5:
| E | F |
|---|---|
| Job Title | Code |
| Analyst | 1 |
| Manager | 2 |
| Director | 3 |
| VP | 4 |
In G2 (next to your first job title in D2), enter:=XLOOKUP(D2,$E$2:$E$5,$F$2:$F$5,0)
The final 0 means “return 0 if not found” — safer than #N/A. Drag down. If someone typed "Vice President" instead of "VP", you’ll see 0, and know to fix the source or expand the lookup table.
Pro tip: Name your ranges. Select E2:E5, press Ctrl+Shift+F3, check Top row, click OK. Now you can write =XLOOKUP(D2,JobTitle,Code,0). Much easier to audit — and way less fragile when you insert columns later.
Cheat Sheet
| Task | Formula / Shortcut | Notes |
|---|---|---|
| Binary dummy (exact match) | =--(A2="Yes") |
Works on any version. Case-insensitive. |
| Clean leading/trailing spaces | Alt+H+F+W → Find: space, Replace: blank |
Do this *before* building dummies. |
| Multi-category dummy | =XLOOKUP(A2,CategoryList,CodeList,0) |
Requires Excel 365 or 2021. |
| Convert Y/N text to 1/0 | =IF(A2="Y",1,IF(A2="N",0,"")) |
Handles blanks explicitly — avoids accidental 0s. |
| Verify dummy column sum | =SUM(B2:B100) |
Should equal count of matching rows — spot-check with =COUNTIF(A2:A100,"Approved"). |
