In Excel, I use subtotals quite often, but theres got to be a way to use
them more efficiently! I like the tool quite a bit, but sometimes what I
need is JUST the subtotals, not the detailed records themselves. I need to
create a presentation-quality worksheet with only the totals. Collapsing the
subtotals by nesting them isn't the answer; the worksheets reviewers dont
need / want the detail records, and the presentation of the collapsed view is
clunky at best.

What I'm doing requires several steps:
1. After subtotaling, copy and Paste Special €“ Values (replacing the
subtotal formulas with values)
2. Remove the subtotals - Data, Subtotals, Remove All
3. Sort the range so the €œsubtotals€� are grouped together
4. Delete the detail records
5. Insert a column, and create a formula to strip out the word €œTotal€� from
each Description
6. In the new column, Copy and Paste Special Values to remove that formula
Delete the old Description column.


Yuck! Too many steps. Is there a better way to accomplish the goal of
taking a subtotaled range and just displaying the totals?

U¿ytkownik "Tom" napisa³ w wiadomo¶ci
...
In Excel, I use subtotals quite often, but there's got to be a way to use
them more efficiently! I like the tool quite a bit, but sometimes what I
need is JUST the subtotals, not the detailed records themselves. I need
to
create a presentation-quality worksheet with only the totals. Collapsing
the
subtotals by nesting them isn't the answer; the worksheet's reviewers don'
t
need / want the detail records, and the presentation of the collapsed view
is
clunky at best.

What I'm doing requires several steps:
1. After subtotaling, copy and Paste Special - Values (replacing the
subtotal formulas with values)
2. Remove the subtotals - Data, Subtotals, Remove All
3. Sort the range so the "subtotals" are grouped together
4. Delete the detail records
5. Insert a column, and create a formula to strip out the word "Total"
from
each Description
6. In the new column, Copy and Paste Special Values to remove that
formula
Delete the old Description column.


Yuck! Too many steps. Is there a better way to accomplish the goal of
taking a subtotaled range and just displaying the totals?


i think that pivot table should resolve your problem or you could use sum.if
function
look at http://www.contextures.com/tiptech.html
mcg

I agree with mcg that pivottables might be the way to go...

If you want to read more about pivottables...

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx

=============
But you could do all your data|subtotals
show only the subtotals (hide the details
select that range
edit|goto|special|visible cells only
edit|copy
edit|paste (to a new location on a new worksheet???)

Then select the column with the " Totals"
Edit|replace
what: _Totals (_ represents the space character)
with: (leave blank)
replace all

And continue your formatting.



Tom wrote:

In Excel, I use subtotals quite often, but theres got to be a way to use
them more efficiently! I like the tool quite a bit, but sometimes what I
need is JUST the subtotals, not the detailed records themselves. I need to
create a presentation-quality worksheet with only the totals. Collapsing the
subtotals by nesting them isn't the answer; the worksheets reviewers dont
need / want the detail records, and the presentation of the collapsed view is
clunky at best.

What I'm doing requires several steps:
1. After subtotaling, copy and Paste Special €“ Values (replacing the
subtotal formulas with values)
2. Remove the subtotals - Data, Subtotals, Remove All
3. Sort the range so the €œsubtotals€� are grouped together
4. Delete the detail records
5. Insert a column, and create a formula to strip out the word €œTotal€� from
each Description
6. In the new column, Copy and Paste Special Values to remove that formula
Delete the old Description column.


Yuck! Too many steps. Is there a better way to accomplish the goal of
taking a subtotaled range and just displaying the totals?

--

Dave Peterson