ThisCalculator

Everyday

If Not Blank Multiple Cells? Excel Formula Guide

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This article explains how to check if multiple cells are not blank in Excel and return a specific result when they are not. You will learn the correct formulas using IF, AND, ISBLANK, and COUNTA, along with practical examples and common troubleshooting tips.

Formula

The core formula to test if multiple cells are not blank is:

=IF(AND(A1<>””, B1<>””, C1<>””), “Not Blank”, “Blank”)

Here, A1, B1, and C1 are the cells you are checking. The <>”” operator means “not equal to empty text,” which effectively checks if a cell is not blank. The AND function returns TRUE only if all conditions are met. If all cells are non-blank, the formula returns “Not Blank”; otherwise, it returns “Blank”.

For checking if any cell is not blank, replace AND with OR:

=IF(OR(A1<>””, B1<>””, C1<>””), “Not Blank”, “Blank”)

Worked Example

Suppose you have three cells: A1 contains “Apple”, B1 is empty, and C1 contains “Cherry”. You want to know if all three are not blank.

  1. Write the formula: =IF(AND(A1<>””, B1<>””, C1<>””), “Not Blank”, “Blank”)
  2. Evaluate each condition: A1<>”” is TRUE (has “Apple”), B1<>”” is FALSE (empty), C1<>”” is TRUE (has “Cherry”).
  3. AND returns FALSE because not all are TRUE.
  4. Therefore, the formula returns “Blank”.

If you use the OR version, it returns TRUE because at least one cell is non-blank, so the result is “Not Blank”.

When to Use This Formula

This formula is useful in data entry forms, project trackers, and dashboards where you need to ensure that all required fields are filled before processing. For example, you might want to highlight incomplete rows or prevent calculations if any input is missing.

You can also use it to conditionally format cells, show warnings, or trigger other formulas. The same logic works in Google Sheets and most spreadsheet applications.

How to Read the Result

The formula returns a text label you define (e.g., “Not Blank” or “Blank”). You can change these to any text, such as “Complete” or “Incomplete”, or return numbers like 1 and 0 for further calculations.

If you need to count how many cells are not blank, use =COUNTA(A1:C1). This returns the number of non-empty cells in the range. For a quick check if any cell is blank, use =COUNTBLANK(A1:C1)>0.

Common Mistakes and Misconceptions

  • Using ISBLANK with a range: =ISBLANK(A1:C1) does not work as expected because it only checks the first cell. You must use ISBLANK for each cell separately or use COUNTA.
  • Confusing empty text with blank: A cell containing a formula that returns “” (empty string) is not truly blank. <>”” treats it as non-blank, while ISBLANK returns FALSE for such cells. Decide which behavior you need.
  • Forgetting to lock cell references: When copying the formula down a column, use absolute references (e.g., $A$1) if you want to check the same cells; otherwise, use relative references to check each row.
  • Using AND/OR incorrectly: AND requires all conditions TRUE; OR requires at least one. Mixing them up gives wrong results.

Limitations and Alternatives

The formula works well for a small, fixed number of cells. For a large range, it becomes tedious to list every cell. In that case, use =IF(COUNTA(A1:C1)=3, “Not Blank”, “Blank”) where 3 is the number of cells in the range. This is more concise and easier to maintain.

Another alternative is to use =IF(COUNTBLANK(A1:C1)=0, “Not Blank”, “Blank”). This counts blank cells and returns TRUE if there are none.

For dynamic ranges, consider using ARRAYFORMULA in Google Sheets or dynamic array functions in Excel 365, such as =IF(AND(A1:A3<>””), “Not Blank”, “Blank”) (though this requires entering as an array formula in older versions).

Frequently Asked Questions

How do I check if any cell in a range is not blank?

Use =IF(COUNTA(A1:C1)>0, “Not Blank”, “Blank”). COUNTA counts all non-empty cells; if the count is greater than zero, at least one cell is not blank.

What is the difference between ISBLANK and <>””?

ISBLANK returns TRUE only if a cell is truly empty (no formula, no value). The <>”” operator returns TRUE for any cell that does not contain an empty string, including cells with formulas that produce “”. This distinction matters when dealing with formulas that return empty text.

Can I use this formula with conditional formatting?

Yes. In conditional formatting, use a formula like =AND($A1<>””, $B1<>””) to highlight rows where both cells are filled. Adjust the range and references as needed.

How do I check if all cells in a row are blank?

Use =COUNTBLANK(A1:C1)=3 (where 3 is the number of cells). If the count of blank cells equals the total number of cells, the row is entirely blank.

Why does my formula return FALSE when I use ISBLANK on a range?

ISBLANK is designed for a single cell. When you give it a range, it only evaluates the first cell. To check multiple cells, use ISBLANK separately for each cell, or use COUNTA/COUNTBLANK.

Does this work in Google Sheets?

Yes, the formulas are identical in Google Sheets. The only difference is that Google Sheets may require array formulas for certain operations, but the basic IF, AND, OR, COUNTA, and COUNTBLANK functions work the same.

Related

Formula used
in count | Number of cells to check | - | 3 | 1
in blank | Number of blank cells | - | 0 | 0
out all_not_blank | All cells not blank | - | IF(blank = 0, 1, 0) | 0
out any_not_blank | Any cell not blank | - | IF(blank < count, 1, 0) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.