Identify positive and negative numbers in excel

A common requirement in Excel is to identify positive and negative numbers. This is usually to apply a different calculation to it e.g. a positive (credit) bank balance earns say 5% but a negative bank balance (debit or overdraft) incurs an interest charge of 10%.

YouTube Identify positive and negative numbers

Using IF to find positive and negative numbers

Although there are a number of ways to do it with special functions, the easiest way is to use an IF function. The question is how do we tell Excel to look for a positive or negative number.  The logical operator generally needs to use a bigger than, equals to or less than.

A positive number is defined as any number bigger than zero. So in Excel terms a positive number is >0. As shown below the logical test for the bank balance in cell B7 would be


If this is TRUE, then we know it is a positive number. If it is not TRUE it must be a negative number (or a zero but this won’t have any impact in this case).

Identify positive and negative numbers

If you prefer to look for a negative number then the logical test of the IF statement would change. In this case the test would be:


If this test is TRUE then we have a negative number. If it is FALSE we have a positive number.

To learn more about the IF function, have a look at our free Excel Fundamentals Course.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Excel negative numbers

Match positive and negative numbers in Excel

Excel formula to change negative to positive