How to Filter Odd Numbers in Excel with a Formula

Formulas & functions · Updated November 20, 2026

To filter odd numbers in Excel, add a helper column with =IFERROR(AND(ISNUMBER(A2),MOD(A2,2)=1),FALSE), fill it down, then use Data > Filter to show only TRUE. This works in Microsoft 365 for Windows and in many older Excel versions. If you have Microsoft 365 or Excel 2021, you can also return the odd numbers in a separate list with the FILTER function.

What You’ll Need

  • A worksheet with the numbers you want to check in one column. The examples below use cells A2:A20, with a heading in A1.
  • An empty column beside the numbers for the helper formula, such as column B.
  • For the dynamic-array alternative, Microsoft 365 or Excel 2021, plus an empty area where the results can spill.

Odd numbers are integers that are not evenly divisible by 2: for example, 1, 7, and -3. This guide treats text, blanks, and decimal values as not odd. If your Excel uses semicolons as formula separators because of regional settings, replace the commas in the examples with semicolons.

Step-by-Step

  1. Check the source column. Put a clear heading in A1, such as Number, and keep the values to be checked in A2:A20. There should be one value per row. Remove merged cells from the list and avoid blank rows within it if you can; a continuous range is easier to filter. The helper formula below will ignore blank cells and text, but a clean, single-column list makes the results easier to read.
  2. Add a helper-column heading. In B1, type a label such as Odd? The heading identifies what the formula means and helps Excel include the column when you turn on filters. Keep the helper column next to the source data rather than placing it several columns away.
  3. Enter the test formula. With the number being checked in A2, click B2 and enter =IFERROR(AND(ISNUMBER(A2),MOD(A2,2)=1),FALSE). Press Enter. ISNUMBER checks that the cell contains a number, MOD returns the remainder after division by 2, and a remainder of 1 marks an odd integer. IFERROR returns FALSE rather than showing an error for an unexpected value. For example, if A2 contains 7, B2 displays TRUE; if A2 contains 8, a blank, or text, it displays FALSE.
  4. Copy the formula down. Select B2 and use the fill handle, the small square at the lower-right corner of the selection, to copy the formula through the last row of your data. You can also copy B2, select B3:B20, and paste. Check a few rows: the reference should change from A2 to A3, A4, and so on. If it stays at A2, the formula was not filled as a relative reference.
  5. Turn on AutoFilter. Click a cell in the data table, then choose Data > Filter on the ribbon. Excel adds a drop-down arrow to the headings. On Windows, Ctrl+Shift+L toggles filters on and off; on Mac, use Data > Filter if the shortcut differs on your keyboard or in your Excel settings. If the arrows do not cover both columns, undo the filter, select the full range A1:B20, and choose Data > Filter again.
  6. Show only odd values. Click the drop-down arrow in B1, clear the check box for FALSE, leave TRUE selected, and click OK. The visible rows now contain odd numbers. The original values are still in column A; AutoFilter hides rows that do not match rather than deleting them. To restore the whole list, open the same drop-down and choose Select All, or use Data > Clear.
  7. Optionally create a separate odd-number list. If you use Microsoft 365 or Excel 2021, you can return matching values elsewhere instead of hiding rows. With source numbers in A2:A20 and an empty destination cell such as D2, enter =FILTER(A2:A20,IFERROR(IF(ISNUMBER(A2:A20),MOD(A2:A20,2)=1,FALSE),FALSE),"No odd numbers"). Press Enter; the results spill down from D2. For the sample list 12, 7, -3, 20, 15, this returns 7, -3, and 15, in the same order as the source. The formula does not change or filter the original rows.

Choose the helper-column approach when you need to filter a complete table, including other details on each row. Choose FILTER when you want a separate, automatically updating list of the odd values alone. If you need to keep each odd number attached to information in other columns, use the helper-column method so the filter hides or shows whole records together.

Common Mistakes

  • Using the wrong test for oddness. A test such as “number is not divisible by 2” needs to check for a remainder of 1. MOD is a straightforward way to do that. The formula also checks ISNUMBER so text labels are not treated as odd values.
  • Filtering only the helper column. If the filter range includes column B but not the related data columns, Excel may show odd/even flags without keeping all the row information together. Select the entire table, including its headings, before turning on Filter.
  • Leaving FALSE selected. AutoFilter initially shows all available values. If both TRUE and FALSE remain checked, the list will not look filtered. Open the helper-column menu and verify that TRUE alone is selected.
  • Expecting decimals to count as odd integers. Odd and even describe whole numbers. A value such as 5.5 is not an odd integer, so the formula returns FALSE. If your source contains decimals that should be rounded first, make that rule explicit rather than assuming Excel will classify them as odd.
  • Typing the formula into the wrong row. The example starts in B2 because A2 is the first number. If your data starts on another row or uses another column, adjust the reference before filling the formula down.
  • Assuming AutoFilter deletes excluded values. It only hides rows from view. Avoid copying a filtered range without checking what is visible, and use the clear-filter option when you need to see the full list again.

Tips

  • Use an Excel table for a list that grows. Select the data and choose Insert > Table, making sure the table has headers. When you add rows to a table, its filter controls and calculated helper-column formula can extend with the data. Check that the new row received the formula before relying on the filter.
  • Keep the result tied to the source when needed. A helper column is best if each number belongs with a name, date, or other record. Filtering the helper column keeps those details on the same visible rows.
  • Use the FILTER result for a live extract. A dynamic-array formula recalculates when source values change. Leave the cells below and beside its output clear; content in the spill area can prevent the list from appearing.
  • Know how negative values behave. Excel’s MOD function returns a positive remainder when the divisor is positive, so -3 is classified as odd by the formula. If your business rule excludes negative values, add that requirement separately instead of changing the definition of odd.
  • Save a copy before major cleanup. Filtering is reversible, but deleting rows or pasting over the source is not the same as filtering. Keep an untouched copy if you plan to transform the data after isolating the odd values.

Troubleshooting

The formula shows an error. Check that the cell reference is correct and that the formula begins with an equals sign. If your regional settings use semicolons, change the argument separators. The IFERROR in the helper formula handles many invalid cell values, but a mistyped formula or a missing parenthesis still needs to be corrected.

TRUE and FALSE do not appear in the filter menu. Make sure the formula was entered in B2 and filled down, and check that B1 contains a heading. If the filter was applied before the helper column existed, select the full data range and choose Data > Filter again. Also confirm that the cells in column B contain formula results rather than blank cells.

Odd values are missing. Click a missing value and check that Excel recognizes it as a number. A number stored as text may not pass the ISNUMBER check. Convert text-formatted numbers to numbers using the warning icon’s conversion option when available, or use Data > Text to Columns and finish the conversion. Then check that the helper formula recalculates and displays TRUE for the value.

The FILTER formula returns #SPILL!. Clear cells in the area where the results need to appear, including cells that look blank but contain spaces or formulas. Move the formula to a larger empty area if necessary. If Excel displays #NAME? for FILTER, your Excel version may not include that function; use the helper-column and AutoFilter method instead.

The filter hides every row or none of them. Inspect several helper cells before changing the filter again. If all results are FALSE, verify that the formula points to the correct number column and that the values are numeric. If all rows remain visible, open the filter menu and select TRUE only. Use Data > Clear to remove the filter criteria without deleting your helper formulas.

Frequently Asked Questions

Can I filter odd numbers without adding a helper column?
Yes, if you have Microsoft 365 or Excel 2021, use the FILTER formula in an empty area to return odd values as a separate list. AutoFilter itself does not provide a built-in “odd numbers” condition, so the helper column is the practical choice when you want to hide entire rows or use an older Excel version.

Does this method work for negative numbers?
Yes. With the formula shown, -3 and -11 are odd because they leave a remainder of 1 when evaluated by MOD with a divisor of 2. Negative even values, such as -4, return FALSE.

Will the filter include blank cells or text?
No. The helper formula returns FALSE for blanks and text, so filtering for TRUE excludes them. If you want to display blanks along with odd values, that is a different filter rule and should be added deliberately.

Can I filter odd numbers in a table with other columns?
Yes. Add the helper formula as a table column, then use the table’s filter arrow in that column to select TRUE. The other columns remain aligned with their original records because AutoFilter hides complete rows.

Will the FILTER formula update when I change a number?
Yes. In a supported Excel version, the result recalculates when values in A2:A20 change. If you add rows beyond A20, update the formula’s source range or use a table-based range so new entries are included.

Can I sort odd numbers as well as filter them?
Yes. First filter the helper column to TRUE, then sort the number column using its heading’s sort options. Sorting moves complete rows when the full data range is included, so select the whole table rather than sorting just the number cells.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.