Microsoft KB Archive/109223

{|
 * width="100%"|

XL5 AppNote: List Management & Data Analysis Q&A (XE0926)

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0

-

SUMMARY
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.                   |
 * 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 :

Technology :