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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you enter a formula in a cell, you expect Excel to display the result of that formula once you are completed. If ...
Discover MoreDiscovering different ways to analyze your data can be a challenge. Here's how to work with arbitrary subsets of a large ...
Discover MoreIt is not unusual to need to select two random items from a list. There are a couple of ways you can approach the task; ...
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