When you start entering text into a cell and the first character of that cell is a plus symbol (
+
) you will get an error
#NAME?
and you would have noticed the cell changed to
=+A
. So how can you just display a cell with a plus sign?
| A | |
|---|---|
| 1 |
#NAME?
=+A
|
+
To display a cell that starts with a plus sign (
+
) enter the symbol that lets the spreadsheet know you’re entering text by starting with the single apostrophe (
'
) then entering your plus symbol and whatever ever text next. For example, your cell would now contain
'+A
.
| A | |
|---|---|
| 1 |
+A
'+A
|
'
All other symbols when entered will not have the same effect as those when starting with the plus symbol.
Display Positive Numbers With Plus Sign
Prefixing a cell with the plus symbol is great when the remaining contents of the cell are text, but what if the cell is numeric and you want to show the number as positive by displaying the plus sign?
To display a number with a positive sign, which is the same as a plus sign, right-click on the cell and select
Format cells
then on the
Number
tab in the
Category
section select
Custom
and enter the following code into the
Type
field:
+#,##0;(#,##0)The result of this format code would look like this on your numeric spreadsheet cells:
| A | B | |
|---|---|---|
| 1 |
+2,345
2345
|
(9,876)
-9876
|
What this code format will do to your numeric cells is place a positive sign in front of numbers greater than 0, and will wrap negative numbers in parentheses
(1,234)
. If you need to display decimals with your numbers then you can add the appropriate number of decimals like so (here I’m just using 2 decimal places):
+#,##0.00;(#,##0.00)| A | B | |
|---|---|---|
| 1 |
+2,345.12
2345.12
|
(9,876.54)
-9876.54
|
Or if you are living in countries where the decimal point is denominated as a comma, rather than a dot then you’d be looking to format your cell to this (again 2 decimal places implied):
+#.##0,00;(#.##0,00)| A | B | |
|---|---|---|
| 1 |
+2.345,12
2345.12
|
(9.876,54)
-9876.54
|
Finally, if you prefer to use a negative sign (
-
) to display negative numbers instead of having the negative numbers wrapped in parentheses then you can modify the format code to this:
+#,##0.00;-#,##0.00| A | B | |
|---|---|---|
| 1 |
+2,345.12
2354.12
|
-9,876.54
-9876.54
|
Typing + In Cell: Summary
If you’re starting a cell with the plus sign
+
and want to display the plus sign in the cell, and the cell is just text, then prefix your data entry in that cell with an apostrophe. This lets Excel know to render the cell as text rather than to try and translate it as a number.
If you want to display a numeric cell with a plus sign at the front of the number modify the format of the number by entering a custom type, something like so:
+#,##0;-#,##0
.
To see other creative ways of modifying the format of numbers in Excel check out our other article on displaying numbers with abbreviations such as thousands (k), millions (m), billions (b) and trillions (t) .
