Creating Pivot Tables and Pivot Charts

The most sophisticated data-management feature in Excel is the pivot table, an organization and analysis tool that displays the fields and records in your list in new and potentially useful combinations. Pivot tables are made easy in Excel by a powerful wizard on the Data menu, and in Excel 2000 the wizard has been expanded to create colorful pivot charts: compelling graphical reports that display pivot table information visually. In this section, you'll learn how to create both pivot tables and pivot charts by using the PivotTable And PivotChart Wizard, and you'll learn how to connect to external data sources to feed this command in powerful new ways.

Using the PivotTable and PivotChart Wizard

The best way to learn about a pivot table is to create one. Fortunately, the PivotTable And PivotChart Wizard gives you complete control over the position of row and column headings in your table, so that you can rearrange all the important variables down the road. To create a pivot table, follow these steps:

  1. Click a cell in the list that you want to view as a pivot table.
  2. From the Data menu, choose PivotTable And PivotChart Report. The PivotTable And PivotChart Wizard starts and prompts you for the source of data for the table, as shown at the top of the next page.
  3. NOTE
    If the Office Assistant is enabled, you'll have an opportunity now to learn more about pivot tables and the various options you have when creating a database report. Feel free to seek guidance from the Office Assistant as you use the PivotTable And PivotChart Wizard. When you no longer need it, click the Excel Help button in the lower left-hand corner of the wizard dialog box to dismiss the Office Assistant.

  • Verify that the first option, Microsoft Excel List Or Database, is selected, and click Next.
  • click to view at full size.

    In this example, you'll create a pivot table from a list in your worksheet. However, you can also create pivot tables from external data (such as records received by Microsoft Query), multiple consolidation ranges, or another pivot table or pivot chart. After you select a data source, Excel prompts you for a data range.

  • If you had a list active when you started the wizard, Excel might have already selected it for you. If not, select data from an Excel list now using the mouse. (Be sure to include the column headings.)
  • Don't worry about the dialog box getting in the way— Excel will minimize it when you start selecting cells, giving you a full-window look at your data. Our example screen looks like this:

    click to view at full size.

  • Click Next to display the final screen of the PivotTable And PivotChart Wizard. By default, Excel creates pivot tables in new worksheets, though you can also specify an existing worksheet and even an exact location within a worksheet.
  • click to view at full size.

    In Excel 97, the PivotTable wizard displayed a layout grid at this point to help you build your pivot table, but this feature is now provided directly in the worksheet by means of an enhanced PivotTable toolbar. However, you can use the old pivot table "construction" dialog box if you like by clicking the Layout button in the third wizard screen (Step 3). In addition, you can use the Options button now or in the future to fine-tune how your pivot table or pivot chart appears.

  • Click Finish to accept the default settings and continue building your pivot table. The PivotTable And PivotChart Wizard opens a new worksheet, creates a blank pivot table, and displays the PivotTable toolbar, as shown in the illustration at the top of the next page.
  • Define the initial layout of your pivot table by dragging fields from the PivotTable toolbar into the Row, Column, Data, and Page Fields areas in the worksheet.
  • Fields placed in the Row area will become rows in your pivot table, fields placed in the Column area will become columns, and fields placed in the Data area will be added together with the SUM function. You can arrange, or pivot, these values later, so don't worry too much about the final placement of fields now. (The Page area is reserved for fields that you want to take a closer look at.)

    click to view at full size.

    In the following illustration, the Month field has been placed in the Row area, the Sales Rep field has been placed in the Column area, and the Sale field is being selected on the PivotTable toolbar. (We're just starting to drag it to the Data area.)

    click to view at full size.

    After a field has been placed in the Data area, Excel finalizes the pivot table, as shown in Figure 22-9.

    click to view at full size.

    Figure 22-9. A pivot table allows you to rearrange the rows and columns in your worksheet, creating new views of your data.

    Evaluating a Pivot Table

    It might take you a moment to recognize the data in your pivot table, because it presents an entirely new view of your list. It's almost as if you had created new row and column headings, typed all the data again, and used the Subtotals command to summarize the results! However, you didn't have to rearrange your worksheet manually— the PivotTable And PivotChart Wizard did it for you. Best of all, you can easily transpose one or more fields and use new functions to highlight other trends in your list.

    To help you work with the pivot table and create pivot charts, Excel displays the PivotTable toolbar, shown in Figure 22-10. You'll find this toolbar useful when evaluating and customizing your pivot tables and pivot charts. Take a moment to examine the buttons and commands on the PivotTable toolbar, and then read the summary data in your new pivot table, especially the Grand Total row and column.

    click to view at full size.

    Figure 22-10. The PivotTable toolbar contains several unique commands and buttons specifically designed for manipulating pivot tables.

    Rearranging Fields in a Pivot Table

    To rearrange, or pivot, the data in your pivot table, just click the fields in the table and move them to new locations. You can also remove unwanted fields by dragging them to the PivotTable toolbar and add new fields by dragging field names from the toolbar onto the pivot table.

    As you edit or rearrange the data in the pivot table, note that your changes don't affect the data in your list (which is in its own worksheet)— your original rows and columns remain the same. However, if you change the cells in your list, you'll need to click the Refresh Data button on the PivotTable toolbar to see the changes.

    Changing the Function in a Pivot Table

    By default, the PivotTable And PivotChart Wizard uses the SUM function to add up values in the Data area of your pivot table, but you can easily change the function to calculate another value. For example, you could use the AVERAGE function to calculate the average sales in a month, or the COUNT function to total up the number of sales orders written by a particular employee. The list of functions available is identical to the set employed by the Subtotals command, described in Table 22-1.

    To change the function used in a pivot table, follow these steps:

    1. Open the sheet containing your Excel pivot table, if it isn't already open.
    2. In the upper left corner of your pivot table (the cell above the Row field and to the left of the Column field), double-click the Data field name. In our example, the cell's location is A3 and contains the title Sum of Sale.
    3. You'll see the PivotTable Field dialog box, as shown in Figure 22-11.

    4. In the Summarize By list box, select the new function that you want to use. (For example, select the MAX function to display the largest sales total in the field.)

    click to view at full size.

    Figure 22-11. Double-click the cell containing the current function and field to display the PivotTable Field dialog box.

    You can also use the Field Dialog button on the PivotTable toolbar to display the PivotTable Field dialog box and change the function used to summarize a field in your pivot table. The PivotTable Field dialog box displays different options, depending on the type of entry selected.

    Adjusting the Formatting in a Pivot Table

    When you use the PivotTable And PivotChart Wizard to modify a pivot table, Excel automatically reformats the table to match the data in your list and to calculate the result of the function that you're using. Avoid making manual changes to the table formatting, because the AutoFormat table feature will overwrite them each time you rearrange the pivot table.

    However, you can make lasting changes to the numeric formatting in the Data area by following these steps:

    1. Click any numeric data cell in the pivot table (not a row or column heading).
    2. Click the Field Settings button on the PivotTable toolbar. The PivotTable Field dialog box opens.
    3. Click the Number button. The familiar Format Cells dialog box appears, as shown in Figure 22-12. This dialog box allows you to adjust the formatting of the numbers in the Data area.
    4. Figure 22-12. Use the Number button in the PivotTable Field dialog box to display a list of numeric formatting options for data in your pivot table.

    5. Select a type of numeric format in the Category list box, and then select a formatting style. For example, to add currency formatting to numbers, click the Currency category, specify the number of decimal places you want, and specify a style for negative numbers.
    6. Click OK to close the Format Cells dialog box, and then click OK to close the PivotTable Field dialog box. Excel will change the numeric formatting in the table, and these changes will persist each time you modify the pivot table.

    Use AutoFormat for Fast Style Makeovers
    To change the heading and line style, highlight a cell in the pivot table, and choose AutoFormat from the Format menu. Excel will display a list of table styles for you to choose from. Select the style you want, and then click OK to reformat and recalculate the pivot table.

    Displaying Pivot Charts

    If you find pivot tables addictive, you'll want to take advantage of Excel 2000's newest data analysis tool, the pivot chart. A pivot chart is a graphical version of an Excel pivot table. Pivot charts are created from existing pivot tables and are placed in new charting worksheets in the workbook. Like pivot tables, pivot charts have dynamic, customizable fields that you can drag back and forth to the PivotTable toolbar and move around the charting area. You can also modify the functions used to analyze data in a pivot chart.

    To analyze an Excel list using a pivot table chart, follow these steps. (If you already have an existing pivot table in your workbook, click a cell in the pivot table, and start with step 4.)

    1. Click a cell in the list that you want to view as a pivot chart.
    2. From the Data menu, choose PivotTable And PivotChart Report.
    3. TIP
      Although the PivotTable And PivotChart Wizard gives you the option of creating a pivot chart in the first wizard step, you'll still be required to create a pivot table in the third wizard step to base the pivot chart on. For this reason, we recommend that you simply follow the default pivot table options when you create a pivot chart. (You won't lose any time doing so.)

  • Answer the questions posed by the PivotTable And PivotChart Wizard, and then create a new pivot table by dragging the appropriate row, column, and data values from the PivotTable toolbar to your new pivot table. For more information about creating a new pivot table, see "Using the PivotTable And PivotChart Wizard"
  • Click the Chart Wizard button on the PivotTable toolbar to open a charting worksheet and build a new pivot chart based on the selected pivot table. You'll see a chart that looks similar to the one in Figure 22-13.
  • You are now free to customize and format the pivot chart as you see fit. See Figure 22-13.
  • TIP
    In a pivot chart, the legend represents the column field and the charting category represents the row field. To customize either of these values, right-click the associated field button in the chart, and select a command from the pop-up menu that appears.

    click to view at full size.

    Figure 22-13. Excel 2000 allows you to analyze your database lists with customizable pivot charts.

    Using External Data Sources in Pivot Tables

    When you created your pivot table by using the PivotTable And PivotChart Wizard, you had the option of using external data as the source of your information. One method of extracting external data for your pivot table is by using Microsoft Query, a program shipped with Microsoft Office that you can use to connect to external data sources using a software driver called ODBC (Open Database Connectivity). Query acts as a link between Excel and database files that have diverse data formats, such as Access, FoxPro, SQL Server, dBASE, Paradox, and Btrieve. In Excel 2000, you can also use Query to access external data sources for your pivot tables known as OLAP cubes. OLAP stands for On-Line Analytical Processing, a format designed to consolidate massive amounts of corporate information using units that are sometimes referred to as data warehouses.

    Query uses ODBC to translate complex data-filtering questions, or queries, into a language called SQL (structured query language). As a result, you can use Query to extract information about compatible database files in sophisticated ways. For example, your query might be, How many sales reps do we have who sell more than $20,000 in products per year and who work in the South or the Midwest?

    When you want to work with external database files, consider using Query as a stand-alone tool or as a utility to import your data into Excel. Start Query in Excel by clicking the Data menu and picking a command from the Get External Data submenu. To use Query for accessing data in constructing a PivotTable, select External Data Source in Step 1 of the PivotTable And PivotChart Wizard, and then click Get Data in Step 2. (Query is an add-in program, so you'll have to install it using the Office Setup program before it can be accessed.)

    For more information about using Query to manage external data sources, search for "Query (Microsoft)" in the Excel Help Index. You can also access a series of helpful cue cards from within the Query Help menu.

    Running Microsoft Office 2000 Small Business
    Running Microsoft Office 2000
    ISBN: 1572319585
    EAN: 2147483647
    Year: 2005
    Pages: 228
    Authors: Michael Halvorson, Michael J. Young
    BUY ON AMAZON © 2008-2017.
    If you may any questions please contact us: