How to sort rows by odd or even numbers in Excel?
Sorting numbers in Excel by ascending or descending order is a common task, but sometimes you may need to organize your data so that all odd numbers are grouped together and all even numbers are grouped together. For example, suppose you have a large list of numbers and want to separate odd and even values for easier analysis or reporting. Sorting by odd or even values is not built into Excel as a default feature, but there are multiple practical methods that can help you accomplish this. Below, you’ll find several solutions to sort rows by odd or even numbers—whether you prefer using helper columns, formulas, VBA code, or an advanced add-in.
 |  |  | Sort rows by odd or even numbers with a helper column Sort rows by odd or even numbers with Kutools for Excel |
Sort rows by odd or even numbers with a helper column
One of the quickest ways to sort numbers by odd or even values in Excel is to add a helper column, which allows Excel to distinguish odds from evens for the sort operation. This method is straightforward and suitable for users who are comfortable working with formulas and the Sort feature, and it works in any Excel version. The approach is flexible but does require temporarily adding a new column.
1. Next to your list of numbers, enter the following formula in an empty adjacent cell (for example, if your numbers start in A2, enter this in B2): =ISODD(A2) and press Enter.
See screenshot:
Tip: The ISODD function checks whether a value is odd. If you use column headers, ensure your formula references the correct data row.
2. Use the fill handle to drag the formula down alongside your entire list. The formula will fill TRUE for odd numbers and FALSE for even numbers. This visual distinction will serve as the basis for sorting.
See screenshot:
3. Keep the formula cells selected, then click Data > Sort Smallest to Largest or Sort Largest to Smallest.
4. In the Sort Warning dialog, select Expand the selection and click OK. This ensures all rows are sorted correctly, keeping related data together.
See screenshot:
5. Click the Sort button. Your list will be grouped by even numbers followed by odd numbers if you chose "Sort Smallest to Largest" (as FALSE is considered smaller than TRUE).
See screenshot:
Notes:
1. To have odd numbers appear before even numbers, select Sort Largest to Smallest in step 3.
2. The helper column can be deleted after sorting to keep your worksheet tidy.
3. Be careful not to only sort the helper column, as this would disturb your original data order.
Practical tip: This method is universal, works offline, and does not require macros or add-ins. However, if you frequently need to sort by odd/even, you might want to try other approaches for increased automation.
Sort rows by odd or even numbers with Kutools for Excel
For those who manage large datasets and want a faster, more flexible solution, Kutools for Excel offers an Advanced Sort function that can sort odd and even numbers directly—without the need to create helper columns or write complex formulas. This is well-suited for users who often perform similar sorting tasks or who appreciate a graphical interface. Kutools enhances sorting capabilities and supports batch operations efficiently.
After installing Kutools for Excel, follow these steps:
1. Select the range of data you wish to sort.
2. Navigate to Kutools Plus > Sort > Advanced Sort.
See screenshot:
3. In the Advanced Sort dialog box, select the column you wish to sort by from the Column drop-down. In the Sort On section, choose Odd and even number; then select the desired sort order: A to Z for even numbers first and Z to A for odd numbers first.
See screenshot:
4. Click OK. Your list will instantly be sorted to group odd and even numbers according to your settings. Here are example results:
| Original data | Sort from even to odd numbers | Sort from odd to even numbers | ||
 | |  | |  |
Click to know more about this Advanced Sort feature.
Download and free trial Kutools for Excel Now!
Advantages: No need for extra formulas or columns, works with a simple interface, quickly sorts by odd/even criteria, well suited for frequent users.
Considerations: Kutools is a third-party add-in requiring installation, but trusted by many for robust Excel enhancements.
Demo: Sort rows by odd or even numbers with Kutools for Excel
Sort rows by odd or even numbers using VBA code
If you want to automate the process of sorting numbers by odd or even values, using a VBA macro is an efficient method. This approach is suitable for users with basic knowledge of macros and is particularly helpful if you need to repeat sorting frequently or want to avoid adding helper columns and do not wish to rely on third-party add-ins. VBA offers a quick, no-frills way to sort in-place.
1. Click Developer > Visual Basic to open the Microsoft Visual Basic for Applications window. If you don't see the Developer tab, you can enable it via Excel Options. In the VBA window, click Insert > Module, and paste the following code into the module:
Sub SortByOddEven()
Dim ws As Worksheet
Dim rng As Range, cell As Range
Dim arr As Variant
Dim HelperArr() As Integer
Dim i As Long
On Error Resume Next
Set ws = Application.ActiveSheet
Set rng = Application.InputBox("Select the range to sort (single column):", "KutoolsforExcel", Type:=8)
If rng Is Nothing Then Exit Sub
arr = rng.Value
ReDim HelperArr(1 To UBound(arr, 1))
For i = 1 To UBound(arr, 1)
If IsNumeric(arr(i, 1)) Then
HelperArr(i) = arr(i, 1) Mod 2
Else
HelperArr(i) = 2 ' Non-numeric entries go last
End If
Next i
' Add helper column
rng.Offset(0, 1).Resize(UBound(arr, 1), 1).Value = Application.Transpose(HelperArr)
' Sort by helper column
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=rng.Offset(0, 1), SortOn:=xlSortOnValues, Order:=xlAscending
With ws.Sort
.SetRange rng.Resize(, 2)
.Header = xlNo
.Apply
End With
' Remove helper column
rng.Offset(0, 1).Resize(UBound(arr, 1), 1).Clear
MsgBox "Rows are sorted by odd (1) or even (0) numbers.", vbInformation, "KutoolsforExcel"
End Sub2. Press F5 or click the 
button to run the macro. Choose your data column in the input box that appears, and the macro will automatically sort your data so that even numbers are grouped before odds (or vice versa, depending on sorting order).
Notes and troubleshooting:
- If you apply this macro to a multi-column range, only the selected column and its immediate columns will be sorted. For best results, select just the column to be sorted, or adjust the code for multi-column sorting in advanced use cases.
- Make sure your worksheet is saved before running macros to avoid accidental data loss.
- Non-numeric entries are sorted to the bottom to avoid errors.
Advantages: Fully automatic, no helper column remains, quick for repeated tasks.
Limitations: Requires enabling macros and some VBA experience; use caution when applying on sensitive data.
Identify odd or even numbers using the MOD formula
Besides the ISODD function, you can also use the MOD function to distinguish odd and even numbers. This method is practical for users who are familiar with standard mathematical operations or who may be using older versions of Excel that lack certain functions or localizations. The MOD approach gives you explicit numeric indicators— 0 for even, 1 for odd—allowing you to control the order or integrate with other sorting workflows.
1. In a blank cell next to your numbers (for example, B2), enter the following formula:
=MOD(A2,2)This formula divides the value in A2 by 2 and returns the remainder. Even numbers produce 0; odd numbers produce 1.
2. Copy this formula down to apply to the rest of your number list.
3. Sort your data based on this helper column (Data > Sort), just as described in the earlier helper column method. Sorting from smallest to largest will place all even numbers (0) before all odd numbers (1).
Tips:
- If your data includes non-integer values, MOD will still work, but only whole numbers will show 0 or 1 as intended.
- This method is language- and region-independent and can be more transparent than TRUE/FALSE outputs.
- You can change the order by switching between ascending and descending sort.
Related articles:
How to sort email address by domain in Excel?
How to sort data by the most frequent value in Excel?
How to sort or filter data by strikethrough in Excel?
How to sort rows to put the blank cells on top in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in