步骤1:在空白单元格(例如E1单元格)中输入公式=IF(A1<0,0,A1),见截图: 步骤2:然后按Enter键,并拖动填充柄到你想应用此公式的区域。所有的负数都已变为零。见截图: 步骤3:由于它们是公式,当你将它们复制到其他单元格时,请粘贴为值。 注意:这种方式也会将空白单元格填充为零。 只需几次点击即可将所选区域...
Show Negative Numbers.xlsm Related Articles How to Count Negative Numbers in Excel Excel Formula If Cell Contains Negative Number Excel Formula to Return Blank If Cell Value Is Negative << Go Back to Negative Numbers in Excel | Number Format | Learn Excel Get FREE Advanced Excel Exercises with...
Insert the amount for the maximum number allowed as the formula result -- in this case, zero: "=MAX(0,B1-A1)". Any results that would normally be negative become 0, not just as display text, but as value. If the formula result of B1-A1 is -2, once you use the MAX function, i...
Show a Zero for a Negative Number You can also use custom formatting to show a zero for a negative number. Means if you only need to display negative numbers as zero but keep the actual data unchanged, you can use the below steps to apply it. Select a cell or range of cells. Press ...
This option will display negative numbers in red and enclosed in parentheses. If you want a minus sign (hyphen) instead of the parentheses, you can head to the Custom category. For details on what to do in the Custom category, read below. Method #3 – Using Custom Number Formats This me...
Sub NegativeNumberAsRed() Dim cells As range For Each cells In Selection If cells.Value < 0 Then cells.Font.Color = vbRed Else cells.Font.Color = vbBlack End If Next cells End Sub You must keep the cursor in the last line of the code (see the screenshot below) before running the...
=COUNTIF(范围,"<0") 范围:包含要统计数字的单元格区域; “<0”:表示小于0的条件。请在空白单元格中输入或复制以下公式,然后按Enter键获取结果,如下截图所示: =COUNTIF(A2:A13,"<0")相关功能: COUNTIF:COUNTIF函数是Excel中的一个统计函数,用于统计满足特定条件的单元格数量。更多...
Change to a Different Built-In Negative Number Option Create a Custom Negative Number Format Microsoft Excel displays negative numbers with a leading minus sign by default. It is good practice to make negative numbers easy to identify, and if you're not content with this default, Excel provides...
Above, you have already seen an example of such a formula that checks if a number is greater than a given number. And here's a formula that checks if a cell contains anegative number: =IF(B2<0, "Invalid", "") For negative numbers (which are less than 0), the formula returns "In...
Select theOKbutton andOKagain if you picked a custom option. You’ll then see your selected cells update to display negative numbers in a red font color and any other formatting you chose. With built-in ways to show negative numbers red in Excel, you can make sure those numbers stand out...