Collapse subtotals?

Using WindowsXP, Excel 2002

After running the Subtotal command, with subtotals showing below the
cells that were summed, the subtotal appears in bold as "1201 Total"
and the amount of the subtotal.  Next is "1202 Total," and so on.

Is there a slick way to then collapse the list so that only a list of
subtotals information is displayed?

Thanks,
0
ed9369 (20)
2/20/2009 3:02:56 AM
excel 39879 articles. 2 followers. Follow

6 Replies
1118 Views

Similar Articles

[PageSpeed] 1

Look at the outlining symbols to the left of the worksheet window.

You'll see those +'s and -'s that expand/contract particular groups.

Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...

These "buttons" can be clicked to show or hide levels.

If you don't see those outlining symbols, check:
Tools|Options|View tab|check outlining symbols.

The Hun wrote:
> 
> Using WindowsXP, Excel 2002
> 
> After running the Subtotal command, with subtotals showing below the
> cells that were summed, the subtotal appears in bold as "1201 Total"
> and the amount of the subtotal.  Next is "1202 Total," and so on.
> 
> Is there a slick way to then collapse the list so that only a list of
> subtotals information is displayed?
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12004)
2/20/2009 3:21:50 AM
On Feb 19, 7:21=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Look at the outlining symbols to the left of the worksheet window.
>
> You'll see those +'s and -'s that expand/contract particular groups.
>
> Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
>
> These "buttons" can be clicked to show or hide levels.
>
> If you don't see those outlining symbols, check:
> Tools|Options|View tab|check outlining symbols.
>
> The Hun wrote:
>
> > Using WindowsXP, Excel 2002
>
> > After running the Subtotal command, with subtotals showing below the
> > cells that were summed, the subtotal appears in bold as "1201 Total"
> > and the amount of the subtotal. =A0Next is "1202 Total," and so on.
>
> > Is there a slick way to then collapse the list so that only a list of
> > subtotals information is displayed?
>
> > Thanks,
>
> --
>
> Dave Peterson

Excellent and clearly stated reply.  Thank you.

But there's a wrinkle in my problem that I didn't realize till now.
Even though Outlining Symbols is checked in Tools / Options, they
aren't displayed.  This is why:

Col A has entries for projects:  "Red," "White," Blue."
Col B has entries for account numbers:  1201, 1202, 1203...and so
on...
Col C has entries for various dollar amounts.

1.  I first do a Sort on Col A to group all entries by color.
2.  I then Filter the list to choose only "Red," (or "White" or
"Blue").
3.  I then do a Subtotal on that Filtered "Red" list.

The Outlining Symbols aren't displayed.

The Outlining Symbols will only display when Subtotal is run on the
whole list -- a list that has not been Filtered.

Is there a work-around for this?

I'd like to be able see the collapsed Subtotals of the Filtered list.
Maybe there's a whole different approach that I'm not thinking about
to get the information I want.

Thanks,
0
ed9369 (20)
2/20/2009 3:59:16 PM
First, I would never use data|subtotals and data|Filter|autofilter on the same
range.

The formulas inserted by =subtotal() ignore the rows hidden by the autofilter. 
I think it's better to drop the autofilter and just use the subtotals.



The Hun wrote:
> 
> On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Look at the outlining symbols to the left of the worksheet window.
> >
> > You'll see those +'s and -'s that expand/contract particular groups.
> >
> > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> >
> > These "buttons" can be clicked to show or hide levels.
> >
> > If you don't see those outlining symbols, check:
> > Tools|Options|View tab|check outlining symbols.
> >
> > The Hun wrote:
> >
> > > Using WindowsXP, Excel 2002
> >
> > > After running the Subtotal command, with subtotals showing below the
> > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> >
> > > Is there a slick way to then collapse the list so that only a list of
> > > subtotals information is displayed?
> >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson
> 
> Excellent and clearly stated reply.  Thank you.
> 
> But there's a wrinkle in my problem that I didn't realize till now.
> Even though Outlining Symbols is checked in Tools / Options, they
> aren't displayed.  This is why:
> 
> Col A has entries for projects:  "Red," "White," Blue."
> Col B has entries for account numbers:  1201, 1202, 1203...and so
> on...
> Col C has entries for various dollar amounts.
> 
> 1.  I first do a Sort on Col A to group all entries by color.
> 2.  I then Filter the list to choose only "Red," (or "White" or
> "Blue").
> 3.  I then do a Subtotal on that Filtered "Red" list.
> 
> The Outlining Symbols aren't displayed.
> 
> The Outlining Symbols will only display when Subtotal is run on the
> whole list -- a list that has not been Filtered.
> 
> Is there a work-around for this?
> 
> I'd like to be able see the collapsed Subtotals of the Filtered list.
> Maybe there's a whole different approach that I'm not thinking about
> to get the information I want.
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12004)
2/20/2009 5:22:39 PM
On Feb 20, 9:22=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> First, I would never use data|subtotals and data|Filter|autofilter on the=
 same
> range.
>
> The formulas inserted by =3Dsubtotal() ignore the rows hidden by the auto=
filter.
> I think it's better to drop the autofilter and just use the subtotals.
>
>
>
> The Hun wrote:
>
> > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Look at the outlining symbols to the left of the worksheet window.
>
> > > You'll see those +'s and -'s that expand/contract particular groups.
>
> > > Look up to the top of those outlining symbols and you'll see 1, 2, 3,=
 ...
>
> > > These "buttons" can be clicked to show or hide levels.
>
> > > If you don't see those outlining symbols, check:
> > > Tools|Options|View tab|check outlining symbols.
>
> > > The Hun wrote:
>
> > > > Using WindowsXP, Excel 2002
>
> > > > After running the Subtotal command, with subtotals showing below th=
e
> > > > cells that were summed, the subtotal appears in bold as "1201 Total=
"
> > > > and the amount of the subtotal. =A0Next is "1202 Total," and so on.
>
> > > > Is there a slick way to then collapse the list so that only a list =
of
> > > > subtotals information is displayed?
>
> > > > Thanks,
>
> > > --
>
> > > Dave Peterson
>
> > Excellent and clearly stated reply. =A0Thank you.
>
> > But there's a wrinkle in my problem that I didn't realize till now.
> > Even though Outlining Symbols is checked in Tools / Options, they
> > aren't displayed. =A0This is why:
>
> > Col A has entries for projects: =A0"Red," "White," Blue."
> > Col B has entries for account numbers: =A01201, 1202, 1203...and so
> > on...
> > Col C has entries for various dollar amounts.
>
> > 1. =A0I first do a Sort on Col A to group all entries by color.
> > 2. =A0I then Filter the list to choose only "Red," (or "White" or
> > "Blue").
> > 3. =A0I then do a Subtotal on that Filtered "Red" list.
>
> > The Outlining Symbols aren't displayed.
>
> > The Outlining Symbols will only display when Subtotal is run on the
> > whole list -- a list that has not been Filtered.
>
> > Is there a work-around for this?
>
> > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > Maybe there's a whole different approach that I'm not thinking about
> > to get the information I want.
>
> > Thanks,
>
> --
>
> Dave Peterson


Thanks.  I would like to be able to not filter the list, but for each
group -- Red, White, Blue -- the account numbers repeat, meaning that
Red will have entries for 1201, 1202, 1203 and so on, and White and
Blue will also have entries for 1201, 1202, 1203 and so on.

For me to get Subtotals of only Red 1201, 1202 and so on, I have to
filter out White and Blue so that their entries for 1201, 1202, 1203
are not included in the Subtotal.

I'm not trying to use this as a substitute for full-on accounting,
just to track some daily expenses very quickly and have the basic info
without having to wait 7 to 10 days for the accountant to give me the
info, which by then is far too late.  I just want to get some basic
subtotals very quickly, daily.

It sounds like I've reached a limitation in Excel, and that I'll have
to separate Red, White and Blue without relying on Filter to do it.

Maybe the best work-around is this:
1.  Separate Red, White and Blue with a Sort,
2.  then insert a new Header Row for each group,
3.  then do a Subtotal for each group separately, one at a time

Outline Symbols will then appear for each group, separately, one at a
time.

Again, thanks for your replies.  I appreciate the fact that you didn't
just say "go to this link," or "do it with a ____(whatever the command
is)____."  Half the time, those links don't address the question and
simply waste everyone's effort, and the "whatever command" type of
reply usually leaves us asking WHERE that command, is and HOW we begin
using it.

Thanks,

0
ed9369 (20)
2/20/2009 6:39:23 PM
You may want to look at pivottables.  If you're just looking for summaries, they
may be what you want.

And if I have a class of data I want to exclude, I'll often add a helper column
that evaluates to Keep or Don't Keep.

Then I can sort/filter/pivot (as a page field) by that helper column.

And even if you don't like links, here are a bunch about pivottables.

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/assistance/2002/articles/xlconPT101.aspx

The Hun wrote:
> 
> On Feb 20, 9:22 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > First, I would never use data|subtotals and data|Filter|autofilter on the same
> > range.
> >
> > The formulas inserted by =subtotal() ignore the rows hidden by the autofilter.
> > I think it's better to drop the autofilter and just use the subtotals.
> >
> >
> >
> > The Hun wrote:
> >
> > > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > Look at the outlining symbols to the left of the worksheet window.
> >
> > > > You'll see those +'s and -'s that expand/contract particular groups.
> >
> > > > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> >
> > > > These "buttons" can be clicked to show or hide levels.
> >
> > > > If you don't see those outlining symbols, check:
> > > > Tools|Options|View tab|check outlining symbols.
> >
> > > > The Hun wrote:
> >
> > > > > Using WindowsXP, Excel 2002
> >
> > > > > After running the Subtotal command, with subtotals showing below the
> > > > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> >
> > > > > Is there a slick way to then collapse the list so that only a list of
> > > > > subtotals information is displayed?
> >
> > > > > Thanks,
> >
> > > > --
> >
> > > > Dave Peterson
> >
> > > Excellent and clearly stated reply.  Thank you.
> >
> > > But there's a wrinkle in my problem that I didn't realize till now.
> > > Even though Outlining Symbols is checked in Tools / Options, they
> > > aren't displayed.  This is why:
> >
> > > Col A has entries for projects:  "Red," "White," Blue."
> > > Col B has entries for account numbers:  1201, 1202, 1203...and so
> > > on...
> > > Col C has entries for various dollar amounts.
> >
> > > 1.  I first do a Sort on Col A to group all entries by color.
> > > 2.  I then Filter the list to choose only "Red," (or "White" or
> > > "Blue").
> > > 3.  I then do a Subtotal on that Filtered "Red" list.
> >
> > > The Outlining Symbols aren't displayed.
> >
> > > The Outlining Symbols will only display when Subtotal is run on the
> > > whole list -- a list that has not been Filtered.
> >
> > > Is there a work-around for this?
> >
> > > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > > Maybe there's a whole different approach that I'm not thinking about
> > > to get the information I want.
> >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson
> 
> Thanks.  I would like to be able to not filter the list, but for each
> group -- Red, White, Blue -- the account numbers repeat, meaning that
> Red will have entries for 1201, 1202, 1203 and so on, and White and
> Blue will also have entries for 1201, 1202, 1203 and so on.
> 
> For me to get Subtotals of only Red 1201, 1202 and so on, I have to
> filter out White and Blue so that their entries for 1201, 1202, 1203
> are not included in the Subtotal.
> 
> I'm not trying to use this as a substitute for full-on accounting,
> just to track some daily expenses very quickly and have the basic info
> without having to wait 7 to 10 days for the accountant to give me the
> info, which by then is far too late.  I just want to get some basic
> subtotals very quickly, daily.
> 
> It sounds like I've reached a limitation in Excel, and that I'll have
> to separate Red, White and Blue without relying on Filter to do it.
> 
> Maybe the best work-around is this:
> 1.  Separate Red, White and Blue with a Sort,
> 2.  then insert a new Header Row for each group,
> 3.  then do a Subtotal for each group separately, one at a time
> 
> Outline Symbols will then appear for each group, separately, one at a
> time.
> 
> Again, thanks for your replies.  I appreciate the fact that you didn't
> just say "go to this link," or "do it with a ____(whatever the command
> is)____."  Half the time, those links don't address the question and
> simply waste everyone's effort, and the "whatever command" type of
> reply usually leaves us asking WHERE that command, is and HOW we begin
> using it.
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12004)
2/20/2009 7:16:10 PM
I had some luck with the following procedure.

Insert a column just to the right of the Column B.  Type the formula A1&B1.  
This will create Red1201, Red1202 Blue1201, etc.  Now when you do Data 
Subtotals select the new column for the "At each change in" and you should 
have what you are looking for.
-- 
Frank K


"Dave Peterson" wrote:

> You may want to look at pivottables.  If you're just looking for summaries, they
> may be what you want.
> 
> And if I have a class of data I want to exclude, I'll often add a helper column
> that evaluates to Keep or Don't Keep.
> 
> Then I can sort/filter/pivot (as a page field) by that helper column.
> 
> And even if you don't like links, here are a bunch about pivottables.
> 
> 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/assistance/2002/articles/xlconPT101.aspx
> 
> The Hun wrote:
> > 
> > On Feb 20, 9:22 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > First, I would never use data|subtotals and data|Filter|autofilter on the same
> > > range.
> > >
> > > The formulas inserted by =subtotal() ignore the rows hidden by the autofilter.
> > > I think it's better to drop the autofilter and just use the subtotals.
> > >
> > >
> > >
> > > The Hun wrote:
> > >
> > > > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > Look at the outlining symbols to the left of the worksheet window.
> > >
> > > > > You'll see those +'s and -'s that expand/contract particular groups.
> > >
> > > > > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> > >
> > > > > These "buttons" can be clicked to show or hide levels.
> > >
> > > > > If you don't see those outlining symbols, check:
> > > > > Tools|Options|View tab|check outlining symbols.
> > >
> > > > > The Hun wrote:
> > >
> > > > > > Using WindowsXP, Excel 2002
> > >
> > > > > > After running the Subtotal command, with subtotals showing below the
> > > > > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > > > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> > >
> > > > > > Is there a slick way to then collapse the list so that only a list of
> > > > > > subtotals information is displayed?
> > >
> > > > > > Thanks,
> > >
> > > > > --
> > >
> > > > > Dave Peterson
> > >
> > > > Excellent and clearly stated reply.  Thank you.
> > >
> > > > But there's a wrinkle in my problem that I didn't realize till now.
> > > > Even though Outlining Symbols is checked in Tools / Options, they
> > > > aren't displayed.  This is why:
> > >
> > > > Col A has entries for projects:  "Red," "White," Blue."
> > > > Col B has entries for account numbers:  1201, 1202, 1203...and so
> > > > on...
> > > > Col C has entries for various dollar amounts.
> > >
> > > > 1.  I first do a Sort on Col A to group all entries by color.
> > > > 2.  I then Filter the list to choose only "Red," (or "White" or
> > > > "Blue").
> > > > 3.  I then do a Subtotal on that Filtered "Red" list.
> > >
> > > > The Outlining Symbols aren't displayed.
> > >
> > > > The Outlining Symbols will only display when Subtotal is run on the
> > > > whole list -- a list that has not been Filtered.
> > >
> > > > Is there a work-around for this?
> > >
> > > > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > > > Maybe there's a whole different approach that I'm not thinking about
> > > > to get the information I want.
> > >
> > > > Thanks,
> > >
> > > --
> > >
> > > Dave Peterson
> > 
> > Thanks.  I would like to be able to not filter the list, but for each
> > group -- Red, White, Blue -- the account numbers repeat, meaning that
> > Red will have entries for 1201, 1202, 1203 and so on, and White and
> > Blue will also have entries for 1201, 1202, 1203 and so on.
> > 
> > For me to get Subtotals of only Red 1201, 1202 and so on, I have to
> > filter out White and Blue so that their entries for 1201, 1202, 1203
> > are not included in the Subtotal.
> > 
> > I'm not trying to use this as a substitute for full-on accounting,
> > just to track some daily expenses very quickly and have the basic info
> > without having to wait 7 to 10 days for the accountant to give me the
> > info, which by then is far too late.  I just want to get some basic
> > subtotals very quickly, daily.
> > 
> > It sounds like I've reached a limitation in Excel, and that I'll have
> > to separate Red, White and Blue without relying on Filter to do it.
> > 
> > Maybe the best work-around is this:
> > 1.  Separate Red, White and Blue with a Sort,
> > 2.  then insert a new Header Row for each group,
> > 3.  then do a Subtotal for each group separately, one at a time
> > 
> > Outline Symbols will then appear for each group, separately, one at a
> > time.
> > 
> > Again, thanks for your replies.  I appreciate the fact that you didn't
> > just say "go to this link," or "do it with a ____(whatever the command
> > is)____."  Half the time, those links don't address the question and
> > simply waste everyone's effort, and the "whatever command" type of
> > reply usually leaves us asking WHERE that command, is and HOW we begin
> > using it.
> > 
> > Thanks,
> 
> -- 
> 
> Dave Peterson
> 
0
FrankK (2)
2/21/2009 11:42:00 PM
Reply:

Similar Artilces: