'%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 2016 Cannot Spill Multiple Cells from a Formula
Question details
Understand why formulas returning dynamic ranges from Microsoft 365 produce a #VALUE! error or a single result in Excel 2016, and learn how to resolve this compatibility issue.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Opening a workbook or entering a formula meant to output an array of values across multiple cells in Excel 2016.
- Observed behavior
- The formula fails to populate multiple adjacent cells automatically, instead returning only the first result of the array or displaying a #VALUE! error.
Determine the exact number of rows and columns your formula output requires, as you will need to manually pre-select this destination range before applying a legacy array formula in Excel 2016.
Use Legacy Array Formulas (Ctrl+Shift+Enter)
Since Excel 2016 lacks the dynamic spill feature, you must enter array formulas manually using a specific keyboard shortcut across a pre-selected range.
Excel 2016 does not natively support dynamic arrays. To output a range of values from a single formula, you must use the legacy array entry method instead of a standard Enter keystroke.
Click and drag your mouse to highlight the entire output range where you want the formula results to appear.
Click into the Formula Bar at the top of the screen and type your desired formula (e.g., =INDEX(AJ28:AJ62, 1):INDEX(AJ28:AJ62, 5)).
Press Ctrl + Shift + Enter simultaneously on your keyboard. Excel will automatically wrap your formula in curly braces {} and populate the selected cells.
Rewrite and Copy Standard Formulas
Use standard formulas designed to return a single value per cell, and manually copy them across the required destination rows.
Upgrade to WPS Office for Seamless Spreadsheet Capabilities
Avoid compatibility issues and limitations found in older software like Excel 2016. WPS Office is a free, lightweight suite that offers robust data processing capabilities, excellent Microsoft Office format compatibility, and a highly familiar user interface for effortless migration.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet, click on 'Open', and select your existing .xlsx workbook.
- 3. Work Without Limits: Enjoy broad function support and calculate complex data sets without legacy software limitations.
Frequently Asked Questions
Why does my formula return a #VALUE! error in Excel 2016?
Excel 2016 does not support dynamic-array spilling. If you open a workbook from Microsoft 365 containing a formula that returns a range of cells, Excel 2016 cannot spill the results automatically, often resulting in a #VALUE! error.
Can I enable dynamic arrays in Excel 2016?
No, the dynamic array feature is exclusive to Microsoft 365 and newer perpetual versions like Excel 2021. To output multiple values in Excel 2016, you must use legacy array formulas.
How do I correctly enter an array formula in Excel 2016?
You must first highlight the entire destination range, type your formula into the top formula bar, and press Ctrl+Shift+Enter at the same time. Excel will add curly braces {} around your formula to indicate it is an array.
What should I do if my formula's output size changes dynamically?
Because legacy array formulas in Excel 2016 cannot resize themselves, you have to manually highlight a larger or smaller range of cells, click into the formula bar, and press Ctrl+Shift+Enter again to update the output area.
