25 Easy Pivottable Reports - Excel - Office

Only available on StudyMode
  • Download(s) : 299
  • Published : April 13, 2013
Open Document
Text Preview
25 easy PivotTable reports - Excel - Office.com

http://office.microsoft.com/en-us/excel-help/25-easy-pivottable-report...

25 easy PivotTable reports
APPLIES TO

Hide All

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:

Compare your salespeople
Compare your products
Compare your orders
Compare your customers
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.

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:
What 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.

What 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.

of 8

2013-03-03 9:58 AM

25 easy PivotTable reports - Excel - Office.com

http://office.microsoft.com/en-us/excel-help/25-easy-pivottable-report...

How 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.

Who 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.

How 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.

How 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.

of 8

2013-03-03 9:58 AM

25 easy PivotTable reports - Excel - Office.com

http://office.microsoft.com/en-us/excel-help/25-easy-pivottable-report...

What 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:

What 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...
tracking img