馃攳
Google Sheets - Summarize Your Spreadsheets by Month, Quarter, or Day of the Week - YouTube
Channel: Prolific Oaktree
[0]
all right when you're using google
[1]
sheets a lot of times you want to
[3]
summarize your table of data by date
[7]
so i have a table here sample data goes
[9]
down to a thousand rows
[11]
and we're going to do a series of three
[13]
different questions and answer those
[15]
different ways and the first question
[17]
that we're going to ask is we just want
[19]
to know the total sales dollars
[22]
by month
[24]
all right so the first way that we're
[25]
going to try to answer that is we're
[27]
going to take our mouse and we're just
[29]
going to click anywhere in this table of
[32]
data to let google sheets know what
[34]
we're looking at
[36]
and you come over and left click on the
[38]
explore button
[40]
so the explore button is supposed to use
[42]
ai to analyze the data
[44]
and it already tries to
[46]
help you out
[48]
with this graph that does nothing this
[50]
graph actually shows that i randomly
[52]
generated these numbers so it may show
[54]
something if it was real data
[56]
um
[59]
there's a scroll here through here
[61]
you can just see that it's trying to
[62]
draw some inferences for you but what we
[64]
want to do we want to stick to the
[66]
original three questions that we're
[68]
going to work on and the first one is
[70]
discerning the total sales by month
[73]
so what you have to do is you have to
[75]
change that question into something the
[77]
spreadsheet understands so the word
[79]
total
[80]
isn't very clear at least it isn't to me
[82]
i don't know if that's the number of
[84]
sales or the dollar amount of sales
[87]
so we're going to use the word sum
[89]
instead so that's an addition instead of
[92]
a count
[94]
sum of and then i'll use the word price
[96]
because that corresponds to this header
[99]
so it'll know that i want the dollar
[101]
amount so i'll say sum of price
[103]
and then buy
[105]
month
[106]
so sheets is aware that there's
[109]
one column in here that's full of dates
[112]
so if i use the word month sheets knows
[114]
that i'm operating on dates so it should
[116]
be able to pick that out and i hit the
[118]
enter key it does a little bit of
[120]
thinking
[121]
and actually does return the right
[123]
answer so you can get this in a formula
[126]
or a chart we want a formula for what
[129]
we're doing so we will leave it on
[131]
formula we'll left click on show formula
[134]
and we'll drag this into our sheet
[137]
right we'll say that's good enough for
[138]
the month hopefully you know that the
[140]
one is january the two is february
[143]
etc but we will at least change these to
[147]
look like dollar values
[148]
so we'll left click on format as
[150]
currency
[151]
get rid of those decimal points
[154]
and there we go so let's do a quick
[156]
check for completeness we will sum this
[159]
column
[161]
sheets is suggesting the range
[164]
g3 through g14 that's right so we'll
[166]
click
[167]
tab
[168]
and it comes up with i need to format
[170]
that
[172]
45 million
[173]
if i left click on column d look in the
[176]
lower right hand corner at that sum
[178]
it's 45 million so it's
[180]
appears as if
[182]
um we're cooking with gas at this point
[184]
and the first thing that we wanted to do
[186]
total sales by month we did with the
[188]
explore button
[190]
now we'll do
[191]
another one which is total number of
[194]
cars sold by quarter
[197]
so this would be well let's just come up
[199]
and talk about it as we type it into the
[201]
explore function
[202]
so we'll select the data again and we
[205]
will say
[206]
i want to total in this case but i'm not
[208]
talking about addition this time i'm
[210]
talking about counting so we will use
[212]
the word count
[214]
and you can count any of these columns
[217]
right i want each one of them to just
[219]
count as one
[221]
so conceptually i think the easiest one
[222]
is to count the car makes so we're
[224]
saying one nissan one lexus one dodge
[227]
so we'll say count of and then i'll type
[229]
car make
[232]
and we'll say buy
[234]
again so it knows how we want it to be
[237]
sorted and we'll just try the word
[239]
quarter to see if that works
[242]
hit enter
[244]
and explore was smart enough again to
[247]
know what i meant by quarter so it says
[248]
quarter one two three four
[250]
we'll click on show formula again and
[253]
we'll drag this into our spreadsheet
[255]
and that's the count so this should be a
[257]
thousand because we have a thousand rows
[260]
let me highlight it
[262]
go in the lower right hand corner and
[263]
we're at a thousand
[265]
so that was pretty easy right so the
[266]
explore function that's two out of three
[268]
let's try the third one
[270]
scroll down a little bit here
[272]
and i keep putting my cell in the wrong
[275]
place we'll go over here
[277]
and let's say
[278]
let's say now that we want to know the
[281]
total sales dollars by day of the week
[284]
so we'll go back to sum
[286]
sum of price
[288]
by
[290]
day of week
[292]
and press enter
[294]
and in this case it can't read my mind
[296]
correctly i want to see down here
[299]
if i let's say it's a chart monday
[301]
tuesday wednesday thursday right but
[303]
it's just doing specific days
[306]
if i click on the formula option
[311]
it's doing the same thing actually at
[313]
first i thought it did it right because
[314]
it only went to seven but if you scroll
[316]
down it's probably going to go all the
[318]
way to 31.
[320]
so that's day of the month if you ask me
[321]
not day of the week
[323]
if you try to change this to say weekday
[328]
it still doesn't get it right
[330]
all right so the explore option is
[332]
batting two out of three on answering
[333]
these questions this is not bad
[336]
right if you want to try to use it go
[338]
ahead i think the key with using it as
[340]
we talked about is getting the right
[342]
words so sum count average
[345]
and then using
[347]
the names of the row headers for what
[349]
you actually want to look at
[353]
let's move on to the second option i
[354]
find this option
[356]
to be
[357]
more reliable than the explore function
[360]
learning curves a little bit higher what
[361]
we're going to be using is pivot tables
[364]
if you're not familiar with how to use
[365]
them i'll link to a video on that in the
[368]
upper right hand corner just a little
[370]
tutorial on it but you should be able to
[372]
get what you need at least for the
[374]
purposes of this video just by watching
[377]
what we're going to do here so what we
[379]
want to do is summarize this with a
[381]
pivot table right so we'll go to insert
[383]
pivot table
[385]
and the pivot table wants to know where
[387]
the data is
[389]
so we will click on select data range
[393]
and these guesses are usually pretty
[395]
good we'll say it's cars
[397]
a1 to d1001
[400]
that's right so we'll
[402]
select that say okay and then it needs
[404]
to know where to put the table
[407]
make sure you check existing sheet
[410]
well if you want to do it 20 i'm doing
[411]
it do that
[413]
we're going to put it in f2 and click ok
[415]
and then create
[417]
and you have a blank pivot table here
[418]
we'll go back to the question it's the
[420]
sales by month
[422]
so for the values i like to start with
[424]
the values conceptually
[427]
so
[428]
we can kind of work backwards so we want
[430]
to end up with
[432]
the price
[433]
and the summarization guess of sum is
[436]
right in this case because we just want
[438]
the total dollar amount
[441]
but we want it by month so here's where
[444]
it gets a little bit tricky
[446]
let's
[446]
think about the fact that we want the
[448]
month values coming down this column so
[452]
each one of those is a row so we will
[454]
add to the rows the manufacture date
[458]
and it's picking up the formatting that
[460]
we did in the last step and then not
[463]
having the formatting below
[465]
so we're going to left-click on column g
[467]
go to formatting and clear the
[468]
formatting
[470]
which didn't work
[473]
so we'll just keep going and we'll fix
[475]
that when we're done so we have one more
[477]
step we do have the dates here and we
[480]
have
[480]
the data that we want for the values
[483]
but the dates aren't summarized yet so
[485]
what we can do though is right click on
[486]
them
[487]
create pivot date group
[491]
and just pick month
[494]
all right so it even gives us the name
[496]
of the month instead of the number
[498]
if you ask me this is beautiful table
[500]
exactly what we wanted and we're done
[502]
for question number one
[505]
now next we want to get the total number
[506]
sold by quarter so that's going to be
[509]
easy let's take a shortcut here we'll
[511]
highlight this existing pivot table
[514]
we'll go up to
[515]
copy
[517]
we'll go below
[518]
and we'll just paste the entire table
[520]
below to get a head start so you don't
[521]
have to recreate the wheel
[524]
right click on these date groups
[526]
and we will change it from month which
[529]
is checked right now
[531]
to quarter
[532]
all right so he even puts a q before the
[534]
quarter which looks kind of cool
[536]
q1 q2
[538]
and the last thing that we want to do we
[540]
don't want the total dollar sales we
[542]
wanted the count for this question
[544]
so we'll go to the values we can leave
[546]
it on price you can count the price
[549]
also
[551]
each line is going to have one price
[554]
and we'll say count and it's returning a
[557]
thousand here's one last thing here what
[559]
you need to do is just take the dollar
[561]
signs off
[562]
so we'll just format that as a number
[566]
let's get rid of those decimal points
[569]
all right so we've answered the second
[571]
question pretty easily with the pivot
[572]
table and now for the third question and
[575]
this is the one that explorer
[578]
couldn't get right
[579]
and what we want to do is the total
[581]
dollar sales so we want these values 145
[584]
million of them but we want it by the
[586]
day of the week
[590]
so let's start with this pivot table
[592]
we'll copy it in this case i'll just do
[594]
control c on my keyboard
[597]
and control v is in victor for paste
[601]
because we're halfway there
[603]
we have the
[604]
right values which is the sum of the
[606]
price
[608]
but we're doing it by month here so
[609]
let's just change pivot date group and
[612]
what we're looking for here is that the
[613]
day of the week is an option
[616]
right and it is these are very
[617]
thoughtfully chosen options here because
[620]
it has something called quarters it has
[622]
day of the week so they know what you're
[624]
looking for
[626]
left click on day of the week
[628]
and there it is
[630]
nice and easy same sum 45 million
[634]
45 million so we've answered all three
[637]
of the questions with a pivot table
[640]
the last option that we'll go over is
[642]
the query function so sometimes you
[644]
don't want your data in a pivot table
[647]
for whatever the reason you want to
[648]
generate it on the fly with a function
[651]
instead of having it in a table so we
[653]
will get rid of these two columns
[657]
and we'll start over and we're going to
[659]
generate this first query the easy way
[662]
we're going to go back to the explorer
[664]
function
[666]
ask that original question and use that
[668]
query
[670]
to work through it so we're going to say
[673]
sum of
[675]
price by a month
[682]
we'll take that formula
[689]
drag it into the result and this is what
[691]
we started out with because you get it
[692]
from the explore function but you could
[695]
write this from scratch so we'll double
[697]
click inside of this and we'll talk
[698]
about it briefly
[700]
the query function always needs to know
[703]
the source of your data
[706]
so it's designating this table
[708]
and then it needs to know what to return
[710]
so here is where it gets a little bit
[712]
tricky so we'll just kind of pull this
[715]
apart as we go along it's going to
[717]
return
[718]
column three
[720]
which is starting from the left here one
[722]
two three since it's on the same
[724]
worksheet it could just say c for the
[727]
letter of the column you just leave it
[729]
as column three that way if you put it
[731]
somewhere else it could still work
[733]
but it's using the month function and in
[736]
this query language
[738]
it's based on zero so january
[741]
is the number zero
[743]
so it's adding one to everyone and then
[745]
the next column that it's going to
[747]
return is the sum of column four which
[749]
is the sum of price
[751]
and then we come by and we group it by
[754]
month so that's the
[756]
rows that come down are going to be by
[759]
month
[760]
and then we're going to label the
[761]
columns month
[763]
and sum
[765]
and then this last value
[768]
because we started this range at row 2
[770]
we're saying there's zero headed rows
[772]
all right hit enter
[774]
we already saw how nice and pretty this
[775]
looks and we can do the same thing to
[779]
get the total number sold by quarter so
[781]
we'll go through that one quickly all
[783]
right it gave the results we're going to
[785]
drag this in
[787]
and look at this query function
[790]
and what it changed was you can use the
[792]
word quarter so the sql language
[794]
understands quarter it no longer has to
[797]
add one
[798]
to it because the quarters are one two
[800]
three four they don't start at zero
[803]
and we're going to group it by quarter
[806]
we're going to use the count function
[807]
instead of sum
[809]
and we'll label it count
[811]
alright so that's how to do that with a
[813]
function for the second question now the
[816]
third question was one that the explore
[818]
function could not answer so that one we
[820]
can get a little bit more in depth with
[822]
the query function
[824]
and this question was
[827]
the total sales amounts by day of the
[830]
week
[831]
so since this query function is
[834]
returning the total amounts let's start
[836]
with that
[836]
and these query functions are arrays so
[839]
they return
[840]
multiple rows
[842]
and or columns
[844]
but they reside in the upper left hand
[846]
corner of the range so this function
[848]
lives in f2
[850]
but you can see it's putting
[852]
a frame around the entire
[855]
amount that it's going to return and
[857]
we're just talking about that because if
[859]
you want to repeat this function you
[861]
have to go up into f2 to copy it
[864]
and then we'll place it in f22 and paste
[867]
and it's going to
[868]
write itself out again to the right and
[871]
below
[872]
now you'll notice we had a little bit of
[874]
problem here is that there's a blank row
[876]
above
[877]
and that's because we accidentally
[879]
shifted the range down when we copied
[881]
and pasted this
[882]
all right so now the range is saying a22
[885]
to d1021
[888]
we could just go in here and correct
[889]
this
[890]
by changing
[892]
the a22 back to a two and this back to a
[895]
thousand and one
[897]
but we could also do this in a more
[899]
proper way could have done this from the
[901]
beginning if we wanted to
[903]
select this entire range and i'm going
[905]
to call it
[907]
car
[909]
underscore sales and i'm using the
[911]
underscore because we want to avoid
[913]
spaces and named ranges
[915]
but this is called the named range and
[917]
you do it so
[919]
one of the reasons why is because it
[920]
will never shift down car sales will
[922]
always start in a1
[924]
so let's go back to this query
[927]
change the range
[929]
to car sales
[932]
left click on that and then i'm
[933]
anticipating another change we need to
[935]
make because now we're including the
[937]
header
[938]
let's just come to the end of the query
[940]
function
[942]
and change the zero to a one
[946]
ugh get rid of this i can't see what i'm
[948]
doing
[949]
okay
[951]
zero to a
[952]
one all right so let's double check our
[955]
total
[956]
all right we're at 45 million but we
[958]
still haven't answered the question
[960]
of sales by day of the week we're still
[963]
doing months so let's look at this query
[965]
formula and right now it's still doing
[967]
month and it's adding one
[970]
so the along with month there's
[972]
something called day of the week
[974]
excuse me it's day of week and let me
[976]
bring my reference onto the screen for a
[979]
minute
[980]
this is square language reference
[983]
provided by google
[984]
and when you go down and look at the
[987]
scalar functions
[993]
this is showing you some of the ways
[995]
that it can work with dates
[999]
and there is one called day of the week
[1001]
so it's going to return the day of the
[1003]
week from a date
[1006]
so that's what we want let's just copy
[1007]
this day of week
[1010]
we'll go back into the query we're going
[1012]
to change month to day of the week day
[1015]
of week
[1019]
we'll even change the label we'll just
[1021]
paste day of week in there
[1023]
and let's take out the plus ones because
[1026]
it starts with a one not a zero now
[1030]
we should be able to hit enter
[1034]
and there we go the day of the week goes
[1036]
one through seven
[1038]
the totals are 45 million all right if
[1040]
you want more practice with the query
[1042]
function in this next video we're going
[1044]
to start with the basics of it and then
[1046]
work our way through thanks for watching
[1048]
this video if you want to see others
[1050]
just like it please subscribe in the
[1052]
lower right hand corner thanks
Most Recent Videos:
You can go back to the homepage right here: Homepage