one of the best things about Excel is
that it lets you deal with really large
amounts of data in fact if you need it
to you can have billions of cells in a
single spreadsheet but no matter how
much data you have it can still be
surprisingly easy to manage using a very
special feature called pivot tables
pivot tables let you summarize and
manipulate the data without changing the
original spreadsheet if you've never
used them before they might seem a
little bit intimidating at first but
once you get the hang of it you'll be
able to easily make pivot tables using
just a few mouse clicks
in this example I have some company
sales data and there are columns for
salesperson region account order amount
and month and I need to answer the
question what is the amount sold by each
salesperson
now because each salesperson appears on
multiple rows I would need to add up the
rows to find the answer
but a pivot table can do this
automatically
you'll first need to select the data if
it's formatted as a table you can just
click any cell and if it's not you'll
just need to make sure that you select
everything including the column headers
from the insert tab click the pivot
table command
and you'll usually want to place the
PivotTable in a new worksheet and click
OK
over on the left is our blank pivot
table and on the right is the field list
all of these field names are just taken
from our column headers
below that are the four areas where we
can add fields to build the pivot table
now before we select any of the fields
let's look at the original question
again what is the amount sold by each
salesperson
this question tells us exactly which
fields we need to use to answer it
salesperson and order amount
when you click these they will be added
to the row labels and values areas below
and now on the Left we have a pivot
table where we can see the amount sold
by each salesperson
so now our original question has been
answered and if we want we can modify
the pivot table to answer more questions
if we want to know how much each person
sold each month we can drag the month
field into column labels and this will
add columns for each month
or if we wanted to know how much each
region sold we could just take the month
and salesperson fields out
and then drag region into road labels
so each time we change the field the
pivot table is just combining and adding
the source data in a different way to
give us the answer that we need
you can experiment by dragging the
fields into different areas but you may
find that some combinations don't work
as well for example if I move order
amount to column labels or row labels it
doesn't really give me any useful data
since order amount contains numerical
values it really works best in the
values field
finally if you click on the order amount
field and go to value field settings you
can change the function so that instead
of calculating the sum it can count the
cells or find the average
and there are many other functions that
you can use
in part two we're going to talk about
report filters and we'll also talk about
how to add slicers and pivotcharts which
will give you even more ways to
manipulate the pivot table like what you
just saw rate it add a comment subscribe
to our chin the freedom to learn what
you want when you want absolutely free
GCF learn free.org