To filter integers (whole numbers) in a list, you can use the FILTER function in Google Sheets. The filtered results will be displayed in a new range. You can also apply the filter to the same list by using a custom formula in the Data menu > Create a filter.

In addition to this, in this tutorial, you can also learn how to:

• Highlight whole numbers: You can use the Conditional formatting feature to highlight all the cells that contain integers.
• Mark the rows containing whole numbers in a new column: You can use an IF logical test to create a new column that contains a value of TRUE if the cell in the original column contains an integer and a value of FALSE if the cell does not contain an integer.

How to Filter Integers in Google Sheets with FILTER Function

The syntax of the FILTER function is:

FILTER(range, condition1, [condition2, …])

Assume the list in the range B1:B and B1 contains the field label. If so, you can use the following FILTER formula to filter integers in this array:

=FILTER(B2:B,B2:B=INT(B2:B))

where:

• B2:B is the range of cells to be filtered.
• B2:B = INT(B2:B) is the condition that the cells in the range must meet. This condition checks if the value in each cell is an integer.

The role of the INT function here is to convert the numbers in the array to integers. The formula compares the converted values to the original values. The formula returns the matching values.

This is an array formula that returns multiple values based on the number of whole numbers in the list.

Since the range is vertical, the formula returns multiple rows. So, make sure that there are sufficient blank cells below the formula applied cell. Otherwise, it will return the #REF! error, which wrapping the formula with an IFERROR function will not remove.

How to Filter Integers in Google Sheets with Filter Menu

As you can see in our earlier example, the formula returns the output in a new range. However, if you want to filter the source list, then use the Data > Create a filter command.

First, you need to apply the filter to the list. To do this, select the range B1:B, then go to Data > Create a filter. Alternatively, you can right-click on cell B1 and choose Create a filter from the shortcut menu.

Then, follow these five steps to apply the custom formula to filter integers in Google Sheets:

1. Click on the Filter dropdown menu icon (Doner icon) in cell B1.
2. Select Filter by condition.
3. Select Custom formula is.
4. Enter =B2=INT(B2) in the given field.
5. Click OK.

This will filter only whole numbers in the list.

FILTER Function vs. FILTER Menu for Filtering Whole Numbers in Google Sheets

Here are the two main points that you should consider when choosing one of the above for filtering whole numbers:

FILTER menu command:

• The Create a filter menu creates the filter in the source list.
• When you add more values to the filtered range, you need to refresh the filter by clicking the Doner icon in the first cell of the range and clicking OK.

FILTER function:

• The FILTER function filters the integers to a new range.
• When you add more values, the formula will automatically cover them if the range is open, for example, B2:B (not B2:B16 ) in our example.

I hope this will help you choose the one that you want to use to filter whole numbers in Google Sheets.

Conditional Formatting to Highlight Integers in Google Sheets

To highlight integers in the range B2:B, we can use the following custom formula rule in Conditional formatting in Google Sheets.

=AND(B2>0,B2=INT(B2))

Here is how to apply it:

1. Go to Format > Conditional formatting.
2. In the Apply to range field, enter the range B2:B or B2:B16.
3. In the Format rules section, select Custom formula is.
4. In the custom formula field, enter the above formula.
5. Click Done.

We have learned how to FILTER as well as highlight integers in Google Sheets. The next tip is how to mark them.

How to Mark or Extract Whole Numbers in Google Sheets

In some cases, we need to mark the rows that contain whole numbers.

In our case, the range is B2:B. So we can use the following ARRAYFORMULA in row 2 of any blank column in that sheet:

=ARRAYFORMULA(LET(
     range,B2:B,
     test,IF(INT(range)=range,TRUE,FALSE),IF(range="",,test)
))

We would prefer to enter it in cell C2. It will return TRUE against integers and FALSE against decimals. You can replace TRUE and FALSE in the formula with any custom values such as 1 for TRUE and 0 for FALSE.

If you want to extract only the integers, then replace the logical test IF(range="",,test) with IF(test=TRUE,range). Here is the corrected formula:

=ARRAYFORMULA(LET(
     range,B2:B,
     test,IF(INT(range)=range,TRUE,FALSE),IF(test=TRUE,range,)
))

Anatomy of the Formula

Actually, the formula is as simple as =ARRAYFORMULA(IF(INT(B2:B)=B2:B,TRUE,FALSE)). LET is used to define names to expressions and enhance performance.

The IF formula works by first using the INT function to convert the value in each cell to an integer.

Then, it compares the converted value to the original value.

If the two values are equal, then the formula returns TRUE. Otherwise, the formula returns FALSE.

The ARRAYFORMULA function is used to tell Google Sheets that the formula should be applied to all the cells in the range, not just the active cell.

Related: How to Filter Decimal Numbers in Google Sheets.