Collapse/expand buttons in excel spreadsheet

[niamhc]

i'm pretty new to excel sorry if the answaer to this is obvious. My spreadsheet contains a hierarchy and I want to use the +/- collapse and expand buttons so only certain levels are visible at certain times. Can anyone tell me how? 'Help' has failed me!

 

[Argh2]

niamhc,

In Excel, the functionality you're looking for is on the Main Menu Bar. Try Data / Group and Outline / Group.

In Excel help, type 'group and outline'...look at 'about outlining a worksheet.'

-Argh2.

 

[KenWright]

Or 'Data / Subtotals' may be more appropriate depending on your data.

If your data looked this:-

[tt]
aaa bbb ccc
a 73 58
a 62 36
a 37 79
a 11 78
a 83 73
a 14 47
b 87 81
b 43 96
b 88 15
b 95 42
c 57 79
c 14 63
d 52 36
d 66 68
d 33 35
d 84 84
d 63 98
[/tt]

you can select it all, and then do:-
'Data / Subtotals'
'At each change in aaa'
'Use function SUM'
'Add subtotal to - bbb ccc'
Then hit OK.

Your data will now look like this:-

[tt]
aaa bbb ccc
a 73 58
a 62 36
a 37 79
a 11 78
a 83 73
a 14 47
a Total 280 371
b 87 81
b 43 96
b 88 15
b 95 42
b Total 313 234
c 57 79
c 14 63
c Total 71 142
d 52 36
d 66 68
d 33 35
d 84 84
d 63 98
d Total 298 321
G Total 962 1,068
[/tt]

and you will see the option to collapse or expand any of the sections on the left of your sheet, such that with a single click you could now get the following:-

[tt]
aaa bbb ccc
a Total 280 371
b Total 313 234
c Total 71 142
d Total 298 321
Grand Total 962 1,068
[/tt]

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------