- All right, we're using Apple's Numbers spreadsheet,
and we're going to be asking some questions
about this table of data.
And we're going to be using a pivot table
to answer those questions.
And what it does is it takes a larger set of data
and summarizes it in different ways with no formulas.
So what we're going to do, first, we'll look at this table,
and ensure that it's a good fit for a pivot table.
In order for it to work well, you need good headers.
So these are all descriptive, right?
This is the price, this is the location.
And underneath each of those headers,
you need multiple rows,
and it needs to be a similar type of data.
So under Location,
this is just a piece of text that says West, right?
And then you have some South and some North.
And under Price, you have all currency, et cetera.
I want to pay even closer attention
to though is if you have a date column.
So dates only work if your spreadsheet realizes it's a date.
And we have a little hint on this spreadsheet.
Everything shifts over to the right
when Numbers knows that it is a number data type.
So if you had dates that were shifted to left and the right,
let me mess one up real quick.
Huh, that was supposed to be messed up.
The number will shift over to the left.
It's no longer a number,
and you have to find these and fix them
before you use a pivot table.
So let's go back and fix this date.
I don't remember what it was,
but it doesn't matter 'cause it was fake.
And there we go.
So, now we're at a point where the data's ready
to be pivoted, and we'll talk about how to do a pivot table.
When you just do it...
So let's keep the active cell on this table.
And if you go up and click on Pivot Table,
it's going to create it in a new sheet.
But I want to be able to show it to you on the same sheet,
so I'm going to go to Organize,
come down to Create Pivot Table,
and then just choose On Current Sheet.
So when I click on that,
it'll put it kind of on the same screen for us,
so we can talk through it.
I'm going to just pull it down a little bit.
Hmm, yeah, how about right there?
And you get a little window in the middle
that you can click on, it says Pivot Options.
And we will use this window that comes up.
So this is kind of our control center for the pivot table.
And we're going to use this to answer the first question,
which is, how much revenue was generated
from each type of construction material?
All right, so when you look at the table over here,
we have a header called Construction Material.
So these are the types of construction material.
And then Revenue would be the sum of the price, right?
So let's go back to the pivot table.
We'll come over here.
We said we wanted construction material.
And I picture this being down the left-hand side
as a column, so you actually wanna put it in the rows.
So that's a little bit confusing,
but what we're saying is put each value in a row
so it makes a column.
So there you go.
That's your first step.
How much revenue?
So we want to pick Price,
and we're going to drag that down into the Values.
So it's going to land on the rows as well.
But anything in the middle of the pivot table,
where the actual summation in the data is called a value.
And you need to tell Numbers how to aggregate that value.
So you could do different functions,
which we'll do in a minute, but it defaults to sum.
And for this, that's what you wanted.
You just wanted to add up the revenue.
So let's double check it.
It totals to 2,885.
If I come over to the left,
and look at this sum that I had put in on this column,
it's the same.
So you know you're capturing all of the data.
Now let's just show you.
This is called a pivot table for a reason.
So if I get that editor to come back up.
There we go.
If you wanted to pivot this,
you could just take the construction material,
take it from the rows to the columns, bam, same data.
I put it in the columns.
I put it in the row originally
because it's a lot easier to read,
but this is giving you the same answers.
It's just pivoted from the columns to the rows.
Let's put that back 'cause that's ugly.
That looks better, right?
So we've answered our first question.
Let's move on to our next question,
which is what percentage of sales
is being generated by each sales rep?
So let's think about what we're doing here.
We don't care about the construction material anymore,
so we can come over to the rows,
click on that and just click on the trash can
to get rid of that.
But we do care about the sales rep.
So we'll select that.
We'll bring that down to the rows.
So it's populated the sales rep,
but it's kept the sum of the price,
but we're looking for the percentage of sales
for each salesperson.
So what you do here is you come in,
you still want to sum it
because you want the proportion of that total to the whole.
But let's left-click on this.
So what we're gonna, keep it summarized by sum,
but we want to show it as a percentage
of, and these different choices
are, so a percentage of grand total
or percentage of column total.
So we're only working with one column
of data here right now, so that should be the same.
But we'll just click percentage of grand total.
Okay, and they add up to 100, that's good.
And this is answering our question
of the percentage of sales for each salesperson.
So let's go on to another feature.
We talked about dates a little bit earlier.
You have to make sure that they're valid.
So these are all shifted to the right,
so they're good dates.
And now we're going to harness the power of those dates
to let the pivot table do a little something extra
because it knows a little bit more about how dates work.
So we want to say,
how much revenue was generated each quarter?
So let's go in in here.
We're going to change those percentages.
We'll change them back to dollar value.
We don't care about the people anymore.
Poor people.
So let's delete them.
We need a date field in here to summarize it by quarter.
So let's go ahead and get the date field,
and we will put it in the rows.
So it's done something already.
It's assumed that we want it by year,
but that's not what we want.
We want it by quarter, right?
So this spreadsheet has extra powers
where it can, for you, figure out
how to group it by quarter.
So you can do just by quarter.
So quarter one would have all three years in it,
quarter two, et cetera, or by quarter and year.
So let's do that.
And now this has told us how much of the revenue
was generated in each quarter of each year.
Now we'll do one more of these simple variations,
and then we'll kind of do a multi-level analysis,
which isn't that much harder with a pivot table.
It would be if you're using formulas.
And let's say which region has the most transactions.
So you probably know how to do this already.
We'll take date out, we'll put region in.
Will we?
Location, yeah, there we go.
Right, that was easy.
So we have North, South, and West.
And this is really bothering me
that we don't have commas in here.
So let me highlight those.
I'll get off of organizing on the format.
And we will say that the cells
are not automatic, they are currency.
And those will have a thousands separator.
Ah, much better right now that we have that out of the way.
Let's look at what region has the most transactions.
That's what we just did.
But now we wanna see which region
and which sales rep has the most transactions.
So this is, you can already see how pivot tables are easier,
but doing a multi-level analysis with them
is far easier than if you were to just build this
with formulas, and I'll show you why.
Because now, you could just take the sales reps.
So we'll go back to Organize, so we get the Pivot Options.
And Sales Rep is in here.
And there's two ways to do this.
One is you could put it in the Columns, right?
So this is actually still really easy to read.
If you wanna see what Maggie did,
she sold $197 in the North region,
and 91 in the South, right?
The other way that you could do this
is you could take Sales Rep,
and you could just, I said multi-level,
and this is really what I was talking about.
You can do it in the rows,
so it nest that kind of underneath the location,
and you get some interesting functionality here.
So you can look at the North altogether
because it has a total
for each particular person in that region.
And you can also collapse part of this.
So if you really wanted to see the detail in the North
but still see the totals in the South and the West,
you could do it that way.
So it depends on how you want to see it.
If you want it nice and simple,
I'd say have one in the rows one in the column.
If you want to have a little bit more control,
you can do it both in the rows or both in the columns.
All right, and so now we're going to show you filtering,
and we're going to ask a question how much brass was sold
in each quarter by Darren.
So we have three questions here.
I need to have the Sales Rep in here so I can filter by it.
I need to have the, we don't want Location anymore.
We'll get rid of that.
We do want Construction Material 'cause I need just brass.
We'll put it up in the columns for now.
And then each quarter, so we need the Date.
And now what we wanna do,
let's move these down to rows
just to make it not quite so ugly.
I think the one disappeared.
Sales Rep, Construction Material, and we need Date.
Again.
All right, I don't know if that looks better.
So now we're going to introduce the concept of a filter
because we want to look for just Darren and just brass.
So this will be two filters in what you want to do.
And we're in the tab right now called Pivot Options.
We're going to click on Filter.
We're going to add a filter.
First, we will do it on the construction material
because we just want brass.
So we will deselect all of them,
and put a check mark in brass.
So now we're, I'm gonna have to change my question.
Well, no, we'll keep the same question.
The answer's going to be zero.
There so that's my filter, first one.
And now we want to do a filter for the Sales Rep,
and we're going to deselect them all and just select Darren.
Darren really needs to pick up a slack on selling brass
because there was none.
There were none, was none.
So let's select all again and do this just for Rex.
We'll just leave him checked, click out of there.
And we are almost at our answer, right?
So we have just Rex, we have just brass.
Let's come over and change the way in which it's grouped
because we wanted this by quarter.
So we will group by let's take the year out
and just the quarter.
We've answered our question here.
We changed a little bit but then we got an answer
that Rex sold this much brass in each quarter.
Now let's act like we wanted to know the quantity
that he sold, not the total price.
So just the number of sales that he had.
So instead of Sum, we will come down to the Values,
and we will summarize this by Count instead of by Sum.
Right, so there was one sale and quarter one,
one sale in quarter two.
If I come over to the table,
let's check to see if that's right.
We have Rex selling brass once, twice.
♪ Three time a lady ♪
And that's all.
So that is right.
The other thing that you might want to do
is take these totals off in this situation.
They just look redundant.
Let's see if we can do that.
We don't want a brass total, do we?
Yeah, so let's uncheck Show Total Rows.
We did that for the brass, let's do that for the Sales Rep.
And we'll even change our formatting back
because I forced the formatting to make it currency,
but that doesn't work all the time, right?
Because now we're counting.
So we will change the Cell
to Number.
This next playlist will be all about the advanced functions
and pivot tables.
But I'll be honest with you,
I haven't made very many videos about numbers
so these will all be in Google Sheets.
Maybe I'll see you in that next video anyway.
Thanks for watching.
It's good to have you around.