<!DOCTYPE html>
<html lang="en-US">
<head>
<base href="../../../../">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Handling of Empty Cells</title>
<link rel="shortcut icon" href="media/navigation/favicon.ico">
<link type="text/css" href="normalize.css" rel="Stylesheet">
<link type="text/css" href="default.css" rel="Stylesheet">
<link type="text/css" href="prism.css" rel="Stylesheet">
<script type="text/javascript" src="polyfills.js"></script><script type="text/javascript" src="languages.js"></script><script type="text/javascript" src="fuzzysort.js"></script><script type="text/javascript" src="prism.js"></script><script type="text/javascript" src="help2.js" defer></script><script type="text/javascript" src="a11y-toggle.js" defer></script><script type="text/javascript" src="en-US/langnames.js" defer></script><script type="text/javascript" src="paginathing.js" defer></script><script type="text/javascript" src="en-US/bookmarks.js" defer></script><script type="text/javascript" src="en-US/contents.js" defer></script><script type="text/javascript" src="help.js" defer></script><meta name="viewport" content="width=device-width,initial-scale=1">
</head>
<body itemscope="true" itemtype="http://schema.org/TechArticle">
<div class="header-extrawurst">
<header><div class="logo-container"><a class="logo" href="en-US/text/shared/05/new_help.html"><div class="symbol"></div>
<p>LibreOffice 6.4 Help</p></a></div></header><div class="modules">
<button type="button" data-a11y-toggle="modules-nav" id="modules" onclick="setupModules('', 'en-US');">Module</button><nav id="modules-nav"></nav>
</div>
</div>
<aside class="rightside"><input id="accordion-1" name="accordion-menu" type="checkbox"><label for="accordion-1">Contents</label><div id="Contents" class="contents-treeview"></div></aside><aside class="leftside"><div id="Index">
<div class="index-label">Index 🔎︎ </div>
<div id="Bookmarks">
<input id="search-bar" type="text" class="search" placeholder="Search in chosen module"><nav class="index"></nav>
</div>
</div></aside><div id="DisplayArea" itemprop="articleBody">
<a name="bm_id3146799"></a>
<meta itemprop="keywords" content="empty cells,handling of">
<h1 itemprop="articleSection" id="hd_id1502121">
<a name="empty_cells"></a>Handling of Empty Cells</h1>
<p id="par_id8266853" class="paragraph">In older versions of the software, empty cells were forced to numeric 0 in some contexts and to empty string in others, except in direct comparison where =A1=0 and =A1="" both resulted in TRUE if A1 was empty. Emptiness now is inherited until used, so both =VLOOKUP(...)=0 and =VLOOKUP(...)="" give TRUE if the lookup resulted in an empty cell being returned. </p>
<p id="par_id2733542" class="paragraph">A simple reference to an empty cell is still displayed as numeric 0 but is not necessarily of type numeric anymore, so also comparisons with the referencing cell work as expected. </p>
<p id="par_id4238715" class="paragraph">For the following examples, A1 contains a number, B1 is empty, C1 contains the reference to B1:</p>
<table border="1" class="" cellpadding="0" cellspacing="0">
<tr>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id291535142746121" class="tableheadintable">Case</p>
</td>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id441535142746121" class="tableheadintable">Formula</p>
</td>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id261535142746121" class="tableheadintable">Results and comments</p>
</td>
</tr>
<tr>
<td rowspan="17" colspan="">
<p id="par_id941535142746122" class="tablecontentintable">A1: 1<br>B1: <Empty></p>
</td>
<td rowspan="" colspan="">
<p id="par_id471535142746122" class="tablecontentintable">C1: =B1</p>
</td>
<td rowspan="" colspan="">
<p id="par_id851535142746123" class="tablecontentintable">Displays 0</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id611535142948275" class="tablecontentintable">=B1=0</p>
</td>
<td rowspan="" colspan="">
<p id="par_id981535142948275" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id311535142974515" class="tablecontentintable">=B1=""</p>
</td>
<td rowspan="" colspan="">
<p id="par_id851535142974516" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id161535143000810" class="tablecontentintable">=C1=0</p>
</td>
<td rowspan="" colspan="">
<p id="par_id941535143000810" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id251535143031604" class="tablecontentintable">=C1=""</p>
</td>
<td rowspan="" colspan="">
<p id="par_id1001535143031604" class="tablecontentintable">TRUE (previously was FALSE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id201535143064244" class="tablecontentintable">=ISNUMBER(B1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id421535143064245" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id681535143124250" class="tablecontentintable">=ISNUMBER(C1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id211535143124250" class="tablecontentintable">FALSE (previously was TRUE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id221535143151829" class="tablecontentintable">=ISNUMBER(VLOOKUP(1;A1:C1;2))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id271535143151830" class="tablecontentintable">FALSE (B1)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id861535143183196" class="tablecontentintable">=ISNUMBER(VLOOKUP(1;A1:C1;3))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id371535143183197" class="tablecontentintable">FALSE (C1, previously was TRUE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id191535143228538" class="tablecontentintable">=ISTEXT(B1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id271535143228539" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id251535143279847" class="tablecontentintable">=ISTEXT(C1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id691535143279847" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id641535143301636" class="tablecontentintable">=ISTEXT(VLOOKUP(1;A1:C1;2))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id391535143301637" class="tablecontentintable">FALSE (B1, previously was TRUE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id661535143336012" class="tablecontentintable">=ISTEXT(VLOOKUP(1;A1:C1;3))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id11535143336012" class="tablecontentintable">FALSE (C1)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id661535143373396" class="tablecontentintable">=ISBLANK(B1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id141535143373396" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id281535143396581" class="tablecontentintable">=ISBLANK(C1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id81535143396581" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id31535143422248" class="tablecontentintable">=ISBLANK(VLOOKUP(1;A1:C1;2))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id871535143422248" class="tablecontentintable">TRUE (B1, previously was FALSE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id731535143454577" class="tablecontentintable">=ISBLANK(VLOOKUP(1;A1:C1;3))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id941535143454577" class="tablecontentintable">FALSE (C1)</p>
</td>
</tr>
</table>
<br>
<div class="note">
<div class="noteicon"><img src="media/icon-themes/res/helpimg/note.svg" alt="Note Icon
" title="Note Icon
" style="width:40px;height:40px;"></div>
<div class="notetext"><p>Note that Microsoft Excel behaves different and always returns a number as the result of a reference to an empty cell or a formula cell with the result of an empty cell. For example:</p></div>
</div>
<br>
<table border="1" class="" cellpadding="0" cellspacing="0">
<tr>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id251535143557533" class="tableheadintable">Case</p>
</td>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id441535143557533" class="tableheadintable">Formula</p>
</td>
<td rowspan="" colspan="" class="tableheadcell">
<p id="par_id481535143557533" class="tableheadintable">Results and comments</p>
</td>
</tr>
<tr>
<td rowspan="14" colspan="">
<p id="par_id311535143557534" class="tablecontentintable">A1: <Empty></p>
</td>
<td rowspan="" colspan="">
<p id="par_id321535143557534" class="tablecontentintable">B1: =A1</p>
</td>
<td rowspan="" colspan="">
<p id="par_id961535143557535" class="tablecontentintable">Displays 0, but is just a reference to an empty cell.</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id441535143762523" class="tablecontentintable">=ISNUMBER(A1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id881535143762523" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id751535143810734" class="tablecontentintable">=ISTEXT(A1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id211535143810734" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id961535143845607" class="tablecontentintable">=A1=0</p>
</td>
<td rowspan="" colspan="">
<p id="par_id411535143845608" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id781535143884547" class="tablecontentintable">=A1=""</p>
</td>
<td rowspan="" colspan="">
<p id="par_id521535143884547" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id121535143920008" class="tablecontentintable">=ISNUMBER(B1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id391535143920009" class="tablecontentintable">FALSE (Microsoft Excel: TRUE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id371535143961386" class="tablecontentintable">=ISTEXT(B1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id451535143961387" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id601535143981973" class="tablecontentintable">=B1=0</p>
</td>
<td rowspan="" colspan="">
<p id="par_id701535143981974" class="tablecontentintable"><a name="vtrue"></a>TRUE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id761535144016851" class="tablecontentintable">=B1=""</p>
</td>
<td rowspan="" colspan="">
<p id="par_id111535144016852" class="tablecontentintable">TRUE (Microsoft Excel: FALSE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id771535144043975" class="tablecontentintable">C1: =VLOOKUP(...) with empty cell result</p>
</td>
<td rowspan="" colspan="">
<p id="par_id311535144043975" class="tablecontentintable">displays empty (Microsoft Excel: displays 0)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id761535144080504" class="tablecontentintable">=ISNUMBER(VLOOKUP(...))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id711535144080505" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id11535144101388" class="tablecontentintable">=ISTEXT(VLOOKUP(...))</p>
</td>
<td rowspan="" colspan="">
<p id="par_id831535144101388" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id131535144123734" class="tablecontentintable">=ISNUMBER(C1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id151535144123735" class="tablecontentintable">FALSE (Microsoft Excel: TRUE)</p>
</td>
</tr>
<tr>
<td rowspan="" colspan="">
<p id="par_id981535144159183" class="tablecontentintable">=ISTEXT(C1)</p>
</td>
<td rowspan="" colspan="">
<p id="par_id101535144159184" class="tablecontentintable"><a name="vfalse"></a>FALSE</p>
</td>
</tr>
</table>
<br>
</div>
<div class="search-frame"></div>
<div class="donation-frame"></div>
<footer><div id="DEBUG" class="debug">
<h3 class="bug">Help content debug info:</h3>
<p>This page is: <a href="https://opengrok.libreoffice.org/xref/help/source/text/scalc/05/empty_cells.xhp" target="_blank">/text/scalc/05/empty_cells.xhp</a></p>
<p>Title is: Handling of Empty Cells</p>
<p id="bm_module"></p>
<p id="bm_system"></p>
<p id="bm_HID"></p>
</div></footer>
</body>
</html>