I have an excel spreadsheet, where all values are in A column. However, they should be distributed so A1-A5 are in A column, A6-A10 in B column and so on.
Is there a way to automatically pivot data like that in excel?
I have an excel spreadsheet, where all values are in A column. However, they should be distributed so A1-A5 are in A column, A6-A10 in B column and so on.
Is there a way to automatically pivot data like that in excel?
(I am working off Excel 2008 Mac, but this method should be good for most Excel versions) You need to use the TRANSPOSE function. The trick is that for it to work Excel needs an array as the input to TRANSPOSE. You make the thing into an array by using Crtl+Shift+Enter instead of just Enter after building your function.
So say your stuff is in range A1..A5 and that once you transpose the data you expect it to land on B1..F1.
B1
B1..F1
, the target row cells=TRANSPOSE(
in B1
.A1..A5
, your source column cells. B1
. You should now have =TRANSPOSE(A1..A5)
at B1. Do not hit Enter!B1
is transformed to {=TRANSPOSE(A1..A5)}
and the source data is, well, transposed into the target row cells.Hope this helps