Creating Summary Reports with Pivot Tables

1 Start with Detail DataTo create a Pivot table , you shouldstart with simple transactional want a single row of uniquefield headings above the data . Thereshouldn t be any blank rows orcolumns in the data , nor do youwant any rows or columns withtotals or subtotals in them. It s also abad sign if you have a repeating fieldthat extends across several of having columns like Janu-ary, February, March, etc., stretchingacross 12 columns,have one columnlabeled Month and 12 times asmany Your First Pivot table inExcel 2003 Select one cell within your data data , PivotTable and Pivot -Chart report .

2 Excel presents the firststep ofa three-step wizard. Since yourdata is in Excel, you can simply clickFinish to end the wizard. Excel willinsert a new worksheet, and you ll seea list of your fields on the right and ablank report outline with four sec-tions on the left, as shown in Figure complete the Pivot table , dragfields from the Field List to thereport. Using the list in Figure 1 as anexample, drag the Store field to therow fields area ( Drop Row FieldsHere ) in column A. Drag the Datefield to the column fields area in row the Sales field to the dataitems area.

3 If you want to be able tofilter by Region, drag the Region fieldto the page fields area in row will calculate the unique listof stores, dates, and the total salesfor each cell in the report . Figure 2shows the Pivot table . You can usethe dropdown in cell B1 to filter thereport to a particular Your First Pivot table in Excel 2007 Microsoft simplified the Pivot tableinterface in Excel 2007. Select one cellin the data set. From the Insert tab ofthe ribbon, choose PivotTable. ClickOK to accept the settings in the Cre-ate Pivot table dialog that new PivotTable Field List dia-log has the list offields at the top andfour drop zones at the bottom of thedialog.

4 Instead of dragging fields tothe report , you now drag fields to thedrop zones at the bottom of the dia-log. To build an equivalent report ,drag the date field to the ColumnLabels drop zone. Drag the Regionfield to the report Filter drop Store to the Row Labels Sales to the SValues area. Figure3 shows the completed Field List dia-log. Note that because the Region fielddropdown in cell B1 is filtered to theEast region,the funnel icon appearsnext to the Region field in the top halfofthe PivotTable Field List. You canclick on this filter icon to access sever-al new filters available only in Tables TricksI use Pivot Tables because they areeasy to create,but some of the realCreating Summary Reportswith Pivot Tables |BY BILL JELENIn last month s column, I described how to useExcel s Subtotal function to produce various summaryreports.

5 A more powerful tool for Creating summaryreports is the Pivot table command. In many cases, apivot table will allow you to summarize tens of thou-sands ofrows of data in as few as six mouse FINANCEIJune 2008power comes after the Pivot table iscomplete. For example, if someoneneeds to see the detail behind anynumber in the Pivot table , simplydouble-click that number to producea drill-down you want to create a Pivot tablefor every Region, add the Region fieldto the Page area. In Excel 2003, openthe PivotTable dropdown on thePivotTable toolbar.

6 Choose ShowPages and click OK. Excel will addnew worksheets, each containing asimilar Pivot table for each region. (InExcel 2007, this feature is a bit to the PivotTable Tools Optionstab ofthe the drop-down next to the Options icon on theleft side of the ribbon. Choose ShowReport Filter Pages and click OK.)You can also add multiple fields tothe Row area and use the Expandand Collapse buttons to quicklychange the level of detail shown inthe you include a date field in yourreport, you can quickly summarizedaily dates up to months, quarters,and one ofthe cellscontaining a date.

7 In Excel 2003,right-click the date, choose Groupand Show Detail, Group. Selectmonths, quarters, or years. In Excel2007, select a date field and clickGroup Field in the Options tab ofthe rearrange the Pivot table , sim-ply drag field headings to a new sec-tion of the report in Excel 2003 or toa different drop zone in Excel table TrapsBe aware oftwo potential trapswhen using Pivot Tables . First, youaren t allowed to insert any rows orcolumns in a Pivot table . I frequentlycreate a Pivot table , copy the pivottable, then Paste Values to getaround this limitation.

8 Second, pivottables don t automatically recalculatewhen you change the underlyingJune 2008 ISTRATEGIC FINANCE63 Figure 3continued on page 69 Figure 1 Figure 2 data set. Excel is able to create pivottables quickly by reading the datafrom the worksheet into a Pivot tablecache. If you change the underlyingdata, you need to force Excel to pullthe new data into the cache. In Excel2007, use the Refresh button on theOptions tab. In Excel 2003, click thered exclamation icon on the pivottable Tables are often the fastestway to reduce thousands of rows ofdata to a Summary report and thenhave the flexibility to rearrange thereport as Jelen is the author ofPivot TableData crunching .

9 Send questions forfuture articles to 2008 ISTRATEGIC FINANCE69[EXCEL]cont d from p. 63