Fix Excel OFFSET Returning One Value Instead of an Array
Question details
The user wants an Excel OFFSET formula to return multiple values into multiple cells.

- Product
- Microsoft Excel 2019, Office Professional Plus 2019
- Device & OS
- not provided
- Scenario
- The user is trying to return a range specified by an OFFSET formula across multiple worksheet cells.
- Observed behavior
- The formula returns only one value instead of populating the intended range.
Check your Excel version under File > Account and identify the intended output range. In the examples below, E5 and E6 contain the row and column offsets, while E7 and E8 contain the positive height and width of the range.
Enter OFFSET as a legacy array formula in Excel 2019
Use this method to return the entire OFFSET range in Excel 2019, which does not support dynamic spilling.
On the worksheet, select an empty range with the same number of rows and columns as the values in E7 and E8. Make sure it does not overlap the source range.
With the output range still selected, type =OFFSET($A$2,$E$5,$E$6,$E$7,$E$8) in the formula bar.
Press Ctrl+Shift+Enter instead of Enter. Excel will enter the formula as a legacy array formula across the selected cells.

Fill an INDEX and OFFSET formula across the output range
Use this approach in Excel 2019 when you prefer individual formulas that can be filled down and across.
Allow OFFSET to spill in Microsoft 365
Use this method if you have a version of Excel that supports dynamic arrays.
Work with Excel spreadsheets in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel file formats, making it easy to move existing workbooks into WPS. It does not add dynamic array spilling to Excel 2019.

Frequently Asked Questions
Why does OFFSET return only one value in Excel 2019?
OFFSET returns a reference, but Excel 2019 does not automatically spill an array into neighboring cells. Select the intended output range and confirm the formula with Ctrl+Shift+Enter, or use an individual formula in each output cell.
Can I use OFFSET to return multiple rows and columns?
Yes. Set OFFSET's height and width arguments to positive values. In Excel 2019, select a matching output range before entering the formula as a legacy array formula.
What should I do if Excel shows a #SPILL! error?
In a dynamic-array version of Excel, check the intended spill area for existing values or merged cells and clear the obstruction. Place the formula outside an Excel table if it is inside one.
Why is there an @ symbol before my OFFSET formula?
The @ operator requests a single value through implicit intersection. If you want a range to spill in a dynamic-array version of Excel, check whether removing @ is appropriate for your formula and intended result.




