Sorting recommendations when you have summary rows on the top on your sheet

Ana Aguayo

I am experiencing issues when sorting a sheet and have summary rows at the top. For example, I created summary rows on rows 1 through 10 with formulas and totals, and when sorting, my summary rows get lost in the sorting process. Do you have a workaround to prevent this from happening? Ideally, I would like my summary rows to remain on the top always.

 

Jeremy Myers

I use hidden columns to control this sort of thing.

 

Create a column called "Index" or something similar.  Numbering the cells consecutively going down 1, 2, 3 etc. for your summary rows only.  If you have a lot of cells, just number the first two cells and then <control click> to select those and drag the handle down at the right lower of the bottom cell, it auto-increments just like Excel.

 

Drag the <index> column to the far right and hide it.  Next time you sort, sort on <index> first and then whatever the 2nd column name is.  The results will be your summary rows on top and everything else stacked below.  

 

I use the same general strategy when I need certain rows only to appear in a row.  I create a checkbox column called <include in report of ......>, then when I setup my reports I just select for that checkbox being valid and I am good to go.  No thinking, no muss.

Jeremy Myers

Sorry, error in the above:   Should be:  "I use the same genral strategy when I need cerain rows only to iappear in a REPORT."

Ana Aguayo

Thanks for the recommendation on creating an index column. I will definitely try it. I have used the Check box for reporting purposes and it is reassuring to know that other people find it useful as well.

J. Craig Williams

I also created check boxes for items to EXCLUDE from Reports.

 

Once you have something like that, it (usually) needs to be in every sheet even if that particular sheet doesn't need it - otherwise you get false positives or missed rows.

 

Craig