If cell has value
The easiest way to check if a cell has a value is, in my opinion, to use the equal sign to compare the cell value with the value you are looking for.
The equal sign is a logical operator that turns the formula a logical expression and returns a boolean value, TRUE or FALSE.
Formula in cell C3:
Use double quotation marks before and after the text value in order to do the comparison. B3 is a cell reference to a specific cell at the intersection of column B and row 3.
Copy cell C3 and paste to cells below and the cell reference changes in each cell. B3 is, therefore, a relative cell reference.
If you forget the double quotation marks Excel will evaluate your text value as a built-in function or a user-defined function. If such function can't be found Excel returns #NAME? error.
Formula in cell D8:
Don't use double quotation marks with numbers, if you do Excel will evaluate your number as a text value and the comparison will never return TRUE.
Example,
returns FALSE even though cell B10 has number 7.
The comparison made by the equal sign is, however, not case-sensitive. Use the EXACT function to perform a case-sensitive comparison, see picture above.
A formula like this one has one of the values hardcoded into the formula:
If you want to compare another value you must change each formula in every cell which is not at all efficient.
The picture above shows a formula that compares two cell values, however, one of the cell references are locked to cell C9.
How did I enter this formula?
- Double press with left mouse button on cell C3 with left mouse button.
- Type =B3=C9
- Press function key F4 to automatically change C9 to $C$9.
- Press Enter
- Copy cell C3
- Paste to cells below.
If you now examine the formula you will see that the first cell reference B3 in the formula changes in each cell.
This happens with relative cell reference whereas an absolute cell reference doesn't change.
Having cell references in your formulas allows you to quickly change a value without adjusting the cell formulas at all.
Try it yourself, change the value in cell C9 to Banana and check column C for changes. Cell C6 is now TRUE and C5 is FALSE.
The equal sign (=) is one out of 3 different logical operators, the picture above shows you the greater than sign (>).
You can combine these three logical operators to perform different logical expressions, see table below.
| Logical operator | Description | Example |
| < | less than | B3<$C$9 |
| > | greater than | B3>$C$9 |
| <= | less than or equal to | B3<=$C$9 |
| >= | greater than or equal to | B3>=$C$9 |
| = | equal to | B3=$C$9 |
| <> | not equal to | B3<>$C$9 |
The formula in column C returns TRUE if the value in column B is larger than 4.
Now you know what a logical expression is which is an essential part of an IF function.
Get Excel *.xlsx file
Logic category
Nested IF statements in a formula are multiple combined IF functions so more conditions and outcomes become possible. They all are […]
The picture above shows different values in column B and a formula in column C that tries to identifies the […]
This article demonstrates formulas that perform a partial match for a given cell using multiple strings specified in cells F2 […]
Excel categories
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
