LMM53
Level 1 (4 points)
Apple Music

Q: Is there a way to collapse and expand subcategories in Numbers?

I would like to have a detailed budget that can be collapsed to allow an overview using the same spreadsheet.  I'm visualizing the way Quicken uses categories in reports - you can see, for example, the entire amount spent in a given time period on Housing, and then expand the category to see each individual transaction under the category housing. 

 

What I'd like to do on my budget is similar in concept.  For example, I'd like to have:

 

Equipment

     Type 1

     Type 2

     Type 3

     Type 4

 

With one group of people, I'd like them to see the overall $ amount for Equipment, but then also have the ability to expand that category to see how the $ is allocated for each of the subcategories.

 

Can I do something like this in Numbers?

iPhone 6s, iOS 9.1

Posted on Sep 11, 2016 4:04 PM

by Yellowbox,Solvedanswer
Yellowbox Yellowbox
Level 6 (10,455 points)
Mac OS X
A:

Hi LMM,

 

Add another column. I called it "Show?"

You can hide this column later.

Cell A1 is a checkbox.

Screen Shot 2016-09-12 at 3.42.27 PM.png

Formula in D2 (and Fill Down)

=IF(A$1,"Show","Hide")

Now apply a filter to the table

Screen Shot 2016-09-12 at 3.41.00 PM.png

 

Screen Shot 2016-09-12 at 3.43.35 PM.png

Screen Shot 2016-09-12 at 3.44.31 PM.png

Hide Column D if you wish

Screen Shot 2016-09-12 at 3.46.16 PM.png

 

Regards,

Ian.

Posted on Sep 11, 2016 10:47 PM

Close
LMM53

Q: Is there a way to collapse and expand subcategories in Numbers?

  • All replies
  • Helpful answers

  • by Yellowbox,Solvedanswer

    Yellowbox Yellowbox Sep 11, 2016 10:47 PM in response to LMM53
    Level 6 (10,455 points)
    Mac OS X
    Sep 11, 2016 10:47 PM in response to LMM53

    Hi LMM,

     

    Add another column. I called it "Show?"

    You can hide this column later.

    Cell A1 is a checkbox.

    Screen Shot 2016-09-12 at 3.42.27 PM.png

    Formula in D2 (and Fill Down)

    =IF(A$1,"Show","Hide")

    Now apply a filter to the table

    Screen Shot 2016-09-12 at 3.41.00 PM.png

     

    Screen Shot 2016-09-12 at 3.43.35 PM.png

    Screen Shot 2016-09-12 at 3.44.31 PM.png

    Hide Column D if you wish

    Screen Shot 2016-09-12 at 3.46.16 PM.png

     

    Regards,

    Ian.

  • by t quinn,

    t quinn t quinn Sep 12, 2016 7:08 AM in response to LMM53
    Level 5 (5,002 points)
    Mac OS X
    Sep 12, 2016 7:08 AM in response to LMM53

    Hi LMM53,

     

    Like Ian, I think this is a natural for filters. Here is my take using the personal budget template as a jumping off point.

    The checkbox in the header would, like in Ian's solution, unhide all sub categories. The checkbox at the category heading would only reveal sub categories.

    Screen Shot 2016-09-12 at 7.55.26 AM.png

    F3= IF(OR(B$1,B$2),"show","hide")

    This would be filled down to F4 and additional "Auto" sub categories.

    Screen Shot 2016-09-12 at 8.01.55 AM.png

    F5= IF(OR(B$1,B$5),"show","hide")

    Filled down to "entertainment" subs.

     

    This is a little fiddley as you need to adjust the formula for each category. If Ian's solution works for you it would be much quicker to implement.

     

    quinn

  • by LMM53,

    LMM53 LMM53 Sep 12, 2016 12:41 PM in response to t quinn
    Level 1 (4 points)
    Apple Music
    Sep 12, 2016 12:41 PM in response to t quinn

    Thank you both Ian and Quinn.  I think this is going to work.  Clearly, Numbers is more powerful than meets the eye.  How would I have figured this out using only the Help files?  It seems like I have not know exactly what something is called to be able to find it in the help files?  Is there some other resource for things like this?  (Not that this support community isn't just awesome!)

  • by Yellowbox,

    Yellowbox Yellowbox Sep 13, 2016 1:09 AM in response to LMM53
    Level 6 (10,455 points)
    Mac OS X
    Sep 13, 2016 1:09 AM in response to LMM53

    Hi LMM,

    Is there some other resource for things like this?

    I find the Function Browser useful.

    Type = in a cell to bring up the Function Browser.

    In quinn's example with the OR function, you could click on Logical & Info

    Screen Shot 2016-09-13 at 6.00.45 PM.png

    Or simply type or into the Search Box

    Screen Shot 2016-09-13 at 6.02.52 PM.png

    More help is here: Menu > Help > Formulas and Functions Help

    Screen Shot 2016-09-13 at 6.04.37 PM.png

     

    Also, have a look at Template Chooser for ideas. Quinn has shown how to adapt this Template:

    Screen Shot 2016-09-13 at 6.08.20 PM.png

     

    Happy Numbering!

    Ian.

  • by t quinn,

    t quinn t quinn Sep 13, 2016 7:06 AM in response to LMM53
    Level 5 (5,002 points)
    Mac OS X
    Sep 13, 2016 7:06 AM in response to LMM53

    Hi LMM,

     

    This solution was developed, as I remember, by members on this forum in response to a forum question. It is a creative application of the Numbers app.  One of the reasons I come here is to puzzle out different ways to use the program in specific situations. Another is that I have learned a lot about how to apply the capabilities of Numbers in interesting ways. Browsing this forum is a great way to sharpen your understanding of the app even if the question you are reading about is not a current issue for you.

     

    Poking around the function browser is usually involved when I am trying to find a solution. Templates are great to see specific solutions to common situations. I don't really know of other resources, books, etc.. Poking around seems to be encouraged on this platform.

     

    quinn