'%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)
How to Fix FREQUENCY Array Formula Not Filling Cells in Excel
Question details
The user is unable to get the FREQUENCY array formula to populate multiple cells with results.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to calculate the frequency distribution of a data set across specified bins using the FREQUENCY function.
- Observed behavior
- The formula returns only a single result in one cell instead of populating or spilling the full array of frequency distribution results.
Ensure that your data array contains numeric values and that your bins array correctly defines the intervals you want to measure. Additionally, check which version of Excel you are using, as dynamic arrays behave differently in modern versions compared to older legacy versions.
Use Dynamic Arrays in Newer Excel Versions
For Excel 365 and Excel 2021 or later, the FREQUENCY function utilizes dynamic arrays and automatically spills results into adjacent cells without requiring legacy keyboard shortcuts.
Modern versions of Excel have introduced dynamic arrays. This means that functions returning multiple values, like FREQUENCY, will automatically expand (or "spill") their results into neighboring blank cells based on a single formula entry.
Click on the single top cell where you want the first frequency result to appear.
Type your formula, for example, =FREQUENCY(A2:A180, F2:F18), replacing the ranges with your actual data and bins arrays.
Press the Return (or Enter) key. The results will automatically spill downwards into the required number of cells.
Apply Legacy Array Shortcut (CSE) in Older Excel Versions
If you are using Excel 2019 or earlier, you must pre-select the entire output range and enter the formula as a traditional array using a specific keyboard shortcut.
Calculate Data Frequencies Easily with WPS Spreadsheet
WPS Office provides full support for complex array formulas, including the FREQUENCY function. You can easily analyze data distributions and build statistical reports using this highly compatible and lightweight spreadsheet tool.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your dataset and bin intervals.
- 2. Select the output range: Highlight the blank cells where you want the frequency distribution to appear (remember to select one more cell than your bins).
- 3. Enter and execute the formula: Type =FREQUENCY(data_range, bins_range) in the formula bar and press Ctrl+Shift+Enter to instantly populate the full array of results.
Frequently Asked Questions
Why does my FREQUENCY formula only return one value?
In older versions of Excel, if you only select a single cell and press Enter, the FREQUENCY function behaves like a standard formula and calculates only the first bin. You must select the entire required output range and press Ctrl+Shift+Enter to get all values.
What does the #SPILL! error mean when using FREQUENCY?
The #SPILL! error occurs in newer versions of Excel when the adjacent cells required to display the full array of results are not empty. Clear the cells below your formula to allow the results to populate properly.
Do I need to sort my data array before using FREQUENCY?
No, you do not need to sort the data array. The FREQUENCY function automatically evaluates and counts the occurrences of values that fall within the intervals defined by your bins array, regardless of the data's original order.
Why are there curly braces {} around my formula?
Curly braces indicate that the formula was entered as a legacy array formula using Ctrl+Shift+Enter (or Command+Shift+Return). You should not type these manually; Excel adds them automatically to signify that the formula applies to a range of cells.
