Is there a way to group data in rows in excel having the total on the top rather than on the bottom?

Question

I need to group data as subsets of certain titles rather than having the titles at the bottom of my set, is there a way to switch the way Excel does the grouping?

Answer 1

Sure, I do it all the time where I will be adding more rows of data and don't want to be constantly inserting rows.  To set a "top total" value, for up to 10,000, rows in Row 2 for Column E enter

=SUM(E3:E10002) in Cell E2

Then any value you enter ito any of the first 10,000 rows below row 2 will be included in the total in Cell E2.  This means that any subtotals you might want to insert should be shown in Column F to prevent them from being included in the E2 total.

Alternately, you could expand the statement in E2 to

=SUM(E3:F10002)

Then anytime you create a subtotal in Column E you insert a formula on Column F or the same row to make that cell the negative of the value in Column E.  When you are getting ready to display or print your values you hide Column F, set its width to 1, or set the foreground color to the same as the background so that the offsetting (usually negative) values aren't seen.