How to Enter Formulas in Excel to Show Not Less Than Zero

Written by laurie marie
  • Share
  • Tweet
  • Share
  • Email

There are several ways to prevent values less than zero from displaying in Microsoft Excel. You can use conditional formatting to change the colour of the cell to hide the value. Using this method retains the value in the cell so you can use it in other formulas. Alternatively, you can convert negative numbers into blank cells or dashes. Using this method does not retain the value in the cell.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Microsoft Excel

Show MoreHide

Instructions

  1. 1

    Select the cell(s) in your Excel spreadsheet you wish to format.

  2. 2

    Click “Conditional Formatting” under the “Format” menu.

  3. 3

    Select “Cell value is” from the first drop-down menu for Condition 1.

  4. 4

    Select “less than or equal to” from the second drop-down menu.

  5. 5

    Enter 0 in the next box.

  6. 6

    Click on the Format button and select white in the colour drop-down menu. If the background colour of your cells is something other than white, select a colour that will blend in and make it appear as if the cell is empty.

  7. 7

    Click OK. Any cells you have applied this conditional formatting to will appear to be blank if the value in the cell is equal to or less than zero. If you place your cursor in the cell you will be able to see the cell’s actual value in the formula bar at the top of the worksheet.

  1. 1

    Use the IF function to specify the format of a result that is equal to or less than zero. The syntax of the IF function is IF(logical_test,value_if_true,value_if_false)

  2. 2

    Enter 1 in cell A1 and enter 2 in cell A2 of your worksheet.

  3. 3

    Create an IF formula in cell A3 by typing: =IF(A1-A2>0,A1-A2,“-”). In this example the logical test is whether cell A1 minus cell A2 is greater than zero. If it is the “value_if_true” it will display; if it isn’t, the “value_if_false” will display. In our formula we defined the value_if_true as the difference between cells A1 and A2 and the value_if_false as a dash. Therefore, if A1-A2 is zero or less you’ll see a dash in cell A3, if the result is positive, you will see a number.

  4. 4

    Change the values in cells A1 and A2 to see how it affects what displays in cell A3.

Tips and warnings

  • This process works in Excel for Windows and Mac.
  • To display a blank cell if the value is zero or less replace “-” in the IF function formula with “”. Alternatively, you can put any text string between the quote marks and that text will display in the cell when the logical_test is false.
  • When you use conditional formatting, you cannot use references to cells in other worksheets or workbooks.

Don't Miss

References

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.