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.

Negative Number in Excel

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.

2.1-Selection of data range

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.


2-Using Custom feature to show negative numbers in Excel

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

3-Showing negative numbers

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.

4-Using Number feature to show negative number in Excel

  • Here is the final output showing negative numbers.

5-Negative numbers in Excel with Red color

3. Using Conditional Formatting

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

6-Use Conditional formatting Feature

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.

7-Inserting Conditional Criteria in the Less Than dialog box

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

8-Output of 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
    	ma_rng.Font.Color = vbBlack
	End If
Next ma_rng
End Sub

9-Pasting VBA code in the Module

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

10-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.


11-Applying SUMIF function to Add Negative Numbers

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.


12-Using ABS Function to Convert Negative Numbers to Positive

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.

13-Selection of the Paste Special feature to convert positive number to negative number in Excel

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

14-Choose Multiply operation

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

15-Converting 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.


16-Using IF function to convert positive numbers to negative numbers

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.


17-Using MAX function to convert All Negative Numbers to Zero

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.

18-Selection of the Additional settings… option

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

19-Choose Negative number format

  • 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.


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF