Written by Allen Wyatt (last updated December 26, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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 a few 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 (9601) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel 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!
People often use Excel to maintain lists of information that they need to track. This tip shows several ways you can ...
Discover MoreFor some operations and functions, Excel allows you to use wild card characters. One such character is an asterisk. What ...
Discover MoreSometimes it is helpful to look at data that is rotated 90 degrees. Excel allows you to use Paste Special to transpose ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2024-01-09 16:10:28
J. Woolley
The Tip wants to extract all positive numbers from A2:A158 into column B and all negative numbers from A2:A158 into column C, with headings in row 1.
If you have Excel 2019 or newer with support for dynamic arrays, put this formula in cell B2
=FILTER(A2:A158,A2:A158>0)
and this formula in cell C2
=FILTER(A2:A159,A2:A159<0)
If you have an older version of Excel, you are missing out on a lot of capability.
2024-01-09 03:07:10
Thomas Papavasiliou
As a preliminary step. fill a column with a series of numbers (key column).
Sort the column to segregate.
Move all the negative cells to the first to the right, empty column.
Sort to the key column.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu 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