How to Filter Decimal Numbers in Google Sheets

Published on

Assume you have a list in Google Sheets with mixed types of numbers. I mean a list of decimal as well as integer numbers. How to filter decimal numbers only from this list in Google Sheets?

The above is question # 1, which I am going to answer in this tutorial, and here is the screenshot of the expected output (column C).

sample data for decimal filtering

In question number # 2, as a bonus, I am going to provide you with a cool solution. Then what’s that question?

The question is how to filter decimal numbers with specific decimal fractions. That specific fraction in this example is 0.25 but you can change that.

filter decimal numbers in Google Sheets

The Formula to Filter Decimal Numbers in Google Sheets

The functions that we can use here are INT and IF. It’s a combination formula. Also, instead of IF, you can use the Filter function with INT. Both the outputs will be slightly different.

The output of the former formula combination may or may not contain blank cells but the latter doesn’t.

First, let’s see how to filter decimal numbers in Google Sheets. Then we can move to filter specific decimal fractions. Both the formulas are identical. The only change is in the comparison operator used in the IF statement/Filter function.

Example of how to filter decimals in Google Sheets.

=ArrayFormula(if(A2:A-int(A2:A)<>0,A2:A,))

The range used in this formula is A2: A. Use this formula in cell B2. But before using the formula please remove the values/formulas in the range B2: B.

Here is the alternative filter formula to extract decimal numbers in Google Doc Sheets.

=Filter(A2:A,A2:A-int(A2:A)<>0)

Want to see the screenshot? Here you go! (My formula is in cell C2)

extract decimal numbers

You May Like:- How to Extract Decimal Part of a Number in Google Sheets.

Filter Specific Decimal Fractions in Google Sheets

To filter decimal fractions of your choice you can tweak the above formulas as below.

Formula # 1:

=ArrayFormula(if(A2:A-int(A2:A)=0.25,A2:A,))

Formula # 2

=Filter(A2:A,A2:A-int(A2:A)=0.25)

The above formulas filter numbers with decimal fractions of 0.25. It would return the following numbers as the output.

100.25
300.25
0.25

In concise, to filter decimal numbers in Google Sheets, you can use the function INT with IF or FILTER.

Do check my Google Sheets Functions Guide to learn more Google Sheets functions or check my blog’s homepage for more details.

Related: How to Filter Integers in Google Sheets.

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.