Welcome to Excel
Basics number 4. This is our introduction to
Excel Video Project Number 4. And in this video,
we're going to see how to create summary reports
with the amazing PivotTable feature and this
SUMIFS functions. Now this is part of
our Office 2016 Series, and this is video number 16. Here are all the topics
we're going to cover. And the goal in
this video is simply to create summary
reports and see how to do it with a PivotTable
and SUMIFS functions. That's what it'll look
like for our SUMIFS, and that's the
finished report when we do it with a PivotTable. Now I want to go to our website,
people.highline.edu/mgirvin. Here's our class 216. Here's Excel. Here's all the video
links and files. These files are also
available directly below the YouTube video. But I want to click on the PDF. And if you're not
downloading these PDFs and printing them
out and keeping them, you're not going to
get the free book because all these
handouts together are an amazing free
book about Excel. I'm going to click
on this link, and it will open in our browser. And I want to scroll
down here and just remind ourselves of what Excel can do. Broadly speaking, Excel
can make calculations and perform data analysis. Now the calculations
we've already done in this class
in video project 1, we saw how to create
a formula like this to calculate percentage grade. We did a formula like
this in video number 2 to calculate net income. And here, we use the average
function in video number 1 to calculate the average. Those are examples
of calculations we can make in Excel. Now we haven't seen how
to do data analysis yet, so this will be our first
example of data analysis. And that simple few
words there describes exactly what data analysis is. We're simply converting raw
data into useful information. So in this video, we'll
have this data set. This is our raw data, lots
of little bits of data, dates, region, sales reps, sales
with lots of different sales transactions we've had. We need to convert this raw data
into some useful information. Our useful information is going
to be a regional sales report. You can see each one
of the regions and then the total sales for each
one of those regions. This is how we're going to
do it with SUMIFS function. And here's how we do
it with the PivotTable. Now again, these notes
are pretty awesome. You should be downloading
them and reading them along with watching the video. Now I've already
downloaded Excel file. And so I want to go over to
the sheet SUMIFS or PivotTable. Now there's our data set. And here's a little start to our
regional report for our SUMIFS. And then we'll create
our PivotTable down here. But before we do that,
we want to go over to the sheet Data Analysis
and just remind ourselves. You hear the word data analysis
or sometimes the current phrase you hear is business
intelligence. Both of those phrases simply
mean go from the raw data into some useful information. Now the useful
information, as we'll see in this video
and next video, could be all sorts of
different things from this one single data source. In this video again, we're
creating a regional sales report. Now we have to go
over to the sheet Proper Data Set because when
you're dealing with raw data, you've got to make sure
that the raw data is in a proper data set. Here's the three
things that define a proper data set in Excel. And there's a visual here. In the first row, you have
what are called column headers or the proper name
is field names. Date, region, sales rep, sales-- all in the first row. Now notice, why do we have
to have field names or column headers? Because you have to
have a name at the top that tells the user what
to put in this column. Only region goes in this column. Only date goes in this column. And of course,
sales rep and sales. Field names always
in the first row. And then we have
something called a record. For us, it is a transaction. We have one sale here
made on 10/20/2017 in the Northwest region. Sales rep who made
the sale with Gigi. And the amount of
the sale was $484. So these are called
records or transactions. Later in the class,
we'll see other records like a record with all
the employee information. But for us, this
first example-- this is simply a record of one sale. The final thing that has to be
true about a proper data set is that you have to have
completely empty cells all the way around the data set. You cannot accidentally type
a note here like this and then whatever you have. It is touching the data
set, so it violates our rule for what a proper data set is. So I'm going to click escape. Now we have empty cells
on the right and below. But wait a second. There's no empty
cells over here. Yeah, yeah. No problem. You can have the actual
Excel row headers or column headers touching the
data set but no data in the actual cells. 1, 2, 3. Field names in first row. Records in the rest of the rows. Empty cells all the way around. All right, now we're going
to go over to the sheet SUMIFS or PivotTable. There's our proper data set. Now we're going to create 1,
2, 3 different reports here. We're going to create a regional
sales report, a daily sales report, and then a sales
report for the sales reps. All right, so
we're going to have our first example of creating a
summary report using the SUMIFS functions. The first thing we
have to do is actually look through the region
column and figure out what regions there are, how many
different regions that we have. Now this is a small data
set, so we can simply look through and notice that
we have West, Northwest, and Southwest. So if we're creating a
summary report with the SUMIFS functions, we're actually
going to have to type out the three different regions. So I'm simply going to type. And I better type
them exactly the same as they appear in
this proper data set. And remember, no spaces. We saw in the last video, if you
mistype Excel cell formulas-- and for us, the SUMIFS-- will not work correctly. So I type Northwest, enter,
Southwest, enter, and then West, enter. Now I already created
the titles at the top of each one of our
columns, region and total. Now I'm going to click here
F2 to put it in Edit mode. I'm going to type a space
and say, Total Sales. Enter. Now we can create
our total formula using the SUMIFS functions. SUMIFS, just like
we saw last video. Now the difference between
how we're going to use SUMIFS this video and
last video is last video, we never copied the
formula anywhere. Here, I want to be able
to copy it down and have each one of the rows calculate
the correct Region Total. So sum range, that's the
column with all the sales. So I highlight all the sales. I come over, and I'm going
to hit the F4 key to lock that because as I copy
this formula down, I do not want that blue
range to move as I copy. Now I remember with SUMIFS, it's
not going to add everything. It's only going to
add if for this row, the region is Northwest. For the next row, the
region is Southwest. The final row, region
will be equal to west. So we have our sum
range, and we locked it. Now we type a comma to
get to the next argument. Criteria range. Well, remember, we need to pick
out all the Northwest regions. So absolutely in
criteria range, we need to give it
the entire range. Or in our case, it's a column
with all the possible regions. I also need to lock this, so
I'm going to hit the F4 key. So as I copy that formula down,
that orange range doesn't move. Now comma to get to criteria 1. And I simply click
on the region. That is a relative
cell reference. And that formula
will do the trick. Close parentheses. Control-Enter because I want
to put the formula in the cell and immediately do
something to it. Now I want to add
some number formatting before I copy it down. So I'm going to use Control-1
to open up Format Cells. I want to choose currency. I would like to change
the decimals to zero because we don't have any
pennies in our data set. The symbol is dollar sign. I like that minus
as my negative. I click OK or hit Enter. Now I'm going to copy this down. I point to my fill handle. And when I see my angry
rabbit, I click and drag. Any time we copy a formula, we
go to the last cell and hit F2. That is absolutely beautiful. The blue range and orange
range are absolutely locked. And the lavender purple range
is totally looking at West. Now if we think about
how we might mistakenly do it some other way-- someone might in a cell
manually eye it and then click on the cell for the West. And then this cell for the West
and this cell for the West, adding them all together. That would take much too long. Another mistaken way to do this
is if you sorted the columns and then added a sum at
each change in the region. The problem with that
method, of course, is yes, it takes
longer than this. And if you ever sorted it
by one of the other columns, the totals would
be totally wrong. So the SUMIFS functions
to create a summary report like this is quite an
amazing and easy formula. I'm going to hit Enter. Now I want to do the SUMIFS
for two more reports. And then we'll look at
our PivotTable option. Now I already
typed out the dates here and the sales rep name. So I simply come
to the top cell. Equals SUMIFS. The sum range, those
are all the numbers I potentially want to add. So I highlight the
entire sales column. Remember, you're
in full edit mode as long as the dancing
ants are moving. I want to get the correct range. I have to come over
and F4 to lock that. So as I copy the formula
down, the range does not move. Comma, criteria range 1. Well, this time,
it's not region. We're going to highlight
the date column. Now I'm going to mistakenly
forget to lock this because all of us are human. And we will all do
this at some point in creating summary reports. And we'll see how to fix it. Comma, now the criteria. I simply need to give
it the individual date. By giving it that
individual date, I'm telling SUMIFS to only
add the sales for that one day as a relative cell reference. Now I can close parentheses. Control-Enter. Control-1 to add some
number formatting. Currency. Same thing here. I want 0 decimal places. Click OK. And now I got to show you
a great trick for copying formulas. Normally, we take
our cursor and when we see our angry rabbit
on top of our fill handle, we click and drag. Now I'm immediately going
to Control-Z. A faster way to do it, as long as you
have data to the left, you can simply double-click. I double-click with
my anger rabbit. And instantly, it
copied it down. Now it knew how to copy down
because as soon as I saw an empty cell, it knew to stop. Always when we
copy a formula, we go to the last cell and hit F2. And sure enough,
we're verifying using our color-coded rangefinder. And we can see that
we forgot to lock it. See, if we don't
lock it, it moves. We copied it down five cells,
so the range moves five cells. Now what we have to do
to fix it is Escape. Go back to the top cell. F2. And I need to lock it. Now what you do not want to
do is just click like that because if you hit the F4
key, it'll do just A15. You also don't want
to go like this and mistakenly highlight
just part of it. The safest way to
edit a range or edit an argument in a function is
to click on the ScreenTip. So I click on criteria range 1. And when I click,
it automatically will highlight everything. Then I can simply use
the F4 key to lock it. Now it looks like the
formula is correct. I have sum range locked,
criteria range 1 locked, and criteria as a
relative cell reference. Now I Control-Enter to put
the formula in the cell. And keep the cell selected
because I immediately want to double-click
and copy it down. Now I going to the last
cell and hit F2 to verify. And look at that. All the ranges are
working perfectly. All right, so we've
created two summary reports, one for the
region, one for the day. One more. We need a sales rep report. I'm going to click in cell O15. Equals SUMIFS. For the sum range, I'm going
to highlight the entire sales column. F4 to lock it. Comma. Criteria range. Remember, we're trying to add
for each one of the sales reps in each one of the rows here. So for criteria range, I'm going
to go highlight the Sales Rep column. I'm highlighting every
single sales rep. When I get the correct range,
I come up, F4 to lock it. Comma. Criteria 1, that is a
relative cell reference. Close parentheses. Control-Enter. Control-1. One Currency, no decimals. Click OK. Now I can double-click
and send it down. Immediately go to the
last cell and hit F2. A lot of you are in this
class getting a grade, and the tests come up. And there's some huge
proportion of people on tests in this
class that create a summary report like this. And they didn't lock
the cell reference. And they lose a lot of points. Now if you're not in a class,
you're out working in your job. The problem is you'll
create the wrong summary report with the wrong numbers. So always, just as
an automatic reflex, when you copy the
formula to any range-- whether down, to the side,
or in a rectangular range-- go to the last cell
and F2 to verify. All right, now what we did here
is we created summary reports. If I click right
in Southwest, F2. There is the SUMIFS. What it did was SUMIFS
actually went through for every row in
the Region column. It asked, is the region
for the first record equal to Southwest. False. So it didn't use that number. It went down to
every single record. Only when it found Southwest did
it say true and then go and get the number and use it to add. So for Southwest, it
only found a few of them. There is another one. There's another one. And another one down there. Escape. When we did the date-- let's do this one right, F2. SUMIFS went through and looked
for 10/23 in the date column. It did not find any 10/23's. So it didn't add
any of the numbers. It totally worked. Escape. Over here, F2, SUMIFS. It knew it was
trying to find Chin. So it went exactly through
this column one row at a time. Only when it saw Chin, did
it get the associated number. That June number right there
is not used in that calculation right there. All right, so we saw SUMIFS to
create three summary reports. Now we want to see how
to do the same three reports with a PivotTable. Now the very first thing we
do when we create a PivotTable from a proper data set is
to click in a single cell in our data set. It doesn't matter which single
cell, but select one cell. And now we go up to
the Insert ribbon tab. And in the Tables group,
there's our PivotTable button. I click, and this opens up the
Create PivotTable dialog box. Now there's two parts, 1, 2. The first part is
where is your range. It will always guess correctly
if you have a proper data set with empty cells
all the way around and you've selected
a single cell. So that means the only step
we really need to worry about is step 2, where do you
want your PivotTable? I'm going to put
it on this sheet. So I click Existing worksheet. Click in location. I'm going to drag the Create
PivotTable dialog box off to the side. And I'm going to try
and click in F23. I see the destination
cell for my PivotTable. Now I can click OK. There's our blank PivotTable. And over here, this is that
PivotTable Fields task pane. And look at that. 1, 2, 3, 4. Just like we have over here. 1, 2, 3, 4. The field names in the
first row will always show up in what is called
our Field List over here. And this is amazing
because remember, we need to get a
unique list of region and then add the
sales for each region. But with a PivotTable,
as compared to SUMIFS, watch how easy this is. I'm going to check the region. And just like, that
down in the rows area, I see the field region. And over here, I got a list
of exactly one of each item. Now this has a special name. This is called a unique
list or a distinct list. Now I want you to
think about that. With a PivotTable,
all we did is check. And it went through
this entire column and got exactly
one of each item. Remember, up here with SUMIFS,
we had to do it manually. And we risked mistyping an extra
space or something like that. So that's pretty amazing. But it gets more
amazing than that. This is our number
field with our sales. So if I check it,
it automatically goes down to the values area. The values area is
the calculation area of our PivotTable. And over in the
PivotTable report, instantly, it
calculated the total for each one of our regions. Now the next step in creating
our PivotTable report is noticing that row
labels is not a good label. We actually want it
to say region there. Now there's a default
layout for a PivotTable. And we want to change it. So we come up to our new
PivotTable tools, Analyze or Design ribbon tab. We're going to go to
Design, over to Layout, and click the dropdown
for Report Layout. And we either want to show
an outline or tabular. By default, it comes in compact. And that one doesn't
show your field names. I'm going to select
show in tabular. And just like that,
we have the field name at the top and our gray lines. The next thing
that we want to do is we want to add number
formatting to the values area of our PivotTable. Now the way we add number
formatting to the values area in a PivotTable is
different than how we would add it to cells in our spreadsheet. Cells in our
spreadsheet, we would highlight all the
cells and use Control-1 to open up Format Cells. Notice all the different tabs. But that's for cells. We're in a
PivotTable, so we have to do it in a different way. We don't highlight everything. We simply click in
on cell anywhere in the values area,
not in the rows area, but actually where
our calculation is. Then, with one
cell, right-click. Do not select Format Cells. If we do that, we
see all of the tabs, and that's not what we want. When we're in a
PivotTable, we actually want to add number
formatting to the field. So right-click. And there it is. Only in a PivotTable
will this option show up, number formatting. So for example, right-click. You can't find number
formatting because that's not a PivotTable. Ready, right-click,
number formatting. And there it is. You can tell for sure
that you're in the format the field in the values area
dialog box because there's only one tab, the number tab. Now I'm going to
select currency. And we don't have any
pennies, so I'm not going to show any pennies. That will make the
report less cluttered. Dollar, negative
sign is OK, click OK. And just like that, we've
added number formatting to the actual sum of sales. Now our report is finished. And look at that. Just like the SUMIFS, that
calculation right there-- $3,117. That's the total
amount for Southwest. Internally, the PivotTable
did exactly what the SUMIFS did, right? It looked through the
Region column, only picked out the records that had
Southwest, and then only got the numbers associated
with each Southwest sale. Then it added. So at its essence, what
a PivotTable does is it makes a calculation with
some condition or criteria. And for this report
and the other three we'll make in this
video, we're only adding with a single condition. In our next video, we'll see how
to use the PivotTable to make calculations with more than
one condition or criteria. All right, so that's
our first report. Let's create our second report. I'm going to go over and
click in any single cell, up to Insert, PivotTable. It always guesses right because
we have a single cell selected in our proper data set. Existing, click in location,
and I'm very carefully going to select cell J23. Click OK, or hit Enter. There's our same field list. And because this is
our second PivotTable, it shows the same field
list, but nothing is checked. Now instead of
checking, I'm going to show you how to drag
fields to different areas in the bottom part of our
PivotTable Fields task pane. Now the reason that
dragging is better than checking is
next video, when we start dragging multiple
fields down to different areas, if you use the check
box, it doesn't always get sent to the right
part of the PivotTable. So the dragging
method will always get your field to the
correct part of your report. Now we don't want region
like in our last one. We want to drag date. Now I'm going to click and drag. You can see my move cursor. Click and drag. Notice the no symbol. Over here is no. But as soon as we get
to one of these areas, we're allowed to drag it. You can see that green bar. You can also see
the little icon. There's blue representing
the rows area. Over here, there's blue
representing the columns area. I'm going to drag
it down to rows. And instantly, we get a unique
list of dates in our data set. Now notice, it really did
give us a unique list. There are no sales for 10/23. And so it's not listing it. Now I'm going to go up and
drag sales down to values. Now notice, sales
is a number field. So when I drag it
down to values, the default calculation is sum. Next video, we'll see
how to change that. But just with a few
clicks, I already have the actual totals
for each one of the days. Now I want to get
rid of row labels. So I go up to PivotTable
Tools, Design, over to Layout. Report layout, dropdown,
show in tabular. There I have the field name. Click in a single cell. Right-click. Number formatting. I'm going to select currency,
show 0 decimals, click OK. And there I have my
daily sales report. Each one of the
calculations here is a calculation made with a
single conditional criteria. Internally, PivotTable looked
for 10/24 in this date column. It found all of the 10/24's,
added all of these numbers. Notice just like last video,
if I highlight any numbers anywhere in my spreadsheet and
look down to the status bar, I can verify that the sum
total is correct for 10/24. Now let's create
our third report. I'm going to click in
any one single cell. Insert PivotTable. There's my create PivotTable. It got it right because
we have a single cell in our proper data set. We want existing location. I'm going to select N23. Click OK. Here's our PivotTable
Field task pane. I'm going to drag the
field sales rep down to the rows area in our
PivotTable Fields task pane. Instantly, I see a unique list. I still am amazed by that. It literally went
through this whole column and picked out one of each. Now for the calculation. Click and drag sales
down to values. We don't like row labels. So we come up to Design,
and Layout, Report layout, show in tabular. We need to add some
number formatting. Right click. Number formatting. Currency, 0. Later we'll see how to
use different formattings like accounting. But currency, 0 decimal places. Click OK. And there's our finished
sales rep sales report. Now look at this. We did one, two
regional reports, one, two daily sales report, and
two sales rep sales reports. Now if we compare and
contrast either the SUMIFS to our PivotTable-- well, the SUMIFS required that
we type out each one of the row area conditions or criteria. It definitely takes
longer, and we run the risk of a typing error. Then we had to create our
formula and copy it down. With the PivotTable, we
had, with just a few clicks, the ability to get
our unique list and make our calculation with
each one of those conditions or criteria. The one drawback
to a PivotTable, which isn't too
much of a drawback, is that if any of the
source data changes, we would have to refresh
manually the actual PivotTable report. Whereas with formulas, as
soon as the data changes, our reports will
instantly update. So I'm going to come to the
second record in our data set for Northwest, Gigi, 10/20. Northwest total, 10/20, Gigi. Select the cell, and
I'm going to type a big number like 5,000. I have not hit Enter. But I want you to notice what
happens right there and there as soon as I hit Enter. So are you ready? Enter. Instantly, that updates. I don't see it updating here. 10/20 updated. I don't see it updated here. Gigi's total totally
reflected the change, but not down in the PivotTable. Now it's easy enough to change. You come to the PivotTable,
right-click, refresh. And just like that,
there's the total for Northwest, 10/20, and Gigi. Now I'm going to
Control-Z to undo that. Control-Z to undo that. And now we can see,
I undid the refresh, and then I undid the
actual data change. So we have some SUMIFS
and a PivotTable feature to create these types
of summary reports. Each one has their places. In general, it's
faster and easier to use PivotTable
on proper data sets to create our summary reports. However, there are definitely
reporting situations where you want reports
to instantly update. And in that case, you
would use formulas. All right. We saw how to use
SUMIFS to create a summary report, PivotTable
to create a summary report. We very importantly defined
what a proper data set is. And for our first
time, we saw how to convert raw data
into useful information. And that's our definition
of data analysis. All right. If you like this video, be
sure to click that thumbs up, comment, and sub
because there's always lots more videos
to come from Excel is Fun including our
next Excels basics number five, where we'll do a lot
more with pivot tables. All right, we'll
see you next video.
