To use ISNUMBER in Google Sheets
- Have your data in a column.
- In an adjacent column, Enter the formula =ISNUMBER(A1).
- Press Enter.
- Copy the formula down to apply it to the entire column.
- This will return TRUE for numeric values and FALSE for non-numeric values in column A.
- Assume you have a dataset in column A with a mix of numbers and text.
- In a new column (e.g., column B), use the formula =IF(ISNUMBER(A1), A1 * 2, “Not a Number”).
- Press Enter.
- Copy the formula down.
- This formula doubles the numeric values and returns “Not a Number” for non-numeric values.
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.
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
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
Type the ISNUMBER function
Pass the cell address (A1)
Press Enter key, and you get the result
Copy the formula below to apply the formula to the entire column A
Explanation of results
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.
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
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
Tip: We need to calculate the total cost and GST for each expense.
Apply a simple sum formula for Cost and GST
Here is the result, but wait have you noticed, that some values are not summed up and they are showing only the two values
Apply the ISNUMBER function and check each value in the cost column
Now you can see some of them are not numbers and that’s why they did not sum up
Tip: Now we need to use the IF function with ISNUMBER and SUM
Type the IF function
Pass the ISNUMBER as the first argument of the IF function
Pass the cell reference (to validate) inside the ISNUMBER function
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
Now specify a custom message (inside double quotes) to print if the condition is FALSE
This is what you get using the IF function with ISNUMBER
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.
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.
Same data set
Go to Format > Conditional formatting
Select your entire data range in the Apply to range section
From the below Format rules section select a condition to apply the formatting, here select “custom formula is”
Now you need to pass a value of the formula; here we will pass the ISNUMBER function
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
Click on done.
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.
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.
Type NOT function at the starting of the ISNUMBER function
Add brackets of NOT function
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.
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
- 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
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.