I need to convert the Excel matrix `FIRST`

in the table `LATER`

:

**FIRST**:

```
P1 P2 P3 P4
F1 X
F2 X X
F3 X X
F4 X X
```

**LATER**:

```
F P VALUE
F1 P1 X
F1 P2
F1 P3
F1 P4
F2 P1 X
F2 P2 X
F2 P3
F2 P4
F3 P1
F3 P2 X
F3 P3
F3 P4 X
F4 P1
F4 P2 X
F4 P3 X
F4 P4
```

## Solution 1

## To reverse pivot, unpivot or flatten:

For Excel 2003: Activate any cell in your summary table and choose Data - PivotTable and PivotChart Report:

For later versions access the Wizard with `Alt`+`D`, `P`.

For Excel for Mac 2011, it's `+``Alt`+`P` (See here).

Select

*Multiple consolidation ranges*and click`Next`.In Step 2a of 3, choose

*I will create the page fields*and click`Next`.In Step 2b of 3 specify your summary table range in the

**Range**field (A1:E5 for the sample data) and click`Add`, then`Next`.In Step 3 of 3, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):

Click

`Finish`to create the pivot table:Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or

`7`

):The PT may now be deleted.

- The resulting Table may be converted to a conventional array of cells by selecting
**Table**in the Quick Menu (right-click in the Table) and**Convert to Range**.

There is a video on the same subject at Launch Excel which I consider excellent quality.

## Solution 2

Another way to unpivot data without using VBA is with PowerQuery, a free add-in for Excel 2010 and higher, available here: http://www.microsoft.com/en-us/download/details.aspx?id=39379

Install and activate the Power Query add-in. Then follow these steps:

Add a column label to your data source and turn it into an Excel Table via Insert > Table or `Ctrl` - `T`.

Select any cell in the table and on the Power Query ribbon click "From Table".

This will open the table in the Power Query Editor window.

Click the column header of the first column to select it. Then, on the **Transform** ribbon, click the **Unpivot Columns** drop-down and select **Unpivot other columns**.

For versions of Power Query that don't have the **Unpivot other columns** command, select all columns except the first one (using Shift-click on the column headers) and use the **Unpivot** command.

The result is a flat table. Click **Close and Load** on the Home ribbon and the data will be loaded onto a new Excel sheet.

Now to the good part. Add some data to your source table, for example

Click on the sheet with the Power Query result table and on the Data ribbon click **Refresh all**. You will see something like:

Power Query is not just a one-time transformation. It is repeatable and can be linked to dynamically changing data.

## Solution 4

The addition of the LET function & dynamic arrays allows for this non-VBA solution.

```
=LET(data,B2:E5,
dataRows,ROWS(data),
dataCols,COLUMNS(data),
rowHeaders,OFFSET(data,0,-1,dataRows,1),
colHeaders,OFFSET(data,-1,0,1,dataCols),
dataIndex,SEQUENCE(dataRows*dataCols),
rowIndex,MOD(dataIndex-1,dataRows)+1,
colIndex,INT((dataIndex-1)/dataRows)+1,
dataColumn, IF(INDEX(data,rowIndex,colIndex)="","",INDEX(data,rowIndex,colIndex)),
unfiltered, CHOOSE({1,2,3},INDEX(rowHeaders,rowIndex),INDEX(colHeaders,colIndex), dataColumn),
filtered, FILTER(unfiltered, dataColumn<>""),
unfiltered)
```

This will show all items including those with blank data. To eliminate the blanks change the last parameter to filtered.

## Solution 5

One more to add to the BoK. This requires Excel 365. It unpivots B1:E5 by A1:A5.

```
=LET( unPivMatrix, B1:E5,
byMatrix, A1:A5,
upC, COLUMNS( unPivMatrix ),
byC, COLUMNS( byMatrix ),
dmxR, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) - 1,
dmxSeq, SEQUENCE( dmxR ) + 1,
upCells, dmxR * upC,
upSeq, SEQUENCE( upCells,, 0 ),
upHdr, INDEX( INDEX( unPivMatrix, 1, ), 1, SEQUENCE( upC ) ),
upBody, INDEX( unPivMatrix, dmxSeq, SEQUENCE( 1, upC ) ),
byBody, INDEX( byMatrix, dmxSeq, SEQUENCE( 1, byC ) ),
attr, INDEX( upHdr, MOD( upSeq, upC ) + 1 ),
mux, INDEX( upBody, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ),
demux, IFERROR( INDEX(
IFERROR( INDEX( byBody,
IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ),
SEQUENCE( 1, byC + 1 ) ),
attr ),
upSeq + 1, SEQUENCE( 1, byC + 2 ) ),
mux ),
FILTER(demux, mux<>"")
)
```

NB: the byMatrix can be a range with multiple columns and it will replicate the row values of the columns. e.g. you could have byMatrix of A1:C5 and unPivMatrix of D1:H5 and it would replicate the A2:C5 column values (ignoring A1).