Create a Frequency Distribution

January 16, 2023 - by Bill Jelen

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 Revenue in the row labels. In order to get a count of the number of invoices, move any text field to the values area. Add Revenue three more times to the Values area. You will get a pivot table that looks like this.

Build a pivot table with Revenue in the Rows, Count of Customer in Values, and then Sum of Revenue three more times in Values. — Figure 936. Arrange fields opposite of normal, numbers in the row labels and text in the values area.

Select the first revenue field in column D. Use the Field Settings icon and change the Show Values As to % of Column Total. Change the Field Settings for column E to Running % In. Change the headings in B3:E3 as shown below.

Change the final two revenue fields to % of Total and Accum %. — Figure 937. Add columns for percentage of total and running percentage of total.

Select cell A4 and click Group Field on the Analyze ribbon tab. Enter starting, ending, and step values. Perhaps from 1 to 30,000 in 5000 dollar increments. Click OK.

In the Grouping dialog, Start at 1, End at 30000, grouping by 5000 — Figure 938. Group into equal-size buckets.

Result: a pivot table version of a frequency diagram, without ever having to figure out the FREQUENCY array-function.

The final pivot table has six rows of data. In the $1 to $5000 category, there were 104 orders, $326K of revenue. It is 4.9% of total. — Figure 939. A frequency distribution.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ana Municio on Unsplash