right now we're working with our candle
company sales spreadsheets it has less
than 100 rows but imagine that this
Excel spreadsheets contains thousands of
rows of information with information
about all the salespeople how much each
sold the dates they sold its accounts
numbers and more it is almost too much
to read and comprehend if I wanted to
know who sold the most in February
how much March sales there were or which
account bought the most emergen dice a
pivot table would 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 spreadsheets
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 and the tables group click
pivot table and then click pivot table
again Excel selects cells in the actual
spreadsheet and the create pivot table
dialog box opens select a table or range
is already selected and the table range
field shows the range of the selected
data new worksheets is also selected by
default as the place where the reports
will be placed if you do not want the
pivot table to appear in a new worksheet
just click existing worksheets 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 sheets
pivot table if this is your first look
at a pivot table you're probably
wondering why they're 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
spreadsheets 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 information about the salespeople
and the order amounts to create this
report we need to select the checkbox
next to the salesperson field in the
pivot table fields a list and then
select the check box next to the order
amount field and just like that we can
see how much each person 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
PivotTable you'll notice that each of
the selected fields appears in a default
area below and in the layouts area all
my cells people data appears on the left
side as rows and the order amounts 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 awesome if you decide you
don't want a field in a report just
clear the checkbox next to the field a
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 ad
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
reports filter section release the mouse
button
now region appears at the top of the
report as a filter the arrow by the
region field 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
filters 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 two
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 a 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 reports back to the original view
right-click one of the salesperson
headings will 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 were 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 sales person's order top but we
could change that to just count the
number of cells we can also sort to the
data or even clear the entire pivot
table and start from scratch you can
click a 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 pivottables and pivotcharts
because they do go
in hand after you create a pivot table
you can create a pivot chart was very
little extra effort this allows you to
display the information in your pivot
reports 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 like and click OK the chart
will now appear on the same sheet as a
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 accounts and month of the cell
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 charts 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
she starts 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