14 August 2008
Create a PivotTable report Open the workbook where you want to create the PivotTable report. If you are basing the report on a Microsoft Excel list or database, click a cell in the list or database. 1. On the Data menu, click PivotTable and PivotChart Report. 2. In step 1 of the PivotTable and PivotChart Wizard, follow the instructions, and click PivotTable under What kind of report do you want to create? 3. Follow the instructions in step 2 of the wizard. 4. In step 3 of the wizard, determine whether you need to click Layout. Do one of the following: • If you clicked Layout in step 3, after you lay out the report in the wizard, click OK in the PivotTable and PivotChart Wizard – Layout dialog box, and then click Finish to create the report. • If you did not click Layout in step 3, click Finish, and then lay out the report on the worksheet. Lay out a new report with the PivotTable and PivotChart Wizard – Layout dialog box Use this procedure after you click Layout in step 3 of the PivotTable and PivotChart Wizard.
From the group of field buttons on the right, drag the fields that you want onto the ROW and COLUMN areas in the diagram.
In PivotTable reports, row field and item labels appear on the left. Column field and item labels appear across the top. In PivotChart reports, row field items are plotted on the category axis,and column field items are plotted as data series.Drag the fields that contain the data that you want to summarize onto the DATA area. In PivotChart reports, data fields determine the values for the value axis. For OLAP source data, only fields identified by at the beginning of the row on the toolbar can be used for data fields. These fields appear at the bottom of the group of field buttons.
For PivotTable or PivotChart reports that are based on excel list you can use a different summary function for a data field. If you want to do this, double-click the field in the DATA area, click the function that you want in the Summarize by list, and then click OK
To include a data field more than once so you can select an additional summary function, drag the field onto the DATA area a second time. Drag fields that you want to use as page fields onto the PAGE area.
By default, Microsoft Excel retrieves data for all page fields items at once. To have page fields retrieve data separately for each item, double-click each field in the PAGE area, click Advanced, click Query external data source as you select each page field item, and then click OK twice.
To rearrange fields, drag them from one area to another. To remove a field, drag it out of the diagram. When you are satisfied with the layout, click OK, and then click Finish.
There are two ways to rearrange the fields after Excel creates the report. You can try rearranging the fields on the worksheet or you can click the finished PivotTable report (or the associated PivotTable report for a PivotChart report), click PivotTable Wizard on the PivotTable toolbar to return to step 3, and then click Layout. Delete a PivotTable report 1. Click the PivotTable report. 2. On the PivotTable toolbar,click PivotTable, point to Select, and then click Entire Table. 3. On the Edit menu, point to Clear, and then click all.
CAclubindia
