Summarize Data Easily With Pivot Tables In Numbers

Hi, this is Gary with MacMost.com. Let me show you how you can use pivot tables in Numbers to easily summarize data. 

Now you can use Numbers to create simple spreadsheets to store data. Sometimes you may want to summarize this data by using formulas to add up totals based on the information there. This means using complex formulas or you could use pivot tables which might seem more complex but it is actually simpler. As a matter of fact you can summarize some complex data in just a few quick steps with no formulas at all. 

So as a simple example here, here's a list of products and amounts. These could be sales amounts or inventory or whatever. The idea here is that you want to find the totals for each product. So, for instance, you want to know how many oranges you've got even though here you received 32 oranges and then later on you've received 19 and 15  and so on. Now if you want to sum these up with formulas you have to create formulas that sum up the oranges and then sum up the pears and then you have to search through and find out which other products you've got and create formulas for each one of those. Or you can get more complex and figure out which products you've got with some formulas and then, on top of that, use some SUM formulas to add them all up.

But you can do this with Pivot Tables with very few steps. So with just this table selected I'm going to create a Pivot Table. I'm going to do it, not with the button here in the Toolbar which will create a new sheet with a Pivot Table, but I'm going to use Organize and then Create Pivot Table where here I can select on the current sheet to add a Pivot Table to this one. 

Now since I had this table select first it automatically links that table, called Data, to this new Pivot Table. So all I need to do now is tell the Pivot Table what to summarize. It just shows the two columns here, Product and Amount. I'm going to add them both. When I do I immediately get a useful summary. You can see here that each row is a product. It figures out which products are listed here and shows each one exactly once. Then the second column shows the Sum, the total, for every single row with that product. So immediately I get the summary I want based on this table. I didn't even have to alter anything. But I can go further or modify this if it is not what I want. 

So, for instance, it gives me a grand total here at the bottom. This isn't something I want since these are all different products. So let's get rid of that. Under Organize Pivot Options here if I go down to Rows and click the three dots button next to it I can select Hide Grand Total Row. That gets rid of that. So now this is what I want here. But I would like one more bit of data. I want to know how many times each one of these appears. In other words how many shipments of each of these products have I received. So I've got my rows here which are products and I've got my values here which are the amounts, this column here summarized. It is summarized using a SUM. I can add this column a second time. I'm going to drag amount down here and place it a second time. I can put it after this first column or before it. I'm going to do it right before it, like that. Now you can see I've got it there twice. I'm going to take that first one, click the i button there, and say summarize not by SUM but by Count. So now it is going to count the number of times each one appears. 

Now I can also Sort here if I want to sort by this column here. I can select the little drop down menu here at the top, say sort values, and I can say sort ascending. So I sort by the counts here. Or I can go into this column here and sort values and let's say sort descending. So now I can sort with the largest sums first. The great thing about this is it will update somewhat automatically. I don't have to change anything to update this. So I can add another row here and I'll add ten more apples here in another shipment. Now this doesn't update by itself. I have to select the Pivot Table and then click this button here. You'll watch apples change to 11 and 455 when I do that. You can see it does it here. It will also make me reassert the sort but I didn't have to add any new formulas. As a matter of fact notice that I didn't use formulas at all in doing all of this. So I got some really useful information, perhaps all that I need, without having to type a single formula or understand a single function. 

If you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 

Here's another example.  So here are some test results for students in a class. I've entered these in as records in a database. So instead of having the quizzes going across in different columns I simply have each one as a record. The student's name and their grade. There are many different quizzes here. The same students taking many different quizzes over time. Now if I wanted to summarize this and find out how each student is doing I may have to do another table with some complex formulas. Or with this table selected here I can go to Organize, Create Pivot Table on Current Sheet, and I'm going to add Student and Grade here. It's going to give me each student and it is going to give me the sum of their grades, which isn't useful. What I want is the average. So I'll click the Information Button next to the grade here and I will change that to Average. 

Now I'm going to get all these decimal places here and that's fine because you can format cells in a Pivot Table just like you can in a regular table. So with this selected I'll go to the Format Sidebar here, go to Cell and I'll change it to Number and then change the number of decimals to, let's say, zero here but I could do one if I wanted to. So I get a grade like that. Now I've got the grade average here. Let's say I also want to check to make sure that each student has taken each quiz or that I remembered to record them all. So I will add a second time here, the grade, and I'll put that before and like before I will set that, not to sum here, but to the count. So you can see here each student has taken 7 quizzes so I know I'm not missing any data. No student is missing a grade. I've got the average here and I can sort by the values if I want here to see which students are doing well and which aren't. Then I can see the average grade here for everybody.

Here's a more complex example. Let's say you trade stocks. The tricky thing about trading stocks is you may purchase the same stock multiple times. So just seeing the current value of a stock it is hard to judge how well you are doing because a purchase from a long time ago may be doing well but a recent purchase may not be doing well. So I've got all these different stock trades here. Each one has a recorded purchase date, the price it was purchased at, how much was purchased, and then a simple formula here just to multiple these two together to get the purchase value. Then it is going to use the Stock Function here to get the current price and the current value is again multiplying the amount owned by the current price to get a gain or loss for that particular purchase. But you can see here each stock has multiple purchases. So you kind of want to get an idea of how each stock is doing. How each of your holdings is doing. You can do that with a Pivot Table. 

So with this table selected here I'm going to do like I did before, Organize, Create Pivot Table on the Current Sheet, and I'm going to add just some things here. I'm going to add the stock, so now I get each stock listed once. I'm also going to add the Gain/Loss amount, like that. So this is how much each one has gained and lost summed. So, if for instance I made multiple purchases of Apple Stock, some have gained and some have lost depending upon what I bought. This is the total now reflected for all of those. But I have tons of data here that I can summarize. So as an example I may want to look at the purchase amount here. Let's add that and that is going to sum those all up. So I can see how much of each stock I own. In this case the grand total here is actually very useful. I can see how many stocks shares of everything I own and my total here which includes these negatives here so I know my total holdings. 

So you can see how Pivot Tables in Numbers can actually be simpler than using formulas and help you see things in your data very easily. Hope you find this useful. Thanks for watching.