03 Best Ways_ How to Hide Zero Values in Excel

03 Best Ways: How to Hide Zero Values in Excel

By

It is very essential in the Excel dashboard preparation that how to hide zero values in Excel? It gives a dashboard unique appearance, easily visible the numbers, so anyone can analysis the data properly. 

In many of our daily worksheet calculations evaluate to zero, generally, we hide zero values. As a result, this will help to highlight those cells that contain actual numeric values and remove any distraction created by all the zeros. 

Please remember that zero is considered a number and a blank cell has no numerical value. Hide zero values in excel is controlled on an individual worksheet, but other worksheets remain unaffected.  

03 Best Ways: How to Hide Zero Values in Excel

Method 1: Hide Zero Values in Excel by the ‘Workbook Settings’

The workbook settings will hide zero values in Excel. It is applicable for a particular worksheet, not for all the worksheets. 

(01) Click on the ‘File menu at the corner.

(02) Click on ‘Options’ under it to open the Excel Options wizard.

(03) Choose the ‘Advanced’ from the sidebar menu.

(04) Scroll down to find ‘Display options for this worksheet’ and uncheck the box against “Show a zero in cells that have zero value”.

(05) Then click OK or press Enter.

 

Steps of Hide Zero Values in Excel by the 'Workbook Settings'

 

The zero values hide in cells, ranges and the result as below as,

As a result, hide zero values in Excel, but it is applicable for a particular worksheet, not applicable to all the worksheets.  

Result of Hide Zero Values in Excel by the 'Workbook Settings'

 


Method 2: Hide Zero Values in Excel by the ‘Format Cells‘ Window

The ‘Format Cells’ window will allow us to hide specific zero values in Excel.

(01) Select the range of cells that contain zero values. For example, here the range is L3:N12.

(02) It is best practice to use the Excel shortcut Ctrl+1 to open the ‘Format cells’ Window ➪ Click on the ‘Number’ Tab.

 

(i) Open ‘Format Cells’ using the Ribbon (via Number Section)

Click the Home tab ➪ go to Number section and click on Number settings (the small arrow in the bottom-right corner) that will open Format cells wizard ➪ Click on Number tab (if required).

 

Hide Zero Values in Excel by the 'Format Cells' Window -Using the ribbon Number setting

 

Alternatively,

(ii) Open ‘Format Cells’ using the Ribbon (via Alignment Section)

Click the Home tab ➪ go to the Alignment section and click on Alignment settings (the small arrow in the bottom-right corner) that will open Format cells wizard ➪ Click on Number tab.

 

Hide Zero Values in Excel by the 'Format Cells' Window -Using the Alignment setting

 

(03) Choose Custom from the Category list.

(04) Just type 0;-0;;@ in the Type field. Click OK or Enter.

 

Steps of Hide Zero Values in Excel by the 'Format Cells' Window

 

As a result, hide zero values in Excel for a particular worksheet, not applicable to all the worksheets.  

Result of Hide Zero Values in Excel by the 'Format Cells' Window

 

Note: If we want to undo this format, select General and click OK or press Enter.

 

Undo the process of Hide Zero Values in Excel by the 'Format Cells' Window

 


Method 3: Hide Zero Values in Excel by the ‘Conditional Formatting Rules Manager’

The third method is the conditional formatting allows us to hide zero values in a sheet.

Select the range of cells in which contain the zero values and want to hide. For example, here the range is L3:N12.

(01) It is best to use the Excel shortcut by pressing Alt+O+D to open the ‘Conditional Formatting Rules Manager and then Alt+N for New Rule or click on it.

 

Hide Zero Values in Excel by the 'Conditional Formatting Rules Manager'-using Excel shortcut

 

(i) Open ‘Conditional Formatting Rules Manager’ using the Ribbon

Click on the Home tab ➪ then click on the Conditional Formatting option in the Styles section ➪ Choose New Rule.

 

Hide Zero Values in Excel by the 'Conditional Formatting Rules Manager'-using the Excel ribbon

(02) After opening the New Formatting Rule, select the second option ‘Format only cells that contain’.

(03) From the second dropdown, choose ‘equal to’.

(04) Enter zero (0) in the third control.

 

Steps of Hide Zero Values in Excel by the 'Conditional Formatting Rules Manager'

 

(5) Then click on the ‘Format’ box and open the ‘Format cells‘ window.

(6) Click on the Font tab.

(7) From the Color dropdown, choose the theme color White or the same color as in the background

(8) & (9) Click OK twice.

Finally, we got the result. Remember that as a result hide zero values in Excel for a particular worksheet, not applicable to all the worksheets..


Read-More-6

Sudipta Mondal
Follow AEF
03 Best Ways: How to Hide Zero Values in Excel

It is very essential in the Excel dashboard preparation that how to hide zero values in Excel? It gives a dashboard unique appearance, easily visible the n

Editor's Rating:
5

Leave a Comment

You may also like