|<<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>>|
Step 2: Create a PivotTable Report
TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle
To create a PivotTable report, you first use
the PivotTable and PivotChart Wizard to specify the data you want to use
and to create the report framework. You can then use the PivotTable toolbar to arrange the data within that framework.
For example, you can choose the categories of data to
include and the corresponding data values to summarize.
Then, tell Microsoft® Excel how to arrange the
data categories in the rows and columns of the PivotTable report. When
Excel creates the finished report, it automatically summarizes and totals
the data values.
Start by using the
PivotTable and PivotChart wizard
Open the workbook where you want to create the PivotTable report.
you're basing the report on an Excel list or database, click a cell in the
list or database.
On the Data menu, click PivotTable
and PivotChart Report.
In step 1 of the PivotTable and PivotChart Wizard, follow the
instructions, and click PivotTable
under What kind of report do you
want to create?
Follow the instructions in step 2 of the wizard.
you're retrieving external data for the PivotTable report, you can get
more information about using Microsoft Query or the Query Wizard. Just
type ways to retrieve data from an
external database in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.
In step 3 of the wizard, click Finish.
Lay out the PivotTable report
On the PivotTable
toolbar, drag field buttons to the labeled areas on the PivotTable
To see a demonstration of how to drag the field buttons, click the Start
If you want to rearrange the fields, drag them from one area to
another. To remove a field, drag it outside the PivotTable report.
In some cases, you might not want to lay out the PivotTable
report directly on a worksheet. For example, if your data source is a
large external database, worksheet layout may be too time consuming. In
this case, you can click Layout
in step 3 of the PivotTable and PivotChart Wizard to lay out the
PivotTable report. (For more information about laying out a PivotTable
report, type PivotTable Wizard
Layout dialog box in the Office Assistant or on the Answer
Wizard tab in the Excel Help window, and then click Search.)
For details on laying out a PivotTable report that uses OLAP
source data, type lay out a
PivotTable in the Office Assistant or on the Answer Wizard tab in the Excel Help window, and then click Search.
Anatomy of a PivotTable Report
You may have noticed that Excel uses specific terms to
identify the parts of a PivotTable report. If you're not familiar with
these terms, read this section for a quick primer.
A field from the source data that you assign to a row orientation
in a PivotTable report. For example, Region and Salesperson are row
A field from the source data that you assign to a column
orientation in a PivotTable report. For example, Type is a column field.
A field from the source data that you assign to a page (or filter)
orientation in a PivotTable report. For example, Year is a page field. You
can use the Year field to display summarized data for only 1997, 1998, and
A subcategory of a row, column, or page field. For example, the
Type field contains the following items: Beverages, Dairy, Meat, and
Produce; the Salesperson field contains the following items: Buchanan,
Davolio, Dodsworth, and Suyama.
A field from the source data that contains data to be summarized.
For example, Sum of Sales is a data field.
If you're using non-OLAP source data, you can choose how to
summarize data (for example, by sum, average, or count). A data field
usually summarizes numeric data, but it can also summarize text. For
example, you can count the number of times a specific text entry (such as
Yes or No) appears in a field.
The cells in a PivotTable report that contain summary data. For
example, the value in cell C5 summarizes Buchanan's beverage sales for the
East region in 1997. In other words, it's a summary of the sales figures
for every row in the source data that contains the items Buchanan,
Beverage, East, and 1997.
For more information, type elements
of a PivotTable report in the Office Assistant or on the Answer
Wizard tab in the Excel Help window, and then click Search.