Excel transpose and merge

New Contributor

Does anyone know a function or method in excel of getting data in a state from Illustration 1 to Illustration 2. Transform could be part of the solution but I need a way to consolidate rows and columns at the same time


6 Replies


Power Query could help

cool. I've never used before. do you happen to know how that could work and the steps?


Sure, if only you share sample file instead of screenshot

i updated my post to include. much appreciated.

@clieboff How about a pivot table? See attached.


Thank you.

- Not exactly necessary, but it's better to transform source data into structures table (Ctrl+L or Cltrl+T staying within it)

- Right click on it and Get Data from Table/Range or Get Data from Sheet or like depends on your Excel version

- in Power Query editor select Service Level 2 column, Transform->Pivot Column, Value as value

- land result into Excel sheet

- adjust formats as needed


Alternatively you may create PivotTable based o  your source, also in attached.