Excel SIGN Function Tutorial

Find out if a value is negative

Businessman reviewing spreadsheets on a laptop

Ariel Skelley / Getty Images  

The purpose of the SIGN function in Excel is to tell you whether a number in a specific cell is either negative or positive in value or whether it is equal to zero. The SIGN function is one of Excel's functions that is most valuable when it is used along with another function, such as the IF function.

Instructions in this article apply to Excel for Office 365, Excel 2019, 2016, 2013, and 2010.

Syntax for SIGN Function

The syntax for the SIGN function is:

= SIGN(Number)

Where Number is the number to be tested. This can be an actual number, but it is usually the cell reference for the number to be tested.

If the number is:

  • Positive, such as 45, the function returns a 1
  • Negative, such as -26, the function returns a -1
  • Zero, the function returns a 0 

Try Using SIGN With This Example

  1. Enter the following data into cells D1 to D3:

    45 -26, 0

    45, -26 and 0 in D1:D3 in Excel
  2. Select cell E1 in the spreadsheet, which is the location of the function.

    Cell E1 selected in Excel
  3. Select the Formulas tab of the ribbon menu.

    Formulas tab selected in Excel
  4. Choose Math & Trig from the ribbon to open the function drop-down list.

    Math & Trig in the Function Library in Excel
  5. Select SIGN in the list to bring up the SIGN function's dialog box.

    SIGN in Math & Trig list in Excel
  6. In the dialog box, select the Number line.

    SIGN Function Arguments dialog box
  7. Select cell D1 in the spreadsheet to enter that cell reference as the location for the function to check.

    D1 selected in Number field of SIGN Function Arguments dialog
  8. Select OK or Done in the dialog box. The number 1 should appear in cell E1 because the number in cell D1 is a positive number. 

    1 in cell E1
  9. Drag the Fill Handle in the bottom right corner of cell E1 down to cells E2 and E3 to copy the function to those cells.

    Dragging fill handle in column E
  10. The cells E2 and E3 should display the numbers -1 and 0 respectively because D2 contains a negative number (-26) and D3 contains a zero.

  11. When you select cell E1, the complete function =SIGN(D1) appears in the formula bar above the worksheet.