This article will show how to separate odd and even numbers in Excel. In addition to that, we will also see how to filter odd and even numbers and see them by different colors using the conditional formatting of Excel. You are cordially invited to visit our blog for more Excel-related articles.

**Table of Contents**hide

## How to Separate Odd and Even Numbers in Excel: 6 Handy Ways

Assume that we have some numbers in cell **B5:B21**. Now we will see how to separate the odd and even numbers from them.

### 1. Combine FILTER and MOD Functions to Separate Odd and Even Numbers

If you are an Excel 365 user (the 2019 or 2021 version is also compatible), then **the FILTER function** is the best solution. We will incorporate **the MOD function** (available in Excel from 2010) within this formula.

**Steps:**

- First off, go to
**cell C5**and write the following formula.

`=FILTER($B$5:$B$21,MOD($B$5:$B$21,2)=1)`

- Hit the
**Enter**button and you will get all the odd numbers!

**Formula Explanation:**

**MOD($B$5:$B$21,2)**

The **MOD **function returns the remainder after the numbers in **B5:B21** are divided by 2.**Output:** {0;1;1;0;1;1;1;1;0;0;0;1;1;0;1;0;1}

**MOD($B$5:$B$21,2)=1**

This checks whether the condition set is **TRUE**. If it’s not **TRUE**, it returns **FALSE**.**Output:** {FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE}

**FILTER($B$5:$B$21,MOD($B$5:$B$21,2)=1)**

The **FILTER **function returns the value from **B5:B21** when it finds the condition **TRUE**.**Output:** {91;21;29;89;93;21;79;49;9;51}

- For even numbers, the formula will be

`=FILTER($B$5:$B$21,MOD($B$5:$B$21,2)=0)`

### 2. Use an INDEX Formula If FILTER Function Is Not Available

If you are using MS Excel’s earlier versions, (Excel 2016, Excel 2013, Excel 2010, etc.), then you have to use another formula to separate odd and even numbers. This formula is composed of **INDEX**, **SMALL**, **IF**, **ISEVEN**/**ISODD**, **COLUMN**, and **COLUMNS **functions. We can also use **the IFERROR function** to handle errors.

**Steps:**

- Go to
**cell C5**and type the following formula to get the odd numbers.

`=IFERROR(INDEX($B$5:$B$21,SMALL(IF(ISODD($B$5:$B$21+0),ROW($B$5:$B$21)),ROWS($C$5:C5))-ROW($B$4)),"")`

- Now, drag the
**F****ill Handle**icon to copy the formula down and stop when you start to get empty cells.

**Formula Explanation:**

The formula is designed in the following way.

The **ISODD **function will return **TRUE **for odd numbers (or **FALSE **for even numbers) to the **IF **function. The **IF **function will then return the row number of **B5:B21** at which its set condition is **TRUE**. The **SMALL **function will find the smallest number in this array and the **INDEX **function will return numbers accordingly.

- The formula for even numbers is-

`=IFERROR(INDEX($B$5:$B$21,SMALL(IF(ISEVEN($B$5:$B$21+0),ROW($B$5:$B$21)),ROWS($C$5:C5))-ROW($B$4)),"")`

**Read More: ****How to Fill Odd Numbers in Excel**

### 3. Separate Odd and Even Numbers in Excel by Sorting

You can also separate by using the sorting option of Excel. We will first add a helper column to do this.

**Steps:**

- Add a helper column at
**column C**and write the following formula in**cell C5**.

`=MOD(B5,2)`

- Drag the
**F****ill Handle**down.

- Now, select
**cell B5**to**C21**and go to the**Data**tab ⇒**Sort & Filter**⇒**Sort**command.

- Now, select
**Sort by**:**Helper Column**and press**OK**.

- Have a look at the following image. The even numbers are sorted at the top and the odd numbers are at the bottom.

- Now, you can delete the helper column.

**Read More: ****How to Sort Odd and Even Numbers in Excel **

### 4. Use IF Formula to Mark Even and Odd Numbers Separately

If you don’t want to physically separate odd and even numbers, rather just place a comment about whether a number is even or odd, you can use this formula.

Just go to **cell C5 **and place the following formula.

`=IF(ISODD(B5:B21+0),"ODD!","EVEN")`

**Similar Formula with MOD Function:**

`=IF(MOD(B5:B21,2)=1,"ODD!","EVEN")`

**Read More: ****How to Verify Odd and Even Using IF Function in Excel**

### 5. Filter Odd and Even Numbers in Excel

You can filter out odd and even numbers in Excel in the following way.

**Steps:**

- Add a helper column at
**column C**and insert the following formula in**cell C5**.

`=ISEVEN(B5)`

- Then drag the
**F****ill Handle**to copy the formula down.

- Now, select a cell inside and go to the
**Home**tab ⇒**Editing**group ⇒**Sort & Filter**⇒**Filter**.

- Now unmark
**FALSE***to get the even numbers*and unmark**TRUE***to get the odd numbers*.

- Then press
**OK**.

**Read More: ****How to Check If a Number Is Odd in Excel**

### 6. Distinguish Odd and Even Numbers with Different Colors Using Conditional Formatting

You can also apply different colors to odd and even numbers to distinguish them. Use the following steps.

**Steps:**

- Select the range
**B5:B21**and go to**Home**⇒**Conditional Formatting**⇒**New Rule**.

- Select “
**Use a formula to determine which cells to format**” and type the following formula in the**Rule Description**box.

`=ISEVEN($B5)`

- Then press the
**Format**button.

- Now apply a suitable format and press
**OK**twice.

- So you will see that the even and odd numbers are now colored differently and can easily be distinguished.

**Read More:** **Excel Conditional Formatting for Odd and Even Numbers and Rows**

**Download Practice Workbook**

Before we start, please download the following workbook so that you can practice while reading his article.

## Conclusion

So, this was the discussion on how to separate odd and even numbers in Excel. I hope you find this article useful. If you still face any problems, please leave us a comment.