How to Show or Hide Zero Values in Excel?
Tags: kutools
Published: 2023-04-04 05:22:05

---
title: How to Show or Hide Zero Values in Excel?
published: true
date: 2023-04-03 04:36:45 UTC
tags: Kutools
canonical_url: https://aawexcel.com/how-to-show-or-hide-zero-values-in-excel/
---

[Excel](https://www.microsoft.com/en-us/microsoft-365/excel) is a powerful tool that is widely used to **organize, analyze,** and **display data**. One important aspect of data presentation in Excel is the display of zero values. Zero values can be useful to **show the absence of data** or **to display calculations** , but they can also clutter the appearance of a worksheet and make it harder to read. In this tutorial, we will show you how to show or hide zero values in Excel. We will cover three methods: **using the Excel Options settings, using a conditional formatting rule** and **using**  **Kutools**. These methods will allow you to **customize** your worksheet to display zero values only when necessary, making it easier to read and understand your data.

## Show/Hide Zero Values in the Spreadsheet

There are two methods to **show or hide zero values** in the spreadsheet:

### **Using the Excel Options settings**

Step 1: Open your Excel spreadsheet.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-1.webp)
_Data range_

Step 2: Click on the “ **File** ” tab in the top-left corner of the Excel window.

Step 3: Click on “ **Options** ” in the left-hand menu.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-2-1024x871.webp)
_File > Options_

Step 4: In the Excel Options dialog box, select the “ **Advanced** ” tab.

Step 5: Scroll down to the “ **Display options for this worksheet** ” section and uncheck the “ **Show a zero in cells that have a zero value** ” box to hide zero values or check the box to show them.

Step 6: Click on “ **OK** ” to save the changes.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-3-1-1024x789.webp)
_Uncheck the Zero values_

### Using Conditional Formatting

Step 1: Select the cells or range of cells where you want to show or hide zero values.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-4.webp)
_Selecting the Data range_

Step 2: Click on the “ **Home** ” tab in the Excel ribbon.

Step 3: Click on “ **Conditional Formatting** ” in the “ **Styles** ” group.

Step 4: Click on “ **New Rule** ” in the Conditional Formatting dialog box.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-9-1024x416.webp)
_Home > Conditional Formatting > New Rule_

Step 5: In the “ **New Formatting Rule** ” dialog box, select “ **Format only cells that contain** ” from the “ **Select a Rule Type** ” section.

Step 6: In the “ **Format only cells with** ” section, select “ **Cell Value** ” from the first drop-down menu, “ **equal to** ” from the second drop-down menu, and enter “ **0** ” (zero) in the third field.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-6-1024x583.webp)
_Formatting Cells_

Step 7: Click on the “ **Format** ” button and select the font color, background color, or any other formatting options you want to apply to the cells that contain zero values.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-7-1024x419.webp)
_Format > OK_

Step 8: Click on “ **OK** ” to save the formatting changes.

![](https://aawexcel.com/wp-content/uploads/2023/04/Show-zero-9-1.webp)
_Output_

Step 9: Now, the zero values in the selected cells will either be shown or hidden based on the settings you have chosen.

## Show/Hide Zero Values in the Spreadsheet with Kutools

To show or hide the zero values in the worksheet, do as follows:

**Step 1:** First, you need to enable the **Design Tab. ** To do this, on the **[Kutools Plus](https://aawexcel.com/kutools/)** tab, select the **[Worksheet](https://aawexcel.com/how-to-create-worksheets-from-a-list-of-worksheet-names-in-excel/) Design** option.

![select the Worksheet Design option](https://aawexcel.com/wp-content/uploads/2020/10/EXCEL_cJkt0ALDpj.png)
_Selecting the Worksheet Design option_

**Step 2** : Now, it will show the **Design** tab **,** where you can see the **Show Zero** option.

![Design Tab](https://aawexcel.com/wp-content/uploads/2020/10/Design-Tab-1.png)
_Design Tab_

**Step 3:** Let’s consider the below example data, where you can see the **range of cells with zero values** that are highlighted in this image.

![Example data](https://aawexcel.com/wp-content/uploads/2020/10/Example-data-5.png)
_Example data_

**Step 4:** On the **Design** Tab, you need to check the **Show Zero** option to **display all the zeros** in the selected range.

![Enable Show Zero option](https://aawexcel.com/wp-content/uploads/2020/10/Enable-Show-Zero-option.png)
_Enable Show Zero option_

**Step 5:** If you want to **hide the zeros** in the range, then uncheck the “ **Show Zero”** option.

![Hide Zeros](https://aawexcel.com/wp-content/uploads/2020/10/Hide-Zeros.png)
_Output_

### Notes

- The **Show Zero** option works for the whole worksheet.
- If you want to **close** the **Design Tab,** you need to **click** the **Close Design** option in the **Options** Tab under the **Design Tab.**

![Close Design option](https://aawexcel.com/wp-content/uploads/2020/10/Close-Design-option.png)
_Close Design option_

## Advantages

There are several advantages to showing or hiding zero values in Excel:

- **Improved presentation** : Hiding zero values that are not needed can help create a more visually appealing and professional-looking worksheet.
- **Reduced errors:** By only showing zero values that are relevant, you can reduce the risk of errors in your analysis and improve the accuracy of your results.
- **Increased efficiency** : By using the appropriate settings to show or hide zero values, you can save time and effort in your data analysis process, allowing you to focus on more important tasks.
- **Improved readability** : By hiding zero values that are not necessary, you can reduce clutter and make your data easier to read and understand.
- **Customization** : Excel allows you to customize your worksheet to display zero values in a way that works best for your specific data and analysis needs.

Overall, showing or hiding zero values in Excel is a useful technique that can help to improve the quality and effectiveness of your data analysis.

## Verdict

In conclusion, **showing or hiding zero values in Excel** can greatly improve the readability of your data. By following the steps outlined in this tutorial, you can **customize** your worksheet to display zero values only when necessary and hide them when they are not relevant to the analysis. This will make it easier for you and your audience to understand the data and draw meaningful insights. Whether you choose to use the **Excel Options** settings or a conditional formatting rule, these methods are simple to implement and can save you time and effort in the long run. With these techniques, you can take your Excel skills to the next level and **create clear** and **concise** worksheets that effectively communicate your data. For more articles, you can visit our [homepage](https://aawexcel.com/).

## Video Tutorial

Here is the video tutorial to **showing or hiding zero values in Excel** for your better understanding.

<video controls poster="http://aawexcel.com/wp-content/uploads/2023/04/How-to-Show-or-Hide-Zero-Values-in-Excel.png.webp" src="http://aawexcel.com/wp-content/uploads/2023/04/Show-Zero-values.webm"><track src="http://aawexcel.com/wp-content/uploads/2023/03/no-captions.vtt" srclang="en" label="English" kind="subtitles"></track></video>
_Showing or hiding zero values in Excel_

## FAQ

**How can I show or hide zero values in Excel?** 

There are two methods you can use to show or hide zero values in Excel. You can either use the Excel Options settings or a conditional formatting rule.

 
 
**Why would I want to hide zero values in Excel?** 

Hiding zero values can help reduce clutter and make your data easier to read and understand. It can also help to create a more visually appealing and professional-looking worksheet.

 
 
**Can I still use zero values in my calculations if I hide them?** 

Yes, hiding zero values does not affect their use in calculations. You can still use zero values in your calculations even if they are hidden from view.

 
 
**Can I selectively hide zero values in specific cells or ranges?** 

Yes, you can use a conditional formatting rule to selectively hide zero values in specific cells or ranges based on certain criteria.