Show +/- before number and how to make negative numbers red in excel and change negative to positive

Only negative signs are displayed before numbers, and positive signs are not displayed by default in Excel. If you want to display positive signs, you need to customize the format of cell. If a negative and positive sign(±) is required before the number, there are two input methods, one is to use the shortcut key, and the other is to insert from the Excel symbol.

In Excel, positive and negative numbers are marked with different colors or both with color and parentheses, you can use custom format of cell, but the former can also use conditional format. You can also convert negative numbers to positive numbers or vice versa in Excel.

I, Excel displays plus(+) or minus(-) or positive and negative sign(±) before number

(I) A plus or minus sign is displayed before the number(greater than 0, + is displayed; less than 0, - is displayed)

1. How to add plus or minus in excel

Select B2:B8, press Ctrl + 1, open the "Format Cells" dialog box, select the "Number" tab, click "Custom" on the left side of the dialog box, and copy the format +0;-0 into the textbox under "Type", click "OK", then + will be displayed before positive numbers, and - will be displayed before negative numbers; the operation steps are shown in Figure 1:

Figure 1

2. How to display plus or minus sign in excel(Show + or - before integers or decimals)

Select cell B2, hold Shift, click B8, select all numbers to display positive or negative signs, right-click them, select "Format Cells" in the pop-up menu, and select "Custom" in the dialog box that has been opened, copy the format +0.0;-0.0;0 into the textbox under "Type", and click" OK ", then the selected numbers will display + before the positive numbers, and - before negative numbers; the operation steps, as shown in Figure 2:

Figure 2

Tip: If you use the format +0;-0, the decimals will be automatically rounded to integers. In addition, from the result of retaining decimals, it is known that integers are also retained one decimal place, and integers are usually only retained themselves. To achieve this requirement, you need to use the Round function, as shown in the following method.

3. Excel formula for plus or minus, and only decimals are rounded to two decimal places

(1) Double-click cell B2, copy the formula =IF(INT(A2)=A2,IF(A2>0,"+"&A2,A2),IF(A2>0,"+"&ROUND(A2,2),ROUND(A2,2))) to B2, press Enter to return the result +5.35; select B2, move the mouse to the cell fill handle in the lower right corner of B2, the mouse becomes a bold black plus sign, double-click the left button, the results of other numbers are returned; the operation steps are shown in Figure 3:

Figure 3

(2) Formula =IF(INT(A2)=A2,IF(A2>0,"+"&A2,A2),IF(A2>0,"+"&ROUND(A2,2),ROUND(A2,2))) explanation:

A. INT(A2) is used to convert A2 to an integer. INT(A2)=A2 is criteria of the first IF. If the value in A2 is an integer, then execute IF(A2>0,"+"&A2,A2); otherwise, execute IF(A2>0,"+"&ROUND(A2,2),ROUND(A2,2)).

B. IF(A2>0,"+"&A2,A2) is used to add a positive sign(+) before the integer. If the criteria A2>0 is true, return "+"&A2, otherwise return A2.

C. IF(A2>0,"+"&ROUND(A2,2),ROUND(A2,2)) is used to add a positive sign(+) before the decimal and round to 2 decimal places. If the criteria A2>0 is satisfied, execute "+"&ROUND(A2,2), that is, the value in A2 is rounded to 2 decimal places and add a positive sign(+); otherwise execute ROUND(A2,2), that is,  the value in A2 is rounded two decimal places.

(II) Positive and negative sign(±) before the number(How to insert plus and minus sign in excel)

1. Method 1: Use the shortcut keys Alt + 41408 to enter. Double-click cell A1, position the cursor behind the numbers, hold Alt, and enter 41408 on the small key(you need to enter in the Eng), then enter the sign ±, and then enter 0.02; the operation steps are shown in Figure 4:

Figure 4

2. Method 2: Inserted method. Position the cursor into B2, select the "Insert" tab, click "Symbols" in the upper right corner of the screen to open the "Symbol" dialog box, click the "Subset" drop-down list box, select "Latin-1Supplement" in the pop-up menu, double-click ±, the sign ± is inserted into the cell; click "Cancel" to close the "Symbol" dialog box; the operation steps, as shown in Figure 5:

Figure 5

II. How to make negative numbers red in excel(negative numbers are red, positive numbers are green)

(I) Mark different colors with Conditional formatting

1. Select B2:B8, the current tab is "Home", click "Conditional Formatting", and select "Highlight Cells Rules → Less Than" in the pop-up menu, open the "Less Than" dialog box, enter 0, click the "With" drop-down list box, select "Red Text", and click "OK", all negative numbers are marked in red. Click "Conditional Formatting" again, select "Highlight Cells Rules → Greater Than", enter 0, click the "With" drop-down list box, select "Custom Format" to open the "Format Cells" dialog box; Select the "Fill" tab, select "Green", and click "OK" twice. All positive numbers are marked in green. The operation steps are shown in Figure 6:

Figure 6

2. The advantage of this method is that the numbers are not rounded the specified number of decimal places, that is, the original value is retained after coloring.

(II) Mark different colors with format

1. Select B2:B8, press Ctrl + 1 to open the "Format Cells" dialog box, select "Custom", copy the format [Green]0.0;[Red]-0.0 to the textbox under "Type", and click " OK ", then positive numbers are marked in green and negative numbers are marked in red. The operation steps are shown in Figure 7:

Figure 7

2. It can be seen from the marked result that the integers are also rounded to one decimal place. If it is all integers, they are not rounded with this format [green]0;[red]-0.

III, Excel negative numbers are bracketed and displayed in red

1. Just use the format 0.0;[Red](-0.0)(or 0.0_);[Red](-0.0)) to achieve the purpose. Select the values to be set, press Ctrl + 1 to open the "Format Cells" dialog box. The specific operation is shown in Figure 8:

Figure 8

2. If it is all integers, use the format 0;[Red](-0)(or 0_);[Red](-0)); if it is the percentages, use the format 0%;[Red](-0%)"(or "0% _);[Red](-0%)).

IV, Excel positive and negative number conversion

(I) How to change negative numbers to positive in excel

Select C2, enter -1, press Enter, select C2 again, press Ctrl + C to copy; select B2:B8, press Ctrl + F to open the "Find and Replace" dialog box, and enter a negative sign - to the right of "Find What" , Click "Find All", press Ctrl + A to select all negative numbers, click "Close" to close the "Find and Replace" dialog box; right-click the selected cells, select "Paste Special" in the pop-up menu, open the "Paste Special" dialog box, select "Value" for "Paste", "Multiply" for "Operation", and click "OK" to turn all negative numbers into positive numbers; the operation steps are shown in Figure 9:

Figure 9

If you want to change positive numbers into negative numbers, you can also use the method above. If you want to change positive numbers into negative numbers, and all the numbers you want to change are positive, you can use the same method; if the numbers you want to change are positive and negative, because it is not convenient to select only positive numbers, you cannot use this method. Use the following formula.

(II) How to convert negative to positive in excel with formula in batches, while positive numbers remain unchanged

1. Double-click C2, copy the formula =ABS(B2) to C2, and press Enter to return to 83; turn other negative numbers into positive numbers with double-clicking cell fill handle of C2; the operation steps are shown in Figure 10:

Figure 10

2. If you need to convert positive and negative numbers into each other, you can use the formula =IF(B2>0,-B2,ABS(B2)).