0

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?

2
  • Instead of pivot, the correct term is "transpose"
    – Olli
    Jan 31, 2014 at 9:45
  • I guess shame on me for using SQL terminology...
    – kyooryu
    Jan 31, 2014 at 9:48

1 Answer 1

1

(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.

  1. Select cell B1
  2. Now extend the selection to cover B1..F1, the target row cells
  3. Without losing the selection type =TRANSPOSE( in B1.
  4. Excel should change to select range mode. Select A1..A5, your source column cells.
  5. Close the parenthesis in B1. You should now have =TRANSPOSE(A1..A5) at B1. Do not hit Enter!
  6. Hit Ctrl+Shift+Enter instead
  7. The formula in B1 is transformed to {=TRANSPOSE(A1..A5)} and the source data is, well, transposed into the target row cells.

Hope this helps

Not the answer you're looking for? Browse other questions tagged .