It's 4:47 PM on Friday. Your manager just asked for a consolidated report by 5. You have 12 spreadsheets open and no idea how to combine them. Column A is Jan sales, B is Feb, C is Mar—and you need all values in a single column, sorted, with month labels attached. You try dragging. You try Paste Special > Transpose. Nothing works.
The Problem
You’ve got data spread across columns—but your analysis tool, Power BI import, or colleague’s template expects one long list. Not three side-by-side columns. Not five rows per month. One column. With context.
Here’s what your raw data looks like in Sheet1, cells A1:C8:
| Jan Sales | Feb Sales | Mar Sales |
|---|---|---|
| $24,800 | $31,200 | $28,500 |
| $19,300 | $22,700 | $26,100 |
| $33,600 | $35,900 | $34,200 |
| $27,100 | $29,400 | $30,800 |
| $41,200 | $43,500 | $42,700 |
| $18,900 | $20,300 | $21,600 |
| $36,400 | $38,100 | $37,900 |
That’s 7 rows × 3 columns = 21 values. But you need 21 rows × 2 columns: one for value, one for month label. And it must be repeatable—not manual copy-paste.
The Solution
Do this. Exactly. No deviations.
- Select A1:C7 (not C8—skip the header row). Press Ctrl+C.
- Go to a blank sheet or blank area. Click cell E1. Press Alt+H+V+T (Home → Paste → Transpose). Now you’ll see 3 rows × 7 columns.
- Select that transposed block (E1:K3). Press Ctrl+C again.
- Click M1. Press Alt+H+V+S (Paste Special → Values only). Now paste as values.
- In column L, type
Janin L1,Febin L2,Marin L3. Select L1:L3. Drag the fill handle down to L21. Excel auto-fills repeating Jan/Feb/Mar. - Select M1:M21 and L1:L21. Press Ctrl+C. Go to N1. Press Alt+H+V+V (Paste Values). Done.
Wait—no. That’s not right. That’s the old way. Here’s the real solution.
Use Power Query. It’s built-in. It’s fast. It’s repeatable.
- Select A1:C8 (including headers). Press Ctrl+T to make it a table. Name it
SalesByMonth(Formulas tab → Define Name). - Go to Data tab → Get & Transform → From Table/Range. Check “My table has headers”. Click OK.
- In Power Query Editor, select all three columns (Jan Sales, Feb Sales, Mar Sales). Right-click → Unpivot Columns.
- Rename Attribute column to
Month. Rename Value column toSales. - Close & Load. Output lands in a new sheet: two clean columns, 21 rows, perfectly stacked.
Result in Sheet2, starting at A1:
| Month | Sales |
|---|---|
| Jan Sales | $24,800 |
| Jan Sales | $19,300 |
| Jan Sales | $33,600 |
| Jan Sales | $27,100 |
| Jan Sales | $41,200 |
| Jan Sales | $18,900 |
| Jan Sales | $36,400 |
| Feb Sales | $31,200 |
| Feb Sales | $22,700 |
| Feb Sales | $35,900 |
| Feb Sales | $29,400 |
| Feb Sales | $43,500 |
| Feb Sales | $20,300 |
| Feb Sales | $38,100 |
| Mar Sales | $28,500 |
| Mar Sales | $26,100 |
| Mar Sales | $34,200 |
| Mar Sales | $30,800 |
| Mar Sales | $42,700 |
| Mar Sales | $21,600 |
| Mar Sales | $37,900 |
Yes—21 rows. Yes—it keeps labels. Yes—it updates automatically if source changes.
Going Further
You don’t always want raw column names as labels. To fix that:
- In Power Query, before Unpivot: select Jan Sales column → Transform tab → Rename → type
Jan. Repeat for Feb →Feb, Mar →Mar. - After Unpivot, add a custom column:
=Date.MonthName(Date.FromText([Month]&"/1/2024")). Gives "January", "February". - Need to stack non-contiguous columns? Hold Ctrl while selecting A1, C1, E1, then use From Table/Range. Power Query will name them Column1, Column2, Column3—rename before unpivoting.
- Stacking text columns with headers? Add an Index column (Transform tab → Add Column → Index Column) before unpivoting. Lets you preserve original row order.
Surprising tip: If your source data has blank cells in the middle of a column, Power Query treats them as nulls—and stacks them too. Don’t delete blanks first. Let Power Query do it.
When NOT to Use This
Don’t use Power Query stacking if:
- You’re sharing the file with someone using Excel 2010 or earlier. Power Query isn’t available.
- Your data is live-connected (e.g., ODBC to SQL Server) and refreshes hourly—but you only need a one-time snapshot. Use Paste Special + Text to Columns instead.
- You have 200 columns and need to stack only columns 3, 7, 12, and 19. Power Query forces you to select or exclude all—or write M code. Better to use INDEX + SEQUENCE in Excel 365.
- You’re pasting into a legacy ERP system that requires strict row limits per upload—and stacking pushes you over. Check first.
Also: never stack columns that contain merged cells. Unmerge them first. Power Query fails silently on merged ranges and returns garbage.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Power Query Editor | Alt+A+P | Data tab → Get Data → Launch Editor |
| Unpivot Selected Columns | Alt+J+U+U | Home tab → Transform → Unpivot Columns |
| Rename Column | Alt+H+R | Right-click column header → Rename also works |
| Close & Load | Alt+F+C | Saves query and dumps result to worksheet |
| Convert to Table | Ctrl+T | Required before Power Query import |
