How to Separate Odd and Even Numbers in Excel (6 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Odd and Even Numbers in Excel


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!

Combining FILTER and MOD Functions to Separate Odd and Even 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)

Combining FILTER and MOD Functions to Separate Odd and Even Numbers


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)),"")

Use an INDEX Formula to Separate Odd and Even Numbers If FILTER Function Is Not Available

  • Now, drag the Fill 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)),"")


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 Fill Handle down.

  • Now, select cell B5 to C21 and go to the Data tab ⇒ Sort & FilterSort command.

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

Separate Odd and Even Numbers in Excel by Sorting

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

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")

Use IF Formula to Mark Even and Odd Numbers Separately

Similar Formula with MOD Function:

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

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 Fill Handle to copy the formula down.

  • Now, select a cell inside and go to the Home tab ⇒ Editing group ⇒ Sort & FilterFilter.

Filter Odd and Even Numbers in Excel

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

Filter Odd and Even Numbers in Excel

  • Then press OK.


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.

Distinguish Odd and Even Numbers with Different Colors Using Conditional Formatting

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


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.


<< Go Back to | Excel for Math | Excel ODD or EVENLearn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo