Negative Number in Excel (Add, Format, and Convert)

In this article, we will learn how to add a negative number in Excel. We will also learn how to format a negative number and even convert negative numbers to positive numbers.

Any number which is less than 0 (zero) is a negative number. Usually, this type of number refers to decreasing or lowering in quantity.

So, we use negative numbers to show the loss in business or a decrease in temperature, etc. Negative numbers have a huge usage in practical life.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Why Do We Use Negative Numbers in Excel?

Negative numbers are used in Excel for several purposes:

• Presenting losses or expenses: Negative numbers are commonly used to indicate financial losses, expenses, or deductions. They allow for accurate tracking and calculation of negative values, such as costs, liabilities, or reductions in value.
• Expressing opposite values: Negative numbers represent the opposite of positive numbers, indicating a decrease or opposite change.
• Analyzing deviations: Negative numbers analyze variations from expected values, aiding performance analysis and decision-making.
• Representing budget reports: In budget reports, negative values highlight negative budgets and areas of overspending.
• Cash flow management: Negative numbers are crucial for managing cash flow in Excel. They represent cash outflows, expenses, or debts, enabling accurate tracking and forecasting of financial inflows and outflows.
• Mathematical operations: Negative numbers are essential for math operations, allowing subtraction and expression of decrease or reduction.

How to Show Negative Numbers in Excel

Here we will show negative numbers by using the Excel features and formula.

1. Using Custom Feature

Select data range >> Press Ctrl + 1.

A Format Cells pops up >> Choose Number tab >> Select a Custom feature from the Category drop-down list.

Hence, select the below format from the Type drop-down list >> Hit OK.

• Finally, you will be able to show negative numbers with the Red color.

2. Applying an Inbuilt Number or Currency Formatting

Now, we will apply an inbuilt Number or Currency formatting to show negative numbers in Excel.

• Select your data range >> Go to the Home tab >> Choose the Number Format down arrow from the Number group.
• A Format Cells window will appear >> Choose Number tab >> Select a Number feature from the Category drop-down list.
• Choose a negative number >> press OK.

• Here is the final output showing negative numbers.

3. Using Conditional Formatting

Select the data range >> Go to the Home tab>> Styles group >> Conditional Formatting >> Highlight Cells Rules >> Less Than.

A Less Than dialog box will appear >> Insert 0 with the Light Red Fill with Dark Red Text in the Format cells that are LESS THAN box >> Hit OK.

As a result, you will be able to show the negative numbers using the Conditional Formatting feature.

4. Run an Excel VBA Code

Now we’ll apply the VBA code to show the negative numbers in Excel.

Press Alt + F11 to pop up the Microsoft Visual Basic Applications window. Hence, select Module from the Insert tab to open a Module for inserting the VBA code.

• Paste the below VBA code in that Module.

Sub Show_Negative_Numbers()
Dim ma_rng As Range
For Each ma_rng In Selection
	If ma_rng.Value < 0 Then
    	ma_rng.Font.Color = vbRed
	Else
    	ma_rng.Font.Color = vbBlack
	End If
Next ma_rng
End Sub

• After running the Macro, you will see the negative numbers in the Red color font.

Read More: Excel Formula to Return Zero If Negative Value is Found

How to Add Negative Numbers Using SUMIF Function in Excel

Insert the following SUMIF function in cell C11 to add negative numbers in Excel.

How to Convert Negative Numbers to Positive Using ABS Function in Excel

Type the ABS function in cell F5 to convert the negative values to positive and AutoFill the formula.

How to Convert Positive Numbers to Negative in Excel

Now, we will use the IF function, and Paste Special features to change positive numbers to negative numbers.

1. Applying Paste Special Feature

Type -1 in any cell, and press Ctrl + C. Hence, select cells E5 to E13. Now, go to the Home tab >> Choose the Paste Special feature from the Clipboard group.

• After that, select the Multiply option under the Operation group >> Press OK.

• Finally, you will be able to convert positive numbers to negative numbers.

2. Using IF Function

You can also convert positive numbers to negative numbers using the IF function. In cell F5, write down the following formula and Autofill it.

How to Make All Negative Numbers Zero in Excel

It may be required to ignore negative values or portray them as no result in a dataset. So, if the value is negative, our formula will return zero. The MAX function helps us to do this job.

Enter the following formula in cell F5.

What to Do When Negative Numbers Aren’t Showing with Parentheses in Excel

It may occur to some users that the negative numbers cannot be shown with parentheses in Excel. The reason for that is the ($1,234.10) option isn’t available. To solve this problem, follow the steps below.

• Open the Control Panel >> Choose the Change date, time, or number formats.
• After that, the Region window will pop up, select Additional settings… from this window.

• From the Customize Format window, select the format (1.1) for the Negative number format >> Click OK.

• Close the Excel file and reopen it.

Hopefully, this will solve your problem of negative numbers not showing in the Excel file.

Things to Remember

• When using the Paste Special method, make sure to copy (-1) and select the range after that. Otherwise, you won’t get the desired result.
• If you have used the VBA code, make sure to save it in the Enabled Macro book. Otherwise, the code won’t run.

Frequently Asked Questions

1. Why is Excel unable to sum the negative numbers?

Answer: Excel is unable to sum negative numbers by default because the SUM function treats negative numbers as subtracting from the total rather than adding to it. This is designed to align with traditional mathematical conventions where negative numbers represent deductions or debts. However, you can still calculate the sum of negative numbers in Excel by using alternative formulas or adjusting the settings.

2. How can I perform calculations with negative numbers in Excel?

Answer: Excel treats negative numbers like any other numbers when performing calculations. You can use mathematical operators such as addition (+), subtraction (-), multiplication (*), and division (/) with negative numbers just as you would with positive numbers.

3. Can negative numbers be used in conditional formatting?

Answer: Yes, negative numbers can be used in conditional formatting to apply specific formatting styles based on their values. This allows you to highlight or emphasize negative numbers in your Excel worksheets.

Conclusion

To present negative numbers differently, we used the Number Format feature, Custom Format feature, Conditional Formatting, VBA, etc. If you have any questions or feedback regarding this article, please share them in the comment section.

Negative Number in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel