Reformatting table

New Contributor

Hi everyone,


I have a sheet in excel where i have counts for certain drugs, and each column corresponds to a specific state.  



However, I would like to reformat this so that there is a single column in which the states are listed, as shown below:





I have done this with one of my datasets by manually copying and pasting the numbers for each state into column C, and then copying and pasting the state name into column B, as shown here.   However, I have to do this with multiple datasets and it will take quite a bit of time!  Any insight on potential shortcuts I can take?




3 Replies


That could be done with Power Query if you have Excel for Windows desktop

Thank you! Would you mind briefly walking through the steps on how to do this?


Let assume source data as


Power Query this table, select column B and Unpivot other columns. Practically that's all, result is as


if only to play with formatting more.