3 ways to suppress zero in Excel


If you don’t want to display zeros, use one of these quick and easy methods to suppress them in Microsoft Excel.

Excel logo

Image: iStock/muchomor

It’s not uncommon to want to hide—suppress—zeros in a spreadsheet. When this is the case, Excel offers three easy ways to get the results you want. In this article, I’ll show you a setting, a format, and a function solution. Because they respond differently, you’ll want to know how to implement all three.

SEE: 60 Excel tips every user should master

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use easier versions. You can work with your own data or download the demonstration .xlxs file. The browser will display the results correctly. The last method is the only one that you can implement in the browser.

How to suppress zeros in an entire Excel sheet

The easiest method to suppress zeros is a simple setting with an all-or-nothing result, which is both a pro and a con, depending on your needs. Remember, this setting works on the entire sheet:

  1. Click the File tab, choose Options in the left pane, and then click Advanced in the left pane.
  2. In the Display options for this worksheet section, uncheck Show a Zero in Cells that Have Zero Value (Figure A).
  3. Click OK to close the dialog.

SEE: How to use Find All to manipulate specific matching values in Excel (TechRepublic)

Figure B shows the results of unchecking this option to the left; you can see where zeros, expressions that evaluate to zero, and blanks were for comparison. (Column D contains the simple expression, =B3+C3, and three cells are blank.) Just remember that the entire sheet is suppressing zero. Try to enter 0 or an expression that returns zero in an empty cell. You can enter it, and you can see it in the Formula bar, but you can’t see it in the cell. If this setting is overkill, you can use a custom format.

Figure A

excelsuppress0-a.jpg

   Uncheck this option to hide all zeros. 

Figure B

excelsuppress0-b.jpg

  This option suppresses literal zero values and expressions resulting in zero.

How to suppress a range in Excel

You can limit the cells that suppress zero by applying a custom format. As a result, you control which cells to suppress. You get more control, but it does usurp your formatting capabilities and might require a bit of thought.

Before we continue, we need to review a custom format’s structure, or syntax:

positive; negative; zero; text

The simplest custom format that will suppress zero is:

0;0;;@

The zeros are placeholders and will display any positive or negative value. Leaving the third component, the zero component, empty is what suppresses zeros. The @ is a text placeholder, similar to the 0 placeholder. Now, let’s apply this custom format and see what happens:

  1. Select the range you want to format. In this case that’s B3:D7.
  2. Click the Number group’s More button (on the Home tab).
  3. In the Category list, click Custom (at the bottom).
  4. Enter 0;0;;@ in the Type control, as shown in Figure C.
  5. Click OK to close the dialog.

Figure C

excelsuppress0-c.jpg

  Apply this simple custom number format to suppress zeros.

At first, it looks like it worked fine, but compare the formatted results in Figure D to the original data to in Figure B. We’ve lost the currency symbol and the decimal places. (If you remove the @ symbol, the format will also suppress all text.)

Figure D

excelfindtrick-d.jpg

    The custom format didn’t work as expected.

The good news is we can easily modify the custom format so it accommodates our needs. Specifically, retry using the $0.00;$0.00;;@ format. In Figure E, you can see that the second format maintains the currency symbol and the decimal places. If a custom format won’t work for you, you might want to use a formula.  

Figure E

excelsuppresse-c.jpg

  This format handles the requirements.

How to suppress zero in a cell in Excel

When using an expression that might return a zero, you can wrap that expression in an IF() function to suppress a 0 result. Generally, you won’t want to do this, but to be comprehensive, you’ll want to know how to do this.

Our simple expression in column D is =B3+C3, and two of them (D5 and D7) both return 0. Select cell D3 and enter the expression

=IF(B3+C3=0,””,B3+C3)

and copy to D4:D7. Figure F shows the results; D5 and D7 are blank. The IF() function evaluates the real expression that sums the two values and returns an empty string (“”) when the result is 0. If it isn’t 0, the IF() returns the non-zero result. The big difference between this solution and the first two is that you’ll use this only with expressions, not values.

SEE: How to calculate bonuses and commissions in Excel (TechRepublic)

Figure F

excelsuppress0-f.jpg

  Wrap your expression in an IF() to suppress 0.

Stay tuned

This article shows you three easy ways to suppress zero values. In a subsequent article, I’ll show you how to handle situations where you want to different values when suppressing zero.

Also see



Source link