What are Pivot Tables and why would I need to use them?
	What is the PivotTable Wizard?
	How do I create a Pivot Table from an Excel list?
	How do I create a Pivot Table from an external database?
	How do I use a Pivot Table to consolidate multiple ranges?
	How do I use Crosstab in Excel 5 and 7 like I did in version 4.0 to analyze data?

What are Pivot Tables and why would I need to use them?
Keywords: Pivot Tables Database Lists Search
Posted July 16, 1996

Excel enables you to organize your data into lists that allow quick access for analysis. Database analysis has different levels of complexity. The first level is simple lookup and retrieval systems using the data form. The next level involves retrieval using the criteria and extract techniques explained in Lists and Reports. The next level of database analysis involves a single question to multiple variables. This is where the Pivot Table is an invaluable tool.

Pivot tables work by summarizing the data in one field and breaking it down according to the data (data field) in another field. This type of analysis is sometimes known as Cross Tab analysis. These values (row field) become the row headings. If you wanted to analyze a database consisting of sales by sales representatives, you could break them down by Region (East, West, Midwest, and South) as row headings. You could further analyze the information using a third field, sales per quarter (column field). With a pivot table using the "pivoting" feature, you could drag the column field over to the row field area. The result would show each region as the main row category, with the quarters as regional subcategories.

What is the PivotTable Wizard?
Keywords: List Database PivotTable Wizard
Posted July 16, 1996

Excel provides the PivotTable Wizard as a tool to save you time in creating and editing pivot tables. The Wizard uses a four step process that enables you to build a pivot table from scratch. The four steps are:

1. Specify the type of source list to use for the pivot table - The key here is that data can be in Excel, Access, etc...
2. Identify the location of the data
3. Define the row, column, page, and data fields for the table
4. Select a location, name, and other options for the table and then create the table

The PivotTable Wizard dialog box contains buttons that enable you to navigate the Wizard quickly. The buttons are:

• Help - Displays a Help window for the current step
• Cancel - Closes the PivotTable Wizard without creating the table
• Back - Goes back to the preceding step
• Next > - Goes to the next step
• Finish - Creates the pivot table. However you have to click in a cell representing the upper left most corner of the Pivot.

To access the PivotTable Wizard, from the menu bar choose Data - PivotTable.

How do I create a Pivot Table from an Excel list?
Keywords: Create PivotTable List Database
Posted July 16, 1996

The most common source for pivot tables is an Excel list. Just about any list can be made into a pivot table, however, the list should have the following characteristics:

• At least one field, and best if it's two or three fields, that is "groupable" in that the field contains a limited number of distinct data (text, numbers, or dates)
• Each field must have a heading

To organize a list with the preceding criteria into a pivot table:

1. Select a cell inside the data range
2. From the menu bar choose Data - PivotTable -the PivotTable Wizard will appear
3. 
   Shortcut: You can also access the PivotTable Wizard using the PivotTable Wizard button on the Query and Pivot toolbar.
4. In Step 1 of 4. select the Microsoft Excel List or Database option and then choose Next >
5. In Step 2 of 4, the range from the cell you previously selected is displayed in the Range text box; if it is missing, type in the correct range or drag the mouse over the cells that contain the data you are analyzing; then choose Next >
   NOTE: If the workbook containing the list is not open, use the Browse button, highlight the workbook in the Browse dialog box, and choose OK.
6. In Step 3 of 4, specify the layout of the pivot table by dragging the field labels on the right to the appropriate areas; then click Next >
7. Step 4 of 4 allows you to change many aspects of the pivot table
8. • In the Starting Cell text box, select the upper-left corner of the range to be used
   • Enter a name for the table in the PivotTable Name box
   • The check boxes in the PivotTable Options group to refine the table's display:

     Grand Totals For Columns - to add an extra row at the bottom of the table to show grand totals for each column

     Grand Totals For Rows - to add an extra column on the right of the table to show grand totals for each row

     Save Data With Table Layout - to make pivot table updating faster

     AutoFormat Table - to format the pivot table using Excel's default AutoFormat

   • Select the Finish button for Excel to create the pivot table in the location you specified and display the Query and Pivot toolbar

NOTE: For additional help with learning about PivotTables, check out our Excel Online Experience on PivotTables.

How do I create a Pivot Table from an external database?
Keywords: Create Database List PivotTable External
Posted July 16, 1996

The PivotTable Wizard can assist you in creating a pivot table from an external source such as an Access, FoxPro, or SQL Server database. With Microsoft Query installed on your computer, the PivotTable Wizard will load Microsoft Query so that you can create a result set for the data you want to analyze. To create a Pivot Table using an external source:

1. From the menu bar, choose Data - Pivot Table-Excel will display the PivotTable Wizard
2. In Step 1 of 4, select the External Data Source option and click Next >
3. In Step 2 of 4, select the Get Data button-Excel loads Microsoft Query
4. Using the Query, select a data source, filter the data, and display the result set you want to use
5. Select Query's File - Return Data to Microsoft Excel command-the Wizard stores the result set but does not display it in the worksheet and click Next > to continue
6. In Step 3 of 4, specify the layout of the pivot table by dragging the field labels on the right to the appropriate areas; then click Next >
7. Step 4 of 4 allows you to change many aspects of the pivot table
8. Select the Finish button for Excel to create the pivot table in the location you specified and display the Query and Pivot toolbar

How do I use a Pivot Table to consolidate multiple ranges?
Keywords: Database List PivotTable Ranges Consolidate
Posted July 16, 1996

You can easily consolidate multiple ranges using Pivot Tables as long as the ranges you are dealing with have identical row and column labels. This procedure is accomplished using the Data - Consolidate command. However, using the PivotTable Wizard results in a table that is more flexible and customizable than the static table produced by Excel's Consolidation feature. For example, to consolidate three worksheets:

1. From the menu bar, choose Data - PivotTable-Excel displays the PivotTable Wizard
2. In Step 1 of 4, select the Multiple Consolidation Ranges option and then click Next >
3. In the Step 2a of 4 dialog box, activate either the Create a single page field option or I will create the page field option and then click Next >
4. For each range you want to consolidate, enter a reference in the Range text box in the Step 2b of 4 dialog box-then choose the Add button and click on Next >
5. In Step 3 of 4, specify the layout of the pivot table by dragging the field labels on the right to the appropriate areas; then click Next >
6. Step 4 of 4 allows you to change many aspects of the pivot table
7. Select the Finish button for Excel to create the pivot table in the location you specified and display the Query and Pivot toolbar

How do I use Crosstab in Excel 5 and 7 like I did in version 4.0 to analyze data?
Keywords: Crosstab Version Analyze Data
Posted January 16, 1996

Beginning with Microsoft Excel version 5, data analyzation became much easier and more dynamic. Excel introduced a revolutionary feature, the Pivot Table. You can use the PivotTable Wizard to create Crosstab-like tables from Microsoft Excel data or external data. The PivotTable Wizard is a set of interactive dialog boxes that guide you through all the steps and help you select a data source and a layout for your table. Once you have created a "table" otherwise known as a "pivot table", you can customize it by dragging around categories, or by changing the calculation or the formatting, or by hiding and/or showing detail. If you have Crosstab tables in Excel 4, you can easily convert them to Pivot tables.

To convert these tables, start by opening Microsoft version 5 or 7, then opening the worksheet containing the Microsoft Excel version 4.0 Crosstab table. Then from the Data menu, choose PivotTable and follow the instructions for choosing a layout. When you are done, click on Finish. Note that when you save the file, it will be saved as an Excel 5 or 7 workbook and the Crosstab will be permanently converted.

Back to Top

Back to the FAQ Table of Contents

Department of Redundancy Department