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