How to stack Excel data in a single column
Let's assume that you have the following table in Excel:
| Erik | |||
|---|---|---|---|
| Jeff | |||
| Elon | Elon | ||
| Jeff | Bill |
If you want to stack all items in one column:
- Data > Get data from Table/Range
- Select the whole table
- The Power Query Editor opens
- Press
ctrl + Ato select all the columns - Transform > Unpivot columns
You'll have the following result:
| Atributte | Value |
|---|---|
| Columna1 | Erik |
| Columna3 | Jeff |
| Columna1 | Elon |
| Columna2 | Elon |
| Columna2 | Jeff |
| Columna4 | Bill |
- Home > Right-click on the first column > Remove
- Right-click on the remaining column > Remove duplicates
- Close and load
Result:
| Value |
|---|
| Erik |
| Jeff |
| Elon |
| Bill |
Hi, I'm Erik, an engineer from Barcelona. If you like the post or have any comments, say hi.