It’s 3:12 PM. You just got an email from Finance: 'Please expand the Q2 sales list so each product appears once per region—even if it wasn’t sold there.' You stare at your 7-row table in Sheet1. There are 4 regions. That’s 28 rows. You highlight, copy, paste… then notice Region names are misaligned in column C. You undo. Try again. Paste special fails. Your coffee’s cold.
The Setup
You’re working with Product Sales by Region (Q2 2024), a small but messy source table in A1:C8. It only shows actual sales—not all combinations. But leadership wants every product listed for every region, even with zero sales. That means repeating each product line across 4 regions. No guessing. No manual drag-and-drop.
| Product | Region | Revenue |
|---|---|---|
| AlphaLink Pro | North America | $12,450 |
| AlphaLink Pro | EMEA | $8,920 |
| NovaShield S3 | Asia Pacific | $15,600 |
| NovaShield S3 | North America | $6,710 |
| CloudVault Mini | EMEA | $3,280 |
| CloudVault Mini | Latin America | $4,150 |
| TerraCore X7 | North America | $18,340 |
| TerraCore X7 | Asia Pacific | $9,820 |
This is your A1:C8 range. Product names repeat—but only where data exists. You need every product repeated for all four regions: North America, EMEA, Asia Pacific, Latin America.
The Challenge
Repeating lines in Excel isn’t about copying and pasting—it’s about systematic expansion. The trap? Assuming FILL DOWN or dragging will work. It won’t. Dragging repeats values, yes—but only vertically, and only within existing structure. If you have 7 rows and need 28, dragging won’t auto-generate missing region combos.
Another snag: COPY → PASTE SPECIAL → FILL SERIES doesn’t help here. That works for numbers or dates—not for cross-tab expansions. And don’t even think about nested IF statements trying to loop through regions. That’s unmaintainable and breaks on row 11.
What makes this tricky is the mismatch between input shape (sparse) and output shape (dense grid). You’re not repeating one line—you’re repeating *each* product line *across* a fixed list of regions. That’s a Cartesian product. Excel doesn’t do that natively unless you force it—either with formulas or Power Query.
Walking Through It
We’ll use two reliable methods. First, the formula method—no add-ins, no refresh needed. Second, the Power Query method—best for repeatable, scalable work. We’ll start with formulas because you likely already have the data open—and we’ll get results before your next Teams notification.
Method 1: Formula-Based Repetition (No Power Query)
Step 1: List your 4 regions in a separate column—say, F1:F4:
- F1: North America
- F2: EMEA
- F3: Asia Pacific
- F4: Latin America
Step 2: In H1, enter this array formula (press Ctrl+Shift+Enter if using Excel 2019 or earlier):
=INDEX($A$2:$A$8,INT((ROW(A1)-1)/ROWS($F$1:$F$4))+1)
This grabs each product and repeats it 4 times—once per region. Why INT((ROW(A1)-1)/4)+1? Because it maps rows 1–4 → product #1, rows 5–8 → product #2, etc. It’s arithmetic, not magic.
Step 3: In I1, enter:
=INDEX($F$1:$F$4,MOD(ROW(A1)-1,ROWS($F$1:$F$4))+1)
This cycles through regions cleanly—no lookup tables, no VLOOKUP. Just math. Copy both formulas down to row 28.
Before (first 7 rows of original):
| Product | Region | Revenue |
|---|---|---|
| AlphaLink Pro | North America | $12,450 |
| AlphaLink Pro | EMEA | $8,920 |
| NovaShield S3 | Asia Pacific | $15,600 |
After (first 12 rows of formula output):
| Product | Region | Revenue |
|---|---|---|
| AlphaLink Pro | North America | blank |
| AlphaLink Pro | EMEA | blank |
| AlphaLink Pro | Asia Pacific | blank |
| AlphaLink Pro | Latin America | blank |
| NovaShield S3 | North America | blank |
| NovaShield S3 | EMEA | blank |
(We’ll add Revenue later—via XLOOKUP or INDEX/MATCH.)
Surprising tip: You don’t need to know how many products you have upfront. Replace $A$2:$A$8 with $A$2:INDEX($A:$A,COUNTA($A:$A)) to auto-detect last row. Yes—it works inside INDEX.
Method 2: Power Query (One-Click Refresh)
Go to Data → Get Data → From Table/Range. Make sure ‘My table has headers’ is checked. Click OK.
In Power Query Editor, select the Product column. Hold Ctrl, click Region. Right-click → Remove Duplicates. Now you have unique Product + Region pairs—but still sparse.
Here’s the pivot: Go to Home → Advanced Editor. Replace the code with:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Regions = {"North America","EMEA","Asia Pacific","Latin America"},
Products = List.Distinct(Source[Product]),
CrossJoin = Table.FromRecords(
List.TransformMany(
Products,
each Regions,
(p,r) => [Product=p, Region=r]
)
)
in
CrossJoin
Click Done. Then Close & Load. You now have a clean 28-row table—fully dynamic. Add Revenue later with Merge.
The Result
Here’s your final expanded table—28 rows, all products × all regions. Revenue remains blank for missing combos (you can fill with 0 or leave as-is). This is what Finance actually needs—not raw input, but complete coverage.
| Product | Region | Revenue |
|---|---|---|
| AlphaLink Pro | North America | $12,450 |
| AlphaLink Pro | EMEA | $8,920 |
| AlphaLink Pro | Asia Pacific | — |
| AlphaLink Pro | Latin America | — |
| NovaShield S3 | North America | — |
| NovaShield S3 | EMEA | — |
| NovaShield S3 | Asia Pacific | $15,600 |
| NovaShield S3 | Latin America | — |
| CloudVault Mini | North America | — |
| CloudVault Mini | EMEA | $3,280 |
| CloudVault Mini | Asia Pacific | — |
| CloudVault Mini | Latin America | $4,150 |
What Could Go Wrong
Mistake #1: Using FILL DOWN on mixed data types. If column A has text and column C has numbers, dragging fills the number pattern—not the text. You’ll get 1, 2, 3 instead of Product A, Product A, Product A. Always check cell format before dragging.
Mistake #2: Forgetting absolute references in formulas. If you write =INDEX(A2:A8,INT((ROW(A1)-1)/4)+1) without $ signs, copying right breaks it. Use $A$2:$A$8—or better yet, name the range (Formulas → Define Name → ProdList) and use ProdList in formulas. Trust me, I learned this the hard way during a board demo.
Mistake #3: Running Power Query on unstructured data. If your source table has blank rows, merged cells, or header repeats, Power Query imports garbage. Always convert to a proper Excel Table (Ctrl+T) first—even if it feels like overkill.
Next step: Pick one method and test it on your own data *right now*. Don’t wait for Monday. Here’s your quick-reference cheat sheet:
| Task | Shortcut / Formula | Notes |
|---|---|---|
| Repeat product list 4x | =INDEX($A$2:$A$8,INT((ROW(A1)-1)/4)+1) |
Paste down 28 rows |
| Cycle through 4 regions | =INDEX($F$1:$F$4,MOD(ROW(A1)-1,4)+1) |
Assumes regions in F1:F4 |
| Convert to Table | Ctrl+T | Required before Power Query |
| Array formula entry | Ctrl+Shift+Enter | Only needed in Excel 2019 or earlier |
