How to "unpivot" or "reverse pivot" in Excel?

I have data that looks like this:

Id | Loc1 | Loc2 | Loc3 | Loc4
---+------+------+------+-----
1  | NY   | CA   | TX   | IL
2  | WA   | OR   | NH   | RI

And I want to convert it to this:

Id | LocNum | Loc
---+--------+----
1  |   1    | NY
1  |   2    | CA
1  |   3    | TX
1  |   4    | IL
2  |   1    | WA
2  |   2    | OR
2  |   3    | NH
2  |   4    | RI

What's the easiest way to do this in Excel 2007?

Solution 1:

You can do this with a pivot table.

1. Create a "Multiple Consolidation Ranges PivotTable." (Only on Pivot Table Wizard. Callup with ALT+D, P on Excel 2007)
2. Select "I will create my own page fields".
3. Select your data.
4. Double click on the grand total value - the one at the intersection of Row Grand and Column Grand, all the way on the lower right hand corner of your pivot table.

You should see a new sheet containing all of the data in your pivot table, transposed in the way you're looking for.

Datapig technologies provides step-by-step instructions that are actually more complicated than you need - his example transposes only part of the data set & uses the pivot technique combined with TextToColumns. But it does have lots of pictures.

Note that a pivot table will group the data. If you want it ungrouped, the only way to do it is to copy the pivot table, and "paste special" as values. You can then fill in the blanks with a technique like this: http://www.contextures.com/xlDataEntry02.html

Solution 2:

If your data isn't an Excel Pivot Table but just data, you might want to "un-pivot" it with some simple VBA code. The code depends on two named ranges, Source and Target. Source is the data you want to un-pivot (exclusive of the column/row headers, e.g. NY-RI in the sample) and Target is the first cell where you want to place your result.

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub

Solution 3:

I have built an add-in that will let you do that, and that makes it easy to adapt to different situations. Check it out here: http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

Solution 4:

There is quite a nice solution in excel 2010, just need to play a bit with pivot table.

Create a pivot table from your data with these settings:

• no subtotals, no grand totals
• report layout: tabular form, repeat all item labels
• add all the columns you need, keep columns you want to transform at the right
• for each column you want to transform: open field settings – in layout & print tab: select “Show item labels in outline form” and check both check boxes below it
• copy your table to a separate location (just values)
• if you have empty cells in your original data then filter for empty values in the rightmost column and delete those rows (don’t filter in pivot table as it won't work as you need!)

Solution 5:

Best I've come up with so far is this:

Id   LocNum  Loc
---------------------------------
1    1       =INDEX(Data,A6,B6)
1    2       =INDEX(Data,A7,B7)
1    3       =INDEX(Data,A8,B8)
1    4       =INDEX(Data,A9,B9)
2    1       =INDEX(Data,A10,B10)
2    2       =INDEX(Data,A11,B11)
2    3       =INDEX(Data,A12,B12)
2    4       =INDEX(Data,A13,B13)

This works, but I have to generate the Id's and LocNum's manually. If there's a more automated solution (besides writing a macro), please let me know in a separate answer.