This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Create a Frequency Distribution
Problem: The VP of sales wants to stop accepting small orders. I need to see how many orders are “small" orders and the impact to our revenue.
Strategy: Build a pivot table with the invoice amount in the row labels. In order to get a count of the number of invoices, move any text field to the values area. Add invoice amount a second time to the values area. You will get a pivot table that looks like this.
Figure 871 Arrange fields opposite of normal, numbers in the row labels and text in the values area.
Switch from Compact Form to Tabular form so you can see a real heading in A3. This is in the Report Layout dropdown on the Design tab.
Rename each field to Invoice Size, Number of Invoices, Total Revenue.
Select cell A4 and click Group Field on the Options ribbon tab. Enter starting, ending, and step values. Perhaps from 0 to 30,000 in 5000 dollar increments. Click OK.
Result: a pivot table version of a frequency diagram, without ever having to figure out the FREQUENCY array-function.