Excel - Re-arrange data from columns into rows


I have a dataset that looks something like this:

Plot    Species        Cover 
1           A              1 
1           B              2 
1           C              1 
2           C              4 
2           D              5 
3           A              6  
3           B              6 
3           C              2 
3           D              1 
3           E              3

With over 300 plots, over 150 species and 6 covers.

I would like to rearrange the data to look like this:

 A    B     C    D    E 
1           1    2     0    0    0  
2           0    0     4    5    0  
3           6    6     2    1    3

With species as columns, plots as rows and covers as values.

No species occurs more than once I a single plot and not all plots have the same number of species.

I have several datasets like this, so doing it manually for all datasets would be quite tedious. This seems like it would be a common issue, but I haven't been able to find an answer. I thought pivot tables would work, but there is no way to insert raw values into the table (is there?), just descriptive statistics about the values. I've tried nested lookup's, if's, etc.

It isn't necessary that the data stay in excel, so if there is another program/ programming language that would do this task, I'd be willing to try that. Any advise would be greatly appreciated!


Find out unique values of plots and species by using advance filter see help undergetting unique values under Excel.

in your case the unique plots are 1 2 3 and unique species are A B C D E

below the data choose a cell e.g. A13 onwards down and to right as follows

Plot A B C D E  

in B14 copy this formula

=IF(ISNA(INDEX($C$2:$C$11,MATCH(1,($A$2:$A$11=$A14)*($B$2:$B-$11=B$13),0),1)),0,INDEX($C$2:$C$11,MATCH(1,($A$2:$A$11=$A14 )*($B$2:$B$11=B$13),0),1))  

invoke this formla with CONTROL+SHIFT+ENTER

copy B14 down and to the right

you get this result from A13 down and right=A13 will be blank )

A B C D E  
1 1 2 1 0 0  
2 0 0 4 5 0  
3 6 6 2 1 3 

BY THE BY 1C is 1 and not 0


Thanks to venkat1926 for this tip on the forum.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - Re-arrange data from columns into rows », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).