Transposing a table



I have a panel data table. The value of an item is recorded each year. The column has values corresponding to the year ( table 1). So, there are 11 years and 11 different columns. I want to have only 2 columns, all years in 1 column (table 2). Simple transpose doesn't work. I also tried sumifs but didn't succeed. Please advise.

5 Replies

The Power Query Unpivot Other Columns function is perfect for this

Apologies for the music choice

UTC + 8


As Mr. Wyn Hopkins  said, Power Query is the most suitable tool for this.

Here is some information and help for the first steps from Microsoft.

Combine data from multiple data sources (Power Query)


* Knowing the Excel version and operating system would also be an advantage.


I would be happy to know if I could help.



I know I don't know anything (Socrates)


Unpivoting tables is core functionality of Power Query. 

It is also possible to effect such a transformation using formulas but that involves detailed messing about with row and column indices.  It is most elegantly performed in the latest 365 version of Excel.


= UNPIVOT(array, item, year)

which is a named version of

= LAMBDA(arr,Itm,Yr,
      m, ROWS(arr),
      n, COLUMNS(arr),
      k, SEQUENCE(m*n),
      YearLst, INDEX(Yr, 1+MOD(k-1,n)),
      itemLst, INDEX(Itm, 1+QUOTIENT(k-1,n)),
    CHOOSE({1,2,3}, itemLst, YearLst, ELEMENT(itemLst, YearLst) ) )
  )(array, item, year)

The key elements of the calculation are the index 'k' numbering the output array and, from that, the MOD and QUOTIENT formulae that identify the row and column of element required from the array.


That should probably convince you to use PQ but it might be worth knowing other possibilities exist. 

@Peter Bartholomew , you missed ELEMENT()

@Sergei Baklan 

Just a glorified INDEX but specific to a particular array

= LAMBDA(itm,yr,
    r0, XMATCH(itm, item),
    c0, XMATCH(yr, year),
    INDEX(array, r0, c0) )

The intention is that the Lambda function should be the Name the user knows the array by and the reference within the formula is to the range that the array occupies.   That gives a more standard function notation than the INDEX formula.