Where is the best spot to put a Total row?

[Masa Ito]

I am having trouble figuring out the 'best practice' for where to put a
total row.

I am sending excel files out to many people, who will all maintain (and
send back to me) their individual sheets. Their sheets involve frequent
adding of lines, occasional removing of lines, and frequent editing of
existing lines. They are not skilled (at all) with Excel, and I am
writing some code to pull their sheets into a database.

Problem is, they want a total row, and like it just below their data.
When I do this, it seems to get sucked into the sorts, filters etc, and
makes it harder for them to add lines (they often add them below the
total line).

If I put the total at the top, just below the headers, and freeze the
top few rows, it looks great, but has the same problem getting 'lost'
included in the data when sorting/filtering.

I tried doing a split screen, and throwing it WAY down the page. This
works ok, but people seem to hate/get confused by the split screen, and
I don't seem able to freeze the top rows (header, titles & logos) while
also splitting the screen to see the totals.

I did some playing with the subtotals, but didn't see a way that would
show a single totals column for all the columns I want totaled, while
keeping a simple 'table' for users to add/edit.

Anyone have any advice for an Excel newbie?

Thanks,

Masa

 

[Kieran]

Masa,

I usually place totals like this at the top of the page, but I leave
blank line between it(the totas) and the data. This then will make i
harder for 'sort' from moving the totals line as it wil not b
autoamically selected with the reset of the data.

Additionally if you were to use the subtotal command =SUBTOTAL(9
range) instead of SUM, the total can be sensitive to any hidden/visibl
rows when autofilter is on.
Depending on the users needs, you may be able to use this 'feature' a
a selling point on your choice of layout

 

[Dave Peterson]

I like to use =subtotal() so that it adjusts when I filter my data.

I put the subtotals in Row 1. The headers in Row 2 (with alt-enters to have
multiple lines within each cell) and the data below.

 

[Earl Kiosterud]

Masa,

It sounds as if your sort needs to select the data range, and not include
the totals. Whether they are at the top or bottom, if you're using range
expansion (selecting one cell and letting it expand the selection) you can
keep the totals out of the sort selection by having an empty row (which you
can hide) between. The safest sorting is via Data - Sort, using a selection
of your table, not a range expansion from a single cell. There's a bit more
on this at www.smokeylake.com/excel. Excel Truths.

I agree with Dave about putting the totals at the top and then using Freeze
Panes. Also using SUBTOTAL if you're filtering, so the totals will be only
for the filtered data. But some people get glazed over, gassy, and I don't
know what-all when you don't put totals at the bottom!

To keep people from adding rows below the totals, you may have to use
Worksheet Protection. It takes a bit of work to make an Excel sheet
user-proof.