Excel: Create a Frequency Distribution

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

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

  2. A frequency distribution.

For more resources for Microsoft Excel