SIMPLE way to format negative and positive signs in Excel

Formatting negative and positive signs in Excel is a feature we sometimes need. Especially when you want to show the metrics you want to change. But Excel thinks it's unnecessary and removes them. However, if you need TipsMake will guide you on how to format and display negative and positive signs, convert negative numbers to positive, etc. easily.

SIMPLE way to format negative and positive signs in Excel Picture 1

Excel is a great spreadsheet, but you may experience negative and positive signs disappearing. Specifically here is the plus sign. When you enter any number with a plus sign in front, it will automatically disappear, similar to adding a leading zero before a number.

What is the yin and yang sign format in Excel?

Simply put, the positive and negative sign format in Excel is a feature that allows you to customize the way negative and positive numbers are displayed in the spreadsheet.

That is, through the application of number format codes, you can decide the position, color and symbol of the negative and positive signs.

SIMPLE way to format negative and positive signs in Excel Picture 3

Displaying positive and negative signs in Excel is extremely convenient. Helps you customize your spreadsheet information so that it is easy to distinguish and identify. Many people use the yin and yang sign format to create visual and aesthetic effects for spreadsheets.

For example, another common use case is that you need to display a + (positive) sign for a phone number.

In this article, I will show you a simple way to display yin and yang signs in Excel.

How to format negative and positive signs in Excel

Normally, there are many ways to display the - sign in Excel. You can simply enter it directly. The difficulty here is usually that you want both negative (-) and positive (+) signs to appear.

Suppose you have a data file like the example in the picture and you want to add a plus sign to all positive numbers in column D.

SIMPLE way to format negative and positive signs in Excel Picture 5

We perform the following steps to reformat:

Step 1 : Select the cell you want to format with positive and negative signs.

Step 2 : Right-click and select Format Cells .

SIMPLE way to format negative and positive signs in Excel Picture 7

Step 3 : In the Format Cells dialog box,  select the Number tab and click Custom .

SIMPLE way to format negative and positive signs in Excel Picture 9

In the Type box, enter the following information: +0;-0;0

SIMPLE way to format negative and positive signs in Excel Picture 11

Step 4 : Click Ok and the result table with yin and yang signs appears.

SIMPLE way to format negative and positive signs in Excel Picture 13

Note, its value remains unchanged, just the way it is displayed is as a number with plus and minus signs before it.

Another interesting thing is that you can customize the yin and yang sign format in other Excel such as:

1. Positive numbers have a plus sign and negative numbers in brackets: +0;(0);0
2. Positive numbers have a plus sign and negative numbers are red: +0;[Red]-0;0
3. Positive numbers are green and negative numbers are red: [Green]+0;[Red]-0;0

I will further guide you on converting negative numbers into positive numbers using the ABS function. It will be useful for you in some other cases when you do not need to format yin and yang signs in Excel.

Convert negative numbers to positive numbers in Excel with ABS

In some cases you need to convert negative numbers to positive numbers in Excel.

From the yin and yang sign format above, you can use 2 common ways to convert as follows:

Method 1 : Multiply by -1 to create a positive number

Apply the formula: =IF(A2>0,A2,-A2) to the cell you want to convert to a positive value.

Where A2 is the original value, -A2 is the value that has a negative sign (- with – becomes +).

SIMPLE way to format negative and positive signs in Excel Picture 15

For more flexibility, you can use the ABS function.

Method 2: Using the ABS function :

Similar to above, use the formula: =ABS(A2) for the cell you want to convert to a positive value.

SIMPLE way to format negative and positive signs in Excel Picture 17

The ABS function is currently only applicable to negative numbers converted to positive numbers. Only applicable to numeric data, text data will report the #VALUE! error.

So I have helped you better understand how to customize the yin and yang sign format in Excel. Hope you like this article, please comment if you have any questions or need further answers!

Lesley Montoya

Update 08 December 2023