2013-12-12 11:11:20 8 Comments

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
```

### Related Questions

#### Sponsored Content

#### 7 Answered Questions

### [SOLVED] Convert Rows to columns using 'Pivot' in SQL Server

**2013-04-10 16:38:38****Lynn****709364**View**247**Score**7**Answer- Tags: sql sql-server pivot pivot-table

#### 34 Answered Questions

#### 48 Answered Questions

#### 17 Answered Questions

### [SOLVED] Simple Pivot Table to Count Unique Values

**2012-08-09 03:09:56****user1586422****812686**View**128**Score**17**Answer- Tags: excel excel-formula pivot-table

#### 36 Answered Questions

#### 9 Answered Questions

### [SOLVED] How can I index a MATLAB array returned by a function without first assigning it to a local variable?

**2010-09-02 12:34:36****Joe Kearney****62394**View**324**Score**9**Answer- Tags: matlab indexing matrix return-value variable-assignment

#### 1 Answered Questions

### How to convert matrix to 4-column table without using VBA (Data - PivotTable and PivotChart Report)

**2017-08-20 10:33:13****Monika Dolega****676**View**0**Score**1**Answer- Tags: excel matrix pivot-table pivot-chart

#### 3 Answered Questions

### [SOLVED] Text values in a pivot table?

**2011-10-12 17:32:55****Dylan****4730**View**2**Score**3**Answer- Tags: sql excel matrix pivot-table

#### 1 Answered Questions

#### 3 Answered Questions

### [SOLVED] Pivot or unpivot

**2014-03-29 01:01:21****dinobrago****221**View**3**Score**3**Answer- Tags: sql-server pivot-table unpivot

## 3 comments

## @Matt 2018-11-14 15:09:53

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.

## @pnuts 2013-12-12 12:31:41

## 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 rangesand clickNext.In “Step 2a of 3”, choose

I will create the page fieldsand clickNext.In “Step 2b of 3” specify your summary table range in the

Rangefield (A1:E5 for the sample data) and clickAdd, thenNext.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

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

Tablein the Quick Menu (right-click in the Table) andConvert to Range.There is a video on the same subject at Launch Excel which I consider excellent quality.

## @KyleMit 2014-12-15 21:57:09

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!

## @Siddharth Rout 2015-09-07 09:34:58

++ What can I say? Simply beautiful :)

## @JDC 2016-03-10 16:28:30

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

## @Stefan Steiger 2017-09-11 10:32:27

The ALT+D,P-Shortcut won't be available in non-English Excel versions. See stackoverflow.com/questions/32115219/… for a workaround.

## @teylyn 2014-12-27 21:10:28

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

Transformribbon, click theUnpivot Columnsdrop-down and selectUnpivot other columns.For versions of Power Query that don't have the

Unpivot other columnscommand, select all columns except the first one (using Shift-click on the column headers) and use theUnpivotcommand.The result is a flat table. Click

Close and Loadon 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.

## @unubar 2018-09-01 06:07:13

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