Advertisements

Display or hide zero values

Sometimes you might prefer zero (0) values to be hidden on your worksheets, or the contrary. Whether your format standards or preferences justify zeroes being shown or concealed, there are multiple ways to enforce this.

Hide or display all zero values on a worksheet

  1. Select File > Options > Advanced.
  1. Below Display options for this worksheet, choose a worksheet, and next, do one of the following:
  • To display zero (0) values in cells, tick the Show a zero in cells that have zero value check box.
  • To show zero (0) values as blank cells, clear the Show a zero in cells that have zero value check box.

Hide zero values in selected cells

These steps hide zero values in selected cells by using a number format. The hidden values only display in the formula bar and are not printed. If the value in one of these cells switches to a non-zero value, the value will be presented in the cell, and the format of the value will be consistent with the general number format.

  1. Click the cells that include the zero (0) values that you want to hide.
  1. You can press Ctrl+1, or on the Home tab, select Format > Format Cells.
  1. Select Number > Custom.
  1. In the Type box, enter 0;-0;;@, and then press OK.

To display hidden values:

  1. Choose the cells with hidden zeros.
  1. You can press Ctrl+1, or select Format > Format Cells on the Home tab.
  1. Select Number > General to use the standard number format, and then press OK.

Hide zero values returned by a formula

  1. Pick the cell with the zero (0) value.
  1. On the Home tab, select the arrow beside Conditional Formatting > Highlight Cells Rules Equal To.
  1. In the box on the left, enter 0.
  1. In the box on the right, pick Custom Format.
  1. In the Format Cells box, select the Font tab.
  1. In the Colour box, pick white, and then select OK.

Display zeroes as blanks or dashes

Use the IF function to do this.

Data in cells A2 and A3 in an Excel worksheet
© Microsoft 2019

Use a formula similar to this to get a blank cell when the value is zero:

=IF(A2-A3=0,””,A2-A3)

Here’s how to read the formula. If 0 is the result of (A2-A3), don’t show 0 – display nothing (indicated by double quotes “”). If that’s false, present the result of A2-A3. If you don’t want the cells empty but want to display another result apart from 0, put a dash “-“ or other character between the double quotes.

Hide zero values in a PivotTable report

  1. Select the PivotTable report.
  1. On the Analyse tab, in the PivotTable group, pick the arrow adjacent to Options, and then select Options.
  1. Press the Layout & Format tab, and then choose one or more of the following:
  • Change error display – Check the For error values show check box under Format. In the box, enter the necessary value to be presented in the place of errors. To show errors as blank cells, remove any characters in the box.
  • Change empty cell display – Check the For empty cells show check box. In the box, enter the value to be shown in empty cells. To display blank cells, clear any characters in the box. To display zeroes, uncheck the check box.

Advertisements
%d bloggers like this: