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

I have this data in the following format:

ID   Product A   Product B
1    19          27
2    12          32

But what I would like to have ii in this format:

ID   Product     Quantity
1    Product A   19
1    Product B   27
2    Product A   12
2    Product B   32

Just a hint in the right direction could be very helpful, thanks!

share|improve this question
Have you looked at Pivot tables? – user55021 Nov 9 '10 at 16:07
No I had not, thanks for the tip. – Michiel van Oosterhout Nov 10 '10 at 12:46
up vote 2 down vote accepted

As @Mark mentioned, you need to use Pivot Tables.

alt text

Drag the ID to Row Fields and Each product to Data Items section.

alt text

Now move summation of values from column level to row level, because you want groups based on ids, not products, and the desired table is ready.

alt text

share|improve this answer
Great, thanks! I set some design options, such as hide totals, repeat item labels and display as table, and it looks great. The only issue remaining is that I can't use the original column name 'Product A' as the value instead of 'Sum of Product A'. – Michiel van Oosterhout Nov 10 '10 at 12:42
@michiel You can actually rename them - just click on Values dropdown list - Value Field Settings - and provide the desired name under Custom Name – Sathya Nov 10 '10 at 14:47
Unfortunately I can't choose 'Product A' as the name, Excel complains that the name is already used in the pivot table (I think because it's the name of the original column). – Michiel van Oosterhout Nov 11 '10 at 7:33

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.