• Around The HomeToggle Menu

    • Entertainment
    • Productivity
    • Smart Home
  • FamilyToggle Menu

    • Parenting
    • Toys
    • Pets
    • Travel
  • ReviewsToggle Menu

    • Phones
    • Tablets
    • Laptops
    • Desktops
    • Wearables
    • Audio
    • Cameras
    • Headphones
    • Printers
    • Smart Home
    • TVs
    • Gaming and Video
  • One Cool ThingToggle Menu

    • Frugal Tech
    • Kickstarters
    • Videos
  1. Home
  2. Around The Home
  3. Productivity
  4. How to Enter Formulas in Excel to Show Not Less Than Zero

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

By: Laurie Marie
  • Share
  • Share on Facebook

There are several ways to prevent values less than zero from displaying in Microsoft Excel. You can use conditional formatting to change the color 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.

Hide Negative Values With Conditional Formatting

Step

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

Step

Click "Conditional Formatting" under the "Format" menu.

Step

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

Step

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

Video of the Day

Step

Enter 0 in the next box.

Step

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

Step

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.

Display Negative Values As Blank Cells or Dashes

Step

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)

Step

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

Step

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.

Step

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

Show Comments

Related Articles

How to Use Less Than or Equal to Function in Excel

How to Use Less Than or Equal to Function in Excel

Around The Home
Productivity
By: Steven Melendez
How to Use Excel to Calculate How Much a Loan Will Cost

How to Use Excel to Calculate How Much a Loan Will Cost

Around The Home
Productivity
By: Mark Kennan
How to Use Negative Numbers to Calculate in Excel

How to Use Negative Numbers to Calculate in Excel

Around The Home
Productivity
By: Kate Bluest
How to Use PI in Excel

How to Use PI in Excel

Around The Home
Productivity
By: Nick Peers
How to Calculate the Percent of Loss/Gain in Microsoft Excel

How to Calculate the Percent of Loss/Gain in Microsoft Excel

Around The Home
Productivity
By: Mark Kennan
How to Calculate Absolute Value in Excel

How to Calculate Absolute Value in Excel

Around The Home
Productivity
By: C. Taylor
  • HOW WE SCORE
  • ABOUT US
  • CONTACT US
  • TERMS
  • PRIVACY POLICY
  • COPYRIGHT POLICY
  • Advertise

An error occurred. Try again later.

Thanks for signing up!
© 2019 Leaf Group Ltd. Leaf Group Media

Get great tech advice delivered to your inbox.

Keep your family productive, connected, entertained, and safe.

Please enter a valid email.