How to separate positive and negative numbers into two columns in Excel?
There a list of multiple values including positive and negative values in a worksheet, and you need to separate them into two columns, one contains positive values only, the other has negative values only. Now this tutorial is talking about the methods on separate the positive and negative numbers into two columns in Excel.
Separate positive numbers from the list first.
Select a blank cell and type this formula =IF($A1>=0,$A1,"") (A1 is the cell in your list), press Enter button and drag fill handle to fill range you want, you can see only positive values are separated into the column.
Then type this formula =IF($A1<0,$A1,"") into another column cell, and press Enter button, and drag fill handle to fill range you want. Now you can see the positive and negative values are separated into two columns.
If you have Kutools for Excel installed in your Excel, you can use its Select Specific Cells feature to finish the separating.
1. Select the data list and click Kutools > Select Tools > Select Specific Cells. See screenshot:
2. In the Select Specific Cells dialog box, check Cell in the Selection type section, and select Greater than or equal to from the drop-down list below the Specify type, and type 0 into the box at the right of the drop-down list. See screenshot:
3. Click Ok, and only the positive values and 0 are selected, then you can copy them to one column.
4. Apply the Select Specific Cells feature again, and select Less than in the drop-down list below the Specify type, and type 0 into the text box. See screenshot:
5. Click Ok, and only the negative values are selected, then you can copy them to another column.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!