25 easy PivotTable reports

Applies to
Microsoft Excel 2002

Having trouble visualizing how to use a PivotTable© report to analyze your data? This article shows how to start with a basic data source and create PivotTable reports that answer common questions about your data. While you can use these techniques for just about any type of source data, the following sections show reports you could use to:

To work directly with these PivotTable reports, download Excel 2002 Sample: PivotTable Reports, the companion workbooks for this article. In addition to copies of all of the source data and reports in the article, the workbooks contain four PivotChart reports that show you how to present the information graphically.

To make the examples extra easy to follow, they're all formatted with the Report 5 format, which you can apply to your own PivotTable reports with the Format Report command.

Compare your salespeople

Start with source data that contains information about your salespeople. Here are some of the source data records used for the reports in this section. To see the entire source range, download the companion workbooks for this article.

Example salesperson source data

To set up your PivotTable:

  1. Click a cell in the source data.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Follow the instructions in steps 1 through 3 of the wizard.

 Note   You can lay out the PivotTable report by clicking the Layout button in step 3 of the wizard, or you can lay out the report directly on the worksheet.

Now you are ready to try the following reports:

ShowWhat are the order amounts for each salesperson?

Drag the Salesperson field into the Row area, and then drag the Order Amount field into the Data area.

Layout diagram for order amounts report Order amounts PivotTable report

ShowWhat are the order amounts for salespeople in a specific country?

Drag the Country field into the Page area. You can then switch between "pages" to view the data for each country.

Layout diagram for country order amounts report Country order amounts PivotTable report

ShowHow are salespeople ranked by order amounts?

To sort the salespeople, click the Salesperson field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under AutoSort Options, click Descending, and then click Sum of Order Amount in the Using field box.

AutoSort settings for the sorted report Sorted salesperson PivotTable report

ShowWho are the top five salespeople?

To view the top items in a field, click the Salesperson field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On, click Top in the Show box, and then enter 5.

AutoShow settings for the top five report Top 5 salespersons PivotTable report

ShowHow did salespeople perform in a specific quarter?

Drag the Order Date field into the Row area. Note that the source data contains actual order dates, but it would be nice to group the dates by quarter. To do this, right-click the Order Date field, point to Group and Show Detail, and then click Group. In the By box , click Quarters. Note that in the example below, the Order Date field name has been changed to Quarters.

Layout diagram for the quarter by salesperson report Group settings for the quarter by salesperson report

Quarter by salesperson PivotTable report

ShowHow does each salesperson's performance vary by quarter?

Drag the Sales person field to the left of the Order Date field. Note that in the example below, the Order Date field name has been changed to Quarters.

Layout diagram for the salesperson by quarter report Salesperson by quarter PivotTable report

ShowWhat are the details for a specific order amount?

Double-click a cell in the PivotTable report to see the data "behind" it. Here are part of the details for cell C5, Buchanan's first-quarter sales:

Details for a data cell in a PivotTable report

ShowWhat percent is a specific order of the total order amounts?

In this case, you'll create a new data field and use a custom calculation. That way, you can compare the original data field (Summary) and the new calculated data field (Percent of Total). First, drag the Order Amount field into the Data area. Double-click the new Order Amount field, click Options, and under Show data as, click % of total.

Layout diagram for percent of country total report Percent of country total PivotTable report

ShowWhat bonus amount should each salesperson receive?

All salespeople receive a 10% bonus, but you'd like to award a 15% bonus if they exceed $20,000 in orders per quarter. To create a formula that uses PivotTable data, you can use a calculated field. Click the report, click PivotTable on the PivotTable toolbar, point to Formulas, and then click Calculated Field. In the Name box , type Bonus Amount. In the Formula box , enter the formula you want. If you want to use data from an existing field in your formula, click the field in the Fields box, and then click Insert Field.

Formula and settings for bonus amount report Bonus amount PivotTable report

Compare your products

Start with source data that contains information about your products. Here are some of the source data records used for the reports in this section. To see the entire source range, download the companion workbooks for this article.

Source data for products PivotTable reports

To set up your PivotTable:

  1. Click a cell in the source data.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Follow the instructions in steps 1 through 3 of the wizard.

 Note   You can lay out the PivotTable report by clicking the Layout button in step 3 of the wizard, or you can lay out the report directly on the worksheet.

Now you are ready to try any of the following reports:

ShowWhat are the sales totals for each category of product?

Drag the Category field into the Row area, and then drag the Sales field into the Data area.

Layout diagram for the category sales report Category sales PivotTable report

ShowWhat are the sales totals for each product?

Drag the Product field into the Row area.

Layout diagram for product sales report Product sales PivotTable report

ShowWhat are the three best-selling products in each category?

To view the top items in a field, click the Product field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then enter 3.

AutoShow settings for the top 3 products report Top 3 products PivotTable report

ShowWhat are the quarterly sales by product?

Drag the Quarter field into the Column area.

Layout diagram for the all quarters report All quarters PivotTable report

ShowHow do the sales in the first quarter compare with those in the second?

To focus on two quarters only, click the dropdown arrow in the Quarter field. Select the check boxes for just the first two quarters.

Quarter item settings for the Q1 & Q2 report Q1 & Q2 PivotTable report

ShowWhat are the average, largest, and smallest Beverage sales subtotals?

You can use more than one summary function for subtotals. Double-click the Category field, and then click one or more options under Subtotals.

Field settings for the multiple subtotals report Multiple subtotals PivotTable report

ShowWhat is the average sale and minimum sale?

In this case, you'll create two new data fields, and then change the summary function for each data field. That way, you can compare the original data field (Sum) and the two new data fields (Average and Minimum). First, drag the Sales field into the Data area a second time, and then drag it a third time. Double-click the second data field, and then click Average under Summarize by. Double-click the third data field, and then click Min under Summarize by.

Layout diagram for the average sales report Average sales PivotTable report

Compare your orders

Start with source data that contains information about your orders. Here are some of the source data records used for the reports in this section. To see the entire source range, download the companion workbooks for this article.

Source data for orders PivotTable reports

To set up your PivotTable:

  1. Click a cell in the source data.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Follow the instructions in steps 1 through 3 of the wizard.

 Note   You can lay out the PivotTable report by clicking the Layout button in step 3 of the wizard, or you can lay out the report directly on the worksheet.

Now you are ready to try the following reports:

ShowHow do you view an individual order?

Drag the Product, Unit Price, Quantity, and Discount fields into the Row area. Drag the Extended Price field into the Data area. Then drag the OrderID field into the Page area. You can then switch between "pages" to view the data for each order.

PivotTable reports can include or exclude hidden information in subtotals. For example, the totals in the orders below don't include items for tax and freight charges. If you want to include all items in the totals, you can use the Include Hidden Items in Totals button on the PivotTable toolbar.

Layout diagram for the orders report Orders PivotTable report

ShowHow many units of each product were sold?

Drag the Product field into the Row area, and then drag the Quantity field into the Data area.

Layout diagram for the quantity report Quantity PivotTable report

ShowWhat's the average unit price of each product?

Drag the Product field into the Row area, and then drag the Unit Price field into the Data area. Now you can change the summary function for the Unit Price data field from Sum to Average. Double-click the Unit Price field, and then click Average under Summarize by.

Layout diagram for average report Average unit price PivotTable report

ShowWhat are the ten most expensive products?

First, you need to change the summary function for the Unit Price data field to Maximum. Double-click the Unit Price field, and then click Max under Summarize by. Next, display the top ten items in the Product field. Click the Product field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then enter 10.

AutoShow settings for top 10 report Top 10 product prices PivotTable report

Compare your customers

Start with source data that contains information about your customers. Here are some of the source data records used for the reports in this section. To see the entire source range, download the companion workbooks for this article.

Source data for customers PivotTable reports

To set up your PivotTable:

  1. Click a cell in the source data.
  2. On the Data menu, click PivotTable and PivotChart Report.
  3. Follow the instructions in steps 1 through 3 of the wizard.

 Note   You can lay out the PivotTable report by clicking the Layout button in step 3 of the wizard, or you can lay out the report directly on the worksheet.

Now you are ready to try the following reports:

ShowWhat products do your customers purchase, and in which quarter?

Drag the Customer and Product fields into the Row area. Then drag the Qtr 1, Qtr 2, Qtr 3, and Qtr 4 fields into the Data area.

Layout diagram for customers report Customers PivotTable report

ShowWhat were the top two products purchased by each customer in the third quarter?

Click the Product field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then enter 2.

AutoShow settings for top 2 products report Top 2 products PivotTable report

ShowWho was your top customer in the first quarter?

Click the Customer field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then enter 1.

AutoShow settings for top Q1 customer report Top Q1 customer PivotTable report

ShowWho were the top two customers for each product?

Drag the Product field to the left of the Customer field. Click the Customer field, click PivotTable on the PivotTable toolbar, and then click Sort and Top 10. Under Top 10 AutoShow, click On. In the Show box , click Top and then enter 2.

Layout diagram for top 2 customers report Top 2 customers PivotTable report