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

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

**Excel:** `=IF(C2="",0,C2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Rep | Q1 | Q2 | Q3 | Q2 (no blanks) |
| Ana | 4200 |  | 3900 |  |
| Ben | 3100 | 2875 |  |  |
| Cara |  | 5650 | 4100 |  |
| Dev | 2900 | 3300 | 3050 |  |
| Eli | 4475 |  | 2980 |  |

Formula in E2, filled to E6: `=IF(C2="",0,C2)`

Result: 0; 2875; 5650; 3300; 0

## 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.

## Other ways

- `=C2+0`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/replace-blank-cells-with-zero