PivotTables In MS Excel
One of Excel’s advanced features is the ability to turn a long list of data,
say laboratory results or sales transactions, into an information matrix. Look
at the following lists and try to see which reagent has the greatest effect or
which salesperson has sold the most Gadgets.
It’s just impossible to see at a glance. How can the data be arranged to
allow for quick analysis?
A PivotTable will take these lists and arrange the data into rows and columns
containing summarized data. The analysis presented can be a sum of numbers, a
count of transactions, an average, standard deviation, running total or other
summary.
Take a look at the tables below to see how a PivotTable makes data analysis
easier.
Now it’s easy to see that the average change for reagent Delta is the highest
and that Chris sells the most Gadgets. If you’re impressed with the results from
these small lists, imagine summarizing lists containing thousands of rows!
To create your own PivotTables, try the following exercises.
1) Use the file
phonbill.xls.
Select the entire data range.
This data is from the TeleSales group. We’d like to name the data range so
that it’s easier to work with. We can do that now that the entire data range
is selected.
Just type TeleSales in the NameBox,
and be sure to hit <RETURN>!
2)
Then go to Data,
PivotTable and PivotChart Report…
This brings up a wizard which will help you create your PivotTable.
Click Next> on the first step
to accept “Microsoft Excel list or database” and PivotTable only.
On the second step, type the name TeleSales into the dialog box as our source data. You must type it, as it will not show up in Browse… or under one of the
buttons.
3) The third step is where the PivotTable is created.
Click on the button marked Layout... If you are using Excel97 or a previous
version, you will see the layout box automatically.
Let's
keep our first one simple just to get a feel for it.
Grab the box marked "called" and drag it to the area marked
"Row."
Grab the box marked "Amount" and drag it to the area marked
"Data."
Notice that the box changes to "Sum of Amount."
Click OK.
Click on Finish.
4) Here's
what your table should look like:
5)
The next step in our analysis would be to find out which extensions are calling
these states. We can create a PivotTable that breaks down these numbers by
extension, too.
Go back to the
sheet with the original data, make sure it's all still selected, and choose Data, PivotTable
and PivotChart Report… again.
6)
Just like last time, answer OK
to this first dialog box, and OK
to the second dialog box. But now you get a strange error message:
Answer
Yes to this message, because we would like to use less memory and we
have no reason to keep the PivotTables separate.
There
is only one PivotTable available in the next dialog box.
Just click on Next >.
7)
Finally! We get to create our new PivotTable.
Grab the box marked "called" and drag it to the area marked
"Row."
Grab the box marked "Amount" and drag it to the area marked
"Data."
Notice that the box changes to "Sum of Amount."
And this time, grab the box marked "Extension" and drag it to the
area marked "Column."
Click on OK.
Click on Finish.
8)
Here's our new report:
Now
we can see all the calls broken down by extension. Pretty good for finding out
who is calling where. In a call center, this data might be mapped to sales by
extension to see how effective the agents are.
Just one last breakdown, OK! Now what if we want this data broken down by
month as well?!
9)
OK, but I'm not going through the whole process again… here's all you should
need to know:
Grab the box marked "called" and drag it to the area marked
"Row."
Grab the box marked "Amount" and drag it to the area marked
"Data."
Grab the box marked "Extension" and drag it to the area marked
"Column."
Grab the box marked "Date" and drag it to the area marked
"Page."
Click on Finish.
10)
Here's the report:
Looks
pretty much like the last one.
But click on the button marked Date at the top left of the spreadsheet and choose a month instead of (All).
Try all of the months one at a time.
Now we can see the data broken down in three dimensions—a very powerful
display! If you have trouble imagining it, think of a three ring binder with a
page for January broken down by state and extension, and one for February broken
down by state and extension, etc. The (All) page would be the summary page at
the front of the binder showing the summary for all months.
Other features of the PivotTable allow you to see subtotals, show each entry
as a percent of the total, and other calculations for data analysis.