How can i group rows and columns of data like MS Excel?
How can i group rows and columns of data like MS Excel?
MacBook Pro (Retina, 13-inch, Mid 2014), OS X El Capitan (10.11)
How can i group rows and columns of data like MS Excel?
MacBook Pro (Retina, 13-inch, Mid 2014), OS X El Capitan (10.11)
Unfortunately numbers falls short of excel as far as the grouping and auto-filtering is concerned. It is a pity because they are the most used excel features. To answer the question of what one is trying to do, it is simply to easily be able to "Collapse" (hide) column or rows, yet, still have all the information in one table. Consequently, in my wife's business she has 3 rows relating to the parents info, 3 relating to the students information, 5 regarding the service and at least 9 columns relating to the billing, and at least 2 with additional financial information. 22 columns is a lot to have in one view. However, the information is never needed at the same time. If the excel grouping possibility existed the maximum number of rows filling your screen would be 12, achievable through 3 clicks (each click hiding a group)
PIerre1412 wrote:
grouping and auto-filtering ... are the most used excel features.
Are you sure about that? Is there some survey that shows they are the most used features?
Filtering is really useful, and it's available in Numbers too.
Grouping I used a little some years ago, but don't know anyone who's used it extensively. There is almost always a way to organize data differently so as not to rely on grouping.
SG
Hi nenshad,
I think you are discovering one of the differences in approach between the two programs. It has been many years since I used Excel so if I want to group rows and columns I create a table for those cells. If I want more than one table on a sheet that is what I do.
quinn
nenshad wrote:
How can i group rows and columns of data like MS Excel?
What do you mean by "group rows and columns of data"? Can you give an example? (I use Excel, but am unsure what you are referring to).
SG
Hi t quinn, That option seems cumbersome 😮. Can you share how you do it?
hi SGIII, in Excel if you have row 1 titled as expenses and a list of expenses in rows 2 till 10. You can select rows 2-10 and click on data and then click group rows. This will add a "+" sign to the left of the row numbers and you can then click on it to contract rows 2-10 and only view row 1 which says expenses or if you wanted a detailed list of the expenses you would again click on the "+" sign and it would expand row 2-10.
nenshad wrote:
hi SGIII, in Excel if you have row 1 titled as expenses and a list of expenses in rows 2 till 10. You can select rows 2-10 and click on data and then click group rows. This will add a "+" sign to the left of the row numbers and you can then click on it to contract rows 2-10 and only view row 1 which says expenses or if you wanted a detailed list of the expenses you would again click on the "+" sign and it would expand row 2-10.
I see that. It's like what used to be called (don't know how many versions ago) "outline."
Numbers 3 doesn't have that (though Numbers 2 had something similar called Categorize).
How about hiding and unhiding rows as needed?
That does basically the same thing as group and is almost as convenient. Select the rows, move the cursor to the row numbers on the left, right-click, and choose from the drop-down menu.
SG
There are some ways to do similar things to Excel's grouping but the correct one to use depends on what you are trying to do, whether you change these groups often, whether you create/remove groups often, etc. A few ideas have been provided already, Here is another:
Use a column that will indicate which rows you would like to show. Set up a filter to show only those rows when the filter is on. In the screenshot below, I used the header column and the word "total" for the filter. The header row will always show, it is not affected by the filter.
EDIT: I haven't used Numbers in a while and didn't realize there is a feature I hadn't used before. Instead of setting up a filter like I showed above, you can right click on a column letter and choose "Filter Table" followed by clicking on which term(s) you want to filter by. You turn it back off by clicking on them again. It basically does the same thing as what I showed above but doesn't require the Filter side panel to be open.
And my first idea is below.
How can i group rows and columns of data like MS Excel?