Learn How To Use Pivot Tables In Numbers By Example

Hi, this is Gary with MacMost.com. Let's take a look at using Pivot Tables in Numbers with some practical examples. 

MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to MacMost.com/patreon. There you could read more about the Patreon Campaign. Join us and get exclusive content and course discounts. 

Pivot Tables have been around in Numbers for almost a year now. But I'm still getting a lot of questions that tell me a lot of people aren't using them yet. Let me show you some practical examples of how you can use Pivot Tables with your spreadsheets. So here's something a lot of people use Numbers for to simply catalog things that they own. For instance you may have a catalog of all the books in your collection. So here is a sample list of about a 1000 books. I put the Title, the Series, the Author, and other information. Now let's say we want to know some basic things about this collection. For instance how many books are there per genre. You can see there's Fiction, Classic, Science Fiction, and so on. Now you can calculate the total number of fiction books by using functions like Sum If. But then to calculate that for all of the different genres, figuring out how many different ones there are, putting that into another table, could take a lot of work. But it is easy to do if you have a Pivot Table. 

So to create a Pivot Table just select any cell or any part of the table you want to start with. So in this case my book collection table. Now I'm going to click this Pivot Table button here. That will create a Pivot Table in a separate sheet. So I have one sheet here called Database. If I click this it's going to create another one called Book Collection Pivot. It's an empty Pivot Table right now. It tells me to go to Pivot options over here and you could see it conveniently goes to Organize Pivot Options. Now I can create the Pivot Table from the columns in the original database. This is why it is super important to have a nicely formatted table that contains just one thing. In this case records of all the books I have. I haven't added extra rows with different calculations and data that is not related to this. I have one Header Row and it has all of the headings. Then every other row is a record of a book that I have. There are no blank rows or anything like that. 

Once you have a table that is nicely built like this you can do lots of things with it including these Pivot Tables. So in this case I'm going to answer the question how many books do I have per genre. So the first thing I want to select here is Genre. It's automatically going to add that as a row here. I'll see each genre listed here on the left. Now I want to count how many books I have in each genre. So what I want to do is go down here to where it says Values. I want to add something to that. Now since I'm just counting I don't really need to add anything specific there. I can just add Genre again. I'm going to drag it here to Values and drop it in. I'm going to get a count. You can even see it says right here, Genre Count All. I can change that by clicking the i button here and I could summarize by Sum, Average, Maximum, Minimum, and all that. The only thing that makes sense here in this Pivot Table is to actually count them. So I'm going to do Count All and it's going to tell me how many books I have that are Classics. How many that are Fantasy. How many that are Fiction. I can easily Sort By this column just like a regular table. I'll click here. I'll sort values and I'll sort Descending. So it is going to put the largest number at the top. So I can see Fiction, Classics, Fantasy, Young Adult. These are all the top ones in this list. 

Now my next question is, how many books do I have per Format? So instead of having Genre here I'm going to take that away and then with that empty I'm going to Add, Format and I'll add to Rows and I'll drag Format here to Values as well. Now I'll get a count, the same kind of thing except now it's doing it by Format. So you could see I've got 576 paperbacks, 269 hardcover. 

What if I wanted to do both? Well, I could take Genre and Format. If I check Genre it's going to put it as the Columns here. So now I can cross-reference this. So I can see, for instance, hardcover Classics are 15. I don't have to view it this way. I could drag genre down here to Rows. Now it is everything with Format being the first column, Genre being the second. If I wanted to reverse that I simply swap these. I'll just drag this to the top. Now its Genre and Format. You could see these little disclosure triangles here.  So when I look at Classics, for instance, I could see I've got everything revealed. Box Set, eBooks, Hardcover, and so on. In addition at the bottom I have Classics Total and the total number there. I could click the Disclosure Triangle there and compact Classics into one thing. I can actually Option Click and it will take everyone and shrink it and just give me that actual total number for Format. So I see, for instance, all of the Classics I've got but if I want to see Classics by Format I can open it up. Really handy. I can get a lot of information about my collection just by using this Pivot Table. 

Now let's remove these and let's look at Authors. I'll add Author here to Rows and I'm going to drag Author, as well, down to Value. Now I could see which authors I have the most books. I can Sort, Descending and I could see my top authors. Now I have Date and the Date Published column here isn't complete. Some are blank. But I could actually count the dates. If I do that here let's put Date as a row. You could see it automatically shows me Years. If I click here you could see there's a Group By and Group By will allow me to do things like Year or Month. So I could, for instance, see how many books I have that were published in October. Let's go and drag Date down here also to Values and you could see that 363 books were published in October. Which is an odd thing to want to know. More likely you're going to want to know, say, by year. Now I could see each year and I can Sort them. You could see that there are 32 books that I have that were published in 2005. 

Now can you combine Pivot Tables with Graphs? Yes you can! Let's go and resort this again. I'm going to click on the top of Column A and Sort By Date Ascending. So it goes 1915 to 2015 here and I'm going to select all of these rows, except the last one which is blank. I don't want that row in there. With those selected I can simply go to Chart and then I could select one of these charts, like for instance this one right here. There I go. I have now a graph showing me how many books I have for each year. 

Here's another example. Expenses. Say you kept track of all your work expenses, Date, Category, and the Amount. So let's go and create a Pivot Table that tells us the total for each Category. So with any part of this table selected I'm going to create a Pivot Table here. It's an empty Pivot Table. I'm going to add Category to it. Now what I'm going to do for Values is drag Amount down. So I'm going to put that under Values. It's going to show me the Sum for each one. Now I could go and click here under Sum and summarize by something else. For instance the average expense for each category. Or perhaps I wanted to do Count how many for each category. Or the Maximum for each category. Let's stick with Sum which is what we want. Now we can see the Sum here. If I want I could Sort here and see the top one. What if instead of Catagory I wanted by Month. So what I could do here is add the Date. It's going to add that in Columns here which is going to give me a really interesting chart where I can say, Okay here's April Office Expenses. That's nice. But let's go and move the date down into Rows and then it gives me this in-depth thing where I can look at Meals, say, for each month. Let's just get rid of the Category altogether and now I've just got it broken down by month here and the amount. I can Sort this as well to find the month where I spend the most. 

If I did want to have category by month I could select this and you can see categories going across. Months going down. Not only can you see an individual item like, for instance, transportation for May but I can look easily at the total transportation here for the entire year or I could look at, say, the total amount spent in May. Now on top of that I get the total of all expenses. There is so much information here in this really simple Pivot Table. 

What if I want to flag a month where I spent too much? Maybe there's a limit and you're only supposed to spend $500 per category per month. Is there a way to do that? You would think you would be able to select these and then go to Format, then be able to set conditional highlighting in some way. But you can't. However, there's nothing to prevent you from setting a special format here. Here I've got Data Format Automatic. But what if I were to change this to a Custom Format and then set it up so that normally it's got a dollar sign, it's got a number, its got two decimal places like that. But add a Rule saying IF it's greater than say 500 then do the same format, like that, but just put some text before it. Or let's do Control Command Space and then Add an emoji to it, something like this, put that there maybe with a space and now OK. Notice that because the Format is a little different for anything above 500 it's easy for me to Flag which months and categories actually went above $500.

Here's one last example. This data is about Sales. Say you run a shop or restaurant or something like that. You've got transactions that happen at a certain time. A produce was sold. An Amount. There was a Cost and Sale price which you use to calculate the Profit. Then there was the Salesperson. You could use this in a Pivot Table to get all sorts of interesting data very easily. So let's figure out which product sold the most. So with anything selected in this table I'm going to click Pivot Table. Now I'm there. Let's do Product and then let's drag Product down to Values and we get a Count of that. We can see here that both bananas and peaches sold the most. But what actually had the most profit? So let's drag Profit down to Values here and we can see here that the highest one was peaches. It made the most profit there. Now what if we wanted to see this by Salesperson instead. So I'm going to drag Sold By down to Rows. Put it here at the beginning. It will then show me each person here and each product. Now we could just simply Option click here to compact them all and actually get all these numbers here. I don't have to remove the product from Rows because I don't need to see it. I can just close it up like that. It's kind of handy to have there because if I do want to look at someone and say well they didn't sell as much. Let's take a look at exactly what they were dealing with. I can do that. But otherwise it is out of the way. 

Now let's go and take Date and Time and also add that here. So we now have Sold By and Date and Time and Product. If I were to expand each one you can see how complex it gets. Let's Option Click here to close all those up. So now you could see for Jane each month and how many sales there were. Or I can reorder these. Put Date and Time first. Now I can see January, let's close all these up with Option Click, and you could see here there's January, there's February, there's March. So for January there's how many were sold by each person. It probably is going to be easier to see this though if I use Columns. So let's go and say, keep Date and Time where it is going to do Year and Month. Sold By is going to be columns like that. You can see there are still two values. The Count and the Profit. Let's go and just get rid of the Count, like that. So now I get a clear table here. I could also clear-up Rows by getting rid of Product. Now you can see each person, per month, exactly how much they sold. 

I can easily swap that out for Product by moving Product to Columns and getting rid of Sold By. Now I very quickly shifted to seeing the months and the products like that. It would be great if I could create a chart of that and I can. I'm just going to select these values here, so I'm not collecting any of the totals or anything like that. I'm going to do a chart and I can choose this type of chart right here. I can expand this a bit. I can see there's January, February, March and I could see each product. So pears, for instance, are red. So I could see how pears did per month and also the total. Or with this selected I could have done this kind of chart here that actually groups together each of the months first and then inside each month it groups each of these. If I select this and go to Format here I could scroll down here and there's gaps. I could have gaps between sets be larger so I could more easily see which ones which month like that. 

One tip I've got to give you though is remember if you ever update the data in here by adding rows or changing a value, then in the Pivot Table you have to select it and here, under the Pivot option you have to click this button to Update it.

Here's another tip. If you're using dates and trying to group things by years or months make sure the Date Cells in your Data are formatted for date and time. So here I've got all these dates selected and see under Format Cell I've got them set to Date and Time Format. That works great. But if you have them set to Automatic then you'll find they often don't work. They will look fine here in your table. But when you try to create the Pivot Table and then set it to monthly grouping or yearly grouping it won't work. 

Now here's something I want you to think about after watching this entire thing. I didn't once show you a formula. There were not Functions or anything like that in this tutorial. I use Pivot Tables to do all these calculations without ever having to actually type a function into a cell. The amount of information I got from these Pivot Tables is huge. So if you're not good at using formulas and functions in spreadsheets but you still like to use them to gather and organize data, then Pivot Tables are something you should learn how to use to get more from your information. 

Hope you found this useful. Thanks for watching.