Excel is a great spreadsheet tool, but there are a few things about it that may irritate you sometimes. For example, if you add a plus sign before a number in a cell in Excel, that plus sign would just go away. The same happens when you try and add leading zeros before a number. The reason for this is that Excel considers these as redundant (which is correct). But sometimes, you may need to be able to add a plus sign before a number (especially when you’re showing changes as shown below):
Another common use-case of this is when you want the plus sign before a phone number (as phone are often written with a leading + sign) While the plus sign goes away when you add it in front of a number by default, if you need it to be there, you can use a workaround. In this tutorial, I will show you a simple way to add a plus sign before a positive number in Excel. So let’s get started!
Using a Custom Number Format
You can create a custom number format for the cell where you want to show a plus sign before the number (only the positive numbers). Doing this would not change the value in the cell. It will only change the way the cell data is displayed. So while you will see the plus sign before the number, that sign is not there as the actual content of the cell. So you can continue to use these numbers in calculations. Suppose you have a dataset as shown below and you want to add a plus sign before all the positive numbers:
Below are the steps to do this: You would notice that a plus sign has now been added to the positive numbers, while everything else remains the same.
Note that this actual content of the cell hasn’t changed. For example, the value in cell D3 is still 35. It’s just shown to you as a number that has a plus sign before it. Also, just like any other formatting, you can copy-paste this format to other cells as well. To do this, just copy the cells that have this custom format and paste only the formats in the destination cells. Some other custom formats that you may find useful:
Positive Numbers with a plus sign and negative numbers in bracket: +0;(0);0 Positive Numbers with a plus sign and negative numbers in red: +0;[Red]-0;0 Positive Numbers in green and negative numbers in red: [Green]+0;[Red]-0;0
Manually Adding the Plus Sign Before Positive Numbers
The above custom number formatting method is the way to go in most cases. It’s fool-proof and works well even if you have large data sets. In some cases, if you only have a few cells where you need to add the plus sign before a positive number, a quick way would be to simply add an apostrophe and then add the plus sign. Adding an apostrophe converts the cell content into text, so when you add the plus sign, it sticks. Also, the apostrophe itself is not visible in the cell so you can use this in reports/dashboards and even print it. So these are some ways you can use to add a plus sign before numbers in Excel. I hope you found this tutorial useful! Other Excel tutorials that you may like:
How to Copy Conditional Formatting to Another Cell in Excel How to Remove Table Formatting in Excel (Easy Guide) How to Remove Cell Formatting in Excel (from All, Blank, Specific Cells) Change Negative Number to Positive in Excel [Remove Negative Sign] How to Make Negative Numbers Show Up in Red in Excel
