Stop Hiding Zeros With Conditional Formatting — Try This Instead

The first thing most people do when they want to do not show zeros in Excel is apply conditional formatting to make cells with zero appear blank. That’s a trap — and it’s why your pivot tables miscount, your SUMIFS return wrong totals, and your colleagues swear the data is 'corrupted'. Zero values are still there. They’re just wearing invisibility cloaks. And Excel doesn’t respect illusions.

Quick Answer

To truly do not show zeros in Excel, skip conditional formatting entirely. Use Excel’s built-in Display Options (File > Options > Advanced > 'Show a zero in cells that have zero value' — uncheck it) for workbook-wide suppression, or apply a custom number format like 0.00;-0.00;;@ to specific ranges like B2:E15. Both methods preserve underlying values for calculations — unlike hiding zeros with formatting tricks.

All the Methods

Method	Steps	Best For	Limitations
Workbook-Level Toggle	File > Options > Advanced > uncheck 'Show a zero in cells that have zero value'	Entire workbook; ideal for reports shared with finance teams	Affects all worksheets — can’t apply selectively
Custom Number Format	Select range > Ctrl+1 > Number tab > Custom > enter 0.00;-0.00;;@	Specific columns (e.g., revenue, margin %), dashboards, client-facing sheets	Doesn’t affect text cells or formulas returning "" — only numeric zeros
IF + ISBLANK in Formulas	Replace =A2-B2 with =IF(A2-B2=0,"",A2-B2)	When you need blanks instead of zeros *and* must retain empty-cell behavior (e.g., charts, COUNTA)	Converts numbers to text; breaks SUM, AVERAGE, and data validation
Filter Out Zeros	Data > Filter > click dropdown > uncheck '0' under numeric filters	Temporary review — auditing discrepancies in P&L line items	Doesn’t hide zeros visually — just hides rows containing them
Conditional Formatting (Avoid)	Home > Conditional Formatting > New Rule > 'Format only cells that contain' > cell value = 0 > set font color = white	None — seriously, don’t use this	Zeros remain active in formulas, break slicers, and confuse Power Query imports

Method 1 Deep Dive: Workbook-Level Toggle

This is the cleanest way to do not show zeros in Excel — and also the most overlooked. It changes how Excel renders zeros *everywhere*, without touching formulas or formats. Here’s how:

1. Click File > Options (or press Alt+F+T)
2. In the Excel Options dialog, go to the Advanced category
3. Scroll down to the Display options for this worksheet section
4. Uncheck the box labeled Show a zero in cells that have zero value
5. Click OK

That’s it. Instantly, every zero in all worksheets disappears — but try clicking any blank-looking cell: the formula bar still shows 0. Your =SUM(C2:C20) stays accurate. So does =COUNTIF(C2:C20,">0"). No side effects. (Trust me, I learned this the hard way after three hours debugging a dashboard where someone had used white-font conditional formatting.)

Here’s real sample data from a Q2 sales tracker (Sheet1, A1:E12):

Rep	Region	Q1 Sales	Q2 Sales	Delta
Sarah Chen	APAC	$142,500	$158,900	$16,400
Diego Mendoza	LATAM	$89,200	$89,200	0
Amina Patel	EMEA	$211,700	$198,300	-$13,400
James Wilson	NA	$0	$102,400	$102,400
Linh Tran	APAC	$0	$0	0

Before disabling the option, E2, E5, and E6 all showed 0. After? They’re blank — but if you select E5 and press F2, you’ll see 0 in the formula bar. That’s the gold standard: invisible to the eye, fully functional behind the scenes.

Method 2 Deep Dive: Custom Number Format

Use this when you only want to suppress zeros in certain columns — say, the Delta column above (E2:E12), but keep zeros visible in Q1 Sales or Q2 Sales. Custom number formats give surgical control.

The magic string is: 0.00;-0.00;;@

• First segment (0.00): positive numbers (shows two decimals)
• Second segment (-0.00): negative numbers (also two decimals)
• Third segment (empty): zero values — render as blank
• Fourth segment (@): text — leave unchanged

To apply it:

1. Select E2:E12
2. Press Ctrl+1 (opens Format Cells)
3. Go to Number tab > Custom
4. In the Type field, paste 0.00;-0.00;;@
5. Click OK

Now look at E5 and E6 again: blank. But E2 still shows 16400.00, E3 shows -13400.00, and if you later type "N/A" in E10, it displays normally. Nothing breaks.

Here’s what happens if you try to force zeros back — because sometimes you need to show zeros:

How to show zeros in Excel (when required)

You might think “just re-enable the workbook option.” But that’s overkill if you only need zeros visible in one report tab. Better: override locally. Select the range (say, G2:G10), open Format Cells, and use this custom format instead:

0.00;-0.00;0.00;@

Notice the third segment is now 0.00 — not empty. That forces zeros to display as 0.00. Or, for whole numbers only: 0;-0;0;@.

Why would you ever want zeros visible? Two real cases:

• Audit trails: In a reconciliation sheet (Sheet2!A1:D15), zero means “no discrepancy” — hiding it could mask missing entries. You’d rather see 0 than an empty cell next to "Bank Fee".
• Forecast templates: A sales planner (Sheet3!F5:F20) pre-fills zeros for future months. If those cells appear blank, users assume they’re unpopulated — and skip updating them.

So yes — how to show zeros in Excel matters just as much as hiding them. It’s about intention, not aesthetics.

Cheat Sheet

Action	Shortcut	Cell Range Example	Notes
Disable zeros globally	Alt+F+T → Advanced → uncheck box	All sheets	Fastest for new workbooks
Apply custom zero-suppressing format	Ctrl+1 → Custom → 0.00;-0.00;;@	E2:E12	Preserves number type; safe for formulas
Force zeros to display	Ctrl+1 → Custom → 0.00;-0.00;0.00;@	G2:G10	Use in audit or template sheets
Filter out zero rows	Ctrl+Shift+L → dropdown → uncheck 0	A1:E12	Temporary view only — doesn’t hide zeros
Check if cell contains zero (not blank)	—	=ISNUMBER(E5)*E5=0	Returns TRUE only for numeric zero — distinguishes from ""