Excel Novice

[HE_Blandswift]

How can I hide a negative number (or show it as 0)?

[#2]

@HE_Blandswift 

In Excel, you can hide negative numbers or display them as 0 by using a custom number format. Here is how you can do it:

1. Select the cell or range of cells that contains the numbers you want to format.
2. Right-click on the selected cell(s) and choose "Format Cells" from the context menu.
3. In the Format Cells dialog box, go to the "Number" tab.
4. In the "Category" list on the left, select "Custom."
5. In the "Type" input box, you can specify a custom number format. To hide negative numbers or display them as 0, you can use the following format:

For hiding negative numbers: 0;0;0;

For displaying negative numbers as 0: 0;0;0;0

1. Click the "OK" button to apply the custom number format.

Now, the selected cells will display negative numbers as either 0 (if you chose the second format) or as completely blank (if you chose the first format). Positive numbers will be displayed as is.

Keep in mind that this formatting change is only cosmetic. The actual values in the cells remain unchanged. If you need the modified values for calculations, you may need to perform additional operations or use formulas to work with them as needed. The text and steps were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

@HE_Blandswift 

A custom number format will do that.  A good introduction can be found in ExcelJet's Excel custom number formats, or you can read Microsoft's Review guidelines for customizing a number format.

The basic form of Excel custom number formats can have up to four sections, separated by semicolons:

1. format code for positive numbers
2. format code for negative numbers
3. format code for zeros
4. format code for non-numbers (text)

If you omit the encoding for a section – but retain the semicolons around it – values corresponding to that section will not be displayed. E.g., to not display negative numbers, or to display them differently, you might use one of these custom formats (these work best with integer values; real numbers are rounded):

0;;0;@
0;"";0;@
0;"(neg)";0;@
0;"0";0;@

See the attached workbook. The built-in function TEXT used in formulas therein does much the same work as the Format Cell dialog*; that's useful for trying out custom or built-in formats without having to open and close the Format Cell dialog many times.

* - As the TEXT function generates a text value, its result does not follow the rules for aligning number values. I had to force right-alignment on the rows for number values.

BTW, a quick way (in Excel for Windows) of getting to the Format Cells dialog is to press Ctrl + 1. Select the cells you want to have a new/changed format before pressing that key combination.