Spreadsheet Practice

How to replace blank cells with 0 using a formula in Excel

Formula (Excel and Google Sheets)

=IF(C2="",0,C2)

Return 0 when the cell is empty and the value otherwise: =IF(C2="",0,C2).

Result on the example
one result per row in E2, filled down to E6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E2, filled down to E6. Change any input and the result updates, or edit the formula to experiment.

How it works

The IF tests whether C2 is empty. If it is, the formula returns 0; otherwise it returns the value unchanged. The result has no gaps, which some charts, imports and pivot tables need.

=IF(C2="",0,C2)

Result0

  1. C2(empty)

    The value in C2.

  2. C2=""TRUE

    The = operator tests whether the two sides are equal.

  3. IF(C2="",0,C2)0

    IF: Returns one value if a test is true, another if false.

    • logical_testC2=""TRUE
    • value_if_true0
    • value_if_falseC2(empty)not used

In Google Sheets

The same formula works in Google Sheets: =IF(C2="",0,C2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=C2+00, 2875, 5650…Adding 0 turns an empty cell into 0 and leaves numbers alone. It returns #VALUE! for text, so use it on number columns only.

Common mistakes

  • Zeros change averages: AVERAGE skips blank cells but counts zeros. Ana's average over the three quarters is 4,050 with the blank and 2,700 with a 0. Only fill blanks with 0 when 0 is the true value.
  • A cell with a space, or a formula returning "", isn't truly blank. TRIM(C2)="" catches both.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Replace blank cells with 0 using a formula

In E2, return the Q2 sales from column C, or 0 when the cell is blank, and fill down to E6.

Questions

How do I fill the blanks in place?

Select the range, press F5 > Special > Blanks, type 0 and press Ctrl+Enter.

How do I show "N/A" instead of 0?

Return text instead: =IF(C2="","N/A",C2).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.