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 a spreadsheet with the information set up as such:

ORDER     PLU/Recipe #     Name/Desc                  Components
2.01                           263719
2.02                           261453
2.03                           260018
2.04                           261263
2.05                           319384
2.06                           261487
2.07                           WATER
2.08                           260844
3       99804      APPLE DANISH 
3.01                           320267
3.02                           CINNAMON STREUSSEL FORMULA
3.03                           263046
3.04                           261354
3.05                           263186
4       69961      BLUEBERRY    
4.01                           320367
4.02                           CINNAMON STREUSSEL FORMULA
4.03                           261859
4.04                           261354
4.05                           263186
5       89804      CHEESE DANISH    
5.01                           320627
5.02                           CINNAMON STREUSSEL FORMULA
5.03                           263103
5.04                           261354
5.05                           263186

I would like to be able to collapse the ingredient lines (the ones with the decimals) so that it is easier to browse the items in the list and have that be sortable. When I try to do this, the collapsed lines don't stay with the item names and the item names disappear when I collapse the group. How can I make it so the list is sortable but the ingredients stay with the item names when collapsed?

The "Order" column exists so I can resort the list into the rather arbitrary order my employer has the original list in.

For reference, I am using Excel 2003.

Thanks for any help!

share|improve this question
Have you grouped them separately with a blank between groups? – Raystafarian Feb 28 '12 at 19:20
I just tried that. It only sorted the lines that weren't grouped. When I expanded the grouped cells after sorting, they were not attached to the main line. – false0start Feb 28 '12 at 19:39
There seems to be a case for a PivotTable here. – pnuts Mar 19 '13 at 15:50

I know this works in Excel 2010, hopefully it also works in Excel 2003.

Part of your problem may be that Excel links grouped rows to the row below the group, not the row above the group.

First sort your data in descending order so that the item row is below the ingredients that belong to it. Group your ingredient rows. Grouped rows

Now that the components are grouped to the correct items, you should be able to easily sort on any of the item columns and have the components move with them.

To re-sort the components, you have to select and sort each group individually.

To get the 2nd picture, I hid the components for items 2 & 4 and then sorted by Order. grouped rows after a sort

share|improve this answer

FYI to anyone who stumbles onto this.

To fix this issue: "Part of your problem may be that Excel links grouped rows to the row below the group, not the row above the group." it looks like there is an option to reverse it: "summary rows below detail"

To get to that option, do this: for excel 2010: go to Data Tab on ribbon > Click the little arrow in the bottom right hand corner of the "Outline" Section > and un-check "Summary Rows Below Detail" - taken from here:

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.