Negative Numbers in Excel

Excel Negative Numbers

Microsoft Excel uses positive and negative numbers in performing mathematical calculations. In Excel, negative numbers are displayed with minus symbols by default. These are the numbers that are lower than 0 in value. Excel makes it easy to recognize the negative numbers by facilitating various best practices.

It needs to recognize the negative numbers while performing the calculations. In some scenarios, there is a need for converting the negative numbers to positive numbers or representing them with appropriate format to use the negative values appropriately, such as determination of variance. It is required to represent them by applying a specific conditional format based on the requirement.

The present article is aimed at the identification of ways to manage the negative numbers in Excel.

Explanation of Negative Numbers in Excel

Handling the negative numbers is very easy in Excel. Excel has the ability to handle both positive and negative numbers effectively. But, negative numbers can generate some confusion to the users in sometimes. There is a chance for the occurrence of issues while handling negative numbers when the inappropriate format is chosen for them. This results in displaying the errors when a user tries to perform calculations on the negatives.

Entering the negative numbers with a minus sign is very easy but analyzing them is tough when a mixture of positive and negative values is presented. To avoid this problem, it needs to change the format of the excel cells that contained negative values to get accurate results.

Negative-Numbers-in-Excel

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Negative Numbers in Excel (wallstreetmojo.com)

How to Show Negative Numbers in Excel?

Negative numbers have a lot of significance in handling the various applications in Excel. Applying the set of formats is important in various areas.

Top 3 Ways to Show/Highlight Negative Numbers in Excel

This section is going to describe the various ways to show/highlight negative numbers in Excel.

You can download this Negative Numbers Excel Template here – Negative Numbers Excel Template

Example#1 – With Conditional Formatting

Representing negative numbers with conditional formatting

This example is to illustrate the excel conditional formatting to highlight the negative numbers in various steps. For this example, we need to use numbers that are lower than 0.

The following data is considered to illustrate this example.

Excel Data - Example 1.1

Step 1: Select the entire range of data as shown in the figure

Select Data

Go to Home and select the conditional formatting. Select the ‘highlight cells rules’ and choose ‘less than’ under the options presented.

Conditional Formatting Option

It opens the ‘less than’ dialog box, as shown in the figure.

Negative Numbers - Less Than Window

Step 2: Enter the value in the empty box presented under ‘Format cells that are LESS THAN’ and Click on the dropdown right to ‘with’ to apply the desired formatting to the numbers. Ensure that ‘Light Red Fill with Dark Red Text’ is highlighted. Then click on ‘OK.’

Negative Numbers - Less Than Options

The output is shown as,

Negative Numbers Conditional Formatting Output

From the above figure, it is identified that the cells with values less than 0 are highlighted with a red fill and red color to text. This type of conditional formatting is also applied with greater than, between, equal to, and duplicate values with negative values.

Example#2 – With Inbuilt Number Formatting

Representing negative numbers with inbuilt number formatting

This example is to illustrate the excel inbuilt number formatting to highlight the negative numbers in red color. For this example, we need to use numbers that are lower than 0. The following are the steps required

The following data is considered to illustrate this example.

Excel Data - Example 2.1

Step 1: Select the data that you want to apply the inbuilt custom formatting with a red color.

Select Excel Data - Example 2.2

Step 2: Go to the ‘Home’ tab and then click on ‘Number format’ and click on the small tilted icon on the right side corner.

Number format option select

It helps in opening the ‘format cells’ dialog box.

Negative Numbers - Format Cells Window

Step 3: In the Format Cells dialog box, ensure that the ‘Number’ tab is highlighted. Go to ‘Number’ under the Number tab. On the right side of the options, select the red text option with brackets for negative numbers. And click on ‘OK.’

Negative Numbers - Select Negative Values

Step 4: The formatted excel negative numbers will look as follows,

Negative Numbers - Negative Values Result

Two decimal points are automatically added to numbers when the formatting is applied.

Example#3 – With Custom Number Formatting

Representing negative numbers with custom number formatting

This example is to illustrate the custom number formattingCustom Number FormattingIn Excel, Custom Number Format refers to changing the format of the numeric values as per your preferences to make the data look more visually appealing. This includes formatting large numbers in thousands or millions, formatting dates to only display the month names etc. read more to highlight the negative numbers in excel. For this example, we need to use the numbers which are lower than 0.

The following data is considered to illustrate this example.

Excel Data - Example 3.1

Step 1: Select the data that you want to apply the inbuilt custom formatting with a red color.

Select Excel Data 3.2

Step 2: Go to the ‘Home’ tab and then click on ‘Number format’ and click on the small tilted icon on the right side corner.

Negative Numbers - Number format (3)

It helps in opening the ‘format cells’ dialog box.

Negative Numbers - Format Cells

Step 3: In the format cells, the dialog box ensures that the ‘Number’ tab is highlighted. Go to the ‘custom option’ under the Number tab. Select the appropriate format, as shown in the figure. Click on Ok to apply the formatting.

Negative Numbers - Custom Number

Step 4: The negative numbers will look as follows in excel.

Negative Numbers - Custom Number Result

Things to Remember

  • Conditional formatting is volatile in nature. Whenever there is a change in values in the Excel sheet, it reassesses the condition and changes the formatting on the dataset. It will become a problem for large datasets.
  • We can apply any kind of color to the background and text in excel cells to highlight the negative numbers.

This has been a guide to Negative Numbers in Excel. Here we discuss how to show/highlight the negative number in excel with the help of examples. You can learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>