WEBVTT
Kind: captions
Language: en

00:00:08.269 --> 00:00:12.440
Right now we're working with our candle company
sales spreadsheet.

00:00:12.440 --> 00:00:16.970
It has less than 100 rows, but imagine that
this Excel spreadsheet contained thousands

00:00:16.970 --> 00:00:22.989
of rows of information with information about
all the salespeople, how much each sold, the

00:00:22.989 --> 00:00:28.849
date they sold it, account numbers, and more.
It is almost too much to read and comprehend.

00:00:28.849 --> 00:00:34.760
If I wanted to know who sold the most in February,
how many March sales there were, or which

00:00:34.760 --> 00:00:39.610
account bought the most merchandise, a Pivot
Table could help me answer these questions

00:00:39.610 --> 00:00:43.190
quickly.
Sure, I might be able to find the answers

00:00:43.190 --> 00:00:48.540
by creating a Chart, or sorting, but Pivot
Table reports give you power because you can

00:00:48.540 --> 00:00:53.970
quickly find the answer to many different
questions.

00:00:53.970 --> 00:00:59.350
Pivot Table reports, or Pivot Tables as they
are usually called, can help you answer questions

00:00:59.350 --> 00:01:05.990
about your spreadsheet by analyzing the numerical
information in various ways.

00:01:05.990 --> 00:01:11.490
To create a Pivot Table, place your cursor
anywhere in the spreadsheet data or select

00:01:11.490 --> 00:01:18.490
the data you want to use in the report.
Now, select the Insert tab.

00:01:18.939 --> 00:01:25.939
In the Tables group, click Pivot Table, and
then click Pivot Table again.

00:01:29.109 --> 00:01:36.109
Excel selects cells in the actual spreadsheet
and the Create PivotTable dialog box opens.

00:01:39.299 --> 00:01:45.029
Select a table or range is already selected
and the Table/Range field shows the range

00:01:45.029 --> 00:01:50.240
of the selected data.
New Worksheet is also selected by default

00:01:50.240 --> 00:01:55.490
as the place where the report will be placed.
If you do not want the Pivot Table to appear

00:01:55.490 --> 00:02:02.189
in a new worksheet, just click Existing Worksheet
and select the name of the sheet.

00:02:02.189 --> 00:02:09.189
We'll just leave this as New Worksheet.
Click OK and the Pivot Table appears in a

00:02:10.380 --> 00:02:17.380
new sheet.
Let's go ahead and name this sheet Pivot Table.

00:02:19.630 --> 00:02:24.150
If this is your first look at a Pivot Table,
you are probably wondering why they are named

00:02:24.150 --> 00:02:30.920
that. Basically, Pivot Tables allow us to
pivot, or move, data so you can produce answers

00:02:30.920 --> 00:02:37.110
to questions.
You can very easily see what effect pivoting

00:02:37.110 --> 00:02:46.750
the data has on the spreadsheet information.
If we look at our actual spreadsheet, we can

00:02:46.750 --> 00:02:53.750
see that our column headings are salesperson,
region, account, order amount, and month.

00:02:55.620 --> 00:03:01.140
When you create a Pivot Table, each column
label in your data becomes a field that can

00:03:01.140 --> 00:03:08.140
be used in the report.
Let's take a look at the Pivot Table.

00:03:09.980 --> 00:03:16.540
On one side is the layout area for the report,
and on the other side is the Pivot Table Field

00:03:16.540 --> 00:03:21.680
List.
This list shows the column titles from the

00:03:21.680 --> 00:03:28.680
source data.
Before you create a Pivot Table report, decide

00:03:29.480 --> 00:03:36.130
what information you want to find.
For example, in this spreadsheet, let's find

00:03:36.130 --> 00:03:41.510
out how much each salesperson sold.
To answer this question, we're going to need

00:03:41.510 --> 00:03:50.180
info about the salespeople and the order amounts.
To create this report, we need to select the

00:03:50.180 --> 00:03:56.880
check box next to the Salesperson field in
the PivotTable Field List, and then select

00:03:56.880 --> 00:04:05.390
the check box next to the Order Amount field.
And just like that - we can see how much each

00:04:05.390 --> 00:04:12.390
salesperson sold, and how much was sold total.
I didn't select these other fields because

00:04:13.350 --> 00:04:18.959
I didn't need them to answer this question
and build a report.

00:04:18.959 --> 00:04:25.080
Now, let's look a little closer at the Pivot
Table.

00:04:25.080 --> 00:04:31.849
You'll notice that each of the selected fields
appears in a default area below and in the

00:04:31.849 --> 00:04:37.229
layout area.
All my salespeople data appears on the left

00:04:37.229 --> 00:04:42.860
side as rows, and the order amount data appears
on the right.

00:04:42.860 --> 00:04:49.860
This is a default setting in Excel - data
with numbers will always appear on the right.

00:04:50.360 --> 00:04:56.279
The heading says "Sum of Order Amount"
because Excel uses the Sum function to add

00:04:56.279 --> 00:05:02.270
up fields with numbers.
Also, if you decide you don't want a field

00:05:02.270 --> 00:05:08.909
in a report, just clear the check box next
to the field name by clicking it.

00:05:08.909 --> 00:05:15.229
Now, you can use Pivot Tables to answer just
one question, but I want you to see how quickly

00:05:15.229 --> 00:05:21.039
it can be used to answer multiple questions.
What if we wanted to know how much was sold

00:05:21.039 --> 00:05:28.039
in each region? To answer this, add the Region
field as a report filter.

00:05:29.969 --> 00:05:36.469
Just click the Region field in the list.
By default, it will appear in the Row Labels

00:05:36.469 --> 00:05:41.620
group.
Left-click and drag it into the Report Filter

00:05:41.620 --> 00:05:46.740
section.
Release the mouse button.

00:05:46.740 --> 00:05:52.509
Now, region appears at the top of the report
as a filter.

00:05:52.509 --> 00:05:59.509
The arrow by the Region fields shows.
To show just the data for a particular region,

00:06:00.210 --> 00:06:06.430
click the drop-down arrow and select the region.
To see multiple regions, click the Multiple

00:06:06.430 --> 00:06:13.430
Regions box, select the regions to display,
and then, click OK.

00:06:13.620 --> 00:06:20.370
Now, we did talk about what pivoting means
and how you can pivot data in reports.

00:06:20.370 --> 00:06:26.849
In this example, I might want to move the
region filter to Column Labels so the information

00:06:26.849 --> 00:06:33.849
is presented in a different way.
I can still filter, and all my data is here,

00:06:35.229 --> 00:06:41.830
it's just organized in a different way.
I can do this by left-clicking and dragging

00:06:41.830 --> 00:06:48.830
and dropping Region in the Column Labels or
even to Values.

00:06:51.249 --> 00:06:58.249
Each just changes the look of the report.
Another way to pivot this report is to right-click

00:06:58.719 --> 00:07:05.719
one of the rows, select Move, and then click
Move Salesperson to Columns.

00:07:08.050 --> 00:07:13.509
This will move the entire Salesperson field
from the Row Label area to the Column Label

00:07:13.509 --> 00:07:18.789
area of the report.
Again, this is just another way you can work

00:07:18.789 --> 00:07:24.900
with your data.
To return the report back to the original

00:07:24.900 --> 00:07:31.900
view, right-click one of the Salesperson headings
we'll just click Jane Doe, and then select

00:07:32.229 --> 00:07:39.029
Move Salesperson to Rows.
That will move the field back to the row area

00:07:39.029 --> 00:07:44.499
of the report.
This is really just a basic overview of Pivot

00:07:44.499 --> 00:07:47.430
Tables.
There is so much more you can do, but the

00:07:47.430 --> 00:07:53.860
best way for you to learn some of these concepts
is to practice the basics that we just discussed.

00:07:53.860 --> 00:08:00.849
You may also want to explore the Options tab.
From here, you can change the name of the

00:08:00.849 --> 00:08:06.300
Pivot Table.
You can change the Value of the Field Setting.

00:08:06.300 --> 00:08:11.469
Right now, we're calculating the sum of each
salesperson's order total, but we could change

00:08:11.469 --> 00:08:17.360
that to just count the number of sales.
We can also sort the data or even clear the

00:08:17.360 --> 00:08:24.360
entire pivot table and start from scratch.
You can click Move Pivot Table and move it

00:08:26.539 --> 00:08:33.539
to an existing worksheet or a new worksheet.
I do want to spend a few minutes telling you

00:08:34.370 --> 00:08:38.680
about Pivot Charts.
You can't really cover Pivot Tables without

00:08:38.680 --> 00:08:43.810
talking about Pivot Charts.
Microsoft actually refers to these as Pivot

00:08:43.810 --> 00:08:50.120
Tables and Pivot Chart Reports because they
do go hand in hand.

00:08:50.120 --> 00:08:57.120
After you create a Pivot Table, you can create
a Pivot Chart with very little extra effort.

00:08:57.810 --> 00:09:04.200
This allows you to display the information
in your Pivot Report in a graphical way.

00:09:04.200 --> 00:09:09.820
To create a Pivot Chart, select the Pivot
Chart command from the Options tab.

00:09:09.820 --> 00:09:16.590
The Insert Chart dialog box appears.
Select the chart you'd like, and click OK.

00:09:16.590 --> 00:09:21.720
The chart will now appear on the same sheet
as the Pivot Table.

00:09:21.720 --> 00:09:25.820
You'll notice that the information in the
chart is the same information in the Pivot

00:09:25.820 --> 00:09:29.650
Table.
Although our original spreadsheet has information

00:09:29.650 --> 00:09:35.340
about the Account and Month of the sale, this
information is not included in the Pivot Chart

00:09:35.340 --> 00:09:41.760
because it is not part of the Pivot Table.
You have a Filter Pane that will allow you

00:09:41.760 --> 00:09:47.270
to filter data for the chart, and I encourage
you to explore this on your own.

00:09:47.270 --> 00:09:52.900
For right now, we'll just close this and we
can always left-click the chart and drag it

00:09:52.900 --> 00:09:59.890
a bit over if it is in the way of our data.
When you first use a Pivot Table, the whole

00:09:59.890 --> 00:10:05.430
thing can seem overwhelming.
Once you start working with Pivot Tables,

00:10:05.430 --> 00:10:10.150
you'll realize that they are really not that
difficult.

00:10:10.150 --> 00:10:14.380
The key thing to remember with Pivot Tables
is that you are not changing your original

00:10:14.380 --> 00:10:19.600
spreadsheet data, so it's OK to try different
things and see what works.

00:10:19.600 --> 00:10:24.210
This video really just covered the very basics
of Pivot Tables.

00:10:24.210 --> 00:10:29.340
We worked with a simple spreadsheet and simple
data, but I hope you can see how robust and

00:10:29.340 --> 00:10:33.740
useful this feature is, especially if you
are working with a large spreadsheet with

00:10:33.740 --> 00:10:38.970
hundreds or even thousands of rows.
Once you feel comfortable with this, you can

00:10:38.970 --> 00:10:43.630
explore and learn more about how to use Pivot
Tables and Pivot Charts with your data.