XL5 AppNote: List Management & Data Analysis Q&A (XE0926)ID: Q109223
|
Following is the complete text for the FastTip for Microsoft Excel
version 5.0 for Windows. It contains the commonly asked questions and
answers about list management and data analysis.
======================================================================
Microsoft(R) Product Support Services Application Note (Text File)
XE0926: LIST MANAGEMENT AND DATA ANALYSIS QUESTIONS AND ANSWERS
======================================================================
Revision Date: 12/93
No Disk Included
The following information applies to Microsoft Excel, version 5.0.
-----------------------------------------------------------------------
| INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY |
| ACCOMPANY THIS DOCUMENT (collectively referred to as an Application |
| Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER |
| EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED |
| WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR |
| PURPOSE. The user assumes the entire risk as to the accuracy and |
| the use of this Application Note. This Application Note may be |
| copied and distributed subject to the following conditions: 1) All |
| text must be copied without modification and all pages must be |
| included; 2) If software is included, all files on the disk(s) |
| must be copied without modification (the MS-DOS(R) utility |
| diskcopy is appropriate for this purpose); 3) All components of |
| this Application Note must be distributed together; and 4) This |
| Application Note may not be distributed for profit. |
| |
| Copyright (C) 1993 Microsoft Corporation. All Rights Reserved. |
| Microsoft and MS-DOS are registered trademarks and PivotTable and |
| Windows are trademarks of Microsoft Corporation. |
|---------------------------------------------------------------------|
Q. The Set Database, Set Criteria, and Extract commands are
not on the Data menu in Microsoft Excel version 5.0. How do I
perform these functions?
A. In Microsoft Excel 5.0, the commands used for managing data in
lists have been changed to make common tasks such as extracting
records, sorting, and summarizing data easier to use and more
powerful. The terminology has also changed: databases are now
referred to as "lists" and extracting records is now referred to
as "filtering records." You no longer need to define a database,
criteria, and extract ranges before you extract or find records
in a list. Instead, Microsoft Excel automatically determines
your list range and you can filter your list in place or you can
copy matching records to a separate part of your worksheet.
HOW YOUR LIST RANGE IS DETERMINED
=================================
Your list range is determined based on the cell or range that is
selected when you choose the Sort or Filter commands from the
Data menu. When you select a single cell in your list, Microsoft
Excel examines the cells in the surrounding region and then
automatically selects your list range and identifies your field
headings. When you select multiple cells, Microsoft Excel
assumes that the selection is the intended list.
When you use the Set Database command in earlier versions of
Microsoft Excel, the name "database" is defined on your sheet
and you are limited to one instance of this name on your sheet.
In Microsoft Excel 5.0, you no longer need this defined name. As
a result, you can have multiple lists on a sheet or in a
workbook.
FILTERING YOUR DATA
===================
When to Use the AutoFilter Command
----------------------------------
Use the AutoFilter command when you have simple criteria such as
matching an item in your list or specifying an "and" or "or"
criteria. To use the AutoFilter command:
1. Select a single cell in your list.
2. From the Data menu, choose Filter, and then choose
AutoFilter.
3. To specify your criteria, click the arrow next to the field
name and either select the item to match from the list or
choose Custom.
NOTE: In the Custom dialog box, you can specify a single
criterion or an "and" or "or" criterion.
When you set your criteria by selecting an item from the list
or by choosing OK in the Custom dialog box, your list is filtered
in place and rows that do not contain records matching your
criteria are hidden. When you filter the list, the arrows for
columns and the row headings change to blue. In addition, the
status bar displays the number of rows that meet the specified
criteria.
When to Use the Advanced Filter Command
---------------------------------------
Use the Advanced Filter command when you want to specify more
complex criteria or when you want to copy matching records to a
different location on your sheet. To use the Advanced Filter
command:
1. From the Data menu, choose Filter, and then choose Advanced
Filter.
2. In the Advanced Filter dialog box, specify the cell range
containing your criteria. You can elect to filter the list
in place or to copy the matching records to a separate
location on the active sheet.
NOTE: For more information about setting up a criteria
range, see pages 397-405 in the User's Guide.
For additional information, see "Sorting and Filtering Data in
a List" Chapter 21 of the "User's Guide." For information about
copying filtered data to another location, see pages 405-407 of
the "User's Guide."
Q. How can I summarize various components of the data in my
list?
A. To display summary figures for data stored in a list or
database, use the Subtotals command on the Data menu. The
Subtotals command automatically calculates the data in
specified columns and inserts subtotal rows directly in your
list. For information about setting up your list or database
for automatic subtotals, see "Summarizing Data in a List" in
Chapter 22 of the "User's Guide."
If you want to add or count only those data items that meet
certain criteria, use the SUMIF() or COUNTIF() functions. The
Microsoft Excel Function Wizard makes it easy for you to enter
functions such as these. To use the Function Wizard, select the
cell in which you want the function to appear, and then choose
Function from the Insert menu. Then follow the directions in
the dialog box. For more information about using the SUMIF()
and COUNTIF() functions, see "Summarizing Data in a List" in
Chapter 22 of the "User's Guide."
Q. In version 4.0 of Microsoft Excel, I used the Crosstab add-
in macro to analyze data and generate reports based on the data
in my list. How do I perform similar analyses in version 5.0 of
Microsoft Excel?
A. In Microsoft Excel version 5.0, you can use the PivotTable(TM)
Wizard to create crosstab tables from Microsoft Excel data or
external data. The PivotTable Wizard is a set of interactive
dialog boxes that guide you through the steps of selecting a
data source and choosing a layout for your table. Once you have
created the table, called a "pivot table," you can customize it
by moving categories, changing the calculation and formatting,
and hiding and showing detail. To start the PivotTable Wizard,
choose the PivotTable command from the Data menu.
CONVERTING CROSSTABS TABLES TO PIVOT TABLES
===========================================
To convert existing Microsoft Excel version 4.0 crosstab tables
to version 5.0 pivot tables:
1. Open the worksheet containing the Microsoft Excel version
4.0 crosstab table in Microsoft Excel version 5.0.
2. From the Data menu, choose PivotTable.
3. Follow the instructions in the PivotTable Wizard for
choosing a layout.
4. When you are done, choose the Finish button.
Note that this conversion process permanently changes the
crosstab table to a pivot table.
For more information about creating and customizing pivot
tables, see Chapters 24 and 25 in the "User's Guide."
DIFFERENCES BETWEEN CROSSTABS TABLES TO PIVOT TABLES
====================================================
Pivot tables provide more features, power, and flexibility and
are easier to use than crosstab tables. If you are upgrading
from crosstab tables to pivot tables, you may notice some areas
where pivot tables differ from crosstab tables; some of these
differences include the following:
- If you want your pivot table to analyze only data that meets
certain complex criteria, you must extract that data from the
list before you create the pivot table. (In Microsoft Excel
version 4.0, you don't need to extract data first; a crosstab
table uses only the data that matches the specified criteria.)
To extract data in Microsoft Excel version 5.0:
1. Set up the criteria that you want to base your extract
on.
For more information on setting up criteria, see pages
397-405 in the "User's Guide."
NOTE: You do not need to use the Set Database, Set
Criteria, or Set Extract commands in Microsoft Excel
version 5.0.
2. From the Data menu, choose Filter, and then choose
Advanced Filter.
3. Under Action, select the Copy To Another Location option.
4. In the List Range box, enter the reference for the range
containing the list.
5. In the Criteria Range box, enter the reference for the
range containing your criteria.
6. In the Copy To box, enter the reference for the range
containing your extract range and choose OK.
Microsoft Excel copies only those rows that meet the
criteria to the extract range.
7. To create a pivot table from the extract range, select a
cell in the extract range, and choose PivotTable from the
Data menu.
- Pivot tables cannot use the same field for both row and column
headings; if you attempt to do this, the column heading are
ignored.
- Totals in pivot tables include all the data in the pivot table
view. With the Crosstab command, you have the option to total
all the data in the database or total only the data in the
crosstab table.
- When you create a pivot table, an outline is not automatically
created and you won't be able to apply an outline to the
table.
- You cannot disable the double-click drill-down feature in a
pivot table; when you double-click a cell in the table, the
supporting data is displayed.
For more information about setting up criteria and extract
ranges and using the Advanced Filter command, see Chapter 21 of
the" User's Guide."
Q. How can I search for and extract data from an external
database so that I can work with it in Microsoft Excel?
A. To extract data from an external database, use Microsoft Query.
You can use Microsoft Query by itself or from within Microsoft
Excel to access and organize data from a variety of external
sources. For example, you can use Microsoft Query to select
multiple tables from your external data source, specify search
criteria, relate one data source to another, add or remove
fields from a query, change the way data is organized and
displayed, or calculate totals for records within the result
set. You can also return the result set to a Microsoft Excel
workbook so that you can analyze it, create a report from it,
or display it in a chart.
For information on how to use Microsoft Query, see "Retrieving
Data with Microsoft Query" in Chapter 23 of the "User's Guide." Additional query words: 5.00c
Keywords : xlquery
Version : 5.00 5.00c 7.00
Platform : WINDOWS
Issue type : Last Reviewed: April 25, 1999