- [Instructor] All right, when you're using Google Sheets,
a lot of times you want to summarize
your table of data by date.
So I have a table here of sample data,
goes down to 1,000 rows, and we're going to do
a series of three different questions
and answer those different ways.
And the first question that we're going to ask
is we just want to know the total sales dollars by month.
All right, so the first way that we're going to try
to answer that is we're going to take our mouse
and we're just going to click anywhere in this table of data
to let Google Sheets know what we're looking at.
And you come over and left click on the Explore button.
So the Explore button is supposed to use AI
to analyze the data and it already tries to help you out
with this graph that does nothing.
This graph actually shows that I randomly generated
these numbers, so it may show something if it was real data,
but to scroll through here, you can just see
that it's trying to draw some inferences for you,
but what we want to do, we wanna stick
to the original three questions that we're going to work on.
And the first one is discerning the total sales by month.
So what you have to do is you have to change that question
into something the Spreadsheet understands.
So the word total isn't very clear, at least it isn't to me,
I don't know if that's the number of sales
or the dollar amount of sales.
So we're going to use the word sum instead.
So that's an addition instead of a count.
Sum of, and then I'll use the word price
because that corresponds to this header,
so it'll know that I want the dollar amount.
So let's say sum of price and then by month.
So Sheets is aware that there's one column in here
that's full of dates, so if I use the word month,
Sheets knows that I'm operating on dates,
so it should be able to pick that out.
When I hit the Enter key, it does a little bit of thinking
and actually does return the right answer.
So you can get this in a formula or a chart.
We want a formula for what we're doing,
so we will leave it on formula.
We'll left click on Show Formula,
and we'll drag this into our sheet, right?
And we'll say, that's good enough for the month.
Hopefully, you know that the one is January,
the two was February, et cetera,
but we will at least change these
to look like dollar values.
So we'll left click on Format As Currency,
get rid of those decimal points, never goes.
So let's do a quick check for completeness.
We will sum this column.
Sheets is suggesting the range G3 through G14, that's right.
So we'll click Tab and it comes up with,
I need to format that 45 million.
I left click on column D,
look in the lower right-hand corner
that sum it's 45 million.
So it's appears as if we're cooking with gas at this point
and the first thing that we wanted to do,
total sales by month, we did with the Explore button.
Now we'll do another one,
which is total number of cars sold by quarter.
So this would be, well, let's just come up and talk about it
as we type it into the Explore function.
So we'll select the data again and we will say,
I want the total in this case,
but I'm not talking about addition this time,
I'm talking about counting, so we will use the word count.
And you can count any of these columns, right?
I want each one of them to just count as one.
So conceptually, I think the easiest one
is to count the car makes, so we're saying one Nissan,
one Lexus, one Dodge.
So we'll say count of, and then we'll type car make,
and we'll say by, again, so it knows how we want it
to be sorted and we'll just try the word quarter
to see if that works.
Hit Enter and Explore was smart enough
getting to know what I meant by quarter.
So it says quarter one, two, three, four,
we'll click on Show Formula again, and we'll drag this
into our spreadsheet and that's the count.
So this should be 1,000 because we have 1,000 rows,
let me highlight it,
go in the lower right-hand corner, and we're at 1,000.
So that was pretty easy, right?
So the Explore function, that's two out of three,
let's try the third one, scroll down a little bit here.
And I keep putting myself in the wrong place over here.
And let's say now that we want to know
the total sales dollars by day of the week,
so we'll go back to Sum of price
by day of week and press Enter.
And in this case, it can't read my mind correctly,
I want to see down here, if let's say it's a chart Monday,
Tuesday, Wednesday, Thursday, right?
But it's just doing specific days.
If I click on the Formula option, it's doing the same thing.
Actually, at first I thought it did it right,
because it only went to seven, but if you scroll down,
it's probably going to go all the way to 31.
So that's the day of the month if you asked me
not day of the week.
If you try to change this to say, weekday
it still doesn't get it right.
All right, so the Explore option is batting two out of three
on answering these questions.
This is not bad, if you wanna try to use it, go ahead.
I think the key with using it as we talked about
is getting the right words, so sum, count, average,
and then using the names of the row headers
for what you actually wanna look at.
Let's move on to the second option.
I find this option to be more reliable
than the Explore function.
Learning curve's a little bit higher,
what we're going to be using is pivot tables.
If you're not familiar with how to use them,
a link to a video on that in the upper right-hand corner,
slow tutorial on it, but you should be able to get
what you need, at least for the purposes of this video,
just by watching what we're going to do here.
What we want to do is summarize this with a pivot table.
So we'll go to Insert Pivot Table and the pivot table
wants to know where the data is.
So we will click on Select Data Range
and these guesses are usually pretty good.
We'll say it's cars, A1 to D1001, that's right.
So we'll select that, say, Okay and then it needs to know
where to put the table, make sure you check existing sheet.
Well, if you wanna do it the way I'm doing it, do that,
we're going to put in an F2 and click, Okay and then Create,
and you have a blank pivot table here.
We'll go back to the question and it's the sales by month.
So for the values, like to start with the values
conceptually, so we can kind of work backwards
where we want to end up with the price.
And the summarization guess of sum is right in this case
because we just want the total dollar amount,
but we want it by month.
So here's where it gets a little bit tricky,
let's think about the fact that we want the month values
coming down this column, so each one of those is a row.
So we will add to the rows, the manufacturer date,
and it's picking up the formatting that we did
in the last step and then not having the formatting below.
So we're going to left click on column G,
go to format and clear the formatting, which didn't work.
So we'll just keep going and we'll fix that when we're done.
So we have one more step.
We do have the dates here and we have the data that we want
for the values, but the dates aren't summarized yet.
So what we can do though, is right click on them,
create pivot date group, and just pick month.
All right, so it even gives us the name of the month
instead of the number.
If you ask me this, this beautiful table,
exactly what we wanted and we're done
for question number one.
Now the next one we wanted to get the total number
sold by quarter, so that's going to be easy.
Let's take a shortcut here.
We'll highlight this existing pivot table.
We'll go up to Copy, we'll go below
and we'll just paste the entire table below
to get a headstart so you don't have to recreate the wheel,
right click on these date groups.
And we will change it from month,
which is checked right now to quarter, right?
So even puts a Q before the quarter
which looks kind of cool, Q1, Q2,
and the last thing that we want to do,
we don't want the total dollar sales.
We wanted the count for this question,
so we'll go to the values.
We can leave it on price, you can count the price also.
Each line is going to have one price and we'll say count,
and it's returning 1,000.
Here's one last thing here.
What you need to do is just take the dollar signs off.
So we'll just format that as a number
and let's get rid of those decimal points.
All right, so we've answered the second question
pretty easily with the pivot table.
And now for the third question, and this is the one
that Explore couldn't get right and what we want to do
is the total dollar sales.
So we want these values, want 45 million of them,
but we want it by the day of the week.
So let's start with this pivot table, we'll copy it.
In this case, I'll just do Control + C on my keyboard
and Control + V as in Victor for paste,
because we're halfway there.
We have the right values, which is the sum of the price,
but we're doing it by month here.
So let's just change pivot date group
And what we're looking for here
is at the day of the week as an option, right?
And it is, these are very thoughtfully chosen options here,
'cause it has something called quarters,
it has day of the week,
so they know what you're looking for.
Left click on day of the week and there it is,
nice and easy, same sum 45 million.
So we've answered all three of the questions
with a pivot table.
The last option that we'll go over is the Query function.
So sometimes you don't want your data in a pivot table
for whatever the reason you want to generate it
on the fly with a function instead of having a table.
So we will get rid of these two columns
and we'll start over and we're going to generate
this first query the easy way.
We're going to go back to the Explorer function,
ask that original question and use that query
to work through it.
So we're going to say sum of price by month.
We'll take that formula,
drag it into the result.
And this is what we started out with because you get it
from the Explore function,
but you could write this from scratch.
So we'll double click inside of this
and we'll talk about it briefly.
The query function always needs to know
the source of your data, so it's designating this table
and then it needs to know what to return.
So here's where it gets a little bit tricky,
so we'll just pull this apart as we go along,
it's going to return column three,
which is starting from the left here, one, two, three.
Since it's on the same worksheet, it could just say,
C for the letter of the column,
you just leave it as column three,
that way, if you put it somewhere else, it could still work,
but it's using the Month function
and in this query language, it's based on zero.
So January is the number zero,
so it's adding one to everyone.
And then the next column that it's going to return
is the sum of column four, which is the sum of price.
And then we come by and we group it by month,
so that's the rows of that come down
are going to be by month.
And then we're going to label the columns month and sum
and then this last value,
because we started this range at row two,
we're saying there's zero header rows, I'll hit Enter,
where you saw how nice and pretty this looks,
and we can do the same thing
to get the total number sold by quarter,
so we'll go through that one quickly.
All right, it gave the results, we're going to drag this in
and look at this Query function.
And what it changed was you can use the word quarter.
So the SQL language understands quarter
and no longer has to add one to it
because the quarters are one, two, three, four,
they don't start at zero
and we're going to group it by quarter.
We're going to use the Count function instead of Sum
and we'll label it count.
All right, so that's how to do that with a function
for the second question.
Now, the third question was one that the Explore function
could not answer so that one, we can get a little bit more
in depth with the Query function.
This question was the total sales amounts
by day of the week.
So since this Query function is returning the total amounts,
let's start with that.
And these Query functions are a raise.
So they return multiple rows and/or columns,
but they reside in the upper left-hand corner of the range.
So this function lives in F2, but you can see
it's putting a frame around the entire amount
that it's going to return.
And we're just talking about that,
because if you want to repeat this function,
you have to go up into F2 to copy it,
and then we'll place it in F22 and paste
and it's going to write itself out again
to the right and below.
Now you'll notice we had a little bit of problem here
is that there's a blank row above
and that's because we accidentally shifted the range down
when we copied and paste this.
So now the range of saying A22 to D1021,
we could just go in here and correct this by changing
the A22 back to a two and this back to 1,001,
but we could also do this in a more proper way,
we could have done this from the beginning if we wanted to,
select this entire range, and I'm going to call it
car underscore sales.
And I'm using the underscore,
because we want to avoid spaces and named ranges,
but this is called the named range and you do it.
So one of the reasons why is because it will never shift
down car sales, will always start in A1.
So let's go back to this query,
change the range to car sales, left click on that
and then I'm anticipating another change we need to make,
because now we're including the header.
Let's just come to the end of the Query function
and change the zero to a one.
Get rid of this, I can't see what I'm doing.
Zero to a one.
So let's double check our total,
we're at 45 million, but we still haven't answered
the question of sales by day of the week.
We're still doing months,
so let's look at this query formula.
And right now it's still doing month and it's adding one.
So along with month, there's something called
day of the week, excuse me, it's day of week.
Let me bring my reference onto the screen for a minute.
This is Query Language reference provided by Google.
And when you go down and look at the scalar functions,
this is showing you some of the ways
that it can work with dates.
And there is one called day of the week,
so it's going to return the day of the week from a date.
So that's what we want, let's just copy this, day of week.
We'll go back into the query, we're going to change month
to day of the week, day of week,
we'll even change the label.
We'll just paste day of week in there
and let's take out the plus ones
because it starts with a one, not a zero now,
we should be able to hit Enter.
And there we go, the day of the week goes one through seven,
the totals through 45 million.
All right, if you want more practice with the Query function
in this next video, we're going to start
with the basics of it and then work our way through.
Thanks for watching this video, if you wanna see others,
just like it, please subscribe
in the lower right hand corner, thanks.