Collapse subtotals?

  • Thread starter Thread starter The Hun
  • Start date Start date
T

The Hun

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,
 
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.
 
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.
Click to expand...

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,
 
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.
 
First, I would never use data|subtotals and data|Filter|autofilter on thesame
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.
Click to expand...


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,
 
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
 
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.
 
Back
Top