Page 4 of 8PREVNEXT

PivotTable III: Calculate data in PivotTable reports in Excel 2007

Click Play to view an animation showing how to use a custom calculation to find the percentage of a total.

Now you want to see what percentage of the grand total of sales was made by each salesperson. Peacock had the most orders, but does the number of orders she sold also work out to the highest percentage of the total amount of sales?

You can find out by using a custom calculation. With a custom calculation you can do such things as create a running total, or calculate the percentage for each salesperson's sales compared to the grand total of all sales.

To do this, right-click in the Values area. Point to Summarize Data By, and then click More options. Click the Show values as tab in the Value Field Settings dialog box that opens. Then, in the Show values as box, click the arrow and select % of total.

Peacock had 18 percent of the total sales for the year, which is more than any of the other salespeople, so in this case the highest percentage of total sales does correspond to the highest number of orders sold. King is next with 14.42 percent, followed by Davolio at 12.52 percent.

Click Play to see how to perform a custom calculation.

Tip     To return the values to a normal view, follow the same steps, and then click Normal. That turns off custom calculation.

Page 4 of 8PREVNEXT