Written by Allen Wyatt (last updated January 14, 2023)
This tip applies to Excel 97, 2000, 2002, and 2003
Are you working with a large set of data consisting of mixed values, some negative and some positive, that you want to separate into columns based on their sign? There are several ways this can be approached. One method is simply to use a formula in the columns to the right of the mixed column. For instance, if the mixed column is in column A, then you could place the following formula in the cells of column B:
=IF(A2>0,A2,0)
This results in column B only containing values that are greater than zero. In column C you could then use this formula:
=IF(A2<0,A2,0)
This column would only contain values less than zero. The result is two new columns (B and C) that are the same length as the original column. Column B is essentially the same as column A, except that negative values are replaced by zero, while column C replaces positive values with zero.
If you want to end up with columns that only contain negative or positive values (no zeroes), then you can use the filtering capabilities of Excel. Assuming the mixed values are in column A, follow these steps:
Figure 1. The Advanced Filter dialog box.
You now have the desired two columns of positive and negative values. You can also delete the cells at E1:E2 if you desire.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3198) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Segregating Numbers According to Their Sign.
Best-Selling VBA Tutorial for Beginners Take your Excel knowledge to the next level. With a little background in VBA programming, you can go well beyond basic spreadsheets and functions. Use macros to reduce errors, save time, and integrate with other Microsoft applications. Fully updated for the latest version of Office 365. Check out Microsoft 365 Excel VBA Programming For Dummies today!
If you need to generate a random sequence of characters, of a fixed length, then you'll appreciate the discussion in this ...
Discover MoreTwo rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, ...
Discover MoreCalculating an average of a group of numbers is easy. What if you want to exclude a couple of the numbers from the group ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments