Tip:
Highlight text to annotate it
X
Pivot tables enable you to summarize and reorganize your data dynamically, but
you can't summarize just any data set. In most cases, your data needs to be laid
out as a data list. So what do I mean by data list?
Well, what I mean is that the source data must be laid out as a table with
column headers and no extraneous data surrounding the table.
That's how I have this data laid out here in my DataPrep workbook.
I have columns of data: Year, Quarter, Month, Company, and Revenue, and in each
row within the table represents a particular data point.
For example, if I wanted to find out FirmA's sales revenue for January 2008, I
can just look here in this row, I see 2008. Data was in Quarter number 1, which is correct.
The Month of January, the Company was FirmA and the Revenue was $67,000.
All I need to do is create a data list that has an individual measure for each
item that I want to represent within the pivot table.
It is absolutely vital that you have all of the information for each row: the
Year, the Quarter, in this case, the Month, the Company and the Revenue.
Excel can still create the pivot table if you have blank cells within the
data list. It just won't know how to reorganize the data when you pivot your pivot table.
Say for example, if you had no month in this case for the value in November,
then there is no way that Excel could assign it to the month of November and it
would just put it in a separate group with blank cells.
That doesn't give you any useful information. So make sure that all the data is filled in
your data list. This is a data list and the reason that I
show you this basic data list is just in case you'll be applying some of what you
learned here to Excel 2003 for the PC or perhaps Excel 2008 for the Mac.
In Excel 2007, there is a new construction and that is called an Excel Table.
An Excel Table is a much more powerful and versatile tool, especially when it
comes to creating pivot tables, and I'll show you why that's the case in a few moments.
But first, I'll show you how to create an Excel Table in Excel 2007.
To do that you have your data formatted as a list.
You click any cell within the data list, and then on the Home tab of the ribbon
in the Styles group. You can click Format as Table, and I'll just
select the most basic style here, style number one.
In the Format As Table dialog box, the Excel will ask where the data is for your table.
It selects the active group automatically, A1 through E49 in this case, which is
correct, and my table does have headers. That's the Year, Quarter, Month,
Company, and Revenue. Now I can click OK and Excel creates the table.
Now, what are some of the advantages of creating an Excel table?
Well, first off, Excel, when you create a pivot table, refers to the table in its
entirety, as opposed to data list. If you use a data list as source for a pivot
table, Excel says, "Okay, the data starts in cell A1 and it goes down to sell
E49," in this case. The problem is that if you want to update
the data source when you work from a data list, you need to let Excel know
that you've updated the data source by adding rows to it or subtracting
rows from it, and Excel can't detect that automatically.
If you use an Excel table as the data source for your pivot table, Excel can say,
"I'm referring to the data from the table named Table1." Regardless of how many
rows it contains, and how you change it either by adding or subtracting rows,
Excel knows that it just wants all the data from Table 1.
While I am here, I'll show you, just a few other capabilities of an Excel table.
One thing you can do is to filter the data in a table.
Say for example, if I wanted to see only the results from January, I can click
this filter arrow here at the top of the Month column, clear Select All,
select the January box, click OK, and that limits the data, so only the
results from January appear. In the same way, if I want to remove the filter,
I can Clear the Filter from Month again by clicking the filter arrow and
then clicking this menu item, and all the data reappears.
Another great thing about an Excel table is the Totals Row.
The Totals Row appears at the bottom. I don't have it turned on, but if I click
any cell in the Excel table and then on the Design contextual tab, in Table Style
Options, I can click Total Row, and Excel provides a summary of the information
in the Excel table. In this case, it gives me a summary of the
values in the Revenue column, and it's finding the sum. That's what the value
109 means. That means that it's finding a sum.
And that is an artifact of the =SUBTOTAL formula. You can change the summary operation in the
Total row. Say for example, if I wanted to go to Average, I can click Average,
and you get the average value from the column, but in this case I do want Sum,
so I'll switch it back. Once you have your source data arranged in
a data list, you can create a pivot table. I'll cover the other case in the lesson in
title Consolidating Data for Multiple Sources, which is found later in this chapter.