Fix an Excel VALUE Error When Returning Blank Cells from a Range
Question details
Return values from an imported range while displaying blank source cells as empty instead of producing a #VALUE! error.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- An IF and ISBLANK formula references multiple cells on a sheet populated by an external data source.
- Observed behavior
- The formula returns a #VALUE! error for the multi-cell range, but a single-cell reference works.
Check that the imported values are in Sheet1!N2:N22 and choose an empty output area on another sheet. If you want to use a single formula for the entire range, make sure your Excel version supports dynamic arrays.
Use a Dynamic Array Formula to Return the Range
Use this formula in a dynamic-array-compatible version of Excel to return all 21 cells at once.
On a different worksheet, select an empty cell with at least 20 empty cells below it.
Type =IF('Sheet1'!N2:N22="","",'Sheet1'!N2:N22) in the formula bar and press Enter.
Confirm that the results spill down the column and that blank source cells display as empty. If you see #SPILL!, clear any cells blocking the output range.

Fill a Single-Cell Formula Down the Output Range
Use this method if your Excel version does not support dynamic arrays.
Convert a Blank-or-Text Range to Text
Use this shorter dynamic array formula when every source value is blank or text.
Return Blank Imported Cells with WPS Spreadsheet
Use a single-cell IF formula and fill it down in WPS Spreadsheet to display blank imported cells as empty while returning the other values.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Choose an output cell: On a different worksheet, select cell N2 or another empty cell aligned with the first source row.
- 3. Enter the formula: In the formula bar, enter =IF('Sheet1'!N2="","",'Sheet1'!N2) and press Enter.
- 4. Fill down: Select the formula cell and drag its fill handle down through the row corresponding to Sheet1!N22.

Frequently Asked Questions
Why does my formula work for one cell but not a range?
A multi-cell reference requires Excel to evaluate an array of values. In a dynamic-array-compatible version, enter the range formula in one cell; in older versions, use a single-cell formula and fill it down.
Why do I see #SPILL! after entering the range formula?
The output area may contain data or another obstruction. Clear the cells below the formula so Excel can display all results.
Does the IF formula create truly blank cells?
No. It returns an empty string, written as "", which looks blank but is a formula result.
Can I use the shorter ampersand formula with numbers?
You can, but ='Sheet1'!N2:N22&"" converts numbers to text. Use the IF formula if the returned numbers need to remain numeric.
What if the imported source cells already contain errors?
Check Sheet1!N2:N22 for errors from the external data source. The blank-handling formulas do not automatically correct errors already present in those cells.




