# How to show a blank instead of 0 in Excel

Test the result and return "" when it is 0. With LET the calculation is written once: =LET(t,SUMIFS(…),IF(t=0,"",t)).

**Excel:** `=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))` (Excel 2021, 2024 and Microsoft 365)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Rep | Amount |  | Rep | Total |
| 2026-10-01 | Ana | 1200 |  | Ana |  |
| 2026-10-02 | Ben | 850 |  | Ben |  |
| 2026-10-05 | Ana | 430 |  | Cara |  |
| 2026-10-07 | Dev | 990 |  | Dev |  |
| 2026-10-08 | Ben | 615 |  | Eli |  |
| 2026-10-12 | Ana | 305 |  |  |  |

Formula in F2, filled to F6: `=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))`

Result: $1,935; $1,465; ; $990; 

## How it works

LET stores the SUMIFS result under the name `t`, then IF returns "" (empty text) when `t` is 0 and the total otherwise. Without LET you would write the SUMIFS twice, once to test it and once to return it. Cara and Eli have no sales, so their cells stay blank.

## Other ways

- `=IF(SUMIFS($C$2:$C$7,$B$2:$B$7,E2)=0,"",SUMIFS($C$2:$C$7,$B$2:$B$7,E2))`: The same test without LET, for Excel 2019 and earlier.
- `=SUMIFS($C$2:$C$7,$B$2:$B$7,E2)`: Keep the 0 and hide it with the number format `#,##0;-#,##0;`, whose empty third section is for zeros. Other formulas still see 0. (Shows 0 for Cara and Eli until you apply the format.)

## Common mistakes

- "" is text. SUM skips it, but arithmetic such as `=F4+1` returns #VALUE!.
- Hiding zeros with a number format changes only the display: COUNT and AVERAGE still include them.

## Questions

**Does LET work in Google Sheets?**

Yes. Google Sheets has LET, and the same formula works there.

**How do I show a dash instead of a blank?**

Return "-" instead: `IF(t=0,"-",t)`, or use the Accounting number format, which shows zeros as a dash.

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/show-blank-instead-of-zero