Convert matrix to 3-column table ('reverse pivot', 'unpivot', 'flatten', 'normalize')

Question

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

Busily

A simple google would have fetched you your answer – Paleobotany

Answer 1

To “reverse pivot”, “unpivot” or “flatten”:

1. 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).

2. Select Multiple consolidation ranges and click Next.

3. In “Step 2a of 3”, choose I will create the page fields and click Next.

4. 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.

5. In “Step 3 of 3”, select a location for the pivot table (the existing sheet should serve, as the PT is only required temporarily):

6. Click Finish to create the pivot table:

7. Drill down (ie double-click) on the intersect of the Grand Totals (here Cell V7 or 7):

8. The PT may now be deleted.

9. 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.

Kelwunn

pnuts, this is really neat, the only down side being that if the data updates in the original table, it will not be automatically propagated to the new table. But, otherwise, a perfect solution! – Athwart

++ What can I say? Simply beautiful :) – Circumnutate

This double click on the GrandTotal really is a hidden feature.. and a nice feature! – Dedicated

The ALT+D,P-Shortcut won't be available in non-English Excel versions. See #32115719 for a workaround. – Needless

Answer 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.

Trammel

Thanks. Very simple and very powerful method. And best of all the data is dynamically linked! – Trucker

Fabulous! Thanks so much for this little tutorial. Saved probably hours of experimenting. – Oliveira

Answer 3

All of the solutions so far involve VBA, PowerQuery, etc. which are great, but are "one-time" events. To make it more dynamic, consider using INDEX(MATCH(...)). This will allow for dynamic updates to the table.

Bottom

This solution does have a traditional-Excel simplicity, but note that teylyn’s solution, above, also allows for dynamic updates, and in a much more automated manner. – Oliveira

Answer 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.

Beetle

Answer 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).

Hernando

Answer 6

This is a pretty solved situation now with the latest functions, even to create a dynamic range with spacers in between. I have run into some limitations with using ByCol in a different array so I use index and sequence more than might be considered necassary.

Dataset Before

• Two rows in column axis
• Three columns in rows axis
• Top left being cell a1

---

a1				Jan	Feb	Mar	Apr	May
				Actual	Actual	Actual	Budget	Budget
Disney	Tickets	Revenue		507	607	707	807	907
Disney	Movies	Costs		508	608	708	808	908
LucasFilm	Promo	Revenue		509	609	709	809	909
LucasFilm	Vader	Taxes		510	610	710	810	910
Marvel	HR	Costs		511	611	711	811	911

Formula

Technically you could avoid specifying the data range but the formula becomes more volatile and reduces performance.

=LET(AxisRows,$A$4:$C$8,  AxisColumns,$E$1:$I$2,dataRng,$E$4:$I$8,
      DimsInColumnAxis, ROWS(AxisColumns),
      DimsRowAxis, COLUMNS(AxisRows),
      totalDims,   DimsRowAxis+DimsInColumnAxis,
      totNewCols,  DimsRowAxis+DimsInColumnAxis+1,
      rowCount,    ROWS(AxisRows)*COLUMNS(dataRng),
      zSeq,        SEQUENCE(rowCount,totNewCols,0),
      colCount,    COLUMNS(AxisColumns),
      zDiv,        totNewCols*colCount,
      zAddr,      DimsRowAxis-1,
      modResult,   MOD(zSeq,totNewCols),
      c,           MOD(INT(zSeq/totNewCols),colCount)+1,
      r,           INT(zSeq/zDiv)+1,
      zFinalResult,IF(modResult<DimsRowAxis,
                   INDEX(AxisRows,r,modResult+1),
                   IF(modResult<totalDims,
                      INDEX(AxisColumns,modResult-zAddr,c),
                      INDEX(dataRng,r,c))),zFinalResult)

Ouput

Channing