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

Question

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?

Found the answer: stackoverflow.com/questions/32115219/…
– Quandary
Aug 20, 2015 at 11:17 — Quandary, Aug 20, 2015 at 11:17

Lernkurve · Accepted Answer · 2016-03-10 08:53:47Z

71

You can do this with a pivot table.

Create a "Multiple Consolidation Ranges PivotTable." (Only on Pivot Table Wizard. Callup with ALT+D, P on Excel 2007)
Select "I will create my own page fields".
Select your data.
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

edited Mar 10, 2016 at 8:53

Lernkurve

1,9827 gold badges28 silver badges41 bronze badges

answered Dec 3, 2009 at 19:57

DaveParillo

14.5k1 gold badge40 silver badges46 bronze badges

Thanks for your answer, but I'm not quite sure what you're saying here. For the example in my question, what would I use for Row Labels, Column Labels, Values, and Report Filter?
– devuxer
Dec 3, 2009 at 21:05
If I put Id into Row Labels and Loc1 through Loc4 into Column Labels, then click the lower-right cell (the intersection of the Grand Totals), it does create a new sheet with a table, but it's not what I want. It's basically just a copy of the regular pivot table.
– devuxer
Dec 3, 2009 at 21:20
I'm sorry - I left out a critical piece of data. The pivot Table must be a 'multiple consolidation range' type pivot table, even though in this case you have nothing to consolidate.
– DaveParillo
Dec 3, 2009 at 23:19
4

Ah okay, I looked it up in the help and found a back-door way to get the old Pivot Table Wizard in Excel 2007 (you need to press ALT+D+P). Now I'm able to follow your steps, and it seems to work. Thanks for your help!
– devuxer
Dec 7, 2009 at 20:44
1

Here's how exactly: stackoverflow.com/questions/32115219/…
– Quandary
Aug 20, 2015 at 11:17

| Show 4 more comments

TJMelrose · Accepted Answer · 2013-04-15 14:05:17Z

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

Thanks for this. Works as excepted and make me save a lot of time. — tigrou, Apr 22, 2013 at 14:56
Thank you!! this is just magic! much better than jigiry-pokery with unpivoting reports that never worked for me — trailmax, May 21, 2014 at 16:55
This looks great, but as best I can tell, it will only work for a Table that has only one row header. If the table in the example had multiple row headers, say it had a "SubId" in addition to the Id field, this wouldn't work. Is there an easy way to modify it so that it would work in this situation? — Chris Stocking, Jan 29, 2015 at 20:42

nutsch · Accepted Answer · 2013-09-21 05:47:19Z

5

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/

answered Sep 21, 2013 at 5:47

nutsch

1,96312 silver badges14 bronze badges

Add a comment |

Jawa · Accepted Answer · 2015-03-12 09:32:59Z

5

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!)

edited Mar 12, 2015 at 9:32

Jawa

3,64913 gold badges32 silver badges36 bronze badges

answered Mar 12, 2015 at 9:29

Máté Juhász

21.5k6 gold badges55 silver badges75 bronze badges

1

This is certainly the best answer if you are in 2010 and up, and not at all obvious in the interface. Great find -- sadly I have only one upvote!
– jkf
Nov 3, 2018 at 6:16

Add a comment |

devuxer · Accepted Answer · 2009-12-03 19:40:54Z

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.

Community · Accepted Answer · 2020-06-12 13:48:39Z

If the dimensions of your data are as in the sample provided in your question, then the following set of formulae using OFFSET should give you your required result:

Assuming

1 | NY | CA | TX | IL

2 | WA | OR | NH | RI

are in the range A2:E3, then enter

=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/4,1),0)

in F2, say, and

=MOD(ROW(A2)-ROW($A$2),4)+1

in G2, say, and

=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/4,1),MOD(ROW(A2)-ROW($A$2),4))

in H2, say.

Then copy these formulae down as far as required.

This is the easiest, pure, built-in formula solution that I can think of.

Roie MarianerRoie Marianer · Accepted Answer · 2009-12-07 01:10:17Z

You seem to have gotten the "loc" column (evidenced by your first answer), and now you need help getting the other two columns.

Your first option is to simply type the first several (say, 12) rows into those columns and drag down - I think Excel does the right thing in this case (I don't have excel on this computer to test it for sure).

If that doesn't work, or if you want something more programmer-y, try using the row() function. Something like "=Floor(row()/4)" for the ID column and "=mod(row(),4)+1" for the LocNum column.

Petros · Accepted Answer · 2012-04-29 17:54:52Z

1

There is a parametric VBA conversion utility to unpivot or reverse pivoted data back to a database table, please see

http://www.spreadsheet1.com/unpivot-data.html

edited Apr 29, 2012 at 17:54

answered Apr 5, 2012 at 2:08

Petros

112 bronze badges

1

Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference.
– Peachy
Nov 6, 2012 at 16:55

Add a comment |

Excellll · Accepted Answer · 2014-04-24 13:58:22Z

1

Here is a nice tool to unpivot, normalize a Pivot Table.

Normalisieren von Pivot Tabellen

I hope it helps.

edited Apr 24, 2014 at 13:58

Excellll

12.6k11 gold badges51 silver badges78 bronze badges

answered Apr 24, 2014 at 13:28

L4a-Thompson

111 bronze badge

1

Welcome to SuperUser! While the tool you linked to offers a solution, this post is only useful as long as the link remains active. To make sure your post remains useful well into the future, please edit your answer to include any additional information about the product, including how to use it to solve the problem described in the question. Thanks!
– Excellll
Apr 24, 2014 at 14:02

Add a comment |

BrinkDaDrink · Accepted Answer · 2015-06-16 15:29:17Z

1

@DaveParillo answer is the best answer for a single tab table. I wanted to add some extras here.

For Multiple columns pre the unpivot columns

This method does not work.

Youtube unpivot simple data like the question

This is a youtube video showing how to do it in a simple manner. I skipped the part about adding the shortcut and used @devuxer shortcut that is in DaveParillo answer.

https://www.youtube.com/watch?v=pUXJLzqlEPk

answered Jun 16, 2015 at 15:29

BrinkDaDrink

1611 silver badge6 bronze badges

3

The substance of your answer is a link to a video and the content isn't described. If the link breaks or is unavailable, your answer would have no value. Please include the essential information in your answer and use the link just for attribution and further exploration.
– fixer1234
Jun 16, 2015 at 18:47

Add a comment |

pgSystemTester · Accepted Answer · 2023-04-01 03:27:29Z

Excel has made this question easier to answer and it can be done in a single formula. It requires some newer functions such as ByRow and ByCol but these are scheduled to be available to everyone (someday).

To recap:

No VBA
Dynamic for multiple axes in both rows and columns data ranges
Can be converted to lambda (in desktop version)

With the below dataset pasted in cell A1, you could use this Lambda function to unpivot or flatten the data:

Starting Dataset

See sample file here

(cell 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
StarWars	Promo	Revenue	509	609	709	809	909
StarWars	Vader	Taxes	510	610	710	810	910
Marvel	HR	Costs	511	611	711	811	911

Stand Alone Formula

=LET(dataRng,D3:H7,  rowAxis,A3:C7, colAxis,D1:H2,
   iCol,COLUMN(INDEX(rowAxis,1,1)),   amountCol,TOCOL(dataRng),  totalCells,COUNTA(amountCol),
          HSTACK(
              INDEX(rowAxis,
                     INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
                     BYCOL(INDEX(rowAxis,1,),  LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
              INDEX(colAxis,
                      SEQUENCE(1,ROWS(colAxis),1,1),
                      MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
               amountCol))

Lambda Formula

=LAMBDA(dataRng,rowAxis,colAxis,
   LET(iCol,COLUMN(INDEX(rowAxis,1,1)), amountCol,TOCOL(dataRng), totalCells,COUNTA(amountCol),
          HSTACK(
              INDEX(rowAxis,
                     INT(SEQUENCE(totalCells,1,0,1)/COLUMNS(dataRng))+1,
                     BYCOL(INDEX(rowAxis,1,),  LAMBDA(aCol,COLUMN(aCol) -iCol +1))),
              INDEX(colAxis,
                      SEQUENCE(1,ROWS(colAxis),1,1),
                      MOD(SEQUENCE(totalCells,1,0,1),COLUMNS(dataRng))+1),
               amountCol
                      )))(D3:H7,A3:C7,D1:H2)

Also, if you did happen to want a vba solution, I used to use this:

Function unPivotData(theDataRange As Range, theColumnRange As Range, theRowRange As Range, _
    Optional skipZerosAsTrue As Boolean, Optional includeBlanksAsTrue As Boolean, Optional columnsFirst As Boolean)


'Set effecient range
Dim cleanedDataRange As Range
    Set cleanedDataRange = Intersect(theDataRange, theDataRange.Worksheet.UsedRange)
   
'tests Data ranges

    'Use intersect address to account for users selecting full row or column
   If cleanedDataRange.EntireColumn.Address <> Intersect(cleanedDataRange.EntireColumn, theColumnRange).EntireColumn.Address Then
      unPivotData = "datarange missing Column Ranges"

   ElseIf cleanedDataRange.EntireRow.Address <> Intersect(cleanedDataRange.EntireRow, theRowRange).EntireRow.Address Then
      unPivotData = "datarange missing row Ranges"

   ElseIf Not Intersect(cleanedDataRange, theColumnRange) Is Nothing Then
      unPivotData = "datarange may not intersect column range.  " & Intersect(cleanedDataRange, theColumnRange).Address
      
   ElseIf Not Intersect(cleanedDataRange, theRowRange) Is Nothing Then
      unPivotData = "datarange may not intersect row range.  " & Intersect(cleanedDataRange, theRowRange).Address
   
   End If

   'exits if errors were found
   If Len(unPivotData) > 0 Then Exit Function
   
   Dim dimCount As Long
      dimCount = theColumnRange.Rows.Count + theRowRange.Columns.Count
   
   Dim aCell As Range, i As Long, g As Long, tangoRange As Range
   ReDim newdata(dimCount, i)
   
'loops through data ranges
   For Each aCell In cleanedDataRange.Cells
   
  If aCell.Value2 = "" And Not (includeBlanksAsTrue) Then
     'skip
  ElseIf aCell.Value2 = 0 And skipZerosAsTrue Then
     'skip
  Else
     ReDim Preserve newdata(dimCount, i)
     g = 0
         
    'gets DimensionMembers members
        If columnsFirst Then
            Set tangoRange = Union(Intersect(aCell.EntireColumn, theColumnRange), _
                Intersect(aCell.EntireRow, theRowRange))
          Else
           Set tangoRange = Union(Intersect(aCell.EntireRow, theRowRange), _
           Intersect(aCell.EntireColumn, theColumnRange))
        End If

        For Each gcell In tangoRange.Cells
            newdata(g, i) = IIf(gcell.Value2 = "", "", gcell.Value)
            g = g + 1
        Next gcell

             newdata(g, i) = IIf(aCell.Value2 = "", "", aCell.Value)
             i = i + 1
    End If
   Next aCell
            
   unPivotData = WorksheetFunction.Transpose(newdata)
End Function

jonsca · Accepted Answer · 2013-09-13 23:22:27Z

-3

It's much simple than this. Just click the "Transpose" option in "Paste Special..." to get the transposition you are requesting.

edited Sep 13, 2013 at 23:22

jonsca

4,06215 gold badges35 silver badges47 bronze badges

answered Sep 13, 2013 at 21:09

C Tyler

5

2

This wouldn't work.
– zx8754
Oct 7, 2013 at 10:34

Add a comment |

Stack Exchange Network

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

12 Answers 12

Starting Dataset

Stand Alone Formula

Lambda Formula

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged
microsoft-excel
microsoft-excel-2007
pivot-table
.

Linked

Hot Network Questions

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

12 Answers 12

Starting Dataset

Stand Alone Formula

Lambda Formula

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged microsoft-excelmicrosoft-excel-2007pivot-table.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
microsoft-excel
microsoft-excel-2007
pivot-table
.