Getting the most return from your sales resources is crucial to achieving success in sales. You can get useful information to help you plan your sales strategy by analyzing your customer data. You can find information such as who buys your products, what percentage of your sales are from certain customers or groups of customers, and who is buying a certain product. A Microsoft PivotTable® report can help you analyze your data and quickly arrange it to highlight whatever facts about your customers you want to review. Once you know this information, you can go further and compare the amount of time and other resources you spend on these customers to the return you get for your effort.

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

• What products are your customers buying?
• What are your sales by customer for each quarter?
• What percent of total sales are from each customer?
• Who are your top two customers, and what products do they buy?
• Who's buying a particular product?

You'll also learn to:

• Design your PivotTable report using the Layout feature.
• Move data around from rows to columns.
• Create a PivotChart report to compare product purchases of your top two customers.

This example business is a wholesaler who sells to a limited number of major repeat customers. Of course, this scenario doe not fit every business. If you have a large customer base, it may make more sense for you to analyze your customers by geographic grouping, purchasing method, or other distinction. Once you see how to analyze customer data in this example, you will be able to create similar reports to analyze customers in the most meaningful way for your business.

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 Customers and follow the instructions to download the Excel worksheet. Be sure to save the file where you will be able find it later.

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

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

2. On the Data menu, click PivotTable and PivotChart Report to start the PivotTable and PivotChart Wizard.
3. On the first page of the wizard, under Where is the data that you want to analyze?, click Microsoft Office Excel list or database.
4. Under What kind of report do you want to create?, click PivotTable, and then click Next.
5. Now 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 F, row 120.

   

6. Click Next to continue.

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

7. On the PivotTable and PivotChart Wizard – Step 3 of 3 page, under Where do you want to put the PivotTable report?, click New worksheet, and then click Layout.

Use the Layout feature to arrange your data

In PivotTable reports for Sales 1: Compare your salespeople, the data is dragged directly onto the report. This article walks you through using the layout feature. Both methods get the same result; you can try them both and see which you prefer.

To use the Layout feature to arrange your data:

1. Drag Customer and Product to the Row area, making sure Product appears below Customer.

   This moves your customer list into the first column on your worksheet, and moves the products they purchased into the second column.

2. Drag Qtr 1, Qtr 2, Qtr 3, Qtr 4, and Total to the Data area in the order in which you want them to appear.
3. By default, Excel may count the number of fields for each item—instead of adding the amounts in the fields. If the fields are labeled Count of Qtr 1, and so on, double-click Count of Qtr 1, click Sum in the Summarize by area, and then click OK. The fields should now be labeled Sum of Qtr 1 and so on.

   Repeat this step for Qtr 2, Qtr 3, Qtr 4, and Total.

   

   The column area now shows Sum of Qtr 1 and so on, instead of Count of Qtr 1.

4. Click OK, and then click Finish.

The PivotTable report shows sales in rows by customer, then product, and then quarter.

Show quarters across the top of the worksheet

To make it the report easier to read, you can move the quarters from rows into columns.

1. Click the Data field (cell C3 in this example).
2. On the PivotTable toolbar, click PivotTable, point to Order, and click Move to Column.

The PivotTable report now shows quarterly sales results and total sales in columns.

Change the number formatting

This report could still be made easier to read. To hide decimal places and add the 1000 separator (,):

1. To select columns C through G, drag across the column headings.
2. On the Format menu, click Cells, and then click the Number tab.
3. In the Category list, click Number.
4. Select the Use 1000 separator (,) check box.
5. In the Decimal places box, enter 0 (zero), and then click OK.

The report is now easier to read. You can see your customers and the products they buy for each quarter and for the year.

What are your sales by customer for each quarter?

Now you want to see total sales by customer. To get a better picture of how their spending changes over time, you want to show all four quarters, as well as for the year. And you want to see your top customers first.

1. Drag the Product field back to the PivotTable Field List.
2. Double-click the Customer field, and then in the PivotTable Field dialog box, click Advanced.
3. Under AutoSort options, click Descending, and under Using field on the left, click Sum of Total.
4. Click OK twice.

This report shows sales by customer, starting with the customers with the highest total sales for the year.

What percent of total sales are from each customer?

Blythe is a good customer, but what percentage of your sales does he account for? Find out what percentage of sales each customer contributes for each quarter.

1. Click the Sum of Qtr 1 field, and then click Field Settings on the PivotTable toolbar.
2. Click Options.
3. Under Show data as, click % of total, and then click OK.
4. Repeat for Sum of Qtr 2 through Sum of Qtr 4 and Sum of Total.

Now you can see each customer's contribution to sales. Note that the Grand Total for each period is 100%.

Who are your top two customers for the year?

It's important to identify your top customers or groups of customers so you can pay special attention to their needs and be ready to meet those needs. Knowing who your top customers are can also help you identify other potential customers or target audiences. PivotTable reports can help you save time and get this information quickly.

1. Drag the Data field back to the PivotTable Field List.
2. Drag the Total field to the Drop Data Items Here area.
3. Click the Sum of Total field, click Field Settings on the PivotTable toolbar, and then click Options.
4. Under Summarize by, click Sum, and then click OK.
5. Click the Customer field.
6. On the PivotTable toolbar, click PivotTable, and then click Sort and Top 10.
7. Under AutoSort Feature, click Descending, and in the Using field drop-down box, click Sum of Total.
8. Under Top 10 AutoShow, click On, and in the Show drop-down box, click Top, and then type 2.
9. In the Using field drop-down box on the right, click Sum of Total, and then click OK.

Now you can see your top two customers and their purchases for the year.

Create a chart comparing customer sales

To be able to compare these two customers and what they are buying more easily, you can create a bar chart. The best type of chart depends on your data and the questions you are trying to answer with the chart. In this case, you can use a bar chart to compare your top two customers' sales of each product for the year.

First, drag all fields back to the PivotTable Field List. Then create the following report:

1. Drag the Customer field into the Drop Column Fields Here area.
2. Click the arrow to the right of the Customer field, click the (Show All) box to clear all the selections, click the Blythe box and the Hall box, and then click OK.
3. Drag the Product field into the Drop Row Fields Here area.
4. Drag the Total field into the Drop Data Items Here area.
5. Click the Chart Wizard button on the PivotTable toolbar.

   

    The Chart Wizard button

6. Click the Chart Wizard button again, and under Chart type, click Column.
7. Click the first column chart type (Clustered column), and then click Finish.

The Chart Wizard automatically creates a Microsoft PivotChart® report that compares the product purchases of your top two customers, Blythe and Hall, for the year. You can click the arrows by Product or Customer on the chart to compare different combinations of product and customer.

Who's buying Manny's Dried Apples?

You have a promotion on Manny's Dried Apples coming up. Which of your customer are currently buying Manny's Dried Apples? It's easy to drag all the fields back to the PivotTable Field List and start over to get just the information you need:

1. Drag the Customer field and the Data field back to the PivotTable Field List.

   The PivotTable report is now blank.

2. Drag Product to the Drop Column Fields Here area.
3. Drag Customer to the Drop Row Fields Here area.
4. Drag Total to the Drop Date Items Here area.
5. Click the arrow to the right of the Product field, click the (Show All) box to clear all the selections, click the Manny's Dried Apples box, and then click OK.
6. To sort customers by sales of Manny's Dried Apples:
1. Double-click the Customers field, and in the PivotTable Field dialog box, click Advanced.
2. Under AutoSort options, click Descending, and in the Using field drop-down box, click Sum of Total.
3. Click OK twice.

Seven customers buy Manny's Dried Apples, and Ford is your top customer that product.

What else can your customer data tell you?

Keep moving data around, and see how easy it is to get the answers you need. With additional data, such as information on the amount of time you spend on each customer, you can create additional reports to help you make better strategy decisions. You may also want to add profitability factors such as net profit or commission percentages for different products. The time you spend analyzing your customer data and planning your sales strategy will pay off as you are able to meet your customers' needs and make better use of your time and resources.