03_772402 ch01.qxd 11/30/05 9:41 PM Page 1 Part I Understanding PivotTables and Charts n this part you learn about PivotTables and PivotCharts, which are powerful data-analysis tools in Excel. They are invaluable for pulling meaning from huge masses of seemingly meaningless data. Given their power, PivotTables and PivotCharts are surprisingly easy to use, but using them still involves many unavoidable complexities.
03_772402 ch01.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 3 Tip 1: Understanding How PivotTables Work 3 Understanding How PivotTables Work Why the term pivot? It comes from an analogy between the way PivotTables work and the way you investigate a physical object. Imagine that you have been handed a complex device and asked to figure out what it does. You don’t just look at it from one angle; rather you turn it in your hands, examining it from all possible perspectives to be sure you do not miss any important clues.
03_772402 ch01.qxd 4 11/30/05 9:41 PM Page 4 Part I: Understanding PivotTables and Charts What About Crosstab Tables? If you have used older versions of Excel, you may be wondering how PivotTables relate to another Excel data analysis technique, the crosstab table. The fact is that PivotTables are a replacement for crosstabs, which are not even supported in newer versions of Excel. PivotTables are significantly more powerful than crosstabs and are easier to use.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 5 Tip 3: Creating a PivotTable Report 5 Part I Figure 1-1: The sample data. Creating a PivotTable Report In this section I will guide you through the steps required to create a report that answers the question posed above: What are the sales for the Camping category for each region? To begin, you must start Excel and open the workbook that contains the raw data, SportingGoodsRawData.xls. This workbook is provided for download from wiley.
03_772402 ch01.qxd 6 11/30/05 9:41 PM Page 6 Part I: Understanding PivotTables and Charts In this dialog box, make sure that the options are selected as shown in the figure: • Select Microsoft Office Excel list or database. • Select PivotTable Then click the Next button to move to Step 2 of the wizard, shown in Figure 1-3. Select button Figure 1-3: In the second step of the PivotTable and PivotChart Wizard you select the data on which the PivotTable will be based.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 7 Tip 3: Creating a PivotTable Report 7 Figure 1-5: The newly created PivotTable report waiting to be customized. Your screen displays three elements, as shown in the figure: • The PivotTable itself, which is currently empty. • The PivotTable toolbar, which provides buttons for commonly used PivotTable tasks and commands. • The PivotTable Field List, which lists the data fields (columns) that are present in the raw data.
03_772402 ch01.qxd 8 11/30/05 9:41 PM Page 8 Part I: Understanding PivotTables and Charts Figure 1-6: The completed PivotTable report. I hope that you are suitably impressed with how easy it was to create this PivotTable report. Yes, it’s a simple one, but the same principles apply for more complex requirements. At this time I want to point out a couple of other aspects of PivotTable reports. When the report is active, the PivotTable Field List is displayed.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 9 Tip 4: Creating a PivotTable Report with Multiple Columns 9 The example PivotTable presented in the previous section was about the simplest PivotTable you can create. It will be useful to go through the process of creating a somewhat more sophisticated PivotTable report, one that has multiple columns as well as rows. The data you will use is shown in Figure 1-10. It is inventory data for a chain of videorental stores.
03_772402 ch01.qxd 10 11/30/05 9:41 PM Page 10 Part I: Understanding PivotTables and Charts Click Next to go to the second step of the wizard. You’ll see, as shown in Figure 1-10, that the range you selected earlier, A4:C28, is already entered in the Range field. This illustrates how you can select your data range before starting the PivotTable Wizard, as you have done here, or select it from the wizard, as you did in the earlier example. The results are the same either way.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 11 Tip 4: Creating a PivotTable Report with Multiple Columns 11 1. Drag the Store field from the Field List and drop it in the section of the PivotTable labeled Drop Row Fields Here. 2. Drag the Category field from the Field List and drop it in the section of the PivotTable labeled Drop Column Fields Here. 3. Drag the Titles field from the Field List and drop it in the section of the PivotTable labeled Drop Data Items Here.
03_772402 ch01.qxd 12 11/30/05 9:41 PM Page 12 Part I: Understanding PivotTables and Charts Figure 1-13: The PivotChart breaks the data down by store and by category. Figure 1-14: Selecting which Category fields to include in the chart. Creating a PivotChart A PivotChart is nothing more than a standard Excel chart created from the data in a PivotTable report. In fact there are a few features in PivotCharts that you will not find in charts based on other data — that is, data not in a PivotTable.
03_772402 ch01.qxd 11/30/05 9:41 PM Page 13 Tip 5: Creating A PivotChart 13 Figure 1-15: The PivotChart based on the information in the PivotTable report. This chart has some elements that don’t appear on standard Excel charts. For example, the PivotTable Field List is displayed and above the legend is a box labeled Drop Series Fields Here. These different elements are all PivotChart tools that enable you to customize which data are shown and how they are displayed.
03_772402 ch01.
