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

If you want to hide or suppress zeros in a spreadsheet, Excel offers three easy ways to get these results. In this Excel tutorial, I’ll show you how to implement a setting, a format or a function solution to hide or suppress zeros.

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. Excel for the web will display the results correctly; however, the last method is the only one that you can implement in the browser.

SEE: Microsoft Office Professional 2021 for Windows: Lifetime License (TechRepublic Academy)

How to suppress zeros in an entire Excel sheet

The easiest method to suppress zeros in Excel 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 Excel 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).

Figure A

Use this option to display the 0 values.

3. Click OK to close the dialog.

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.)

Figure B

There are no visible 0 values. Just remember that the entire Excel sheet is suppressing zero. If you like, 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. If you’re working with the demonstration file to follow along, be sure to reset this option before continuing.

SEE: 108 Excel tips every user should master (TechRepublic)

How to suppress a range in Excel

You can limit the Excel 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 continuing, 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 (Figure C).

Figure C

Use this format to inhibit 0 values.

5. Click OK to close the dialog.

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

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.

Figure E

This custom format is more comprehensive than the first one we tried.

If a custom format won’t work for your needs, you might want to use a formula. Be sure to reset the custom format before you continue to the next section.

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 D5 returns 0. Select cell D3 and enter the expression

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

and copy to D4:D6. Figure F shows the results; D5 is blank.

Figure F

You can hide 0s using an IF() function.

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. In addition, you can control which expressions return the empty string instead of zero.