03 Best Ways: How to Hide Zero Values in Excel


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 the dashboard a unique appearance, easily visible the numbers, so anyone can analyze 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 zeros 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,

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

Please keep in mind that we can hide zeros in Excel in a particular worksheet, but not to all the worksheets. 

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.

A. Open ‘Format Cells' using the Ribbon (via Number Section)

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

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

Alternatively,

B. Open ‘Format Cells' using the Ribbon (via Alignment Section)

Click the Home tab ➪ then go to the Alignment section and click on Alignment settings (the small arrow in the bottom-right corner) that will open the Format cells wizard ➪ Click on the 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 conditional formatting allows us to hide zero values in a sheet. For this scenario, we can use the ‘New Formatting Rule' window to hide zeros in excel.

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

(01) We can open the ‘New Formatting Rule‘ window by 03 Methods:

A. Open ‘New Formatting Rule' Window using the Excel Shortcut

It is best to use the Excel shortcut by pressing Alt+O+D (press sequentially, Alt, O, D) o open the ‘Conditional Formatting Rules Manager and then press Alt+N for New Rule or click on it ➪ As a  result, the ‘New Formatting Rule‘ window opens.

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

B. Open ‘New Formatting Rule' using the Alternate Excel Shortcut

Press Alt+H+L+N (press sequentially, Alt, H, L, N) which will open the New Formatting Rule window.

C. Open ‘New Formatting Rule' using the Ribbon

Click on the Home tab ➪ then click on the Conditional Formatting option in the Styles section ➪ Choose New Rule ➪ As a  result, the ‘New Formatting Rule‘ window opens.

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) value in the third control.

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

(05) Then click on the ‘Format' box and open the Format cells window.

(06) Click on the Font tab.

(07) From the Color dropdown, choose the theme color White or choose the same color matching with the background

(08) Click OK which will close the ‘Format Cells' dialog box.

(09) Again click OK which will close the ‘Edit Formatting Rule' box.

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.

If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
AEF_192by192
Advance Excel Forum

Premium Courses on ed2go

03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Advanced Microsoft Excel 2019/Office 365

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Microsoft Excel 2019 Certification Training (Voucher Included)

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Accounts Payable Specialist Certification with Microsoft Excel 2019 (Voucher Included)

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Certified Internal Auditor with Microsoft Excel 2019

 

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Executive Assistant with Microsoft Office Specialist 2019 (Vouchers Included)

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Payroll Manager

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Accounts Payable Specialist Certification with Microsoft Excel 2019

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Executive Assistant (Voucher Included)

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Intermediate Oracle (Self-Paced Tutorial)

Bestseller
4.9/5

Premium Courses on FutureLearn Limited

Premium Courses on Coursera

03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Data Visualization in Excel

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Everyday Excel, Part 1

Top Rated
5/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Everyday Excel, Part 2

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Success

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Advanced Data Science with IBM

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Predictive Analytics and Data Mining

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Organizational Analysis

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

IBM Data Analyst

Bestseller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Data Analysis and Presentation Skills: the PwC Approach Final Project

Bestseller
4.9/5

Premium Courses on Udemy

03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Microsoft Excel Pivot Tables – Master Excel Pivot Tables!

Top Rated
4.7/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

77-728 Microsoft Excel 2016 Expert Certification

Bestseller
4.6/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Ultimate Microsoft Excel Course: Beginner to Excel Expert

Top Rated
5/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Microsoft Excel – Excel from Beginner to Advanced & VBA

Best Seller
4.9/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Google Professional Cloud Data Engineer

Bestseller
4.6/5
03 Best Ways: How to Hide Zero Values in Excel03 Best Ways: How to Hide Zero Values in Excel

Advanced Excel Skills-How to finish works faster

Best Seller
4.8/5
Read-More_4

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.


Like it? Share with your friends!

Share via

Join Our Community List

Community grow with You. * VERIFY & CONFIRM YOUR EMAIL *

Thanks for your interest joining to Advance Excel Forum community.

Fill the Correct Information.

Thanks for your interest joining to Advance Excel Forum community.

Something went wrong.

Join Our Community List

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We use cookies to ensure that we give you the best experience on our website (by analyzing).  Please see our cookies page for further details or agree by clicking the 'Accept' button.

 

Cookie settings

Below you can choose which kind of cookies you allow on this website. Click on the "Save cookie settings" button to apply your choice.

FunctionalOur website uses functional cookies. These cookies are necessary to let our website work.

AnalyticalOur website uses analytical cookies to make it possible to analyze our website and optimize for the purpose of a.o. the usability.

Social mediaOur website places social media cookies to show you 3rd party content like YouTube and FaceBook. These cookies may track your personal data.

AdvertisingOur website places advertising cookies to show you 3rd party advertisements based on your interests. These cookies may track your personal data.

OtherOur website places 3rd party cookies from other 3rd party services which aren't Analytical, Social media or Advertising.