Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I convert values from a column into rows without losing data in existing rows below? For example, I have data in this format:

      A   |    B    | C      |
1 | Apples| Berries | Cakes  |
2 | Pies  | Crusts  | Nuts   |

I need the following:

1| Apples  
2| Berries  
3| Cakes  

1| Pies  
2| Crusts  
3| Nuts
share|improve this question
I should say I need Column A Row 1 Apples Row 2 Berries Row 3 Cakes Row 4 Pies Row 5 Crusts Row 6 Nuts... – Lisa Branham Aug 7 '12 at 19:27
You can edit your question to update it as you like, rather than comment on it. – CharlieRB Aug 7 '12 at 19:28

If you have space available to the right or below your data, select and copy the row data. Then select a cell in the available space and "right" click to get the context menu. Choose Transpose from the paste options.

enter image description here

Once you've converted it, delete the empty rows where the data was before.

share|improve this answer
Thanks CharlieRB. I do not have space available below the data, I have a sheet with over 500 lines and all lines have to be converted to the new format. When I try to use transpose, it replaces existing data. Any other suggestions? Also I do not have readily available space to the right, the columns go all the way to BG. – Lisa Branham Aug 7 '12 at 20:19
Paste transpose the data to a new sheet. – wbeard52 Aug 7 '12 at 21:45

Divide numbers in separate column & write description above to have a lable (say - Number). Afterwards select all data (all rows 7 columns); use Insert Pivot table (as data source all table will be used) & chose where you want to have it. Table Field List will appear & in row lables include Number; A; B; C (these are lables of your shown columns above). In there (in field settings to each of this row lable (each - A, B, C) choose in Subtotals$Filters tab - none; in layout & print tab - Show item labels in outline form - tick here Display in the same column. Afterwards paste special as numbers to format what you need/don't need - filter our only text etc. and you will receive data in columns. later use automatic numbering in column before (insert it). maybe that helps.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.