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

| 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.                    |
|---------------------------------------------------------------------|

1. 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."

2. 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."

3. 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."

4. 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."