How to Specify Non-Blank Cells in Excel
This article explains how to specify non-blank cells in Excel. When you want to conditionally handle non-blank cells, you can easily achieve this using functions like IF or COUNTIF.
Specifying Non-Blank Cells
In Excel, the condition “<>”” is used to specify non-blank cells. This condition means “not blank.” You can use this condition in functions like IF or COUNTIF to process non-blank cells.
While programming languages often use “!” for negation, Excel uses “<>”.
Solution 1: Use the IF Function to Specify Non-Blank Cells
The IF function allows you to perform specific actions on non-blank cells by using the condition “<>”””.
Example: Display “Match” for Non-Blank Cells
In the following example, if column A is not blank, column B will display “Match”, and if blank, it will display “No Match”.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data 1 | =IF(A1<>””, “Match”, “No Match”) | ||
| 2 | =IF(A2<>””, “Match”, “No Match”) | |||
| 3 | Data 3 | =IF(A3<>””, “Match”, “No Match”) |
Steps
- Enter the following formula in cell B1:
=IF(A1<>””, “Match”, “No Match”) - If cell A1 is not blank, “Match” will be displayed in B1. Otherwise, “No Match” will be displayed.
- Similarly, enter =IF(A2<>””, “Match”, “No Match”) in cell B2, and =IF(A3<>””, “Match”, “No Match”) in cell B3.
Results
After entering the formulas, the results will appear as follows:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data 1 | Match | ||
| 2 | No Match | |||
| 3 | Data 3 | Match |
Here, A1 and A3 contain “Data 1” and “Data 3,” respectively, so B1 and B3 display “Match.” A2 is blank, so B2 displays “No Match.”
Solution 2: Use the COUNTIF Function to Count Non-Blank Cells
To count the number of non-blank cells, you can use the COUNTIF function. The following formula counts non-blank cells:
=COUNTIF(A1:A3, “<>”)
Example: Count Non-Blank Cells
In the following table, the range A1:A3 is evaluated to count the non-blank cells.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Data 1 | =COUNTIF(A1:A3, “<>”) | ||
| 2 | ||||
| 3 | Data 3 |
Results
When entered, the result will show “2” as both A1 and A3 contain data.