Function Problems

Fix Excel OFFSET Returning One Value Instead of an Array

Maira MehtabMaira Mehtab Nov 13, 2026 869 views

Question details

The user wants an Excel OFFSET formula to return multiple values into multiple cells.

Fix Excel OFFSET Returning One Value Instead of an Array
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output range

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.

2
Enter the OFFSET formula

With the output range still selected, type =OFFSET($A$2,$E$5,$E$6,$E$7,$E$8) in the formula bar.

3
Confirm the array formula

Press Ctrl+Shift+Enter instead of Enter. Excel will enter the formula as a legacy array formula across the selected cells.

Enter OFFSET as a legacy array formula in Excel 2019
Resizing the result: A legacy array formula does not resize automatically. If the height or width changes, remove the existing array formula by selecting its entire output range and pressing Delete, then select the new range and enter it again.
Free Microsoft Office alternative

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.

Work with formulas and ranges in WPS SpreadsheetOpen and save Microsoft Excel XLSX and XLS filesUse a familiar interface to ease migration from Microsoft OfficeGet a free, lightweight office suite
microsoft office alternative - wps office

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.