What is IS function in Excel? IS functions, ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT are under the Information functions in Microsoft Excel. Each of these functions, referred to collectively as the IS functions, checks the specified value and returns TRUE or FALSE depending on the outcome. For example, the ISBLANK function returns the logical value TRUE if the …
This tutorial shows how to count line breaks in cell in Excel worksheet using example below. To count total lines in a cell, you can use a formula based on the LEN, SUBSTITUTE, and CHAR functions. Formula =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 Explanation In the example, we are using this formula: =LEN(B5)-LEN(SUBSTITUTE(B5,CHAR(10),””))+1 How the formula works First, the LEN function counts …
To count the total words in a cell, you can use a formula based on the LEN and SUBSTITUTE functions. Formula =LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))+1 Explanation In the example shown, C3 contains this formula: =LEN(TRIM(B3))-LEN(SUBSTITUTE(B3,” “,””))+1 How the formula works SUBSTITUTE removes all spaces from the text, then LEN calculates the length of the text without spaces. This …
This tutorial shows how to calculate Return blank if in Excel using the example below; Formula =IF(A1=1,B1,””) Explanation To return a blank result using the IF function, you can use an empty string (“”). In the example shown, the formula in D5 (copied down) is: =IF(B5=1,C5,””) How this formula works This formula is based on the IF function, …
This tutorial shows how to calculate If cell is blank in Excel using the example below; Formula =IF(A1=””,”blank”,”not blank”) Explanation To test a cell and take some action if the cell is blank (empty), you can use the IF function. The formula in cell E5 is: =IF(D5=””,”Open”,”Closed”) How this formula works The logical expression =”” means “is …
This tutorial shows how to calculate If cell is not blank in Excel using the example below; Formula =IF(A1<>””,”Not blank”,”Blank”) Explanation If you want to test a cell and take some action if the cell is not blank (not empty), you can use a simple formula based on the IF function. In the example shown, we have …
This tutorial shows how to Conditionally format column is blank in Excel using the example below; Formula =OR(A1=””,B1=””, C1=””) Explanation To highlight values in one column when values in one or more other columns are blank, you can use the OR function and a basic logical expression to test for blank or empty values. In …
This tutorial shows how to Highlight blank cells in Excel using the example below; Formula =ISBLANK(A1) Explanation If you want to highlight cells that are blank or empty with conditional formatting, you can do so with a simple formula based on the ISBLANK function. For example, if you want to highlight blank cells in the range …
This Excel tutorial explains how to use the NOT function with syntax and examples. Excel NOT Function Description The Excel NOT function returns the opposite to a supplied logical value. That is, If supplied with the value TRUE, the Not function returns FALSE; If supplied with the value FALSE, the Not function returns TRUE. Excel …
This tutorial shows how to Get first non-blank value in a list in Excel using the example below; Formula {=INDEX(range,MATCH(FALSE,ISBLANK(range),0))} Explanation If you need to get the first non-blank value (text or number) in a in a one-column range you can use an array formula based on the INDEX, MATCH, and ISBLANK functions. In the …
This tutorial shows how to set a check register formula that calculates a running balance and also, blank values using formula based on simple addition and subtraction. See illustrations below: Formula =balance-debit+credit Explanation In the example shown, the formula in G6 is: =G5-E6+F6 How this formula works The value in G5 is hard-coded. The formula picks …