Using Smart Categories (Pivot Tables) in Mac Numbers

Version 5.2 of Numbers brings a new/old feature, Smart Categories. This is similar to the old Categories feature in Numbers prior to 2013, and similar to Pivot Tables in Excel. Using Smart Categories is relatively simple, however, as you simply group together similar rows and allows you to see totals, averages, counts and other data pertaining to those groups.

Video Transcript: So one of the most requested features in Numbers is something called Pivot Tables. This is a technique used in Microsoft Excel to allow you to visualize data in different ways. You can do that in Numbers using something called Categories prior to 2013. Then it was removed when Numbers was rewritten. But now we have it back in version 5.2 of Numbers there's something called Smart Categories. Here's how it works.
So say we have a table like this. Here it's got a product column here on the left and each row is a record in here. They're not even sorted or anything like that. They're actually sorted by date.  So you can see there's apples, there's oranges, there's bananas. It's all mixed in here. Say we wanted to visualize things grouped together by product. You can do that using Categories.
One way to do it would be to select a table here, click the little reveal Commands or pull-down menu right there, and select Add Category for Product for this column. You could also go on the right side here for Organize. Organize isn't new. Before we had it as Sort and Filter. Now we have Category, Sort and Filter. So under Category you can add a Category and I can pick the Product column. Either way it's going to get you to the same place which is that it's going to group together all of the different products. So you have apples, oranges, bananas, and peaches.
Now you can click here to reveal all the data under this. So here are all the apples. I can reveal oranges. I can reveal bananas and I can close them as well. So that's really handy. Now you can change this by saying instead of grouping by product, let's group by date. Then it's going to put it together in months. So here we've got January, February, and March. It depends upon what type of data you've got. In my example I've got product, I've got months, I've got amount. That kind of thing.
So you can group it together in all sorts of different ways. I could do amount and it will do it by amount instead. I can do it by product and then I can also add another one and say by date. So now you can see I've got it here where all the apples of January, March, and February and the oranges, etc., so they're all grouped together. So you can easily manipulate what you've got here.
Now you can do more than just visualize this, you know kind of sort it because that's all it's really done so far. Sort it. You can actually add some summaries here. So, for instance, under amount here if I click in this little special box notice it creates this special row here. It's between row one and two and it just says apples and telling you this is a group of apples. 
I can click here and click this little button and I can use some special functions here. I can have the total count, for instance. So there's six items. Or I can say the subtotal. So it's 1120. Now notice it's the same for all of these. I can see how many oranges, peaches, and bananas there are. It all adds up for each group. I can do different things. Like I can say the average or the minimum or the maximum. Here I can do the subtotal.
So I've got all of this. I can bring this in and let's do maximum there. It gives me all this great data here that I can kind of visualize by this group. Of course you can dig down and have multiple groups as I was showing before. You can add another category by date, for instance, so that is there. I can say I want to see the subtotal for these as well. So you can see the subtotal for each month.
So this is how Smart Categories work. They should be very useful. They fulfill most of what Pivot Tables would do but kind of in a really nice Numbers way where you can look at it using nice tables inside of sheets, inside of documents. It takes a little getting used to. If you're looking for Pivot tables and you want them to work the same way they do in Excel then this isn't going to get you there because it's going to look different. So you really should learn how to use Smart Categories separately and figure out how you can apply those to use with your data.

Comments: 2 Responses to “Using Smart Categories (Pivot Tables) in Mac Numbers”

Cromie Stephen

6 years ago

Gary, any idea how to order the sub-categories? E.g. I have a wine index and have categories of White and Red; and sub-categories of year to drink. But the years don't appear in order, but random: 2019 before 2018 before 2020. Any way to order them? Thanks.

Gary Rosenzweig

Cromie: The sort should follow the sort placed on that column. So if the dates are in column B, then click on the B heading and choose the Sort to be by date.

Comments Closed.

Using Smart Categories (Pivot Tables) in Mac Numbers

Comments: 2 Responses to “Using Smart Categories (Pivot Tables) in Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information