How to Separate Negative and Positive Values From a Column in Excel?
Tags: excelpreface, excelbasics, exceltutorials, separatenegativeandp
Published: 2021-02-10 11:46:01

---
title: How to Separate Negative and Positive Values From a Column in Excel?
published: true
date: 2021-01-30 09:16:27 UTC
tags: ExcelPreface,ExcelBasics,ExcelTutorials,Separatenegativeandp
canonical_url: https://geekexcel.com/how-to-separate-negative-and-positive-values-excel/
---

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](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:**

**=[IF](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/)($A1>=0,[$A1](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/),””)**

**Extract all Negative Numbers:**

**=[IF](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/)($A1<0,$A1,””)**

### Functions Explanation:

- [**IF**](https://geekexcel.com/use-if-function-in-microsoft-excel-365-simple-steps/) – This function will help to return one value for a [TRUE](https://geekexcel.com/use-true-function-in-microsoft-excel-365-simple-methods/) result, and another for a [FALSE](https://geekexcel.com/use-false-function-in-microsoft-excel-365-simple-methods/) result.
- [**Absolute Reference ($)**](https://geekexcel.com/use-absolute-reference-in-excel-office-365-with-examples/) – 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](https://geekexcel.com/excel-office-365-columns/) B**.

![Input data](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Input-data-2.png)<figcaption id="caption-attachment-21536">Input data</figcaption>

- Then, enter the 1st formula given above to the [**formula**   **bar**](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/) section.

![Enter a formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-a-formula-15.png)<figcaption id="caption-attachment-21537">Enter a formula</figcaption>

- Press the **ENTER** key and **drag** the Autofill Handle down to other cells.
- It will extract all the positive numbers from the column.

![Drag the cell](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Drag-the-cell-1.png)<figcaption id="caption-attachment-21538">Drag the cell</figcaption>

- 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.

![Enter the formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Enter-the-formula-29.png)<figcaption id="caption-attachment-21539">Enter the formula</figcaption>

- Press the **ENTER** key and then drag the Autofill Handle down to other cells.

![Drag the cell](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Drag-the-cell.png)<figcaption id="caption-attachment-21540">Drag the cell</figcaption>

- Finally, we will get the result as per the below image.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/Result-2-4.png)<figcaption id="caption-attachment-21541">Result</figcaption>

## 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](https://geekexcel.com/)**!!

### Related Articles:

- **[Split Multiple Lines From a Cell into Separate Rows in Excel!!](https://geekexcel.com/split-multiple-lines-from-a-cell-into-separate-rows-in-excel/)**
- **[Excel Formulas to Find Multiple Matching Values in Comma-Separated List](https://geekexcel.com/excel-formulas-return-multiple-matching-values-comma-separated-list/)**
- **[Formulas to Count Cells that contain Positive Numbers in Excel!!](https://geekexcel.com/formulas-to-count-cells-that-contain-positive-numbers-in-excel/)**
- **[Formulas for Counting Cells that Contain Negative Values in Excel!!](https://geekexcel.com/formulas-for-counting-cells-that-contain-negative-values-in-excel/)**