Creating Formulas that Only Return Results from Non-Blank Cells in Microsoft Excel 2010

In this article, we perform a logical test to get results from non-blank cells.

We will use IF & ISBLANK function. The mathematical formulation is a method of Excel 2016 which let you perform many functions.

IF function performs a logic_test and returns values based on the result of the condition.
Syntax of IF function:

=IF(logical_test,[value_if_true],[ value_if_false]

ISBLANK function checks the cell and returns True or False on the basis of the cell is blank or not.
Syntax of ISBLANK function is

=ISBLANK(Cell reference)

Example: Cell A2 contains the text XYZ

                    =ISBLANK (A2),  function will return“FALSE”

             Cell A3 contains nothing

                    =ISBLANK (A3),  function will return “TRUE”

Let’s understand with an example here

Non blank 1

Formula

=IF(ISBLANK(D5)=TRUE,””, D5)

Explanation:

ISBLANK function checks the cell D5.

IF function performs a logic_test if the test is true, it returns blank value else the same value.

The resulting output will be like

Non blank 2

As you can see in the above snapshot that the function only return results from Non-Blank cells, leaving the blank cells blank

Hope you learned how to use IF and ISBLANK function in Excel from this article. You can perform these functions in Excel 2013 and 2010. Explore more articles on IF & ISBLANK function. Please mention your unresolved query in the comment box below. We will help you.

Related Articles:

How to Calculate Only If Cell is Not Blank in Excel

Adjusting a Formula to Return a Blank

Checking Whether Cells in a Range are Blank, and Counting the Blank Cells

How to SUMIF with non-blank cells in Excel

Only Return Results from Non-Blank Cells

Popular Articles:

50 Excel Shortcut to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel 2016

How to use the SUMIF Function in Excel

Users are saying about us...

  1. I have a date in Column A, and I have a formula in Column H as follows: =SUM(A3+30)
    So a date appears in Column H which is 30 days after the date in Column A. Problem is that if there is no date in Column A, this appears in Column H: #VALUE! Is there any way to prevent this? I've spent days trying to figure this out. Greatly appreciated!!!!

  2. Dan Akers' formula was a big help to me. I have a running list and wanted to auto-number each line as new items were added (ie, when something is typed into a field on row 7, another cell on that row would auto-enter a "7" to keep track of how many lines are populated). It's a stupid calculation but I didn't want to drag out 1000 numbers for empty rows. =IF(ISBLANK(B82)=FALSE,(A81+1),””) worked like a champ. Thanks again.

  3. The following formula is not working for me =IF(ISBLANK(B4)=FALSE,DAY(B4),””)

    Here B4 contain a Date. The out put is #NAME?

    My requirement is needs a blank cell instead of 0 in other cell if the B4 cell is blank.

  4. I have a spreadsheet where a due date in column H and date mailed in Column I. i have a calculation in column J to see if we met the 15 day requirement yes or no: =IF(AND(I2<=H2),"Yes","No").
    Problem is its displaying yes in all the fields that don't even have a due date yet. How can I get this not to show anything if a there is no due date entered?

  5. I have a column of times with variable blank cells between them. Columns are unit numbers.

    I want to sort the times automatically with no blank cells between them in new cells so I can use a forumla that to figures out average trip times per unit ie start time and end time

  6. Good Morning. I am trying to use Conditional Formatting with the ISBLANK.
    What I am after is if there is a Value in any CELL eg. (S2:T501), Format the Corresponding Cell in F. eg if there is a Value in S14, then F14 will be formatted. Or Do I have to Conditionally Format each Cell in Col F
    Thanking you

  7. Hi can someone please help. I want a cell to have today's date in, TODAY(), only if another cell is completed. So we only need today's date if a start date has been entered already. Can someone help me with the formular. Many thanks

    • Hi Donna,
      Let's say you put the start date in A2 and you want today's date in B2 then use this formula in B2.
      =IF(ISBLANK(A2),"",TODAY())

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube