This month, we'll look at two ways to create a frequency distribution in Excel. The first method is the FREQUENCY function, but it is difficult to figure out. The second method is to use a pivot table.

To begin, suppose you want to analyze an invoice register to find the number of invoices that fall into various size categories. The invoice amounts are in E2:E564, and they generally fall between $5,000 and $25,000. You could choose to group the invoice into $1,000 buckets, $5,000 buckets, or whatever would make sense for the analysis that you need.

Using FREQUENCY

The FREQUENCY function is one of the few Excel functions that always returns a range of values. This means you must select several cells, type one formula, then press Ctrl+Shift+Enter to get all of the results. These special Ctrl+Shift+Enter formulas are called array formulas.

Before you can enter the formula, you have to set up a range of buckets in a blank section of the worksheet. In Figure 1, the bucket range is in cells I3:I8. The bucket amounts must be sorted ascending. A bucket of $4,999 includes all invoices less than $4,999 but greater than the previous bucket. I've added an explanation in column K of Figure 1 to explain what each bucket means.

To enter the FREQUENCY function, you have to select a range that is one cell larger than the bucket range. This last cell is used for any invoices larger than the largest bucket specified.

Type =FREQUENCY(E2:E564,I3:I8). But rather than simply pressing Enter, hold down Ctrl+Shift while pressing Enter. Figure 2 shows the result. The count in column J shows how many invoices there are in each bucket. For example, there are 104 invoices from $0 to $4,999. If you look in the formula bar, Excel shows the formula wrapped in braces (also known as curly brackets). You don't type the braces. They are shown to indicate that the formula was created using Ctrl+Shift+Enter.

Using a Pivot Table

Pivot tables are a faster way to create a frequency distribution. Follow these steps:

1. Select a cell in the original data set.

2. Go to Insert, PivotTable, then click OK.

3. Drag the Revenue field to the Row Labels drop zone.

4. Drag the Revenue field three times to the Values drop zone. You now have a silly-looking table showing each revenue amount four times in columns A:D.

5. Select a cell in column B. Click the Field Settings icon in the ribbon. Change the Custom Name to "Count." In the "Summarize value field by" list, change the calculation from Sum to Count. Then click OK.

6. Select a cell in column C, and click Field Settings again. Change the name to "Total $." Click OK.

7. Select a cell in column D, and click Field Settings. Change the Custom Name to "% of Total." Click the "Show values as" tab. From the drop-down, choose "% of total." Click OK.

8. Select any revenue amount in column A. Click the Group Field icon.

9. In the Grouping dialog (see Figure 3), specify Starting at: 0, Ending at: 30000, By: 5000. Click OK.

The result shown in Figure 3 is a frequency distribution that provides the number of invoices, the total dollars associated with those invoices, and the % of total from each bucket. The bucket labels in column A are clear. You don't have to remember if $4,999 is the upper or lower limit as you have to do with FREQUENCY.