It’s on the Formulas tab, in the Defined Names group — but only after you’ve selected a range with labels in the top row, left column, or both. But if you’re clicking around the Ribbon hoping for a persistent button labeled 'Create from Selection', you’ll never find it — because Excel hides it dynamically based on your selection.
The Myth
Most people believe 'Create from Selection' is a standalone command buried somewhere in Excel’s interface — maybe under Data > Tools, or perhaps tucked inside Name Manager. Some even search the Quick Access Toolbar customizations or right-click menus expecting to see it listed as 'Create Names from Selection'. They assume it’s like 'Sort' or 'Filter': always visible, always clickable. It’s not. And that assumption causes real workflow friction — especially when building dynamic dashboards or cleaning imported data.
The Reality
'Create from Selection' only appears *when Excel detects a valid label structure* — and only *if you’ve selected the entire data block first*. It’s not missing. It’s conditional. Below is a decision matrix showing exactly when the command activates — tested across Excel 365 (v2405), Excel 2021, and Excel for Mac (v16.85):
| Selection Criteria |
Formulas Tab Visible? |
'Create from Selection' Enabled? |
Keyboard Shortcut Works? |
| A1:D10 with headers in Row 1 only |
✓ |
✓ |
✓ (Alt+M, M) |
| B2:E12 with headers in Column B only |
✓ |
✓ |
✓ (Alt+M, M) |
| C3:F15 with headers in Row 3 AND Column C |
✓ |
✓ |
✓ (Alt+M, M) |
| A1:C5 — no header row or column |
✓ |
✗ |
✗ |
| Entire column A:A selected |
✓ |
✗ |
✗ |
| Single cell (e.g., D7) selected |
✓ |
✗ |
✗ |
| A1:E10 with blank row at A3 |
✓ |
✗ |
✗ |
Why the Myth Persists
Excel 2003 had a 'Create Names' dialog accessible from Insert > Name > Create — a persistent menu item. When Microsoft moved to the Ribbon in 2007, they didn’t add a static button. Instead, they made it context-sensitive — a design choice that confused thousands of users who’d memorized the old path. YouTube tutorials from 2012–2016 still show 'Insert > Name > Create' and never mention the selection dependency. Even Microsoft’s official support page (ID: 271587) says 'on the Formulas tab' — without clarifying it’s disabled until you meet structural criteria. That silence created the myth.
The Right Way
Here’s how to actually use it — step by step, with real data:
1. Select your full data block. For example, select A1:D8 containing:
- Row 1: "Region", "Q1 Sales", "Q2 Sales", "Growth %"
- Rows 2–8: "North", "$42,800", "$45,200", "5.6%" ... up to "Global", "$217,300", "$229,100", "5.4%"
2. Go to the
Formulas tab.
3. In the
Defined Names group, click
Create from Selection (it lights up now — grayed out before).
4. In the dialog, check
Top row and uncheck everything else.
5. Click OK.
What makes this elegant is that Excel instantly creates named ranges like
Q1_Sales,
Q2_Sales, and
Growth_% — each referencing the full column (B2:B8, C2:C8, D2:D8). No manual typing. No risk of misalignment.
Now try it with labels in the leftmost column instead. Select B1:E6 containing:
- Column B: "Acme Corp", "NexaTech", "Zylo Inc", "Veridian Ltd", "Stellar Labs"
- Columns C–E: "2024-03-15", "$14,200", "Active" etc.
Select B1:E6 → Formulas tab → Create from Selection → check
Left column. Excel creates names like
Acme_Corp pointing to C1:E1 — perfect for row-based lookups.
Surprising tip: You can combine both. Select C2:F10 where C2:C10 contains company names and C1:F1 contains metrics. Excel will build names using *both* — creating
Acme_Corp_Q1_Revenue if you check both Top row and Left column. Just don’t have blanks or duplicates in either label zone.
Proof It Works
Before naming, referencing Q2 Sales for NexaTech meant typing
=INDEX(C2:F7,MATCH("NexaTech",B2:B7,0),2). After Create from Selection, it’s just
=NexaTech_Q2_Sales — cleaner, faster, less error-prone.
| Task |
Before Named Ranges |
After Create from Selection |
Time Saved (per formula) |
| Pull Q1 Sales for Zylo Inc |
=VLOOKUP("Zylo Inc",B2:F7,2,FALSE) |
=Zylo_Inc_Q1_Sales |
~12 seconds |
| Calculate average growth % |
=AVERAGE(D2:D7) |
=AVERAGE(Growth_%)) |
~8 seconds |
| Compare Veridian Ltd vs Stellar Labs Q2 |
=INDEX(C2:F7,MATCH("Veridian Ltd",B2:B7,0),3)-INDEX(C2:F7,MATCH("Stellar Labs",B2:B7,0),3) |
=Veridian_Ltd_Q2_Sales-Stellar_Labs_Q2_Sales |
~22 seconds |
| Add new row (Stellar Labs) |
Manual range update required in all formulas |
Named ranges auto-expand if table-based — or adjust with dynamic arrays |
~45 seconds saved per sheet |
Exceptions
There *are* cases where the myth holds true — meaning the command really *is* missing:
• You’re using Excel Online (web version). 'Create from Selection' is unsupported there — no Formulas tab option, no Alt+M, M shortcut.
• Your workbook is in Compatibility Mode (e.g., .xls opened in Excel 365). The command appears but fails silently with #REF! errors in names.
• You’ve enabled 'Ribbon Customization' and accidentally removed the Defined Names group. To restore: right-click any tab → Customize the Ribbon → check 'Formulas' → ensure 'Defined Names' is ticked.
One last thing: Alt+M, M is the universal shortcut — but only works *after* selecting valid data. Try it now on A1:D8 above. Watch the dialog appear instantly. That’s the sound of Excel finally doing what it promised.
Next step: Open a worksheet with headers. Select your full data block (not just headers). Press
Alt →
M →
M. If nothing happens, check for blank rows or inconsistent label placement — then reselect. That’s all it takes.
