Excel Test Cell Value: Formulas for Common Checks

Formulas & functions · Updated November 18, 2026

To test whether a cell contains a particular value in Excel, use an IF formula such as =IF(A2="Paid","Match","No match"). Replace A2 with the cell you want to check and Paid with the value you expect. Excel returns Match when the value agrees and No match otherwise; the right test depends on whether you are checking exact text, a blank, a number, an error, or a partial match.

What it means to test a cell value

Testing a cell means asking a formula to evaluate something about its contents and return a result. That result might be TRUE or FALSE, a label such as Match or Review, or a calculated value. A test formula does not change the source cell: it reads its contents and displays its answer wherever you enter the formula.

For example, a status column can check each order against an expected word, while a separate check can identify missing entries. These formulas are useful for reviewing imported data, checking a list against a rule, and making a workbook respond to changes. When the source value changes, Excel normally recalculates the test result automatically.

In Microsoft Excel for Microsoft 365 on Windows, enter a formula in a cell and press Enter. If you are copying a test down a column, use a relative reference such as A2 so it changes to A3, A4, and so on. Use a dollar sign in a reference, such as $D$2:$D$10, when that part of a formula should stay fixed as you copy it.

Check for an exact value with IF

IF is the most direct way to test a value and return different results for the two outcomes. The first argument is the condition; the second is returned when it is true; the third is returned when it is false. Text values in formulas need straight double quotation marks.

With an order status in A2, where the expected text is Paid, enter =IF(A2="Paid","Match","No match"). If A2 contains Paid, the result is Match. If it contains Pending, the result is No match. The comparison is not case-sensitive in this form, so paid also matches Paid.

If you only need a logical answer rather than a label, the comparison itself is enough. With the value being checked in A2, enter =A2="Paid". The result is TRUE when the cell matches and FALSE when it does not. Logical results can be useful inside other formulas, including IF, AND, and OR.

Choose labels that make sense to someone reading the workbook. For example, use Ready and Check rather than TRUE and FALSE if the result is intended for a review list. Keep the expected value consistent: an extra space in the source text can cause a comparison to fail even when the words look alike.

Test whether a cell is blank

There are two common questions that sound similar: is a cell truly empty, and does it appear empty? ISBLANK answers the first. A direct comparison with an empty string checks the second and also treats a formula returning an empty string as empty-looking.

With A2 as the cell being checked, enter =ISBLANK(A2). It returns TRUE only when A2 is genuinely empty. A cell containing a space, a value, or a formula is not blank according to ISBLANK, even if the formula displays nothing.

With A2 still as the cell being checked, enter =IF(A2="","Blank or empty-looking","Has content"). This returns Blank or empty-looking for a truly empty cell and for a formula result of an empty string. It returns Has content if A2 contains a visible value or a space character. This distinction matters in forms and reports: a formula may make a cell look empty while it is still occupied by a formula.

If you suspect that a cell contains spaces, inspect it in the formula bar or use a cleanup method appropriate to the data. A test for an empty string will not classify a space as blank. Avoid treating every visually empty cell as truly empty without checking how the workbook was built.

Check whether a value is a number, text, or date

Excel stores different kinds of values differently. ISNUMBER can establish whether a cell contains a numeric value, while ISTEXT can identify text. Dates are stored as serial numbers in Excel, so ISNUMBER generally returns TRUE for a real Excel date as well. Number formatting changes how a value looks; it does not necessarily change its underlying type.

With a value in A2, enter =ISNUMBER(A2). If A2 contains the number 125, the result is TRUE; if it contains the text 125, the result is FALSE. This can help find numbers imported as text, which may not behave as expected in calculations or sorting.

With the same A2 cell, enter =ISTEXT(A2). It returns TRUE when A2 contains text, including a text value that looks like a number. A leading apostrophe is one common reason a number-like entry is stored as text. Do not rely on appearance alone when a calculation is giving an unexpected result.

ISNUMBER is not a date detector: it cannot distinguish a date serial from another number. If you need to validate dates, consider the workbook’s date rules and the way the values were entered, rather than assuming that every numeric value is a date. Also note that a number displayed with a currency or date format remains numeric underneath.

Check exact text and letter case

Standard text comparisons in Excel are case-insensitive. That is usually helpful for everyday labels, because Paid and PAID are treated as equal in a simple comparison. If uppercase and lowercase letters must be distinguished, use EXACT.

With A2 holding a status and Approved as the required spelling and capitalization, enter =EXACT(A2,"Approved"). The result is TRUE only when the text matches Approved with the same letter case. For example, Approved returns TRUE, while approved returns FALSE.

EXACT is useful for codes, identifiers, or data where capitalization has meaning. It checks the text precisely, so a trailing space also causes a mismatch. For ordinary status checks, the simpler equality test is often more practical. If imported text has inconsistent spaces, clean or standardize the source rather than making a comparison harder to understand.

Find a word or phrase inside a cell

An exact comparison fails when the target is only part of a longer entry. SEARCH looks for text within another text string and returns a position when it finds a match. Combining it with ISNUMBER turns that position into a straightforward TRUE or FALSE test. SEARCH is not case-sensitive.

With a note in A2, enter =ISNUMBER(SEARCH("late",A2)). If A2 contains Payment is late, the result is TRUE; if it contains Payment complete, the result is FALSE. This is useful for categorizing notes or identifying records that contain a keyword.

SEARCH can also recognize wildcard characters in its search text, so a literal asterisk or question mark needs special handling if those characters are part of the word you are seeking. If capitalization matters, FIND is the case-sensitive counterpart to SEARCH. For more complex text cleanup, it is often clearer to create a helper column that standardizes the source before testing it.

Check whether a value is on an approved list

Sometimes the question is not whether a cell equals one fixed value, but whether its value appears anywhere in a permitted list. COUNTIF can count matches in a range. Testing whether that count is greater than zero produces a TRUE or FALSE result and is convenient for a validation column.

With the value to check in A2 and allowed values in D2:D10, enter =COUNTIF($D$2:$D$10,A2)>0. If A2 appears in the allowed list, the result is TRUE; if it does not, the result is FALSE. The dollar signs keep the list range fixed when you copy the formula down, while A2 adjusts for each row.

For a more readable review sheet, wrap the test in IF. With the same layout—value in A2 and allowed values in D2:D10—enter =IF(COUNTIF($D$2:$D$10,A2)>0,"Allowed","Review"). If A2 is found in the list, Excel displays Allowed; otherwise it displays Review. Check whether your list contains duplicates if you need a count of distinct permitted values, although duplicates do not change this yes-or-no result.

Handle errors in the value being tested

A cell may contain an Excel error such as #N/A or #VALUE! rather than an ordinary value. A comparison that uses that cell can itself return an error, which can make a status column difficult to read. ISERROR tests for any error value; IFERROR lets you supply an alternative result when an expression produces an error.

With A2 as the cell being examined, enter =ISERROR(A2). It returns TRUE if A2 contains an Excel error and FALSE otherwise. This is a diagnostic check: it identifies an error but does not explain its cause.

With A2 as the value to display, enter =IFERROR(A2,"Check source"). If A2 contains an ordinary value, Excel returns that value; if A2 contains an error, the formula displays Check source instead. This can make a report tidier, but it can also hide a problem. During investigation, it is usually better to find and fix the error rather than conceal it.

Choose the right method and troubleshoot mismatches

Start by deciding what the test should mean. Use an equality comparison for one expected value, ISBLANK for a genuinely empty cell, an empty-string comparison for cells that merely look empty, ISNUMBER or ISTEXT for data type, SEARCH for a contained phrase, and COUNTIF for membership in a list. Use EXACT only when letter case matters.

If a formula reports a mismatch that seems wrong, check the source before adding complexity. Common causes include leading or trailing spaces, numbers stored as text, hidden characters from copied data, and formulas that return an empty string. Click the source cell and inspect the formula bar; change the number format temporarily if you need to understand how a number is stored. A display format can make a date, percentage, or currency value look different without changing its underlying value.

Also check the references in a copied formula. If the expected value is in a fixed cell, use an absolute reference such as $D$2 so it does not shift as you fill down. If the formula seems not to update after an edit, confirm calculation is set to Automatic on the Formulas tab under Calculation Options. In Excel for the web, formula entry and recalculation work similarly, though the ribbon layout can differ slightly. Excel for Mac has the same core functions, with some ribbon and keyboard-shortcut differences from Windows.

For a transparent workbook, put test results in a helper column and use clear headings such as Status check or On approved list. That makes the logic inspectable and easier to maintain than embedding a long condition inside a larger formula. If the rule changes, update the expected value or permitted list in one visible place rather than altering many formulas by hand.

Frequently Asked Questions

How do I check if a cell equals a value in Excel?
With the value in A2 and the expected text Paid, enter =A2="Paid" for a TRUE or FALSE result. Use IF if you prefer labels such as Match and No match.

How do I test if a cell is not blank?
With A2 as the cell to check, enter =A2<>"". This treats a truly empty cell and a formula returning an empty string as empty-looking, but a cell containing a space is not empty.

Why does ISBLANK return FALSE when the cell looks empty?
The cell may contain a formula that returns an empty string, a space, or other content. ISBLANK only returns TRUE for a cell that is genuinely empty.

Can Excel test whether a cell contains part of a word?
Yes. With the text in A2, =ISNUMBER(SEARCH("late",A2)) returns TRUE when late appears in the text, without requiring the whole cell to equal that word.

How can I test a cell against a list of values?
With the value in A2 and the allowed list in D2:D10, =COUNTIF($D$2:$D$10,A2)>0 returns TRUE when Excel finds the value in that list.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.