How To Filter Data By Categories In Mac Numbers

The popular way of filtering out your needed data is to use the filter feature of Mac Numbers. But there is another way to do it with the Mac Numbers category. Sometimes, that is my preferred method.

When I have a table with unsort data, the Mac Numbers category auto sorts the table and puts the lines of the same category in one group. I can easily hide or reveal those groups. The beauty of this feature is that all the groups of different unique categories are visible on the screen. Then I can calculate the total in each group. Or I can copy those categories to have a unique list of values.

I will show you the tricks in the instructions below.

01. How Mac Numbers Category works 

I will use the table below to better explain how the category works. The table has three columns: Name, School Year, and State. I want Mac Numbers to put all the students from the same state in one group. And I want a list of unique states where the students come from. 

In the above table, the students from the same states are not grouped together. The first student Bob is from New York, and the second student Anna is from New Jersey. If I enable the category feature for the state column, I expect to see Bob and Hanna in the same group because they are from New York. 

In order to enable the Category feature on the state column, I perform the steps below:

• Right click on any cell in the table
• Click Show Category Options
• Click Add a Category… drop down menu
• Click State column

Once the Category feature is on, Mac Numbers auto groups the students for me. Now Bob and Hanna stay in the same NY group. Anna and Maya stay in the same NJ group. Etc. And you can hide or display a group by clicking the arrow on each category.

If you can collapse all the groups, the list of group titles on the first column is the list of unique values on the state column. Currently Mac Numbers doesn’t have a function to directly extract unique values out of a column of data. So the category is a trick to build that unique list. 

Perform the steps below the collapse all the groups:

• Right click on the very first column
• Click Collapse All Groups    

In summary, the category feature of Mac Numbers looks at the values on a column. Then it forms a group for each unique value. And it places all the lines with the same unique value in one group.  

02. Group your data with multiple categories 

Apparently you can enable the category feature on multiple columns. So how does Mac Numbers handle multiple categories from multiple columns?

The answer is simple. Mac Numbers can have multiple groups within a group. It is like a parent group can have multiple child groups. To help you visualize this better, I enable the category on both the state column and the school year column. And I got the screenshot below.

The NJ group now has two child groups. They are Junior and Freshman groups. So the next question is “how does Mac Number determine the parent groups and the child groups?”. Mac Numbers relies on the order of the categories on the category list.

Because the State category is above the School Year category, Mac Numbers treats the State category as the parent group and the School Year category as the child group. 

If I put the School Year category above the State category, Mac Numbers immediately changes the data grouping. In the screenshot below, the school year Junior is now the parent group, and the state is now the child group.

In summary, you can enable the category feature on multiple columns. And the order of those columns in the category list dictates the parent and child relationship between the groups. The upper categories become the parent groups. And the lower categories become the child groups.