PivotTable reports for Sales 1: Compare your salespeople

Before you can plan an effective sales strategy, you need to effectively analyze your sales data. But it's hard to analyze sales trends and opportunities by looking at a large database of sales results. Even looking at reports someone else has created for you may not tell you what you need to know. Fortunately, Microsoft PivotTable® reports give you a flexible, easy way to sort and analyze your data and to create a variety of reports—reports that meet your specific needs. You can decide what information is most useful to you and how you want to view it.

How is a PivotTable report more useful than a basic spreadsheet?

A basic spreadsheet may just show a list of data. A PivotTable report sorts the information (for example, by salesperson name or by the sales totals for each person) and lets you easily move those pieces around to see how they all relate to each other. Since a PivotTable report is separate from your source data, you can make as many PivotTables as you want without changing or rearranging your source data.

In this example, you'll create a PivotTable report. Then, by moving fields around, you'll be able to answer questions such as:

  • What were each salesperson's annual results?
  • Who are the top sales performers for the year?
  • How are our salespeople ranked by quarterly performance?
  • How does each salesperson's results vary by quarter?
  • Which five salespeople should receive an award for annual sales?
  • How do sales for each country compare by quarter?

This example uses sales data about Country, Salesperson, Order Date, OrderID, and Order Amount to create a PivotTable report that shows which salesperson was responsible for which sales. You can view the data in multiple ways by moving different sets of data on and off the PivotTable report.

Start with data in an Excel worksheet

To create a PivotTable report, you need an Microsoft Excel worksheet with the data you want to use. To follow along with this example, click Pivot Table Salespeople and follow the instructions to download the Excel worksheet. Be sure to save the file where you will be able find it later.

PivotTable report source data

Create a report with the PivotTable and PivotChart Wizard

  1. In the Excel worksheet, select the columns and rows that contain the data from which you want to create a PivotTable report. Be sure to include the header row.

 Note   To quickly select all adjacent columns and rows that contain data, click one cell, and then press CTRL + A.

  1. On the Data menu, click PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard.
  2. Under Where is the data that you want to analyze?, click Microsoft Office Excel list or database.
  3. Under What kind of report do you want to create?, click PivotTable, and then click Next.

In the Range box, you can see the range of data that you selected to use in your PivotTable report. In this example, the range is column A, row 1 through column E, row 800.

PivotTable and PivotChart Wizard - Step 2 of 3

  1. Click Next to continue.

If you are asked if you want this report to be based on the same data as your existing report, click No.

  1. In PivotTable and PivotChart Wizard – Step 3 of 3, under Where do you want to put the PivotTable report?, click New worksheet, and then click Finish.

A blank PivotTable report opens in a new worksheet in Excel. Now you can move your fields from the PivotTable Field List onto the PivotTable report to organize your data.

Blank PivotTable report

  1. Drag the Salesperson field from the PivotTable Field List to the Drop Row Fields Here area of the PivotTable report.

As you can see, Excel automatically populates column A with the data from the Salesperson column in your worksheet.

PivotTable report showing results by salesperson

  1. To add the Order Amount field to your report, drag the Order Amount field from the PivotTable Field List to the Drop Data Items Here area of the PivotTable report.

The PivotTable report now shows a summary of the amount of sales each salesperson has produced.

Order Amount field on PivotTable report

  1. Drag the Country field from the PivotTable Field List to the Drop Page Fields Here area in the upper left corner of your worksheet.

Notice that the PivotTable report creates a drop-down arrow for Country in cell B1.

Screen shot

ShowWhat happened to the PivotTable Field List?

If the PivotTable Field List disappears, it's easy to get it back.

  1. Click the View menu, point to Toolbars, and click PivotTable.
  2. On the PivotTable toolbar, click the Show Field List button.

PivotTable toolbar and Show Field List button

You did it!

Your PivotTable report is complete. If you're following this example, you've taken the data from 800 orders and created a report that shows total sales by salesperson. Now, let's see how easy it is to get more answers from the same data by simply moving the data around.

ShowWhat if I'm only interested in sales results for one country?

To see sales activity by country, you can click the Country drop-down arrow and select a country. You can change the country or countries you want to see at any time, with any of the following reports.

Who are the top sales performers for the year?

See your top performers quickly with the AutoSort function.

  1. Click the Salesperson field (cell A4 in this example), on the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
  2. Under AutoSort Options, click Descending.
  3. In the Using field box, click Sum of Order Amount.
  4. Click OK.

PivotTable toolbar with PivotTable Sort and Top 10 dialog box

How are the salespeople ranked by quarterly performance?

In this example, salesperson Peacock has the highest total sales for the year. To give better recognition and support to your sales force, however, you need to know the top salespeople for each quarter. To create this report:

  1. Drag the Order Date field into the Row area to the left of the Salesperson field.
  2. Right-click the Order Date field, point to Group and Show Detail, and then click Group. In the By box, click Quarters. (Make sure that no other options are selected.)

PivotTable showing salespeople ranked by quarter

How does each salesperson's results vary by quarter?

You can see that Peacock is the number two salesperson in Quarter 1, but in Quarter 2, he slips to number 5. To plan sales strategy improvements, you want to see how each salesperson's performance varies by quarter.

  • Drag the Salesperson field to the left of the Order Date field.

PivotTable showing each salesperson by quarter

Now you can see that salesperson Peacock had an exceptional first quarter, followed by much lower sales in the second and third quarter. Salesperson Davolio, on the other hand, maintained steady sales throughout the year and had his best quarter at the end of the year.

Which five salespeople should receive an award for annual sales?

Say you want to give a special award to your top five salespeople. You can quickly find your top five performers for the year. Before you start this procedure, drag the Order Date field back to the PivotTable Field List.

To view the top five salespeople:

  1. Click the Salesperson field.
  2. Click PivotTable on the PivotTable toolbar, and then click Sort and Top 10.
  3. Under AutoSort options, click Descending, and under Using field, click Sum of Order Amount.
  4. Under Top 10 AutoShow, click On, in the Show box, click Top, and then type 5.
  5. In the Using field box on the right, click Sum of Order Amount.
  6. Click OK.

PivotTable showing top salespeople and PivotTable Sort and Top 10

Create a chart of the top salespeople's results

You can create a chart with literally one click. To see a chart of the top salespeople's results:

  • On the PivotTable toolbar, click the Chart Wizard button.

PivotChart report showing top five salespeople

How do sales for each country compare by quarter?

To compare sales results by country for each quarter, go back to your PivotTable report worksheet and make these changes:

  1. Drag the Salesperson field and the Country field back to the PivotTable Field List.
  2. Drag the Order Date field from the PivotTable Field List to the Drop Row Fields Here area on the left side of the PivotTable report (cell A4 in this example).
  3. Right-click the Order Date field, point to Group and Show Detail, and then click Group. In the By box, click Quarters. (Make sure that no other options are selected.)
  4. Drag the Country field from the PivotTable Field List to the left of the Order Date field.
  5. On the Format menu, click AutoFormat, click Table 1, and then click OK.

AutoFormatted Pivottable with sales results by country

You can now compare sales for each country side by side.

ShowHow can I remove AutoFormatting?

On the Format menu, click AutoFormat, click None, and then click OK.

Create and save multiple PivotTable reports

You can create PivotTable reports for different purposes and save the reports on separate, named worksheets.

  1. Create a PivotTable report, following the steps in this article.
  2. To rename the active sheet, on the Format menu, point to Sheet and then click Rename. Type the new name over the current name.Sheet tabs with Sheet2 selected
  3. In your data worksheet, select the source data again, and create a new PivotTable report.

PivotTable reports are powerful tools

These are just some of the things you can do with your data and PivotTable reports. You can learn to create many more PivotTable reports to help you report sales effectively and plan better sales strategies. For example, you can drag any item in the Field List to any part of the PivotTable report. Don't be concerned about the names of the various areas of the PivotTable report—it just takes a bit of practice moving and dragging fields around to see what works best for you. Keep moving data around until you get the answers you need.

 
 
Applies to:
Excel 2003