I am trying to do an outline and group of columns like in Excel. I know I can hide them, but I need a group/outline function like in Excel, so specific related rows can be collapse or expanded in one click. Hide is not an acceptable solution for several reasons.
Is this feature missing from Numbers?
Hi Dave,
Two IF formulas. The logic is convoluted, but it works.
A single cell table called "Filter?" with a check Box.
Check boxes in Table 1 Column B.
Formula in C2 (and Fill Down)
=IF(Filter?::A$1,IF(B2,"Show",""),"Show")
IF "Filter?" is TRUE, then
IF B is TRUE, then "Show" else "" (NULL) else
"Show"
Now apply a Filter
You can hide Column C.
Regards,
Ian.
This seems pretty complex, but I will undertake it if I have to. But, before I try all that, I'm using a Mac with Numbers, but my boss uses a PC with Excel. I have been successful so far, in exporting all my documents to him (using the export feature). My concern is will this transfer over to him using Excel or will it look like gibberish? Can how come it says I can group in the "help" feature and I can even find Group and Ungroup boxes, but can't actually use them?
Hi Kamy,
Group, in the help topic you are reading, says you can group "objects" and applies to "objects" placed on a Sheet in Numbers.
The top row of three objects (the set will be familiar to most Canadians) are individual objects, as evidenced by the selection rectangles around the space taken by each.
Arrange > Group joins the three to act as a single object—clicking on one selects all three. They can be re-sized as a single object, retaining the proportions of all three, and of the space between them, moving the set to a new location, duplicating the single object, etc.
As to your 'survival' question, it depends...
Simple data tables transfer quite well between Numbers and Excel and back again. When you start adding specific formatting and formulas to the mix, the accuracy of the translation depends on whether those formats and functions are supported in both applications, and whether features are supported in the same way on both sides of the divide.
The only formula in my example above is the one controlling the appearance of "show" in cells in column B:
IF(A$2,"show","")
Excel certainly supports IF, but I don't know if it supports checkbox formatting for cells, or if it does, if that formatting is transferable between Numbers and Excel.
If Excel does support checkboxes, will A$2 be sufficient test for the presence of the boolean value TRUE in the cell, or does that have to be revised to IF(A$2=TRUE,... ? Does the translator know that?
Both Numbers and Excel support filtering of rows. Is their support of this sufficiently similar to allow the filtering in one application to be transported to the other intact?
Only testing will tell.
Regards,
Barry
Hi Kamy,
Barry wrote:
Excel certainly supports IF, but I don't know if it supports checkbox formatting for cells, or if it does, if that formatting is transferable between Numbers and Excel.
In Numbers,
Export to Excel as .xlsx or .xls
Reopen in Numbers
I don't have Excel. I think it supports Checkboxes, but they don't survive on Export from Numbers.
Your Boss will have to reformat them. Don't annoy the Boss 😉.
Regards,
Ian.
Thanks Yellowbox and Barry, together your examples made it possible to do what I wanted to do. It's a little crazy, but it worked. Now I just wish Numbers would add a function like Excel to do this without all the work. 😉
Hi Dave,
How is "collapsing or expanding a group of rows" different from "hiding or showing a group of rows"?
Regards,
Barry
Hi Dave,
Numbers doesn't have the equivalent of Excel's group/outline. You can, however, achieve similar effects using filters on an added column or columns. One click will apply or remove a filter. In many cases that's easier than hiding and unhiding rows.
If you can give a few more details of what you are trying to accomplish, someone here can suggest how that can be done.
SG
This is relatively simple example I created in Excel but gets the basic idea across. Lets say I'm looking at a bill of materials;
I want to be able to expand or collapse specific rows with a click of one of the + buttons on the left.
I may even add another column or two in for example under keyboard -> special keys -> Return, Shift, Space bar, etc.
Hi Dave,
Here's a sample, based on your table posted above. The screen shots are all of the same table, with procressively more rows checked:
The checkbox beside 'Touch pad' does nothing, as there no sub headings to show.
Row display is controlled by a filter on column B (which is hidden), set to show only rows where the text in B is "show".
The numbers are in column C
Cell B2 contains the text "show", so it is always shown, and "Laptop" in column D is always visible.
Cells in rows containing a sub heading in column E have a checkbox in column A, and the formula below in column B:
IF(A$2,"show","")
Cells in rows containing a sub-sub heading in column E contain the same formula, with the cell reference changed to name the cell in the row containing their category. The example below, controlled by the "Screen" checkbox in A6, shows the formula in B7, B8, and B9:
IF(A$6,"show","")
With the filter turned off to show all rows, column B unhidden, and the Laptop and Screen checkboxes checked, the table looks like this:
...And with the same boxes checked and the filter turned on, like this:
Regards,
Barry
Excel Function - Group and Outline Rows
