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.