Adding a negative sign to a number is so easy in Excel. You just need to type in the negative sign before the number. But when it comes to positive numbers, if you want the plus sign to be displayed and you type out the plus sign, the moment you hit the Enter key, the plus sign just disappears. If a number is signless, it is assumed to be positive and as far as Excel is concerned, there is no need for a positive sign in front of a positive number. Excel doesn’t want to state the obvious, obviously.
But what if you have a requirement to explicitly show that a number is positive by displaying a plus sign before it? Well, that’s when things turn tricky. But not anymore, when we are here to help. In this article, we explain in some super simple steps how you can format a number with positive/negative signs, how you can add a plus a sign to a phone number, and many more. Read on, to discover what we have in store for you today.
Section 1: Add Plus and Minus Signs to Positive and Negative Numbers
Let’s consider the below table where we have a column containing positive and negative numbers. There are negative signs associated with negative numbers, but no plus signs associated with positive numbers. In this section, let’s see how you can quickly add plus signs in front of positive numbers.
Step 1: Click on the first cell of the column and then hit the keys CTRL + SHIFT + Down together. This will select all the cells underneath the selected cell in the same column.
Step 2: Once the cells are all selected, right click anywhere on any of the selected cells and then click on the Format Cells option.
Step 3: On the Format Cells window, click on the Number tab first.
Then under the Category options, click on the Custom option.
As next, copy and paste the following value under the Type field.
+0;-0;0
Note: 0 (zero) represents the number in the cell. It is more like saying x, where x is for a varying value. +0 means put a plus sign before positive numbers, whereas -0 means put a negative sign before negative numbers, and finally 0 means if the number is 0, then leave the number as such without any signs. The semicolon (;) is used to delimit the conditional custom formatting.
Finally hit the OK button to proceed.
Step 4: That’s it. You now have plus signs added successfully in front of your positive numbers, enjoy!
Section 2: Add Plus Sign Before Phone Numbers
In the case below, we have a list of phone numbers in one column. We need to add the plus sign before all of the phone numbers. Since there can’t be any negative phone numbers, there is no need to handle the negative part here. Let’s see how this can be done, fast and easy.
Step 1: As usual, click on the first cell of the phone numbers column and then press the CTRL + SHIFT + Down keys at the same time to select all the cells under it.
Step 2: As next, press the keys CTRL + 1 (one) to launch the Format Cells window. Under the Category options, click on the Custom option and then on the right side of the window, under the Type field, simply copy and paste or type in the following value.
+0
Note: The above conditional formatting instructs Excel to add a plus (+) sign in front of the value it already has.
Hit the OK button once you are done.
Step 3: That’s it. Plus signs are added before all the phone numbers that you selected.
Section 3: Add Plus Sign and Country Code Before Phone Numbers
Now let’s consider the case where you have to add the country code as well, along with the plus sign.
Step 1: Firstly, select all the cells where you want to add the plus sign and the country code to.
Step 2: Press the keys CTRL + 1 (one) to open the Format Cells window.
Click on the Number tab first.
Then click on the Custom option from the options available under Category.
On the right side of the window, type in your custom formatting under the Type field. I need +91 to be added to all the selected cells and also a hyphen to separate the country codes from the actual mobile numbers. So my conditional formatting would look like the following.
+91-0
Note: 0(zero) represents the value present in the cell already.
Hit the OK button once done.
Step 3: That’s it. The whole column is now formatted with country codes and plus signs. Enjoy!
Please tell us in the comments section if you have any concerns regarding any of the steps. Also, don’t forget to share the article with your friends if you found it useful.
Stay tuned for more tricks, tips, and how-to articles.