Uploaded January 2015 | Updated September 2026, 1 hour ago

---

Get the Excel file here: http://1drv.ms/1bYwrTa

Imagine 4 columns of data stacked together in 1 column. How do you rearrange it so that you can pivot the data?

Ken Puls at excelguru.ca used Power Query. You could also use VBA (macros).

In this video I solve it using formulas:
0:22 Define the pattern
1:39 Helper formula (points at start of each group)
3:20 Add column headers and horizontal counters
4:10 Add OFFSET formula to pull out the data
5:40 I also create a formula that will tell you if you have the correct number of formulas! Why have formulas doing nothing? Or what if you are missing data and should drag down formulas further?

twitter.com/KevinLehrbass
myspreadsheetlab.com