'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel FREQUENCY Function Displaying Only the First Result
Question details
The FREQUENCY function only displays the result for the first bin instead of outputting the complete frequency distribution.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating a frequency distribution for a dataset using the FREQUENCY function.
- Observed behavior
- Only the first value of the frequency distribution array is displayed when the formula is entered normally, rather than the expected sequence of numbers.
Check your Excel version. If you are not using Microsoft 365 or Excel 2021 (which support dynamic array spilling), you must enter FREQUENCY as a legacy array formula to display all results.
Enter as a Legacy Array Formula (Older Excel Versions)
Use this method if your Excel version does not support dynamic array spilling, which requires you to define the output range before evaluating the formula.
In older versions of Excel, the FREQUENCY function is designed to return an array of values. Because a single cell cannot hold multiple values in these versions, you must tell Excel to distribute the results across multiple cells using a special keystroke.
Highlight the entire range of empty cells where you want the frequency distribution results to appear. This range should typically have one more cell than your bins array.
With all the cells selected, type your formula (for example, =FREQUENCY(B2:B192, G2:G13)) directly into the formula bar.
Instead of pressing just Enter, hold down Ctrl + Shift and then press Enter. Excel will enclose the formula in curly braces {} and populate all selected cells with the frequency results.
Use Dynamic Arrays (Microsoft 365 and Excel 2021+)
For newer versions of Excel, the FREQUENCY function automatically spills results down the column without needing the Ctrl+Shift+Enter shortcut.
Easily Calculate Data Distributions with WPS Spreadsheet
WPS Spreadsheet provides robust support for array formulas and statistical functions like FREQUENCY, making data analysis straightforward and fully compatible with Excel formats.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your data array and bins array.
- 2. Select the result range: Highlight the empty column of cells where you want the calculated frequency distribution to appear.
- 3. Input the formula: Click the formula bar and type the FREQUENCY formula linking your data and bins.
- 4. Execute as an array: Press Ctrl + Shift + Enter simultaneously. WPS Spreadsheet will calculate and display the complete frequency array instantly.
Frequently Asked Questions
Why does my FREQUENCY formula have curly braces around it?
The curly braces {} indicate that the formula was entered as a legacy array formula. You cannot type these braces manually; they appear automatically when you successfully press Ctrl + Shift + Enter.
What happens if I try to change a single cell in the frequency output range?
If you entered FREQUENCY as a legacy array formula, you cannot change, delete, or move individual cells within the result block. You will receive an error message. You must edit or clear the entire array range simultaneously.
Why do I get a #SPILL! error when using the FREQUENCY function?
A #SPILL! error occurs in newer versions of Excel when there are non-empty cells blocking the formula from spilling its results downward. Clear any text or data in the cells immediately below your formula to allow the distribution to populate.
