Welcome to Office 2013, class video number 22.
If you want to download this workbook and follow along,
click on the link below the video
or go to our class website.
We're still studying Excel.
This is Excel Basics video number four.
And we want to have our first introduction
to what are called pivot tables.
Now in this Excel Basics class, some of you
may have heard of pivot tables, some of you
may not have heard about pivot tables.
But here's the deal.
The word on the street and out there in the working world
is that pivot tables are really hard to use.
And we're going to see in this video
and later on in the class that they're really amazingly easy
and one of the most powerful features in Excel.
Now there's some notes here.
You can read those later.
We're going to scroll down and look at our example.
Now your boss gives you this data set and says,
hey, I need you to create a daily sales report, which
just means for each one of these criteria we need
to add from the sales column and get a total.
Hey, this sounds just like the last video.
What did we do last video?
We used the SUMIFS function.
Now before we see the pivot table,
I want to go through how you do this with the SUMIFS.
Now SUMIFS.
We did this last time.
Here's our criteria, so we don't want to add all of the sales.
Just the sales for 10/20 and then 10/21.
That means for our formula, we're
going to have to highlight all the numbers, all the dates,
and the individual criteria.
So let's see how to do this for SUMIFS.
And the one thing that will be different from last video
is that we're copying this formula down,
so we'll have to use relative and absolute cell references.
G14 equals SUMIFS.
SUM range.
Remember our screen tip helps us out.
The word SUM means those are the numbers I'm adding.
So I'm going to highlight the whole sales column.
I'm copying this down.
I don't want this range to move as I copy the formula down
so I hit the F4 key to lock that.
That puts the dollar signs in.
So when the formula's copied, the cell references
are locked or absolute.
Comma, criteria range.
The word range reminds us that that's the whole column
and so we have to highlight the whole date column.
Now that date column needs to be locked.
When I copy it down, I don't want the orange
to move so I hit the F4 key to put those dollar signs in.
Then I type a comma.
And the magic in SUMIFS comes from criteria.
We don't want to add all the numbers.
Our criteria says, add just the ones on 10/20/13.
Now notice that's a relative cell reference,
one cell to my left.
That means as I copy the SUMIFS function down,
that lavender cell reference will move.
So when I copy it down here, it will be looking at 10/23
and SUMIFS will know to add all the sales for 10/23.
Close parentheses, Control Enter.
Double click with my angry rabbit and send it down.
I go to the last cell.
F2 to put it into Edit mode and verify.
Relative cell reference.
Perfect.
The criteria knows to add for 10/25.
And both the blue and the orange ranges have been locked.
So SUMIFS.
That is one way to do it.
But now let's look at a pivot table.
Now in order to use a pivot table,
you have to have a proper data set.
A proper data set means you have field names or column
headers at the top of each column.
Now field names or column headers, what are they?
It's simply text or it's simply a word
that says what kind of data goes in this column.
So in this column, we're only allowed to put date.
In this column, only the region where we sold the product.
Sales rep.
Sales.
And all data sets in the world that we
use to create useful information are kept in this form.
Now not all data comes in this form,
but all data that we then use to create reports and things
like that come with field names in the first row.
Not only that, you have to have your transactions
or your records in each subsequent row,
and you have to have empty cells all the way around
Now you can have empty cells all the way
around the data set or your row headers
in Excel or column headers.
So we have a proper data set.
We can use the amazing pivot table.
Now before creating a pivot table,
you want to look at what your end result
report will look like.
Now notice, we want a different date and calculation
in each row.
And which two columns in the table are we using?
We're using the dates, new data in each row,
and then we're making our calculation
on the sales column.
All right.
So we've visualized our report.
We see what it's going to look like.
Let's create our pivot table.
You click in a single cell.
Go up to Insert, Pivot Table, Create Pivot Table
dialogue box pops up, and look.
It guesses exactly the right data
set because there's empty cells all the way around.
It simply wants to know where do you
want to put this pivot table?
We're going to put it on this existing sheet.
Click in the location text box and I'm
going to click in cell F22.
Now when you put a pivot table onto a sheet with data,
you want to make sure there's no data below.
I'm going to click OK.
And just like that, look at this.
We get a field list, a list of each one of the fields.
And it says, choose fields to add to report.
Down here it says, drag fields between areas below.
Look at this.
There's our rows.
That's saying, what criteria do you want in rows?
And there's our values.
So you simply drag date-- that's our criteria-- to the rows.
Instantly you get a unique list of dates.
And I didn't have to look through this column
and pick out one of each unique date.
It just did it.
The pivot table did that.
Now we take our sales and drag it to values.
That is amazing.
Just a few clicks and we've created
a report just like this one.
Now later in this class, we'll talk
a lot more about pivot tables and we'll
see how to format this.
But I just want to go through those steps one more
time and just show you how amazing this is.
I click in a single cell and it can
be any cell in the data set, Insert,
Pivot Table, existing location, F31, OK.
Date to rows, sales to values.
That is absolutely amazing.
Now let's go look at two more examples.
I'm going to go to the sheet PT2.
And now here your boss didn't ask for a daily report.
The boss asked for a regional report.
We could do it with SUMIFS.
I have the SUMIFS function.
I need the sum range.
F4 to lock it, comma, criteria range.
I need the region column.
The whole region column.
F4 to lock it, comma, and the magic I'm allowed a criteria.
I don't want to add all of them, just northwest.
And that's a relative cell reference.
Notice as I copy that down the relative cell reference
will move to southwest and then to west.
Close parentheses, Control Enter,
double click and send it down.
F2 on the last cell to verify.
Looking good.
Enter.
Now a pivot table.
Click in a single cell, any cell will do.
Insert, Pivot Table, existing location, F17, OK.
Drag region from the field list, down to rows.
Instantly a unique list, each in a different row.
Sales to values.
Wow.
That is amazing.
Now this is sum of sales.
Later in the class, we'll see it's
simple to change the calculation.
But notice pivot table a lot faster.
Let's go to PT3.
The boss said, hey, I need a sales rep sales report.
We could do it with SUMIFS.
SUMIFS function.
I need the sum range.
F4.
Criteria range.
This is the Sales Rep column.
All the sales reps.
F4.
And the magic, I don't want to add them all.
Just please add up June's.
That's a relative cell reference.
The two ranges are locked.
Close parentheses, Control Enter,
double click and send it down.
F2.
Looking good.
Now pivot table.
Click in a single cell.
Insert, Pivot Table.
Guessed right.
Existing sheet.
F18.
OK or Enter.
This is sales rep.
I'm going to take the sales rep field from the field list
and drag it down to rows.
Instantly, a unique list of all the sales rep.
Imagine if you had a table with 500 sales rep.
Instantly, it just lists one of each.
Sales down to values.
Pivot tables are so much fun.
Now later in the class, we'll see all sorts of other things
about pivot tables.
We're just introducing pivot tables
at the beginning of the class to show you how easy they are
and how powerful they are.
We'll also see more about SUMIFS later in the class.
Now one thing I do want to show you
is that one advantage that formulas have over pivot tables
is that if the data is changing, so you have some template,
and the data is changing each day or regularly, watch this.
If I give some big number to Chin, as soon as I hit Enter,
watch Chin's total.
Enter.
Instantly updates.
Now it is easy to update a pivot table,
but sometimes people want their formulas if the data is always
changing and it's changing a lot,
you don't want to have to come down and right click Refresh.
So right clicking Refresh and now it updates.
SUMIFS can do the job and notice what a SUMIFS' doing.
It's adding with one condition.
And guess what?
That's what the pivot table is doing also.
Now there is some homework for you.
Right after the sheet homework, there's
one, two, three, four homeworks.
And you know what?
I haven't shown you how to get to sheets
that are hidden under the scroll bar.
Now we could come over here.
There's navigation sheet arrows.
If you click, click, click, click, it exposes a sheet.
If you click on the one going the other direction,
left, it'll expose sheets from the left.
Now that's fine and dandy, but here's a great trick.
Right click the sheet navigation arrows and up pops
a dialog box, and you can select any sheet
you want like homework three, click OK.
And instantly, we're jumped to homework three.
Now for each one of these homework problems,
they'll be directions in the yellow cells at the top,
and then you create your solution in the sheets.
For each homework problem there is an answer, but don't look.
Don't look at the answer.
You do your homework first and then
when you're completely done having all the fun,
then you come over and check your answer.
All right.
We'll see you next video.