Reformatting table

[aiyer1217]

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?

Thanks!

[#2]

@aiyer1217 

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

[#3]

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

[#4]

@aiyer1217 

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.