Right now we're working with our candle company sales spreadsheet.
It has less than 100 rows, but imagine that this Excel spreadsheet contained thousands
of rows of information with information about all the salespeople, how much each sold, the
date they sold it, account numbers, and more. It is almost too much to read and comprehend.
If I wanted to know who sold the most in February, how many March sales there were, or which
account bought the most merchandise, a Pivot Table could help me answer these questions
quickly. Sure, I might be able to find the answers
by creating a Chart, or sorting, but Pivot Table reports give you power because you can
quickly find the answer to many different questions.
Pivot Table reports, or Pivot Tables as they are usually called, can help you answer questions
about your spreadsheet by analyzing the numerical information in various ways.
To create a Pivot Table, place your cursor anywhere in the spreadsheet data or select
the data you want to use in the report. Now, select the Insert tab.
In the Tables group, click Pivot Table, and then click Pivot Table again.
Excel selects cells in the actual spreadsheet and the Create PivotTable dialog box opens.
Select a table or range is already selected and the Table/Range field shows the range
of the selected data. New Worksheet is also selected by default
as the place where the report will be placed. If you do not want the Pivot Table to appear
in a new worksheet, just click Existing Worksheet and select the name of the sheet.
We'll just leave this as New Worksheet. Click OK and the Pivot Table appears in a
new sheet. Let's go ahead and name this sheet Pivot Table.
If this is your first look at a Pivot Table, you are probably wondering why they are named
that. Basically, Pivot Tables allow us to pivot, or move, data so you can produce answers
to questions. You can very easily see what effect pivoting
the data has on the spreadsheet information. If we look at our actual spreadsheet, we can
see that our column headings are salesperson, region, account, order amount, and month.
When you create a Pivot Table, each column label in your data becomes a field that can
be used in the report. Let's take a look at the Pivot Table.
On one side is the layout area for the report, and on the other side is the Pivot Table Field
List. This list shows the column titles from the
source data. Before you create a Pivot Table report, decide
what information you want to find. For example, in this spreadsheet, let's find
out how much each salesperson sold. To answer this question, we're going to need
info about the salespeople and the order amounts. To create this report, we need to select the
check box next to the Salesperson field in the PivotTable Field List, and then select
the check box next to the Order Amount field. And just like that - we can see how much each
salesperson sold, and how much was sold total. I didn't select these other fields because
I didn't need them to answer this question and build a report.
Now, let's look a little closer at the Pivot Table.
You'll notice that each of the selected fields appears in a default area below and in the
layout area. All my salespeople data appears on the left
side as rows, and the order amount data appears on the right.
This is a default setting in Excel - data with numbers will always appear on the right.
The heading says "Sum of Order Amount" because Excel uses the Sum function to add
up fields with numbers. Also, if you decide you don't want a field
in a report, just clear the check box next to the field name by clicking it.
Now, you can use Pivot Tables to answer just one question, but I want you to see how quickly
it can be used to answer multiple questions. What if we wanted to know how much was sold
in each region? To answer this, add the Region field as a report filter.
Just click the Region field in the list. By default, it will appear in the Row Labels
group. Left-click and drag it into the Report Filter
section. Release the mouse button.
Now, region appears at the top of the report as a filter.
The arrow by the Region fields shows. To show just the data for a particular region,
click the drop-down arrow and select the region. To see multiple regions, click the Multiple
Regions box, select the regions to display, and then, click OK.
Now, we did talk about what pivoting means and how you can pivot data in reports.
In this example, I might want to move the region filter to Column Labels so the information
is presented in a different way. I can still filter, and all my data is here,
it's just organized in a different way. I can do this by left-clicking and dragging
and dropping Region in the Column Labels or even to Values.
Each just changes the look of the report. Another way to pivot this report is to right-click
one of the rows, select Move, and then click Move Salesperson to Columns.
This will move the entire Salesperson field from the Row Label area to the Column Label
area of the report. Again, this is just another way you can work
with your data. To return the report back to the original
view, right-click one of the Salesperson headings we'll just click Jane Doe, and then select
Move Salesperson to Rows. That will move the field back to the row area
of the report. This is really just a basic overview of Pivot
Tables. There is so much more you can do, but the
best way for you to learn some of these concepts is to practice the basics that we just discussed.
You may also want to explore the Options tab. From here, you can change the name of the
Pivot Table. You can change the Value of the Field Setting.
Right now, we're calculating the sum of each salesperson's order total, but we could change
that to just count the number of sales. We can also sort the data or even clear the
entire pivot table and start from scratch. You can click Move Pivot Table and move it
to an existing worksheet or a new worksheet. I do want to spend a few minutes telling you
about Pivot Charts. You can't really cover Pivot Tables without
talking about Pivot Charts. Microsoft actually refers to these as Pivot
Tables and Pivot Chart Reports because they do go hand in hand.
After you create a Pivot Table, you can create a Pivot Chart with very little extra effort.
This allows you to display the information in your Pivot Report in a graphical way.
To create a Pivot Chart, select the Pivot Chart command from the Options tab.
The Insert Chart dialog box appears. Select the chart you'd like, and click OK.
The chart will now appear on the same sheet as the Pivot Table.
You'll notice that the information in the chart is the same information in the Pivot
Table. Although our original spreadsheet has information
about the Account and Month of the sale, this information is not included in the Pivot Chart
because it is not part of the Pivot Table. You have a Filter Pane that will allow you
to filter data for the chart, and I encourage you to explore this on your own.
For right now, we'll just close this and we can always left-click the chart and drag it
a bit over if it is in the way of our data. When you first use a Pivot Table, the whole
thing can seem overwhelming. Once you start working with Pivot Tables,
you'll realize that they are really not that difficult.
The key thing to remember with Pivot Tables is that you are not changing your original
spreadsheet data, so it's OK to try different things and see what works.
This video really just covered the very basics of Pivot Tables.
We worked with a simple spreadsheet and simple data, but I hope you can see how robust and
useful this feature is, especially if you are working with a large spreadsheet with
hundreds or even thousands of rows. Once you feel comfortable with this, you can
explore and learn more about how to use Pivot Tables and Pivot Charts with your data.