Pin Me

Microsoft Excel: Use a Pivot Table to Summarize Detailed Data

written by: Mr Excel•edited by: Tricia Goss•updated: 11/18/2011

Problem: You have 50,000 rows of sales data, as shown in Fig. 841. You want to produce a summary report showing sales by region and product.

  • slide 1 of 2

    Strategy: Use a pivot table. As Excel’s most powerful feature, pivot tables are well suited to this type of analysis. Follow these steps.

    1) Ensure that your data is in list format. It is important that every heading be unique.

    2) Select a single cell in the database. From the Data menu, select Pivot Table and Pivot Chart Report….

    3) Choose Next in Wizard Step 1 to confirm that your data is in Excel, as shown in Fig. 842.

    4) In Step 2, Excel’s intellisense will guess the range of your data. It is usually correct. Choose Next, as shown in Fig. 843.

    5) In Wizard Step 3, choose the Layout button in the lower left corner, as shown in Fig. 844.

    You will now see the powerful layout dialog. As shown in Fig. 845, all of your available fields are along the right side of the dialog. On the left side of the dialog is a template where you can drop various fields.

    To build your pivot table, you will drag a field to the proper location. In this case you will want to have products going down the side of the report and regions going across the top.

    6) Drag the Product field from the Field list and drop it in the Row area of the layout, as shown in Fig. 846.

    7) Drag the Region field from the Field list and drop it in the Column area of the layout, as shown in Fig. 847.

    8) Drag the Revenue field to the Data area of the layout. When you drop a field in the Data area of the layout, Excel chooses a default operation. In this case, Excel correctly chose Sum of Revenue, as shown in Fig. 848. However, if Excel encounters any cells with text or blanks, it will instead choose to use Count of Revenue.

    9) If Excel’s guess for the function in the Data area is incorrect, double click the field to display the PivotTable Field dialog, as shown in Fig. 849. Select the correct function for summarization. Choose OK to close the Layout dialog. You will be back in Step 3 of the Wizard.

    10) As shown in Fig. 850, you are given a choice of creating the pivot table on a new sheet or in a blank section of the current worksheet. For this example, choose to have the pivot table on a new sheet.

    11) Click OK. In a few seconds, the 50,000 rows of data will be summarized and presented on a new worksheet, as shown in Fig. 851.

    Additional Details: Pivot tables offer many powerful options. Although this chapter offers the steps to create your first pivot table, you should read the next several cases to learn more about pivot tables.

    Summary: The pivot table command allows you to summarize thousands of rows of data quickly. It does not require you to know any formulas. You just need to be able to drag fields to a report.

  • slide 2 of 2

    Images

    Fig. 841Fig. 842Fig. 843Fig. 844Fig. 845Fig. 846Fig. 847Fig. 848Fig. 849Fig. 850Fig. 851