Display or hide zero values

In Excel, you can choose whether to display or hide zero values in cells. This is especially useful when you have calculations or data where zero values are not meaningful and might clutter your worksheet.

Here are the ways to hide zero values or display them if they’re hidden:

1. Hide Zero Values Using Excel Options

You can hide zero values across the entire worksheet using the Excel Options.

Steps to Hide Zero Values:

1. Open Excel Options:
   • Go to the File tab (or the Office button in older versions of Excel) and click on Options. This will open the Excel Options dialog box.
2. Navigate to Advanced Settings:
   • In the Excel Options dialog box, click on Advanced in the left sidebar.
3. Find the Display Options for This Worksheet:
   • Scroll down to the Display options for this worksheet section.
4. Disable Display of Zero Values:
   • Under the section, uncheck the box labeled Show a zero in cells that have zero value.
5. Click OK:
   • Click OK to save your changes.

After this, Excel will hide any zero values in your worksheet, making it look cleaner without showing the zeros.

2. Show Zero Values Again

If you later want to display zero values again, you can simply go back to the Excel Options and check the Show a zero in cells that have zero value box.

3. Hide Zero Values Using Conditional Formatting

If you don’t want to globally hide zero values for the entire worksheet but need more flexibility, you can use Conditional Formatting to change how zero values appear (e.g., by making them invisible or using a custom number format).

Steps to Hide Zero Values with Conditional Formatting:

1. Select the Range of Cells:
   • Select the cells or range where you want to hide zero values.
2. Open Conditional Formatting:
   • On the Home tab, in the Styles group, click on Conditional Formatting.
3. Choose New Rule:
   • From the dropdown, select New Rule.
4. Select Format Only Cells That Contain:
   • In the New Formatting Rule dialog, select Format only cells that contain.
   • In the dropdown, choose Cell Value and select equal to.
   • In the value box, enter 0.
5. Set Formatting:
   • Click the Format button and go to the Font tab.
   • Set the Font Color to white or the same color as the background, making the zero values invisible.
6. Click OK:
   • Click OK to apply the formatting.

This method will hide zero values in the selected range, but it won’t remove them; it will just make them invisible by changing the font color.

4. Use Custom Number Format to Hide Zeros

Another way to hide zero values is by using a custom number format that doesn’t display zeros.

Steps to Use Custom Number Format:

1. Select the Range of Cells:
   • Select the range of cells where you want to hide zeros.
2. Open the Format Cells Dialog:
   • Right-click the selection and choose Format Cells or press Ctrl + 1.
3. Select Custom Format:
   • In the Format Cells dialog, go to the Number tab and select Custom from the list on the left.
4. Enter Custom Number Format:
   • In the Type box, enter the following custom format:

     0;-0;;@
     

     This format will display positive numbers normally, negative numbers with a minus sign, and will hide zeros (i.e., show nothing in place of zero values).

5. Click OK:
   • Click OK to apply the format.

Summary of Methods:

• Hide Zero Values Globally: Use Excel Options to hide all zero values across the worksheet.
• Conditional Formatting: Use this to make zero values invisible or format them differently.
• Custom Number Format: Use custom formatting to display values normally, but hide zeros by leaving them blank.

These options allow you to manage the display of zero values in Excel to make your worksheet clearer and more visually appealing.