Sorting grouped rows - troubleshooting

Copper Contributor

I have been working on a problem in a sheet I have been building to track grants and projects for my program. In this sheet, data is sorted by grant IDs and their associated projects. Grants are grouped by their associated projects using the grouping function. My problem is that there seems to be an error when I attempt to sort rows, and Excel is behaving unpredictably.


This image shows the data as I have input it. For example, if I sort by "Funding Amount" largest to smallest, this is the result:


I'm not really sure to make of what Excel is doing here... or if there is a fix for this. Some of my other workbooks use similar organizational patterns so I'm unsure why this one doesn't work. I have attempted to use custom sorting, and I've followed a few other sets of instructions on here to no avail. I would be open to consolidating some of these columns but some will have to stay separated for sorting purposes. Thanks in advance!

1 Reply
Table sorting doesn't work like this. You must make sure all rows of the grouped columns contain data. All rows about grant AAA must have AAA in the first table column, and etcetera.
Once you've filled your table like that, consider creating a pivot table from the raw table rather than using the grouping option. It is always a better idea to keep your data as is and do the analysis elsewhere in the file.
To get your blanks filled:
- Select the columns containing blank cells
- Press the F5 key, then click "Special"
- Select "Blanks"
- Hit the = sign on your keyboard and then press the up-arrow key once.
- Press Control+Enter
- Now select those same columns again
- Hit control+C
- Do a paste-special, Values only.