Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Segregating Numbers According to Their Sign.
by Allen Wyatt
(last updated May 13, 2019)
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:
This results in column B only containing values that are greater than zero. In column C you could then use this formula:
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
At the heart of working with Excel is the process of creating formulas that calculate results based on information within ...Discover More
Two rather common trigonometric functions are secants and cosecants. Excel doesn't provide functions to calculate these, ...Discover More
When you construct a formula and click on a cell in a different workbook, an absolute reference to that cell is placed in ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.