|
Using
Excel as a database makes it a far more powerful tool
than just using it as a spreadsheet. In order to benefit
from Excel’s neat database capabilities, you need
to properly set up the information on your spreadsheet
as a database. The main Excel database tasks are sorting,
subtotaling, filtering and the most powerful of all
is pivot tables.
A pivot table is an interactive worksheet table that
quickly summarizes large amounts of data using calculation
methods you choose. It is called a pivot table because
you can rotate its row and column headings around
the core data area to give you different views of
the source data. As the source data changes, you can
update a pivot table.
If you change data in the source list or table, by
adding new rows (records) or columns (fields), there
are ways to update (or refresh) the pivot table. However,
the safest way seems to be deleting the sheet which
contains your pivot table and start over by creating
a new pivot table, which usually takes only a few
seconds.
Note: There is no limit, other than available memory,
to the number of pivot tables that can be defined
in the same workbook-or even on the same worksheet.
Creating Pivot Tables
Before You Start, make sure you pick one cell in the
body of your database, meaning any cell below the
column labels where there is data. Excel will recognize
the boundaries of your database.
(Figure 5.39)
·
On the menu bar click on “Data”, then choose the option
“Pivot Table and Pivot chart Report”. Pivote Table
and Pivotechart Wizard will open for you.
· Select the first option which is “Microsoft Excel
list or Database” from the heading “ which is the
data you want to analyze” and choose Pivot Table from
the heading “what kind of report you want to create”
and click on Next.
(Figure 5.40)
·
Since your cell pointer was in the body of the database,
Excel automatically knows the range of your database.
Click Next.
(Figure 5.41)
·
Before you click finish. Set your pivot table layout
by clicking Layout button.
(Figure 5.42)
Layout
is the heart of the pivot table where you get to
design your report. Every field of your database
appears as a button to the right. Simply drag the
field(s) that you want your data summarized by to
the “Row” and/or “Column” areas. Keep in mind that
the following are just examples. You can just as
easily summarize your reports by other fields that
are of more interest to you.
Setting up the Layout Area
· Drag the field “Sub Code” (on the right side of
screen) to the ROW area (left side of screen). Then
drag the “Fiscal Year ” field (which we added after
getting the QDB report, as described earlier), to
the COLUMN area. Then drag the numeric field “Expense”
from the right side of the screen to the “DATA”
area.
(Figure 5.43)
·
Remember that the DATA area contains the fields
you want summarized. If instead of “Sum” it defaults
to “Count”, double click on “Count of Expense” and
choose “Sum” as shown below and click “OK”.
(Figure 5.44)
·
When done, click “Finish”. Here is the result of
your pivot table.
(Figure 5.45)
·
The pivot table is created on a new sheet in the
workbook.
· You can rename the sheet that contains the pivot
table by double clicking on the sheet tab name and
typing your new meaningful sheet name.
When you create a pivot table, the Pivot Table toolbar
will appear on your screen and it looks like this:
(Figure 5.46)
If
you don’t see this toolbar then you can choose the
Toolbar command from the View menu and choose PivotTable.
The best way to learn about pivot tables is through
experimenting. Try positioning each field which
is represented by buttons as a row category, column
category, and page break – the pivot table will
reveal different information about the underlying
data with each layout.
|