Presenting Subtotals only

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?

U�ytkownik "Tom" <[email protected]> napisa� w wiadomo�ci
news:[email protected]...
> 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 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?

--

Dave Peterson