Excel show plus (+) or minus (-) sign in front of percentage (or number), and make 0% appear as -

Опубликовано: 10 Ноябрь 2024
на канале: AuditExcel Advanced Excel and Financial Model Training and Consulting

54,457

00:00 Description of putting a + (plus) in front of a percentage difference e.g. +10%
00:10 Change the number format to include the + (plus) or - (minus) sign
00:42 Change the percentage format to include the + or - sign
01:12 Make a zero percentage appear as a dash

If you meant the ± sign look at • Plus Minus Symbol in Excel (±)

From https://www.auditexcel.co.za/blog/sho...
Images on the web page
When displaying results, especially variances or percentage variances, you may want to show + or – sign in MS Excel cells. Also, perhaps you want any zeros to be shown as a – instead of a 0. This can be easily achieved without altering formulas.

sign in Percentages
As shown below in Column E and G, we want to actually see the + (plus) sign in the variance columns. The – (minus) generally comes automatically unless you use brackets.

excel show plus or minus sign

In order to do this you just need to use the Custom Format feature. First thing to do is to format the cells into a percentage. You achieve this by

highlighting the % cells,
going to Format Cells ( CTRL + 1 is a shortcut),
choosing Percentage and the number of decimal places.
DO NOT PRESS OK YET
excel show plus or minus sign

Now immediately click on the Custom option. Excel will show you the code it uses to create the percentage format ( you should see 0.00%)

As shown in the image below, you can change this by typing in the Type section. To explain how it works the semi colon (;) divides the format between

positive numbers
negative numbers
zero
So by changing

0.00%

into

+0.00%;-0.00%;–

we are telling Excel that Positive numbers must have a + in front, negative numbers must have a – in front and zeros (which normally would be displayed as 0.00% will be shown as a – .

excel show plus or minus sign

Sign with Numbers
The above method works with any format in MS Excel.

If I wanted the same thing, but with a number I would firstly, format the number to the closest version of what I want, so below with thousands separators and Red if negative.

excel show plus or minus sign

When you now click on Custom, it will show you what Excel’s code is. You should see something like this (note that your thousands separator may be a comma which would alter this slightly)

##0_ ;[Red]-# ##0\

Note that the word [Red] after the ; tells Excel you want to change a negative number to red. You can play with this colour if you want (try blue).

To put a + sign in front of positive numbers, just add a + to the beginning. We like to see zeros as ‘s so we have also added ; to the end.

excel show plus or minus sign

You should end up with a code similar to this

+# ##0_ ;[Red]-# ##0\ ;-

Keep in mind that this is only playing with the format of the cell (Excel being nice to the human and showing them what they want to see). The number is still seen as a number and can be used elsewhere with no problem.