• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Office » How to Hide Zeros in Excel

How to Hide Zeros in Excel

By: Waseem Patwegar

Sometimes you may find the need to hide zero values in your data and show cells with zero values as blank cells. You will find below different methods to Hide Zeros in Excel.

Hide Zeros in Excel

Hide Zeros in Excel

Before going ahead with the steps to Hide Zeros in Excel, you may want to understand the difference between hiding zeros and removing zeros in Excel.

When you hide zeros in Excel, you are only hiding the data in cells containing zero values, the cells continue to retain their zero values data.

This practically means that the data (zero value) in the cell will be considered in all calculations and formulas.

In comparison, when you remove zeros from an Excel Data field, the cell becomes blank and its won’t be considered in formulas and calculations.

With this understanding, let us go ahead and take a look at different methods to Hide Zeros in Excel.

1. Automatically Hide Zeros in Excel

Follow the steps below to automatically hide zeros in Excel.

1. Open the Excel worksheet in which you want to hide zeros and click on the File tab.

File Tab in Excel

2. In the File Menu, scroll down to the bottom and click on Options.

Open Excel Options

3. On Excel Options screen, click on Advanced in the left pane. In the right-pane, scroll down to ‘Display options for this worksheet’ section > select the worksheet in which you want to hide zero values and uncheck Show a zero in cells that have zero value option.

Hide Zero in Cells Having Zero Value

5. Click on OK to save this setting for the worksheet.

Once you click on OK, all the cells in the data field having zeros will appear blank.

2. Hide Zeros in Excel Using Conditional Formatting

The above method hides zero values in the entire worksheet and cannot be used to Hide Zeros in a specific range in the data.

If you want to hide Zeros in a selected portion of the data, you can use conditional formatting.

1. Select the Portion of Data in which you want to Hide Zero Values.

2. Click on the Home tab > Conditional Formatting > Highlight Cells Rules and click on Equal to option.

Equal to Option in Excel Conditional Formatting

3. In ‘Equal To’ dialog box, enter 0 in the left field. In the right-field, select Custom Format option and click on OK .

Apply Custom Formatting in Excel

4. On Format Cells screen, select the Font tab > use the Color drop-down and select white colour.

Format Cells Screen in Excel

5. Click OK to save this setting.

The above method hides zeros in Excel by changing the font colour of the cells that have zeros in them to white, which make these cells look blank.

3. Hide Zeros In Excel Using Background Colour

The above method to hide zeros in Excel by changing the font colour to white does not work if the cells in the worksheet have a coloured background.

If you have a worksheet with coloured background, you can still hide zero values by following the steps below.

1. Select the Portion of Data in which you want to Hide Zero Values.

2. Click on the Home tab > Conditional Formatting > Highlight Cells Rules and click on Equal to option.

Equal to Option in Excel Conditional Formatting

3. In ‘Equal To’ dialog box, enter 0 in the left field. In the right-field, select Custom Format option and click on OK.

Apply Custom Formatting in Excel

4. In ‘Format Cells’ screen, click on the Number tab and select Custom option in the left pane. In the right-pane, enter ;;; (3 semi-colons) in the ‘Type’ field and click on OK.

Hide Cells Having Zeros Using Excel Cell Formatting

When you apply ;;; (3 semi-colons) format in Excel, it hides both numeric and text values in the cells in which this format is applied.

In this case, we are using conditional formatting to applying the 3 semi-colons format only to cells with 0 values.

Related
  • How to Hide Cells, Rows and Columns In Excel
  • How to Hide Formulas in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Recent Posts

  • Fix: iPhone Camera Black Screen Problem
  • How to Clear Your Activity History In Windows 11
  • How to Find WiFi Password in Windows 11
  • How to Enable Touch Keyboard in Windows 11/10
  • How to Send Group Email In Gmail App On iPhone




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved