Sometimes, you may find it better to hide the zero values in your data and show cells with zero values as blank cells in the report that you are submitting or presenting to an audience.
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 Microsoft Excel.
When you hide zeros in Excel, you are only hiding the data (number zero) in cells containing zero values, the cells will continue to retain their zero value data and this value will be considered in all calculations and formulas.
However, when you remove zeros from an Excel Data field, the cell becomes blank and its value won’t be considered in formulas and calculations.
Hide Zeros in Excel
You can find below, three different methods to Hide Zero value cells in Microsoft 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.
2. In the File Menu, scroll down to the bottom and click on 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.
4. 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.
3. In ‘Equal To’ dialog box, enter 0 in the left field. In the right-field, select Custom Format option and click on OK .
4. On Format Cells screen, select the Font tab > use the Color drop-down and select white color.
5. Click OK to save this setting.
The above method hides zeros in Excel by changing the font color of the cells that have zeros in them to white, which make these cells look blank.
3. Hide Zeros In Excel Using Background Color
The above method to hide zeros in Excel by changing the font color to white does not work if the cells in the worksheet have a colored background.
If you have a worksheet with colored 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.
3. In ‘Equal To’ dialog box, enter 0 in the left field. In the right-field, select Custom Format option and click on OK.
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.
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 apply the 3 semi-colons format only to cells with 0 values.