How to use the Excel ISBLANK function | Exceljet

The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE ... Skiptomaincontent ExcelISBLANKFunction Summary  TheExcelISBLANKfunctionreturnsTRUEwhenacellisempty,andFALSEwhenacellisnotempty.Forexample,ifA1contains"apple",ISBLANK(A1)returnsFALSE. Purpose  Testifacellisempty Returnvalue  Alogicalvalue(TRUEorFALSE) Syntax  =ISBLANK(value) Arguments  value-Thevaluetocheck. Version  Excel2003 Usagenotes  TheISBLANKfunctionreturnsTRUEwhenacellisempty,andFALSEwhenacellisnotempty.Forexample,ifA1contains"apple",ISBLANK(A1)returnsFALSE.UsetheISBLANKfunctiontotestifacellisemptyornot. ISBLANKfunctiontakesoneargument,value,whichisacellreferencelikeA1. Theword"blank"issomewhatmisleadinginExcel,becauseacellthatcontainsonlyspacewilllookblankbutnotbeempty.Ingeneral,itis besttothinkofISBLANKtomean"isempty"sinceitwillreturnFALSEwhenacelllooksblankbutisnotempty.  Examples IfcellA1containsnothingatall,theISBLANKfunctionwillreturnTRUE: =ISBLANK(A1)//returnsTRUE IfcellA1containsanyvalue,oranyformula,theISBLANKfunctionwillreturnFALSE: =ISBLANK(A1)//returnsfalse Isnotblank Totestifacellisnotblank,nestISBLANKinsidetheNOTfunctionlikethis: =NOT(ISBLANK(A1))//testnotblank TheaboveformulawillreturnTRUEwhenacellisnotempty,andFALSEwhenacellisempty. Emptystringsyntax Manyformulaswilluseanabbreviatedsyntaxtotestfor empty cells,insteadoftheISBLANKfunction.Thissyntaxuses anemptystring("") withExcel'smathoperators "="or"<>". Forexample,totestifA1isempty,youcanuse: =A1=""//TRUEifA1isempty TotestifA1 isnotempty: =A1<>""//TRUEifA1isnotempty ThissyntaxcanbeusedinterchangeablywithISBLANK.Forexample,insidetheIFfunction: =IF(ISBLANK(A1),result1,result2)//ifA1isempty isequivalentto: =IF(A1="",result1,result2)//ifA1isempty Likewise,theformula: =IF(NOT(ISBLANK(A1)),result1,result2) isthesame,as: =IF(A1<>"",result1,result2) Bothwillreturnresult1whenA1isnotempty,andresult2whenA1isempty. Emptystrings Ifacellcontainsanyformula,theISBLANKfunctionandthealternativesabovewillreturnFALSE,eveniftheformulareturnsanemptystring("").Thiscancauseproblemswhenthegoalistocountorprocessblankcellsthatincludeemptystrings. OneworkaroundistousetheLENfunctiontotestforalengthofzero.Forexample,theformulabelowwillreturnTRUEifA1isemptyorcontainsaformulathatreturnsanemptystring: =LEN(A1)=0//TRUEifempty So,insidetheIFfunction,youcanuseLENlikethis: =IF(LEN(A1)=0,result1,result2)//ifA1isempty Youcanusethissameapproach tocountcellsthatarenotblank. ISBLANKformulaexamples Onlycalculateifnotblank Thegoalofthisexampleistoverifyinputbeforecalculatingaresult.Thekeypointtounderstandisthatanyvalidformulacanbesubstituted.TheSUMfunctionisusedonlyasanexample.Thelogiccanalsobe... Highlightblankcells Whenyouuseaformulatoapplyconditionalformatting,theformulaisevaluatedrelativetotheactivecellintheselectionatthetimetheruleiscreated.So,inthiscasetheformula=ISBLANK(B4)isevaluatedfor... Ifcellisblank Thelogicalexpression=""means"isempty".Intheexampleshown,columnDcontainsadateifataskhasbeencompleted.IncolumnE,aformulachecksforblankcellsincolumnD.Ifacellisblank,theresultisa... Addrownumbersandskipblanks Intheexampleshown,thegoalistoaddrownumbersincolumnBonlywhenthereisavalueincolumnC.TheformulainB5is: =IF(ISBLANK(C5),"",COUNTA($C$5:C5)) TheIFfunctionfirstchecksifcellC5has... Ifnotblankmultiplecells InExcel,emptydoublequotes("")meanemptystring.Thesymbolisalogicaloperatorthatmeans"notequalto",sothefollowingexpressionmeans"A1isnotempty": =A1<>""//A1isnotempty This... Ifcellisnotblank Inthisexample, columnDrecordsthedatea taskwascompleted.Therefore,ifthecolumncontainsadate(i.e.is notblank),wecanassumethetaskiscomplete. TheformulaincellE5usestheIFfunctiontocheck... Getfirstnon-blankvalueinalist So,thegistoftheproblemisthis:Wewanttogetthefirstnon-blankcell,butwedon'thaveadirectwaytodothatinExcel.WecoulduseVLOOKUPwithawildcard*(seelinkbelow),butthatwillonlyworkfor... Checkregisterbalance ThevalueinG5ishard-coded.TheformulapicksupthevalueinG5,thensubtractsthevalue(ifany)inE6andaddsthevalue(ifany)inF6.Whenthecreditordebitvaluesareempty,theybehavelikezeroandhave... Countlinebreaksincell First,theLENfunctioncountstotalcharactersinthecellB5. NextSUBSTITUTEremovesall"linereturns"fromthetextinB5bylookingforCHAR(10)whichisthecharactercodeforthereturncharacterinWindows.... Returnblankif ThisformulaisbasedontheIFfunction,configuredwithasimplelogicaltest,avaluetoreturnwhenthetestisTRUE,andavaluetoreturnwhenthetestisFALSE.InplainEnglish:ifValue1equals1,returnValue... Calculatedaysopen Inthisexample,thegoalistocalculatethenumberofdaysaticket/case/issuehasbeenopen.Westartcountingonthedateaticketwasopened,andstopcountingonthedateaticketwasclosed.Ifthereisnoclosed... Showcheckmarkifcomplete ThisformulausestheIFfunctiontocheckfor"complete"incolumnC.Whenacellcontains"complete",IFreturns CHAR(252) whichdisplaysacheckmarkwhenthefontis"Wingdings".Whenacellcontainsanyother... Relatedvideos Howtojoinvalueswiththeampersand Inthisvideowelookathowtousetheoperatorforconcatenation,theampersandcharacter,tojoinvaluestogetherinaformula. HowtojoincellvalueswithCONCATENATE Inthisvideowe'lllookathowtousetheCONCATENATEfunction,analternativetousingtheampersand(&)charactertojoinvaluesinaformula. Howtotraperrorsinformulas Trappingerrorscanmakeyourspreadsheetsmoreprofessionalbymakingthemlessclutteredandmorefriendlytouse.Inthisvideo,we'lllookatsomewaystotraperrorsinaformula. Howtofillinmissingdatapart2 Inthisvideo,we'lllookatawaytofillinmissingdatausingaformulacreatedwiththeIFandISBLANKfunctions. Howtobuildasearchboxwithconditionalformatting Inthisvideo,we'lllookatawaytocreateasearchboxthathighlightsrowsinatable,byusingconditionalformatting,andaformulathatchecksseveralcolumnsatonce. Relatedfunctions  ExcelISERRFunction TheExcelISERRfunctionreturnsTRUEforanyerrortypeexceptthe#N/Aerror.YoucanusetheISERRfunctiontogetherwiththeIFfunctiontotestforanerroranddisplayacustommessage,orperformadifferentcalculationiffound. ExcelISERRORFunction TheExcelISERRORfunctionreturnsTRUEforanyerrortypeexcelgenerates,including#N/A,#VALUE!,#REF!,#DIV/0!,#NUM!,#NAME?,or#NULL!YoucanuseISERROR togetherwiththeIFfunctiontotestforerrorsanddisplayacustommessage,or... ExcelISEVENFunction TheExcelISEVENfunctionreturnsTRUEwhenavalueisanevennumber,andFALSEwhenavalueisanoddnumber.ISEVENwillreturnthe#VALUEerrorifavalueisnotnumeric. ExcelISFORMULAFunction TheExcelISFORMULAfunctionreturnsTRUEifacellcontainsaformula,andFALSEifnot.WhenacellcontainsaformulaISFORMULAwillreturnTRUEregardlessoftheformula'soutputorerrorconditions. ExcelISLOGICALFunction TheExcelISLOGICALfunctionreturnsTRUEwhenacellcontainsthelogicalvalues TRUEor FALSE,andreturnsFALSEforcellsthatcontainanyothervalue,includingemptycells.  ExcelISNAFunction TheExcelISNAfunctionreturnsTRUEwhenacellcontainsthe#N/AerrorandFALSEforanyothervalue,oranyothererrortype.YoucanusetheISNAfunctionwiththeIFfunctiontestfor#N/A anddisplayafriendlymessageiftheerroroccurs... ExcelISNONTEXTFunction TheExcelISNONTEXTfunctionreturnsTRUEwhenacellcontainsanyvalueexcepttext.Thisincludesnumbers,dates,times,errors,andformulasthatdonotreturntext. ISNONTEXTalsoreturnsTRUEwhenacellisempty. ExcelISNUMBERFunction TheExcelISNUMBERfunctionreturnsTRUEwhenacellcontainsanumber,andFALSEifnot.YoucanuseISNUMBER tocheckthatacellcontainsanumericvalue,orthattheresultofanotherfunctionisanumber. ExcelISODDFunction TheExcelISODDfunctionreturnsTRUEwhenavalueisanoddnumber,andFALSEwhenavalueisanevennumber.ISODDwillreturnthe#VALUEerrorifavalueisnotnumeric. ExcelISREFFunction TheExcelISREFreturnsTRUEwhenacellcontainsareferenceandFALSEifnot.YoucanusetheISREFfunctiontocheckforareferenceinaformula. ExcelISTEXTFunction TheExcelISTEXTfunctionreturnsTRUEwhenacellcontainsatextvalue,andFALSEifthecellcontainsanyothervalue.YoucanusetheISTEXTfunctiontocheckifacellcontainsatextvalue,oranumeric... 500Formulas| 101Functions Download100+ImportantExcelFunctions Getover100ExcelFunctionsyoushouldknowinonehandyPDF. Email HP Topics FormulaBasics Formula Examples ConditionalFormatting PivotTables ExcelTables ExcelCharts Shortcuts More... Keyfunctions IFfunction VLOOKUP function XLOOKUP function FILTER function SUMIFSfunction COUNTIFSfunction SUMPRODUCTfunction INDEXandMATCH Morefunctions... Hi-I'mDaveBruns,andIrunExceljetwithmywife,Lisa.OurgoalistohelpyouworkfasterinExcel.Wecreate shortvideos,andclearexamplesofformulas,functions,pivottables,conditionalformatting,andcharts. Readmore. I'veusedyourwebsiteasareferenceforexcelhelpforacoupleyearsnowandIjustwantedtotakeaminutetothankyouforallthecontentavailableonyourwebsite.Iamnowtheexcel"guru"atmycompanybutinrealityI'mjustahackwhoknowshowtosearchtheinfoyou'vemadeavailable.-Chris Excelvideotraining Quick,clean,andtothepoint. Learnmore