How to show a blank instead of 0 in Excel

Q: 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.

Formula (Excel and Google Sheets)

=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))

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

Result on the example: one result per row in F2, filled down to F6
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

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

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.

=LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))

Result1935

t1935
A name defined in LET, or a named range.
$C$2:$C$76 rows × 1 col: $1,200, $850, $430, $990, …
A range: 6 cells in one column.
$B$2:$B$76 rows × 1 col: "Ana", "Ben", "Ana", "Dev", …
A range: 6 cells in one column.
E2"Ana"
The value in E2.
SUMIFS($C$2:$C$7,$B$2:$B$7,E2)1935
SUMIFS: Adds values that meet every condition.
- sum_range$C$2:$C$76 rows × 1 col: $1,200, $850, $430, $990, …
- criteria_range1$B$2:$B$76 rows × 1 col: "Ana", "Ben", "Ana", "Dev", …
- criteria1E2"Ana"
t=0FALSE
The = operator tests whether the two sides are equal.
IF(t=0,"",t)1935
IF: Returns one value if a test is true, another if false.
- logical_testt=0FALSE
- value_if_true""not used
- value_if_falset1935
LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t))1935
LET: Names values inside a formula.
- name1t1935
- value1SUMIFS($C$2:$C$7,$B$2:$B$7,E2)1935
- name2, value2IF(t=0,"",t)1935

In Google Sheets

The same formula works in Google Sheets: =LET(t,SUMIFS($C$2:$C$7,$B$2:$B$7,E2),IF(t=0,"",t)). 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.

Formula	Result	When to use it
=IF(SUMIFS($C$2:$C$7,$B$2:$B$7,E2)=0,"",SUMIFS($C$2:$C$7,$B$2:$B$7,E2))	$1,935, $1,465, …	The same test without LET, for Excel 2019 and earlier.
=SUMIFS($C$2:$C$7,$B$2:$B$7,E2)	$1,935, $1,465, $0…Different result	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.

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.

Show a blank instead of 0

In F2, total the amounts for the rep in E2, but leave the cell blank when the total is 0. Fill down to F6.

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.

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.