How to use ISNUMBER in Google Sheets [Guide 2022]

how to use ISNUMBER Function in Google Sheets 9

In this article, will learn how to use ISNUMBER function in google sheets. ISNUMBER function is simply a function that tells you whether the specified cell address has a numeric value or a non-numeric value. It is used to check between a Boolean value and it only returns TRUE or FALSE.

But this is not enough, this function is primarily used with other functions to make a combination and get useful results such as it is widely used with IF function, and conditional formatting rules, to perform a specific task or print a specific message, and apply some conditional formatting based on the result of ISNUMBER function. In this article, we will learn the common uses of ISNUMBER function with step-by-step procedures and simple examples.

Use Case of ISNUMBER Function in Google Sheets

We often perform calculations on large data sets and get a final result in a cell, but have you ever thought if any of the values in such a large data set is a non-numeric value, then the overall calculator may hurt? Right, so to validate that the value is actually a number in the eyes of google sheets, we have the ISNUMBER function as the top favorite to check if its a number of any other data type, but you can think that number will be written as the number and it can be seen at a glance without any formula or function to check it.

If I write a number apply formatting on it with center align and convert its format into String, then no one on the planet is going to identify it at a glance, you just need to have some tricks some steps to check either its a number or a String or text or anything, so ISNUMBER makes it absolutely simple to validate a data type. A similar concept is used in JavaScript that used a typeof operator to check the data type, and some of you who have done JavaScript must understand the importance of this concept. So, therefore, we need to learn how to use ISNUMBER function in google sheets.

See also  How to Resize Rows & Columns in Google Sheets

How to use ISNUMBER in Google Sheets

Here we will see how ISNUMBER helps us in many ways, we will learn the step-by-step procedure to master the ISNUMBE function in google sheets. So let’s get started with the basic syntax of this function and then move forward with examples.

ISNUMBER in Google Sheets – Syntax

=ISNUMBER(value)

Here,

value: It can be a value, and most probably a cell address to validate whether it’s a number or other data type.

Tip: ISNUMBER returns TRUE if the value is a number and FALSE otherwise.

This function is frequently used with the combination of the IF function in conditional statements.

Note that passing a number enclosed in double or single quotes  (e.g. ISNUMBER(“421”), or ISNUMBER(‘313’)) will return FALSE because the number passed inside double or single quotes are strings and not numbers.

ISNUMBER Function in Google Sheets – Simple Usage

In this section, we will learn how to use ISNUMBER function in google sheets as a very simple usage, we will use ISNUMBER solely to see how it works and how it validates data types.

For this example, I have some sample data written in column A, and I will apply ISNUMBER in column B and I will get the TRUE or FASLE values corresponding to the values written in column A

Step 1

Sample data

how to use ISNUMBER Function in Google Sheets 1

Step 2

Type the ISNUMBER function

how to use ISNUMBER Function in Google Sheets 2

Step 3

Pass the cell address (A1)

how to use ISNUMBER Function in Google Sheets 3

Step 4

Press Enter key, and you get the result

how to use ISNUMBER Function in Google Sheets 4

Step 5

Copy the formula below to apply the formula to the entire column A

how to use ISNUMBER Function in Google Sheets 5

Step 6

Explanation of results

how to use ISNUMBER Function in Google Sheets 6

This is how you can see the ISNUMBER function returned TRUE for numbers and false otherwise.

But it does not seem to be a useful use case of the ISNUMBER function, it does not make sense. Now, we will see some useful applications of ISNUMBER combined with other functions.

See also  How to Insert Euro Symbol in Google Sheets [6 Methods]

ISNUMBER Function in Google Sheets – with IF Function

In this section, we will learn how to use ISNUMBER function in google sheets with the IF function. We will validate if the value is actually a number and if TRUE we will perform some calculation, and if not then we will print a custom message. This way we can avoid having ugly reference errors, and type errors.

For this example I have a data set in which I have some numbers and a few strings, strings are disguised like numbers, so we will find them out using our custom formula made up of the combination of IF and ISNUMBER functions

Step 1

Sample data

how to use ISNUMBER Function in Google Sheets 7

Tip: You can see all values are looking like numbers in the Cost column, but there are some text and String values that can not be detected at a glance

Step 2

Tip: We need to calculate the total cost and GST for each expense.

Apply a simple sum formula for Cost and GST

how to use ISNUMBER Function in Google Sheets 8

Step 3

Here is the result, but wait have you noticed, that some values are not summed up and they are showing only the two values

how to use ISNUMBER Function in Google Sheets 9

Step 4

Apply the ISNUMBER function and check each value in the cost column

how to use ISNUMBER Function in Google Sheets 10

Step 5

Now you can see some of them are not numbers and that’s why they did not sum up

how to use ISNUMBER Function in Google Sheets 11

Tip: Now we need to use the IF function with ISNUMBER and SUM

Step 6

Type the IF function

how to use ISNUMBER Function in Google Sheets 12

Step 7

Pass the ISNUMBER as the first argument of the IF function

how to use ISNUMBER Function in Google Sheets 13

Step 8

Pass the cell reference (to validate) inside the ISNUMBER function

how to use ISNUMBER Function in Google Sheets 14

Step 9

Now pass the SUM function with the cell reference of Cost and GST columns to be summed if the condition (in the ISNUMBER function) is true

how to use ISNUMBER Function in Google Sheets 15

Step 10

Now specify a custom message (inside double quotes) to print if the condition is FALSE

how to use ISNUMBER Function in Google Sheets 16

Step 11

This is what you get using the IF function with ISNUMBER

how to use ISNUMBER Function in Google Sheets 17

The values which are actually numbers are summed by function, and the values which are not numbers are detected and the IF function through them into the else block, and thus we avoided getting the wrong value in the calculation of SUM of 1 number, and 1 String.

I hope you find this very helpful, and trust me it’s a real-world use case of ISNUMBER and IF Function, you can see how helpful it is.

Let’s move on and learn another helpful use case of the ISNUMBER function along with conditional formatting rules.

See also  How to Use Google Sheets Conditional Formatting Multiple Columns

ISNUMBER Function in Google Sheets – with Conditional Formatting

In this section, we will learn how to use ISNUMBER function in google sheets with conditional formatting, we will learn how to combine this with conditional formatting rules and apply formatting to the content based on its data type.

So, let’s further simplify the previous problem, the previous problem told us that some values are actually strings, so to detect them, we can apply conditional formatting on the values which are considered as non-numeric values by the ISNUMBER function, so that we can change them.

Step 1

Same data set

how to use ISNUMBER Function in Google Sheets 18

Step 2

Go to Format > Conditional formatting

how to use ISNUMBER Function in Google Sheets 19

Step 3

Select your entire data range in the Apply to range section

how to use ISNUMBER Function in Google Sheets 20

Step 4

From the below Format rules section select a condition to apply the formatting, here select “custom formula is”

how to use ISNUMBER Function in Google Sheets 21

how to use ISNUMBER Function in Google Sheets 22

Step 5

Now you need to pass a value of the formula; here we will pass the ISNUMBER function

=ISNUMBER(F2)

how to use ISNUMBER Function in Google Sheets 23

Step 6

From the below formatting style section, select some formatting; cell color, text color, bold, underline, italic, etc. You can use any of the formatting styles or multiples to be applied to the cells that satisfy the above condition

how to use ISNUMBER Function in Google Sheets 24

Step 7

Click on done.

how to use ISNUMBER Function in Google Sheets 25

Step 8

Now you can see all the values which are numbers have got the formatting, now you can detect the values which are looking like numbers, but are not numbers in reality.

how to use ISNUMBER Function in Google Sheets 26

Probably you find your data a bit messy by having too many colors in the cells having numbers, you can reverse the logic and only apply formatting to cells that are not numbers from specific columns, to do this follow the below steps

You only need to reverse this function, if you remember we have already covered NOT function in this series of articles.

Step 1

Type NOT function at the starting of the ISNUMBER function

how to use ISNUMBER Function in Google Sheets 27

Step 2

Add brackets of NOT function

how to use ISNUMBER Function in Google Sheets 28

Step 3

Now you can see the logic is reversed and now only non-numeric values are formatted, you can also change the color to red to know that they are the error values.

how to use ISNUMBER Function in Google Sheets 29

So, these were the real-world applications of ISNUMBER functions with other functions like IF and concepts like conditional formatting, and also the NOT function. I hope you have learned the usage and importance of the ISNUMBER function in google sheets.

Download/Copy Google Sheets Workbook

Important Notes

  • ISNUMBER will return you only TRUE or FALSE
  • Functions like SUM, MULTIPLY, and DIVISION, will not return an error if the parameters have one value as non-numeric, they will not return correct values but will give some value that will not be valid. So, it can be said as a limitation or a drawback of some of the functions
  • If you apply SUM on two values, one is a number, other is not a number, then the result will be a numeric value only

See also  How to Split First and Last Name in Google Sheets (2 Methods)

Conclusion

Wrapping up how to use ISNUMBER function in google sheets, we learned the syntax first which was very easy then saw a simple usage of the function and after that, we learned two of the most important and most used use cases of ISNUMBER function, one along with IF function and the second with conditional formatting.

I hope you find this tutorial helpful and that now you have learned how to use ISNUMBER function in google sheets. I will see you soon with another helpful article, till then take care. Keep learning with Office Demy.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

We will be happy to hear your thoughts

Leave a reply

OfficeDemy.com
Logo
Enable registration in settings - general