Keywords: Frequency Table, Histogram
Excel provides multiple ways in which one can count the frequency with which particular values occur in a data set. These include the FREQUENCY function, a particular type of a PivotTable, the Histogram tool from the Data Analysis ToolPak, and 'roll your own' implementations. This tutorial reviews the pros and cons of each method and demonstrates one way to implement a custom frequency count that circumvents the limits imposed by the other methods.
| Method | Advantage | Disadvantage |
| FREQUENCY function | Basic capability
built into Excel Ignores empty cells in source range |
Deals only with
numeric data Requires specification of frequency bins |
| PivotTable | Easy to use Handles non-numeric data Well formatted results |
No easy way to
consolidate into 'bins' No automatic update if source changes (without event-driven programming) |
| Histogram tool | Easy to use Creates chart and cumulative graph Can establish frequency bin boundaries |
Does not handle
non-numeric data No automated update if data source changes Requires Data Analysis TookPak |
| 'Roll your own' | Works with widest
range of data, including empty cells, error
values, and non-numeric data Automated update for most types of changes to data source |
Most complex to
create Requires specification of bin boundaries for most numeric data Requires additional work for improved formatting and graphical display of results |
