Goodwill Community Foundation
creating opportunities for a better life
one of the best things about Excel is
the fact that a single worksheet can
hold so much information in fact you
could fill billions of cells if you
needed that much room a file with that
much data could get pretty overwhelming
but there's a feature called pivot
tables that can help pivot tables let
you summarize and manipulate your data
without actually changing anything in
the worksheet let's take a look at how
they work in this example I have some
company sales data with columns for the
salesperson region account order amount
and month and I need to find the answer
to a very specific question what is the
amount sold by each salesperson because
each salesperson has data on more than
one row I'd normally have to add these
up in order to find the answer but a
pivot table can do this for us
automatically which will make the task
much much easier first you need to
select your data you can just click any
cell if it's formatted as a table
otherwise you need to select all the
cell's yourself and don't forget your
column headers when you're ready go to
the insert tab then click the pivot
table command you'll usually want to
place your pivot table on a new
worksheet so make sure this option is
selected then click OK and a new sheet
will appear over on the left is our
blank pivot table it's OK that there's
nothing there yet to the right is a list
of fields that have actually been pulled
from our column headers this is where we
control what is or isn't displayed in
the pivot table but before we continue
let's take another look at that question
what is the amount sold by each
salesperson
this question tells us exactly which
fields to use to find out the answer
salesperson and order amount all you
have to do is check the box next to the
field and it'll be added to one of the
areas below in this case the rows
category another method is to drag and
drop the field into place we'll put this
one under values if we go back to
PivotTable now we can see the
information we were looking for in other
words the amount sold by each
salesperson so our question has been
answered just by choosing what fields to
include one of the best things about
pivot tables is how easy they are to
modify so you can view and analyze
different things let's say for example
that you wanted to know each person's
monthly sales not just their total sales
just drag the month field down here to
columns and this will add a column for
each month in the worksheet if we wanted
to know what each region sold we could
take out the month in salesperson fields
and then drag region down to rows so
each time we change the fields the pivot
table is just combining the source data
in a different way so we can view the
information we need and we're doing all
this without adding or changing anything
in the original worksheet you can even
experiment by dragging the fields into
different areas but some combinations
work better than others for instance if
I move order amount over to columns or
rows you can see that it doesn't really
give us any useful data since order
amount contains numerical values it
really does work best in the values area
finally if we click the arrow next to
order amount and go to value field
settings we can change the function to
something other than sum instead we
could count the number of cells
calculate the average or use any of the
other options here some of these
functions will be more useful than
others depending on your data so that's
just something to keep in mind in part
two of this video we're going to talk
about report filters we'll also take a
look at slicers and pivotcharts which
can give you even more ways to work with
your pivot table
you