Sum, count, average, share

The total revenue shows which city earned more. But a total grows both from large orders and simply from having many orders. In the report below, Boston is ahead, but the total doesn't tell you whether its orders are larger.

You don't need a new PivotTable for this. The fields stay the same: only what Excel does with the revenue inside each row changes. You change it right in the report:

The calculations you can choose

You choose the calculation in the Value Field Settings dialog. It opens from the menu of a field in the Values area, and your choice takes effect after you click OK:

• Sum adds up the revenue of the orders;
• Count counts the orders themselves, not the dollars;
• Average divides the sum by the number of orders: that gives you the average order;
• Max and Min find the largest and the smallest order.

The Grand Total row uses the same calculation, but on all the orders at once. That's why the average order for the whole company sits right in it: $84,000 of revenue from 12 orders gives $7,000.

A share instead of dollars

The same dialog has a Show Values As tab. The % of Grand Total option on it divides each city's revenue by the Grand Total. Boston brought in $32,200 out of $84,000, so it gets 38.33%, and the Grand Total itself becomes 100%.

The header of the column with shares will still be "Sum of Revenue, $". You change it in the same dialog, in the Custom Name box: for example, to "Revenue share".

Why it says Count instead of Sum

Sometimes a PivotTable shows 3, 5 and 4 instead of dollars right away, and its header says "Count of Revenue, $". Nobody chose that calculation: Excel set it by itself.

Excel picks Sum only when the column holds nothing but numbers. A single empty cell or a single number stored as text is enough for the field to get Count. You fix such a number with Convert to Number in the menu of the exclamation mark icon.

The fix doesn't reach the report by itself: a PivotTable calculates from the data it remembered when it was created, and it sees the change only after Refresh. Excel doesn't touch the calculation, though, so you choose Sum yourself.

Several numbers in one report

Switching the calculation back and forth is inconvenient when you need revenue, number of orders and average order all at once. So you put the same field into Values several times, each with its own settings:

When Values holds more than one field, a special field called "Σ Values" appears in Columns. It's what spreads the numbers across neighboring columns.

One table shows everything at once: Boston has $32,200 from five orders, $6,440 on average, while Austin has $27,900 from three, $9,300 on average. Boston's revenue is bigger only because it has more orders.