in this next video I want to show you a
very powerful feature that's called
subtotals uh so let's say we have all
these individual
transactions and let's say your boss
requests that you do subtotals by office
or subtotals by destination those a
relatively easy way to make that happen
now it becomes very important here just
like when we saw with the sort and the
filters that your first row of your data
has your field names like that and then
each row after that is a record or a
transaction and each column is a
different field we call this a list or a
table or a database and we can certainly
sort this data there's other videos for
that we can certainly filter the data
there's other videos for that and we
can also do something that's called a
subtotal so if if I wanted to do
subtotals by office we'd have to sort by
office first if I wanted to do SUB
totals by destination we'd have to sort
by destination first first so let's say
I want to uh do subtitles by office so I
want to click on one of the cells in the
office column and we'll do a basic
sort now all the blaines are together
and all the Bloons are together and all
the briners are together and so on it's
a really important step you really want
to sort your data before you do your
subtotals the subtotals are going to be
found under the data menu so we'll pick
on data and then come over here and pick
on
subtotal okay um so then you get this
new
window where it says at each change in
that has to be the same field that you
just sorted which in our case is the
office
field then it says use function and
we're going to click on that pull down
and we'll do a
sum and then where it says add subtotal
to you're going to pick Fields um that
you actually want to subtotal for each
office or whatever you're doing uh you
know whatever you're doing so you can
pick as many as you wanted to I'm going
to pick on destination amount and the
tickets field actually not destination
but amount tickets and total and then I
don't need the commission
field all right so at each change in
that has to be the same field that
you're sorting that you just sorted
otherwise you're going to get uh
unpredictable results
use function we can do a sum a count an
average a minimum a maximum a
product uh count numbers standard
deviation a variance so you can there's
a lot of calculations that you can do as
we can see if you want a basic subtotal
we'll do a
sum and then you pick which Fields you
actually want a subtotal and then you
could pick as many as you wanted to so I
going to click on okay and watch what'll
happen
all right so here we have the Blan
detail and then we have the bla total so
noce how it gave us a total row and then
it did that for each different office
throughout the entire uh listing all the
way down and then if you scroll all the
way down to the bottom we even have
grand totals now it looks like uh that
when you have the pound sign it just
means you have to make that column wider
so I'm going to go up between column H
and column I and you get this black
cross you can either drag the black
cross or you can double click on it and
then it becomes as wide as it needs to
but look at look at what we have here
for each different office we have a
subtotal and we also have a grand total
now let me show you a couple things I'm
going to scroll back up
here notice this new column over here on
the left hand side that's a direct
result of the subtotal feature so if I
pick on number
two that's going to show the sub totals
only if I pick on number
one that's just going to show the grand
total if I pick on number two that'll
show the subtotals only if I pick on
number three that'll show all the
details so that's kind of built right in
where you can expand and collapse the
sections uh you can also collapse the
sections one by one notice how I have
the Blain detail and then here it says
Blain total well I want to come over
here and click on this minus sign and
notice how that collapsed uh The Bling
section uh watch again if I pick on the
the minus sign next to Bloomington
that'll collapse the Bloomington section
if I want to re-expand those then you'll
pick on the plus sign as you can
see let's say we also want to do a sub
count as well as the sub total so watch
How I would do that and and I going see
the sub total and the sub count at the
same time so in this case you're going
to pick on data and then
subtotal all should have to do is just
change a couple things
here uh instead of a sum i' like to have
a
count and then you're going to come down
here you're going to uncheck it where it
says replace current subtotal you see
that
way that way you're going to uh show
both the count and the sum at the same
time I'm going to click on
okay now notice we have the U we have
the count and the sum and we should have
that for each and every uh different
office all the way through the entire uh
listing and then at the bottom I have a
total count and a grand total of the sum
as well right so you can see we can
subtotal uh and include more than one
calculation now um let me show you just
one more thing about the subtotals if I
pick on data and then
subtotal there's another Choice down
here that says page break between the G
uh groups so that means when you print
it each different office or whatever
you're subtitling by would be its own
separate page so you know that that's a
nice little uh feature there when you're
when you're done with your subtotal
you're going to pick on remove all and
then it goes back to normal so that's
your basic subtotals here in Microsoft
Excel