Firstly, this is the layout of my dataset, with the Sample Number going along the top and Compound going down the side.
I want to rearrange my dataset so that it has just three columns, one for the Compound, one for the Sample Number, and one for the Value. In other words, I want to unpivot my dataset. This can be done using a pivot table, even though this is the opposite of what we usually use a pivot table for.
The keyboard shortcut Alt + D + P will open the old version of the pivot table. There are some things you can do with the older version that you can’t do with the newer version. Make sure you press Alt & D together, then release both Alt & D, then very quickly press P.
Select Multiple consolidation ranges and click Next
Select I will create the page fields and click Next
Select the range with your dataset, click Add, and then click Next
Click Finish
We have now made our Pivot Table.
Remove the Rows and Columns to get just one cell with the total in it. Double click on this cell.
This will make a new table that has 3 columns in it: Row, Column & Value. We have now rearranged the data so that it is laid out the way we want it.
