Formula Errors

Fix an Excel VALUE Error When Returning Blank Cells from a Range

Bushra ParveenBushra Parveen Nov 18, 2026 868 views

Question details

Return values from an imported range while displaying blank source cells as empty instead of producing a #VALUE! error.

Fix an Excel VALUE Error When Returning Blank Cells from a Range
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the output cell

On a different worksheet, select an empty cell with at least 20 empty cells below it.

2
Enter the range formula

Type =IF('Sheet1'!N2:N22="","",'Sheet1'!N2:N22) in the formula bar and press Enter.

3
Check the results

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.

Use a Dynamic Array Formula to Return the Range
Empty strings are not truly blank cells: The formula returns an empty string for a blank source cell, so the result looks blank but still contains a formula result.
Work with imported spreadsheet data

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Choose an output cell: On a different worksheet, select cell N2 or another empty cell aligned with the first source row.
  3. 3. Enter the formula: In the formula bar, enter =IF('Sheet1'!N2="","",'Sheet1'!N2) and press Enter.
  4. 4. Fill down: Select the formula cell and drag its fill handle down through the row corresponding to Sheet1!N22.
Display blank source cells as empty stringsFill formulas down a range without relying on dynamic arraysOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.