SOLVED
Home

Easy way to move column headings to row labels?

%3CLINGO-SUB%20id%3D%22lingo-sub-690384%22%20slang%3D%22en-US%22%3EEasy%20way%20to%20move%20column%20headings%20to%20row%20labels%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690384%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20imported%20data%20from%20a%20survey%20that%20lists%20the%20questions%20in%20the%20top%20row%20as%20individual%20columns%20and%20the%20data%20from%20respondents%20in%20individual%20rows.%20Is%20there%20an%20easy%20way%20to%20swap%20these%20so%20that%20the%20questions%20run%20down%20the%20side%20of%20the%20sheet%20as%20row%20labels%20and%20the%20data%20from%20each%20respondent%20is%20arranged%20in%20a%20separate%20column%3F%20I%20haven't%20been%20able%20to%20find%20a%20quick%20way%20(other%20than%20cut%20and%20paste%20which%20is%20untenable%20for%20a%20survey%20with%20dozens%20of%20questions%20and%20about%20350%20respondents.%20Thanks%20in%20advance%20for%20suggestions.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-690384%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690471%22%20slang%3D%22en-US%22%3ERe%3A%20Easy%20way%20to%20move%20column%20headings%20to%20row%20labels%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690471%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F359730%22%20target%3D%22_blank%22%3E%40kaystephen%3C%2FA%3E%26nbsp%3B-%20Have%20you%20tried%20Transpose%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECopy%20the%20data%20from%20the%20sheet%2C%20create%20a%20new%20sheet%20then%20in%20cell%20A1%20right%20click%20and%20hit%20the%20transpose%20paste%20option%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-690492%22%20slang%3D%22en-US%22%3ERe%3A%20Easy%20way%20to%20move%20column%20headings%20to%20row%20labels%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690492%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F359730%22%20target%3D%22_blank%22%3E%40kaystephen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20operation%20you%20have%20described%20is%20transposing%20the%20dataset.%26nbsp%3B%20The%20formula%20that%20will%20do%20this%20is%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20TRANSPOSE(%20data%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Ebut%20the%20formula%20must%20be%20committed%20with%20ctrl%2Bshift%2Benter%20as%20an%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20alternatively%20perform%20the%20task%20manually%20using%20Cut%20and%20Paste%20transposed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20will%20transpose%20data%20as%20well%20as%20promoting%20and%20demoting%20headers%20to%20the%20first%20row%20of%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20formula%20that%20is%20capable%20of%20reversing%20rows%20and%20columns%20is%20INDEX%20in%20which%20a%20column%20number%20is%20read%20from%20a%20helper%20range%20(%20or%20calculated%20from%20COLUMN()%20)%20and%20then%20used%20as%20the%20row%20index%20in%20the%20function.%26nbsp%3B%20Take%20your%20choice.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-694430%22%20slang%3D%22en-US%22%3ERe%3A%20Easy%20way%20to%20move%20column%20headings%20to%20row%20labels%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-694430%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20the%20quick%20responses!%20It%20took%20me%20a%20couple%20of%20tries%20because%20the%20data%20set%20is%20large%2C%20but%20TRANSPOSE%20worked%20with%20your%20help.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
kaystephen
New Contributor

I have imported data from a survey that lists the questions in the top row as individual columns and the data from respondents in individual rows. Is there an easy way to swap these so that the questions run down the side of the sheet as row labels and the data from each respondent is arranged in a separate column? I haven't been able to find a quick way (other than cut and paste which is untenable for a survey with dozens of questions and about 350 respondents. Thanks in advance for suggestions. 

3 Replies

Hi @kaystephen - Have you tried Transpose

 

Copy the data from the sheet, create a new sheet then in cell A1 right click and hit the transpose paste option

 

Solution

@kaystephen 

The operation you have described is transposing the dataset.  The formula that will do this is

= TRANSPOSE( data )

but the formula must be committed with ctrl+shift+enter as an array.

 

You could alternatively perform the task manually using Cut and Paste transposed.

 

Power Query will transpose data as well as promoting and demoting headers to the first row of data.

 

Another formula that is capable of reversing rows and columns is INDEX in which a column number is read from a helper range ( or calculated from COLUMN() ) and then used as the row index in the function.  Take your choice.

 

Thanks for the quick responses! It took me a couple of tries because the data set is large, but TRANSPOSE worked with your help. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies