How to Put Plus Sign in Excel: Complete Steps

Adding a plus sign (+) in Excel might seem simple, but depending on the context, it requires different methods. Whether you need to display a plus sign before positive numbers, insert a plus sign as text, or use it in formulas, Excel offers various ways to achieve this.

This guide provides a complete step-by-step process on how to put plus sign in Excel naturally and effectively.

1. Adding a Plus Sign Before Positive Numbers

By default, Excel does not show a plus sign before positive numbers. You can enable this manually using custom number formatting.

Steps to Show Plus Sign in Numbers:

1. Select the cells where you want to display numbers with a plus sign.
2. Right-click and choose Format Cells.
3. In the Number tab, select Custom from the category list.
4. In the Type field, enter the following format:
   • +0 ensures a plus sign is shown for positive numbers.
   • -0 keeps the negative numbers with a minus sign.
   • 0 ensures zero is displayed normally.
5. Click OK.

Now, all positive numbers will display with a plus sign.

2. Manually Typing a Plus Sign in a Cell

If you try to enter a plus sign (+) followed by a number in Excel (e.g., +10), Excel treats it as a formula. To input it as plain text:

Steps:

1. Start with a single quotation mark (‘) before the plus sign.
2. Type the number normally.
3. Press Enter.

For example, enter ‘+10 in a cell, and Excel will display +10 as text.

3. Adding Plus Sign Using a Formula

If you want Excel to automatically add a plus sign to positive numbers while keeping negative numbers unchanged, use the following formula:

Formula:

=IF(A1>=0, “+” & A1, A1)

Explanation:

• If the value in cell A1 is positive or zero, it adds a plus sign before the number.
• If it’s negative, it remains unchanged.

Steps:

1. Enter numbers in column A.
2. In column B, use the formula =IF(A1>=0, “+” & A1, A1).
3. Drag the fill handle to apply the formula to other rows.

Now, all positive numbers in column B will have a plus sign.

4. Using Conditional Formatting to Show Plus Signs

You can also use Conditional Formatting to visually display a plus sign without changing the actual values.

Steps:

1. Select the cells containing numbers.
2. Go to the Home tab and click on Conditional Formatting.
3. Select New Rule.
4. Choose Use a formula to determine which cells to format.
5. Enter this formula:
6. Click on Format, go to the Number tab, and apply the Custom Format:
7. Click OK.

This will visually display plus signs for positive numbers without modifying the actual values.

5. Using VBA to Automatically Insert Plus Signs

If you frequently need to insert plus signs in Excel, a VBA macro can automate the process.

VBA Code:

Sub AddPlusSign()

    Dim cell As Range

    For Each cell In Selection

        If IsNumeric(cell.Value) Then

            cell.Value = “‘” & “+” & cell.Value

        End If

    Next cell

End Sub

How to Use:

1. Press Alt + F11 to open the VBA editor.
2. Click Insert > Module.
3. Copy and paste the above code.
4. Close the editor and go back to Excel.
5. Select the cells and run the macro from Developer > Macros > Run AddPlusSign.

This script will automatically add a plus sign to all positive numbers in the selected range.

Conclusion

Knowing how to put plus sign in Excel is essential for formatting financial reports, displaying positive numbers clearly, or improving data presentation. Depending on your needs, you can:

• Use Custom Formatting to show plus signs.
• Manually enter a plus sign using single quotes.
• Use formulas to dynamically add plus signs.
• Apply Conditional Formatting for visual effects.
• Automate the process using VBA.

With these methods, you can efficiently format numbers in Excel while ensuring clarity and professionalism.