![](/uploads/1/2/5/7/125719689/705733156.jpg)
How To: Record absolute and relative macros in MS Excel 2007 How To: Use absolute and relative cell references in MS Excel How To: Name cell ranges with relative references in MS Excel How To: Create a percentage frequency table in Microsoft Excel.
The FREQUENCY function calculates how often values occur within a range of values, and then returns a vertical array of numbers. For example, use FREQUENCY to count the number of test scores that fall within ranges of scores. Because FREQUENCY returns an array, it must be entered as an array formula.
Syntax
FREQUENCY(data_array, bins_array)
The FREQUENCY function syntax has the following arguments:
- data_array Required. An array of or reference to a set of values for which you want to count frequencies. If data_array contains no values, FREQUENCY returns an array of zeros.
- bins_array Required. An array of or reference to intervals into which you want to group the values in data_array. If bins_array contains no values, FREQUENCY returns the number of elements in data_array.
Remarks
Note: If you have a current version of Office 365, then you can simply enter the formula in the top-left-cell of the output range, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas.
- The number of elements in the returned array is one more than the number of elements in bins_array. The extra element in the returned array returns the count of any values above the highest interval. For example, when counting three ranges of values (intervals) that are entered into three cells, be sure to enter FREQUENCY into four cells for the results. The extra cell returns the number of values in data_array that are greater than the third interval value.
- FREQUENCY ignores blank cells and text.
Example
Need more help?
You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
In Excel, you can use the Histogram Data Analysis tool to create a frequency distribution and, optionally, a histogram chart. A frequency distribution shows just how values in a data set are distributed across categories. A histogram shows the same information in a cute little column chart. Here’s an example of how all this works — everything will become clearer if you’re currently confused.
To use the Histogram tool, you first need to identify the bins (categories) that you want to use to create a frequency distribution. The histogram plots out how many times your data falls into each of these categories.
![Frequency Frequency](/uploads/1/2/5/7/125719689/310026606.jpg)
This worksheet shows bins information in the worksheet range E1:E12. The bins information shows Excel exactly what bins (categories) you want to use to categorize the unit sales data. The bins information shown in the worksheet range E1:E12, for example, create hundred-unit bins: 0-100, 101-200, 201-300, and so on.
To create a frequency distribution and a histogram, follow these steps:
![Relative Frequency Table Excel Relative Frequency Table Excel](/uploads/1/2/5/7/125719689/301315749.jpg)
- Click the Data tab’s Data Analysis command button to tell Excel that you want to create a frequency distribution and a histogram.
- Avenir light font free download for windows 7. When Excel displays the Data Analysis dialog box, select Histogram from the Analysis Tools list and click OK.
- In the Histogram dialog box that appears, identify the data that you want to analyze.Use the Input Range text box to identify the data that you want to use to create a frequency distribution and histogram. If you want to create a frequency distribution and histogram of unit sales data, for example, enter the worksheet range $B$1:$B$38 into the Input Range text box.To identify the bins that you use for the frequency distribution and histogram, enter the worksheet range that holds the bins into the Bin Range text box. In the case of the example worksheet, the bin range is $E$1:$E$12.If your data ranges include labels, select the Labels check box.
- Tell Excel where to place the frequency distribution and histogram.Use the Output Options buttons to tell Excel where it should place the frequency distribution and histogram. To place the histogram in the current worksheet, for example, select the Output Range radio button and then enter the range address into its corresponding Output Range text box.To place the frequency distribution and histogram in a new worksheet, select the New Worksheet Ply radio button. Then, optionally, enter a name for the worksheet into the New Worksheet Ply text box. To place the frequency distribution and histogram information in a new workbook, select the New Workbook radio button.
- (Optional) Customize the histogram.Make choices from the Output Options check boxes to control what sort of histogram Excel creates. For example, select the Pareto (Sorted Histogram) check box, and Excel sorts bins in descending order. Conversely, if you don’t want bins sorted in descending order, leave the Pareto (Sorted Histogram) check box clear.Selecting the Cumulative Percentage check box tells Excel to plot a line showing cumulative percentages in your histogram.Optionally, select the Chart Output check box to have Excel include a histogram chart with the frequency distribution. If you don’t select this check box, you don’t get the histogram — only the frequency distribution.
- Click OK.Excel creates the frequency distribution and, optionally, the histogram. Here is the frequency distribution along with a histogram for the example workbook data.
Note: Excel also provides a Frequency function with which you use can use arrays to create a frequency distribution.
![](/uploads/1/2/5/7/125719689/705733156.jpg)