This proposal employs Power Querry which should be included in the 2016 version of Excel.
1. Convert the data in the range A1:D9
2. On the data tab select From Table/Range
3. Paste the following code into the advanced editor to produce the orange/white table in columns F:H
let
Source = Excel.CurrentWorkbook(){[Name="tbl_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Question 1", Int64.Type}, {"Question 2", Int64.Type}, {"Question 3", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Department"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Question"}})
in
#"Renamed Columns"
3. Produce the pivot table from the new table
Let us know if you have any questions.
Bookmarks