Assume that there is a list of multiple values including positive and negative values in a worksheet, and you need to separate them into two columns. Are there any effective ways for solving this problem? Here are some cute tricks for you to separate negative and positive values from a range in Excel. Let’s jump into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Steps to separate the positive and negative value from a column:
- To split the column based on the positive and negative values you may need to use the two formulas. See below:
Extract all Positive Numbers:
Extract all Negative Numbers:
=IF($A1<0,$A1,””)
Functions Explanation:
- IF – This function will help to return one value for a TRUE result, and another for a FALSE result.
- Absolute Reference ($) – It is an actual fixed location in a worksheet.
Practical Example:
Refer to the below example, here we will show how to extract the positive and negative numbers from a range.
- First, we will enter the input data in Column B.
- Then, enter the 1st formula given above to the formula bar section.
- Press the ENTER key and drag the Autofill Handle down to other cells.
- It will extract all the positive numbers from the column.
- Next, we need to get the negative values in another column.
- So, we will enter the 2nd formula given above to the blank cell where we need to display the result.
- Press the ENTER key and then drag the Autofill Handle down to other cells.
- Finally, we will get the result as per the below image.
Closure:
Hope you understood how to separate the negative and positive values from a range in Excel. If you have any issue regarding this article or any unresolved query , please comment in the comment box below. We will assist you. Click here to know more about Geek Excel!!
Top comments (0)