Creating a PivotTable Report

To create a PivotTable report:

1. Select any cell in the source data, and press Ctrl+Shift+* (in Excel 2003, press this or Ctrl+A).
2. Press Ctrl+F3, and then type the defined Name for the source data.
3. From the Data menu, select PivotTable and PivotChart Report.
4. In Step 1 of 3, select Microsoft Excel list or database, and then click Next.

In Step 2 of 3, in the Range box, press F3 to open the Paste Name dialog box, and paste the Name of the source data as defined in step 2.

5. Click Next.
6. In Step 3 of 3, click Layout (in Excel 97, go to step 8).
7. In Excel 2002 and Excel 2003, you can skip this step. Instead, click Finish in Step 2 of 3 and then create the PivotTable report by dragging the fields from the Pivot Table Field List dialog box to the PivotTable report.
8. In the Layout dialog box, drag the Data Fields to the white Data area, and drag all other fields to the white Page area (except fields that are not going to be used in the PivotTable report), and then click OK.
9. In Step 3 of 3, click Finish. The PivotTable report is created.
10. Drag Data (in cell A5 in the screenshot) to the right of the PivotTable report to change the layout from horizontal to vertical.
11. The PivotTable report is now ready to be used. For more details on how to use the PivotTable report properly, see the other tips in this category.

In this way we can create pivot table with excel report.

Users are saying about us...

  1. "But is there a way to update the original worksheet data from the itemized list created from the Pivot Table drill down? In other words to link it somehow.
    It's nice and easy to check which records need updating through a pivot table, but a pain to go back to the original worksheet to update. Let me know! Scott"

  2. Hi Scott, Easiest way to do this is probably to apply an (auto) filter to your data range, and find the records that need to be adjusted that way. You should be able to use the same kind of criteria as you do with the pivot table. You might find you have to move beyond an Auto filter, but the full filtering facility should be able to help I would think. Alan

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube