How to Hide Zero Values in Excel for Mac

To hide zero values in Excel for Mac, click Excel in the menu bar, choose Settings (or Preferences), click View, and uncheck Zero values under Show in workbook. Cells that equal zero on the current sheet will look blank, while the zeros stay in the cells.

These steps follow Microsoft’s Display or hide zero values in Excel for Mac article for Excel for Microsoft 365 for Mac, Excel 2024 for Mac and Excel 2021 for Mac. The same Zero values checkbox is in the View settings of older versions, including Excel for Mac 2011, though the section around it may be labeled differently. The Windows steps near the end come from Microsoft’s Display or hide zero values article.

Which Method Should You Use?

What you want	Method	Is the 0 still in the cell?
Hide every zero on a sheet	Zero values setting	Yes
Hide zeros in certain cells only	Custom number format	Yes
Hide zeros a formula returns in a shaded or specific range	Conditional formatting	Yes
Show a blank or a dash in place of a calculated zero	IF formula	No, the cell holds text
Delete typed zeros for good	Find and Replace	No, the cell is empty

The first three methods only change what you see, so totals and other formulas keep working the same way. Start with the Zero values setting unless you need zeros visible somewhere else on the same sheet.

How to Hide All Zero Values in Excel for Mac

1. Open your workbook in Excel and click the sheet tab where you want to hide zeros.
2. Click Excel in the menu bar.
3. Choose Settings or Preferences. You can also press Command + , (comma).
4. Under Authoring, click View.
5. Under Show in workbook, uncheck Zero values.

Every cell on the sheet that equals zero appears blank, including zeros returned by formulas. The zeros are still in the cells and still count in formulas. Check the box again to bring them back.

The setting applies to the sheet you had open, not to every sheet in the workbook. To hide zeros on another sheet, click its tab and repeat the steps. The choice is saved with the workbook, so the blanks are still there the next time you open the file.

How to Hide Zeros in Only Some Cells

Use a custom number format when some zeros should stay visible, such as a row where 0 means “no sales,” while others should disappear.

1. Select the cells with the zeros you want to hide.
2. On the Home tab, open the Number Format menu and choose Custom. (Command + 1 also opens Format Cells.)
3. In the Format Cells box, make sure Custom is selected under Category.
4. In the Type box, enter 0;-0;;@.
5. Click OK.

Zeros in those cells disappear. Microsoft notes that the hidden values still appear in the formula bar, or in the cell while you edit it, and aren’t printed. If one of the cells later changes to a number other than zero, Excel displays it with the General number format.

What the format code means

A custom number format can have four parts, separated by semicolons: positive numbers; negative numbers; zeros; text. In 0;-0;;@, the third part is empty, so zeros show nothing. The @ keeps any text in the cell visible.

The 0 and -0 parts also show numbers without decimal places, so 12.75 displays as 13. If your cells need decimals, commas or currency, use one of these codes instead:

Show numbers like	Type this code
1,234.50	#,##0.00;-#,##0.00;;@
$1,234.50	$#,##0.00;-$#,##0.00;;@
12.5%	0.0%;-0.0%;;@
A dash for zero	0;-0;”-“;@

The built-in Accounting format on the Number Format menu also shows a dash for zero, if a dash is what you want.

Don’t use these codes on date or time cells. Change only cells that hold plain numbers.

Show the hidden zeros again

1. Select the cells.
2. On the Home tab, open the Number Format menu and choose More Number Formats.
3. In the Category list, pick General, or pick Date or Time and a format if the cells hold dates.
4. Click OK.

How to Hide Zeros Returned by a Formula

Microsoft’s method uses conditional formatting to make zeros match the background:

1. Select the cells that might show a zero.
2. On the Home tab, click Conditional Formatting, then New Rule.
3. Set the Style to Classic, then choose Format only cells that contain.
4. Choose Cell value, then equal to, and type 0 in the box beside it.
5. In the Format with menu, choose custom format, set the font Color to White, and click OK.

This only hides zeros on a white background. If your cells are shaded, pick the shading color instead of white, or use the number format option below.

Work on any background color

In step 5, after you choose custom format, open the Number tab, pick Custom, enter ;;; (three semicolons) in the Type box, and click OK. Cells that equal zero then display nothing, whatever their fill color.

To edit or delete the rule later, select the cells and choose Conditional Formatting › Manage Rules. To learn more about formula-based rules, read about using IF-Then formulas to color cells in Excel.

Show a Blank or a Dash Instead of Zero

Use the IF function. Microsoft’s example is =IF(A3=0,””,A3) for a blank cell, or =IF(A3=0,”-“,A3) for a dash. You can put any text you like between the quotation marks.

To build the formula with Excel’s Formula Builder:

1. Click the cell where you want the result.
2. On the Formulas tab, click Insert Function.
3. Double-click IF in the list.
4. In Logical_test, enter A3=0, using the cell you want to check.
5. In Value_if_true, enter “” for a blank or “-“ for a dash.
6. In Value_if_false, enter A3.
7. Click Done.

The cell shows the blank or dash whenever A3 is zero, and the value of A3 the rest of the time.

You can wrap a calculation the same way. For example, =IF(B2=0,””,A2/B2) shows a blank when B2 is zero, which also avoids a #DIV/0! error.

The IF result is text, not a number. SUM skips text, so totals still work, but a formula that adds or multiplies the cell directly, such as =C2+1, returns #VALUE! when C2 holds “”. Point those formulas at the original values instead.

Hide Zeros in a PivotTable

1. Click anywhere in the PivotTable.
2. On the PivotTable tab, under Data, click Options.
3. Open the Display tab.
4. Check Empty cells as and leave the box empty to show blank cells, or type a dash. Clear the box if you want empty cells to show zeros.
5. Check Error values as and leave the box empty to replace errors with blanks, if you want that too.
6. Click OK.

These options handle empty and error cells. For PivotTable values that are actually 0, use the sheet-wide Zero values setting or the 0;-0;;@ number format above.

Remove Zeros Permanently With Find and Replace

Use this only when you want the zeros gone from the cells, not just hidden. Save a copy of the workbook first.

1. Select the range that holds the zeros.
2. Press Control + H, or choose Home › Find & Select › Replace.
3. In Find what, type 0. Leave Replace with empty.
4. Click Options and check Match entire cell contents.
5. Click Replace All.

Without Match entire cell contents, Excel would also strip the zeros out of numbers like 10 or 2050. Find and Replace looks at what’s typed in each cell, so zeros produced by formulas aren’t affected. Use the Zero values setting for those. For the Mac Replace options, read Find or replace text and numbers on a worksheet.

Hidden Zeros Still Affect Averages and Charts

Hiding a zero doesn’t remove it. A few results can surprise you:

• AVERAGE counts hidden zeros. The average of 10, 0 and 20 is 10, even if the 0 looks blank. To leave zeros out, use =AVERAGEIF(A1:A10,”<>0″).
• COUNT includes hidden zeros. Use =COUNTIF(A1:A10,”<>0″) to count only nonzero numbers.
• Charts still plot zeros. A line chart dips to 0 at every hidden zero. Remove the zeros from the chart’s source data if you don’t want them plotted.

How to Hide Zero Values in Excel for Windows

1. Go to File > Options > Advanced.
2. Under Display options for this worksheet, pick the sheet.
3. Uncheck Show a zero in cells that have zero value.
4. Click OK.

The custom number format, conditional formatting and IF methods work the same way on Windows. Press Ctrl + 1 to open Format Cells. For a PivotTable, Microsoft’s path is Analyze › Options › Options › Layout & Format, then For empty cells show. In current versions of Excel for Microsoft 365, the Analyze tab is named PivotTable Analyze.

Troubleshooting

• Zeros still show after you uncheck Zero values: the setting applies to one sheet. Make sure the sheet you’re looking at is the one that was active when you changed it.
• A cell shows 0 but doesn’t hide: the value isn’t exactly zero. A number like 0.004 displays as 0 when the format has no decimal places. Click the cell and check the formula bar. Values stored as text, such as “0” typed after an apostrophe, don’t hide either.
• You can’t find Zero values: in newer versions the menu item is Settings, not Preferences. In the window that opens, click View in the Authoring row.
• Zeros show on shaded cells: white font only works on a white background. Use the ;;; number format in your conditional formatting rule instead.
• Zeros came back after pasting: pasting values only doesn’t carry number formats or conditional formatting. Apply the format again to the new cells.
• The custom format changed your decimals: 0;-0;;@ shows whole numbers. Use #,##0.00;-#,##0.00;;@ to keep two decimal places.

Frequently Asked Questions

More Excel Guides

• How to enable the Developer tab in Excel for Mac
• Why does Excel keep changing my numbers to dates?
• How to keep the second decimal place in Excel