Excel plus or minus format (make positive red and negative blue)
Positive numbers omit plus signs and negative numbers display minus signs by default In Excel, but sometimes we need to display plus signs.
Chapters:
0:00 Excel plus or minus format
0:34 How to add plus or minus before integers or decimals in Excel?
0:57 Excel plus or minus function
1:55 How to make positive numbers red in Excel?
2:25 How to make positive numbers red and negative numbers blue in Excel?
Description:
Excel plus or minus format (make positive red and negative blue)
Note: The cc should be changed to greater than sign, the ee should be changed to less than sign in the formula.
How should we display plus for positive numbers? It can be implemented with custom number format, when customizing the format, just add a plus sign (+) before the number and a minus sign (-) before the negative number, and all selected numbers will automatically add a plus sign before the positive number and a minus sign before the negative number. In addition to identifying positive and negative numbers with plus and minus signs, we can also use different colors, such as positive numbers in red and negative numbers in blue.
One. Excel plus or minus format
(1) How to add plus or minus in Excel? (Display plus or minus sign in Excel, How to add sign in Excel before a number)
Select the cell we want to display plus or minus signs (for example, E2:E10), press Ctrl + 1 on the keyboard to open the "Format Cells" window, select the "Number" tab, select "Custom" on the left side of the window, enter +0;-0 under "Type" on the right, click "OK", and the plus and minus signs are displayed before the integers.
(2) How to add plus or minus before integers or decimals in Excel?
Select A1:A5, press Ctrl + 1 on the keyboard, click “Custom” on the left side of the “Number” tab on the "Format Cells" window, and enter +0.00;-0.00;0 on the right side of the window. All selected values are added by plus and minus signs, and round to two decimal places.
(3) Excel plus or minus function (Excel formula for plus or minus)
Use formulas to display plus or minus signs before numbers, round to two decimal places, and integers do not display decimal points and 0s.
1. Select cell B1, copy the formula =IF(INT(A1)=A1,IF(A1cc0,"+"&A1,A1,A1),IF(A1cc0,"+"&ROUND(A1,2),ROUND(A1,2))) to B1, press "Enter" on the keyboard, then find the value of A1 that meets the condition; select B1 again, move the pointer to the fill handle of cell of B1 in the lower right corner, hold down the left button and drag down, then all the values of cells the pointor pass through will be rounded to two decimal places, and only keep the whole number for the ones without the decimal point (such as 68 and 49).
2. Explanation of the formula
The formula looks complicated, but it's actually very simple, and although there are two IFs nested in the IF, the logic and conditions are simple.
The outer IF is conditioned by INT(A1)=A1, which means that if A1 is rounded up to equal A1, then IF(A1cc0,"+"&A1,A1);otherwise, IF(A1cc0,"+"&ROUND(A1,2), ROUND(A1,2)).
The condition for the first nested IF is A1cc0, which means that if A1 is greater than 0, add + before A1; otherwise A1 is returned.
The second IF condition for nesting is also A1 cc 0, which means that if A1 is greater than 0, return the plus sign (+) and A1 round to two decimal places (implemented with ROUND(A1,2)), otherwise return A1 to round to two decimal places.
Two. How to make negative numbers positive in Excel?
(1) How to make positive numbers red in Excel?
1. Select the cell with positive numbers to be marked in red (such as E2:E10), press Ctrl + 1 on the keyboard to open the "Format Cells" window, make sure the current tab is "Number", select "Custom" at the bottom of the left side of the window, enter [red]0;-0 under the "Type" on the right side of the window, click "OK", then all positive numbers in the selected cells are marked in red.
2. Note: If you have a decimal place and you want to round to two decimal places, you can change [red]0;-0 to [red]0.00;-0.00;0.
(2) How to make positive numbers red and negative numbers blue in Excel?
Select cell E2:E10 and enter [red]0;[blue]-0 to the right of “Custom” on the “Number” tab of the “Format Cells” window; Click "OK, all positive numbers are highlighted in red, and all negative numbers are highlighted in blue.
Watch video Excel plus or minus format (make positive red and negative blue) online without registration, duration hours minute second in high quality. This video was added by user Loinsure 11 March 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 157 once and liked it 0 people.