Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 45 PivotTables: A Hack in Themselves

PivotTables are one of the wildest but most
powerful features of Excel, an ingenious hack themselves that may
take some experimentation to figure out.

We
use PivotTables a lot when we develop spreadsheets for our clients.
Once a client sees a PivotTable, they nearly always ask whether they
can create one themselves. Although anyone can create a PivotTable,
unfortunately many people tend to shy away from them, as they see
them as too complex. Indeed, when you first use a PivotTable, the
process can seem a bit daunting. Some persistence is definitely
necessary.

You'll find that
persistence will pay off once you experience the best feature of
PivotTables: their ability to be manipulated using trial and error
and immediately show the result of this manipulation. If the result
is not what you expect, you can use Excel's Undo
feature and have another go! Whatever you do, you are not changing
the structure of your original table in any way, so you can do no
harm.

Why Are They Called PivotTables?

PivotTables allow you to pivot data using
drag-and-drop techniques and receive results immediately. PivotTables
are interactive; once the table is complete, you very easily can see
how your information will be affected when you move (or pivot) your
data. This will become patently clear once you give PivotTables a
try.

Even for experienced PivotTable developers, an element of trial and
error is always involved in producing desired results. You will find
yourself pivoting your table a lot!

What Are PivotTables Good For?

PivotTables can produce summary
information from a table of information. Imagine you have a table of
data that contains names, addresses, ages, occupations, phone
numbers, and Zip Codes. With a PivotTable, you very easily and
quickly can find out:

How many people have the same name

How many people share the same Zip Code

How many people have the same occupation

You also can receive such information as:

A list of people with the same occupation

A list of addresses with the same Zip Code

If your data needs slicing, dicing, and reporting, PivotTables will
be a critical part of your toolkit.

Why Use PivotTables When Spreadsheets Already Offer So Much Analysis Capability?

Perhaps the biggest advantage to using PivotTables is the fact that
you can generate and extract meaningful information from a large
table of data within a matter of minutes and without using up a lot
of computer memory. In many cases, you could get the same results
from a table of data by using Excel's built-in
functions, but that would take more time and use far more memory.

Another advantage to using PivotTables is
that if you want some new information, you can simply drag-and-drop
(pivot). In addition, you can opt to have your information update
each time you open the workbook or click Refresh.

PivotCharts Extend PivotTables

Microsoft
introduced PivotCharts in Excel 2000. The table you create via the
PivotTable Wizard produces a PivotChart (or, more accurately, a
PivotTable and PivotChart Report). When you create a PivotTable, you
also can create a PivotChart at the same time, with no extra effort.
PivotCharts enable you to create interactive charts that previously
were impossible without using either VBA or Excel Controls.

The PivotTable Wizard is discussed in more
detail later in this chapter.

PivotCharts are not available in Excel for the Macintosh.

Creating Tables and Lists for Use in PivotTables

When you create a PivotTable, you must
organize the dataset you're using in a table and/or
a list. As the PivotTable will base all its data on this table or
list, it is vital that you set up your tables and lists in a uniform
way.

In this context, a table is no more than a list that has a title, has
more than one column of data, and has a different heading for each
column. A list often is referred to in the context of a table as
well. The best practices that apply to setting up a list will help
you greatly when you need to apply a PivotTable to your data.

When you extract data via the use of
lookup or database functions, you can be a little less stringent in
how you set up the table or list. This is because you can always
compensate with the aid of a function and probably still get your
result. Nonetheless, it's still easiest to set up
the list or table as neatly as possible. Excel's
built-in features assume a lot about the layout and setup up of your
data. Although they offer a degree of flexibility, more often than
not you will find it easier to adhere to the following guidelines
when setting up your table or list:

Headings are required, as a PivotTable
uses them for field names. Headings should always appear in the row
directly above the data. Also, never leave a blank row between the
data and the headings. Furthermore, make the headings distinct in
some way; for instance, boldface them.

Leave
at least three blank rows above the headings. You can use these for
formulas, critical data, etc. You can hide the rows if you want.

If
you have more than one list or table on the same worksheet, leave at
least one blank column between each list or table. This will help
Excel recognize them as separate entities. However, if the lists and
tables are related to each other, combine them into one large table.

Avoid blank cells within your data.
Instead of leaving blank cells for the same data in a column, repeat
the data as many times as needed.

Sort your
list or data, preferably by the leftmost column. This will make the
data easier to read and interpret.

If you follow these guidelines as closely as possible, using
PivotTables will be a relatively easy task.

Figure 4-1 shows a well-laid out table of data, and
a PivotTable in progress. Note that many of the same dates are
repeated in the Date column. In front of this data is the Layout step
for the data showing the optional Page, Row, and Column fields, as
well as the mandatory Data field.

Figure 4-1. PivotTable generated from a well-laid out table of data

The PivotTable and PivotChart Wizard

As noted
earlier, to help users create PivotTables, Excel offers a PivotTable
and PivotChart Wizard. This Wizard guides you through the creation of
a PivotTable using a four-step process, in which you tell Excel the
following:

How the data is set up and whether to create an associated PivotChart
(if PivotCharts are available in that version of Excel)

Where the data is storede.g., a range in the same workbook, a
database, another workbook, etc.

Which column of data is going into which field: the optional Page,
Row, and Column fields, as well as the mandatory Data field

Where to put your PivotTable (i.e., in a new worksheet or in an
existing one)

You also can take many side steps along
the way to manipulate the PivotTable, but most users find it easier
to do this after telling Excel where to put it.

Excel 2000 and later versions have a major advantage over Excel 97: they enable you to choose how to set up your data after the Wizard is finished.

Now that you know more about PivotTables and what they do,
it's time to explore some handy hacks that can make
this feature even more powerful.