In the ‘Styles’ group, click on ‘Conditional Formatting’.Suppose you have a dataset as shown below and you want to hide zeros in the cells.īelow are the steps to hide zeros in Excel using conditional formatting: So, if the value in some cells is 0, you can simply program conditional formatting to hide it (or even highlight it if you want). With conditional formatting, you can apply a specific format based on the value in the cells. In case you only want to hide zeros for a specific dataset (or multiple datasets), you can use conditional formatting. While the above method is the most convenient one, it doesn’t allow you to hide zeros only in a specific range (rather it hides zero values in the entire worksheet). Hide Zero Value in Cells using Conditional Formatting In case you only want this to hide zero values in a specific range of cells, it’s better to use other methods covered next in this tutorial. But this is useful only if you want to hide zero values in the entire worksheet. This is the best and fastest method to hide zero values in Excel. Remember that this method only hides the 0 value in the cells and doesn’t remove these. This change is also applied to cells where zero is a result of a formula. The above steps would instantly hide zeros in all the cells in the selected worksheet. Uncheck the ‘Show a zero in cells that have zero value’ option.Scroll down to the section that says ‘Display option for this worksheet’, and select the worksheet in which you want to hide the zeros.In the Excel Options dialog box that opens, click on the ‘Advanced’ option in the left pane.Open the workbook in which you want to hide all the zeros.
Suppose you have the sales dataset as shown below and you want to hide all the zero values and show a blank cells instead.īelow are the steps to hide zeros from all the cells in a workbook in Excel:
Let’s get started and dive into each of these methods! Automatically Hide Zero Value in CellsĮxcel has an inbuilt functionality that allows you to automatically hide all the zero values for the entire worksheet.Īll you have to do is uncheck a box in Excel options, and the change will be applied to the entire worksheet. And in case you use these cells (that have hidden zeroes) in any formula, these will be used in the formula. While the cells may look empty, those cells still contain the 0s. Important Note: When you hide a 0 in a cell using the methods shown in this tutorial, it will only hide the 0 and not remove it.