- [Instructor] All right, so a lot of times
when you're working with a spreadsheet,
you have a really large table.
So this is 1,000 rows and you wanna summarize it
in meaningful ways.
So we're going to go over several different ways to do that.
And depending on how your data is structured,
I think at least one of these is going to work for you.
(gentle music)
All right, so the first thing that we're going to do,
let's just say, we want to get a count of the number
of each make of car.
All right, so we wanna say, was 17 Nissans, five Lexuses,
plural of Lexuses.
But let's say we're gonna make a column just called Car Make
and then we'll have another column for the count.
So starting in H2 and going down,
we want a column that has just the unique values
in column B.
So if there's 17 Nissans in here,
we only want Nissan listed once,
and the same with all the others.
So there's a function built just for that.
We'll hit the equal sign like you do before any function,
and we'll just type UNIQUE.
All right, so this takes a column
and returns just unique rows, just like it sounds.
So I want to give it the range of B2 to B1001.
And the fastest way for me to do that
is just to click in B2.
I'm going to hold down the shift key and the control key,
and hit the down arrow.
You could also just type in the range,
or select it with your mouse,
but I think it lets you work a little bit faster
if you use the keyboard shortcuts like that.
And we'll close it off with an ending parentheses,
and hit enter.
And there we go.
So it's going down to row 35 now,
and that's really summarized
because this entire table goes down to row 1,000.
So you have a column of unique values,
and let's take a look at what's going on here.
So if I go into cell H3, and hit delete,
it won't go away.
Because all of these values below H2
are coming from the unique formula that's only in H2.
So it writes all of these cells,
and all of these values are dynamic,
and they're shown in the cell.
But if you want to get rid of them or change them,
you have to do it from H2
where they're being generated from.
All right, and I'll show you that these are dynamic
if I go into, let's say B4, and I delete the Dodge,
but let's gets a blank here,
but there are other Dodges in there, and these are in order.
So when you go down, you see that Dodge still comes up.
But the point of that is just to show you that this array,
that the UNIQUE function is generating is dynamic.
And if your data changes over here, it will update.
Okay, so I'll put that back in,
and now we're going to use the COUNT function.
So we will do COUNTIF,
and there's other IF functions available.
So if we wanted to do the average, you could do AVERAGEIF.
If you could do SOMEIF,
but if you're using a more unique function,
you probably just have to start the formula with an IF,
and then put the function inside of that.
We won't cover that right now,
because most of the time you're just looking for something
like an average or a count or a sum.
So we'll say COUNTIF.
So we will come over and count the values in this column.
If they're equal to, in this case, Nissan,
but before I specify this range,
we're going to do something else to make this easier.
The problem that we're going to try to get around
is if I go ahead in here and I say B2 to B1001,
and I want to count if it's equal to Nissan.
So we'll say H2, close that off and hit enter.
It works here.
And Google Sheets is asking if it should autofill this
all the way down.
If I say, Yes, we encounter a problem.
That's kind of hard to notice at first,
and you might not know what's going on in your sheet.
So this formula, if you double click in it,
you can see the range is right,
because of the dotted orange line around the range.
But if you go down the Lexus,
what has happened is the range that it's looking at up in
has shifted down one.
So every row that you do shifts the range by one.
And in this case, you don't want that to happen.
So you could deal with that by fixing this reference.
But another way to deal with this,
which is a little bit more elegant I think,
is to name the range.
All right, so we want to use B2 to B1001.
Let's go in the upper left-hand corner in the name box,
and we're going to say, you know what,
that's the CAR INVENTORY.
So ignore this popping up down here.
That's because I already did this on another worksheet.
We're going to say car underscore inventory.
So now that we've named that range,
we're going to go back up to the top,
and it's not going to shift on us anymore.
So instead of saying, COUNTIF,
and specifying the range by using the coordinates,
we're going to change this to say CAR INVENTORY.
So when you start typing it,
it knows that the only named range that you have
that start with capital CA is CAR INVENTORY.
You can left click on that, and hit enter,
and it'll update this.
All right, and so now when I go into,
let's just go down a little bit.
We'll double-click in I9,
and the range that it's looking up, hasn't shifted.
All right, so the CAR INVENTORY named range
is always going to be B2 to B1001.
Now, by saying always it's always,
unless you wanna change it.
So if you do go to data and go to named ranges.
You can see it here, and you could edit that
if you needed to change it.
All right, so that's just a simple count.
Let's move on to the next technique
that we can use to summarize this data.
We'll stick with Car Make.
And in this case, let's say we want
a little drop down in here.
So the user of the spreadsheet,
can just easily select what they're looking for.
And then this formula on the right will update.
So we would do that using data validation.
So we want to go to data validation,
and we want the criteria to be a list from a range.
All right, so let's select the range where the values are.
So it's this column B.
Click OK.
You want to make sure you have it checked
that show as a dropdown list in the cell.
You save, we'll come back up to where it is.
And now you get this cool dropdown
where your spreadsheet user can just select something.
And then we're going to build another COUNTIF formula.
So we will COUNTIF,
and we'll remember to use that named range
that we did a few minutes ago, CAR INVENTORY,
and then we'll point the criteria back to H38
in this case.
So it's going to count every instance
with that dropdown value set the way it is.
14, and Hyundai's 40.
All right, the next way to summarize data,
frankly, this is typically the one that I would choose,
but it's creating a pivot table.
So these formulas up here, they're familiar.
So you're used to writing formulas.
This is easy to do.
If you don't know how to use pivot tables,
then you can select exactly what you want.
But pivot tables, while they're not as familiar,
they're probably more flexible.
They're certainly faster to change on the fly,
but they're their own animal,
and they work a lot differently.
So we will show you that.
We'll go to data and we will select pivot table in A1
through F1001.
We're going to put it in the existing sheet,
and we will just tell it to go right here in H40.
Click OK, and then create.
And now you have an empty pivot table.
We need to get rid of this named range menu,
and go back and bring up the pivot table menu.
All right, so let's move to the right a little bit
so we can see this pivot table.
And we'll start with something similar
to what we were doing up here.
And then we'll show you the flexibility.
So let's say that the values again
are just going to be the Car Make,
and it's summarized by COUNTA,
which we were saying before counts all values in the cell.
So there are 1,000, that's right,
but that's only one value.
So pivot tables can be a little bit quirky
to learn at first.
But what you wanna think of
is just what you wanna have in the rows,
if anything you want to have in the column.
So it's a lot of times, it's just these rows.
So in the rows we want to have the Car Make.
We want it to look like it does above,
just the makes of the cars.
So we'll left click on that.
And since we already told it to do COUNTA,
it's doing that for each row.
All right, really easy.
So we'll scroll down to the bottom,
and this is showing a total.
We can turn that on and off with this checkbox.
This is so long that's hard to see, but there it is.
But those totals may mean a little bit more
when we do this next step.
So let's just say,
we wanna look at this by date
and see how many cars are sold on specific dates.
So if you were using these formulas above,
that would pretty quickly get complicated
because you need a couple different levels in your analysis.
But here we can just add another row,
and we can say manufacturer date.
All right, so first this doesn't look right.
At least it does, this is not what I would want to see.
So this does it by car, then by date,
but there's too much noise here.
This is showing every single time you sold a car.
So what you can do in a pivot table is you can right click,
create a pivot date group,
and then just say, "Hey, let's look by quarter by year.
Left click on this.
It'll tell you how many cars you sold in each quarter.
And there's no information to left that has quarters.
It's just backing into that by knowing the exact date.
It's a way to group it.
So you could just choose another grouping if you wanted.
You could just do quarter.
That's going to combine all the years together.
But as you see clicking through these,
once you learn how to do it,
it changes automatically, and it's really quick.
So to give you a better illustration
of how the subtotals come in and the totals,
you see the accurate total here.
If I left click on Show Totals for the date,
those will come out of there.
All right, If you want to repeat the rows on the Car Make,
and just have this fill, you can just check that.
Just depends on how you wanna see it.
And one more thing to show while we have the pivot table
is let's say you only want to show,
let's say cars over a specific price.
You can add a filter.
And right now it says, it's showing all the items.
You wanna filter by condition,
we'll say is greater than,
so only show me the count of cars
that are more expensive than 35,000.
All right, so this is a 12 now.
If I click OK, that went down to a seven.
All right, so be careful with filters.
You have to remember that it's on because it stops the data
from coming into the pivot table at all.
So you just wanna make sure that you're aware
that not all of the data from the source table
is coming into the pivot if you have a filter on there.
All right, so those are the ways
I would typically summarize data,
but we're going to do a little bonus here,
and we're going to show a function called QUERY.
And this is a more advanced function.
I'll just show one little usage of it,
but we'll type equals and type out QUERY.
And it runs a query language.
So if you do happen to be a database administrator,
this may be really easy for you.
I am not, but that's okay. (chuckles)
So I wanted to know the range,
we're going to say CAR INVENTORY again,
and now it wants to know the QUERY.
So this query language
is not the native spreadsheet language.
A very powerful function,
but it's also a steep learning curve.
But once you learn it,
it can provide a completely different way to analyze data
that could in some instances be faster
and more flexible than neither the two previous options.
So we'll use the query language here.
We will say SELECT,
scroll over to the left a little bit here so you can see.
All right, so we'll select column B.
And then once we selected that,
we will say that we want to do a count of column B.
We wanna also group it by B,
and you'll see what that means.
We'll close this off with the parentheses in,
sorry, I just realized that we needed
to have started this with a quote.
Yeah, that looks better, turned everything green.
And then we end it with a quote,
and then a parenthesis, and we'll hit enter.
And there we go.
So it really quickly returned a list of the cars,
and the counts.
So this is similar to the UNIQUE function,
where if you go into a cell below it,
and you hit the delete key,
it doesn't get rid of it
because it's all being written out of the function
that's in cell 573 in this case.
All right, so next, we're gonna dive a little deeper
on pivot tables,
and have linked to a full tutorial
so you can learn everything you need to know about them.
We'll see you in the next video.
Thanks for watching.