Log in  \/ 
x
or
x
x
Register  \/ 
x

or
News!

90,000 users can browse or edit multiple Office documents in one single tabbed window just like browsing websites in browser.

Join and Have a Free Try!

How to keep cell blank when applying formula until data entered in Excel?

In Excel, if you apply a formula to a column range, the result will be displayed as zero while the reference cells are blank in the formula. But in this case, I want to keep the cell empty when apply formula until the reference cell entered with data, if there are any tricks to handle it?
doc keep blank until 1

Keep cell blank until data entered in

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble Keep cell blank until data entered in

Actually, there is a formula that can help you to keep the formula cell empty until data entered in reference cells.

For instance, here to calculate the difference between column Value 1 and column Value 2 in column Differences, and you want to keep the cell blank if there are some blank cells in the column Value 1 and column Value2.

Select first cell that you want to place the calculated result, type this formula =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2), and drag fill handle down to apply this formula to the cells you need.
doc keep blank until 2

In the formula, A2 and B2 are the reference cells in the formula you want to apply, A2-B2 are the calculation you want to use.

Fill Blank Cell
In some cases, you may want to fill the blank cell based on the above cell or below cell, you can apply Kutools for Excel's Fill Blank Cellss utility to quickly handle it.
Go to Download
doc fill blank cell

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jason · 3 months ago
    If I have macros that recalls the last cell in a column that has data in it and then displays specified cells data in another cell, how do I use a table for the data but still have the cells that have no value in them show blank so my macros works.
    These are the two macros I have running.
    When I copy the cell it bring the top and bottom lines and there are no other table layouts that do not have lines in them.

    Sub Current_Status()
    Range("F5").Value2 = Range("D10:D100").End(xlDown).Offset(0, 8).Value2
    Range("H5").Value2 = Range("D10:D100").End(xlDown).Offset(0, 11).Value2
    Range("G5").Value2 = Range("D10:D100").End(xlDown).Offset(0, 9).Value2
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("D11:D100")) Is Nothing Then
    Call Current_Status
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    DEE · 7 months ago
    Can someone help me. I already have a formula to identify gender in excel. How to keep the cell blank if there is no identical number in the excel? Because if there is no data, it turns into #VALUE
  • To post as a guest, your comment is unpublished.
    john · 7 months ago
    This suggestion is not correct. Putting a double quote ( " " ) in an excel formula, does not keep the cell blank. It simply enters a blank string which simply is not visible. Blank string makes the cell non-empty and that particular cell is no longer blank. If the cell is checked with the isblank formula, you will notice that it is not blank anymore. So, you cannot treat that cell as a blank cell in another formula.
    • To post as a guest, your comment is unpublished.
      Sunny · 6 months ago
      Yes, that is trud, but could you provide any better sulotions?
  • To post as a guest, your comment is unpublished.
    rohima · 8 months ago
    Hi Can someone help me. i have this formula in a cell =DAYS360($L4,$N4,TRUE ) which counts the number of days between two dates however there is a number populated in the cell although there is no value in the dates cell. I want this to populate when i input a date in one of the other cells but can not seem to do so. Can anyone help?
  • To post as a guest, your comment is unpublished.
    bhushan KB · 10 months ago
    =IF(ISNUMBER(SEARCH("Live",'PIN-code Data'!D10)),'PIN-code Data'!B10,"")
    is my formula - gives B cell data, incase D has Live text in it, I do not want excel to leave the cell blank incase D doesn't have Live but it should search for next D cell and give value in same cell.
  • To post as a guest, your comment is unpublished.
    Angela · 11 months ago
    Can someone help with my formula?? =IF(A1="SPEC", I1+7, I1+21) I want to keep the date in column J blank until data is entered in A1 and I1. Currently, it shows the result as 1/21/00.
    • To post as a guest, your comment is unpublished.
      Sunny · 6 months ago
      Hi, Angela, could you describe your question with more details? I could not understand it.
  • To post as a guest, your comment is unpublished.
    DAVE · 11 months ago
    HOW WOULD I GET THE REFERENCE CELL (IN THIS CASE O7) TO REMAIN BLANK UNTIL A VALUE IS ENTERED INTO K7? HERE IS MY FORMULA: =IF(K7<2,"MOQ NOT REACHED","")

    THANKS!
    • To post as a guest, your comment is unpublished.
      Tim Smith · 4 months ago
      You would have to do nested IF's. One IF statement in another.
    • To post as a guest, your comment is unpublished.
      Sunny · 6 months ago
      Hi, Dave, I do not understand you question, but here is a formula =IF(ISBLANK($D5),"",8) which will display 8 in the formula cell if D5 entered with data, maybe you can change it for your need.
  • To post as a guest, your comment is unpublished.
    Jason · 1 years ago
    You sir/ma'am, are a genius.... thank you

    NOTE: this also works with Google Sheets ^_^
  • To post as a guest, your comment is unpublished.
    George · 1 years ago
    Hello to all. I have a similar problem. This formula asks the result to be Day if the dates are the same and Swing if they are different. How can I leave the result blank until there is a date in column K6?
    =IF(D6=K6,"Day", "Swing")

    Thanks!
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      Sorry, George, I do not know the formula can help you.
  • To post as a guest, your comment is unpublished.
    Guy · 1 years ago
    Thanks for posting. This helped me =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2) to condition cells to be blank.
  • To post as a guest, your comment is unpublished.
    Dan Thomas · 1 years ago
    THANK YOU THANK YOU THANK YOU! I was scouring the internet for this answer for hours, and you provided it! THANK YOU THANK YOU THANK YOU!
  • To post as a guest, your comment is unpublished.
    Ola · 1 years ago
    Please can you kindly tell me how to make DATEDIF(0,B10,"y")&" years "&DATEDIF(0,B10,"ym")&" months "&DATEDIF(0,B10,"md")&" days " display blank while cell B10 is blank?
    • To post as a guest, your comment is unpublished.
      Sunny · 1 years ago
      The cell is blank, so why not the result is blank? I am confused
      • To post as a guest, your comment is unpublished.
        Kay · 1 years ago
        How do you make the 119years, 0 months , 10 days just show a blank ?
        • To post as a guest, your comment is unpublished.
          Sunny · 1 years ago
          Hello, Kay, what is your condition? All cells that contain 119 years, 0 months, 10 days will be display a blank?
  • To post as a guest, your comment is unpublished.
    ganesh · 2 years ago
    i want to create a excel sheet with a formula in f column. ( like f column = c + d column ). and other cells remain blank and saved. if later i fill the data in c and f columns means ,it should do the calculation as per the f column formula and should show me the result in f column.is it possible?
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hello, the formula cells will be shown as zero while the reference cells have no values in general. If you want to display the zero as blank, you can go to Option dialog to uncheck the Show a zero cells that have zero value option, and then the formula cells will keep blank untile the reference cells entered with values. See screenshot:
  • To post as a guest, your comment is unpublished.
    Ozioma · 2 years ago
    Please how can i make this formula to return blank. =IF(D7>69,"A",IF(D7>59,"B",IF(D7>49,"C",IF(D7>44,"D",IF(D7>39,"E","F")))))
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Hello, if you want to display blank while the D7 is blank, you can use this formula =IF(D7>69,"A",IF(D7>59,"B",IF(D7>49,"C",IF(D7>44,"D",IF(D7>39,"E",IF(ISBLANK(D7),"","F"))))))