You are on the Home/Excel/Tutorials/Frequency page
Web This Site

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