Skip to content
Sheet ErrorSheet ErrorSheet Error

Fix ARRAYFORMULA 0 on empty rows in Google Sheets

An empty qty row still shows 0 after ARRAYFORMULA multiplies the column. That is not a formula error. Sheets treated the blank as 0.

Recreate it on a blank sheet

Section titled “Recreate it on a blank sheet”
  1. Open a blank spreadsheet. Leave the tab named Sheet1. In A1 type qty, B1 times2_raw, C1 times2_if_zero.
  2. In A2 type 10 as a number. Leave A3 empty. In A4 type 20 as a number.
  3. In B2 enter =ARRAYFORMULA(A2:A4*2). The column spills 20, then 0, then 40. The middle row is 0. There is no red error triangle.

C3 selected. Formula bar shows 0. A3 is empty. B3 and C3 are 0 from the ARRAYFORMULA spill.

  1. In C2 enter =ARRAYFORMULA(IF(A2:A4="",0,A2:A4*2)). That column is also 20, 0, 40. Writing 0 in the IF empty branch does not make a blank row.

What is actually wrong

Section titled “What is actually wrong”
  • An empty cell times 2 is treated as 0. ARRAYFORMULA fills that 0 into the blank input row.
  • The formula ran. This is ordinary math, not a broken formula.
  • Putting 0 in the IF empty branch still prints 0. The cell is the number zero, not a blank.

Fix

Section titled “Fix”

Leave B2 and C2 as the broken formulas so the zeros stay visible.

In D1 type times2_if_blank. In D2 enter =ARRAYFORMULA(IF(A2:A4="","",A2:A4*2)). The column spills 20, a blank-looking cell, then 40.

D2 selected. Formula bar shows =ARRAYFORMULA(IF(A2=“”,“”,A2*2)). D2 is 20, D3 looks empty, D4 is 40. B3 and C3 are still 0.

That middle cell is "", an empty string, not a truly empty cell. Do not use 0 as the empty-row result.

If a qty cell is blank, keep the output blank with "". 0 is a value.