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.
Table of contents
YouTube Show + or – sign in MS Excel
Watch, like, subscribe
+ 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.
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
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 – .
+ 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.
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.
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.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.