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?

Keep cell blank until data entered in

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.

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.

Batch insert blank rows or columns in a specific interval in Excel range

If you want to insert blank rows in every other row, you may need to insert them one by one, but the Insert Blank Rows & Columns of Kutools for Excel can solve this job in seconds. Click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

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.

Read More... Free Download... Purchase...

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!

Read More... Free Download... Purchase...

27 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

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.
Hooman · 6 months ago
Thanks a lot!
It was absolutely helpful!
- Reply
To post as a guest, your comment is unpublished.
Larry · 8 months ago
I have 2 columns one for due date another for overdue.In the overdue column i have due date cell minus Today(). I then drag that down the column. If i haven't yet put a date in the due date cell I would like to add an additonal formula that says if the due date is blank then its 0
- Reply
To post as a guest, your comment is unpublished.
CK · 8 months ago
Hi I want to create a sheet where in a columns I insert data in my case in one column Weight, another Height and 3rd for Age and those data are part of a formula. namely: BMRw = 655.1+(9.563 x Weight value)+(1.85 x Height value) - (4.676 x Age)
So my idea is to have in a column the data and inserting the data for the 3 variables I have to have a cell that contains the BMRw.

Can somebody stretch a hand?
- Reply
To post as a guest, your comment is unpublished.
Jason · 1 years 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
- Reply
To post as a guest, your comment is unpublished.
DEE · 1 years 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
- Reply
To post as a guest, your comment is unpublished.
john · 1 years 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.
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Yes, that is trud, but could you provide any better sulotions?
  
  Reply
To post as a guest, your comment is unpublished.
rohima · 1 years 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?
- Reply
To post as a guest, your comment is unpublished.
bhushan KB · 1 years 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.
- Reply
To post as a guest, your comment is unpublished.
Angela · 1 years 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.
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years ago
  
  Hi, Angela, could you describe your question with more details? I could not understand it.
  
  Reply
To post as a guest, your comment is unpublished.
DAVE · 1 years 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!
- Reply
- To post as a guest, your comment is unpublished.
  Tim Smith · 1 years ago
  
  You would have to do nested IF's. One IF statement in another.
  
  Reply
- To post as a guest, your comment is unpublished.
  Sunny · 1 years 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.
  
  Reply
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 ^_^
- Reply
To post as a guest, your comment is unpublished.
George · 2 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!
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  Sorry, George, I do not know the formula can help you.
  
  Reply
To post as a guest, your comment is unpublished.
Guy · 2 years ago
Thanks for posting. This helped me =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2) to condition cells to be blank.
- Reply
To post as a guest, your comment is unpublished.
Dan Thomas · 2 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!
- Reply
To post as a guest, your comment is unpublished.
Ola · 2 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?
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 2 years ago
  
  The cell is blank, so why not the result is blank? I am confused
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Kay · 2 years ago
  
  How do you make the 119years, 0 months , 10 days just show a blank ?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Sunny · 2 years ago
  
  Hello, Kay, what is your condition? All cells that contain 119 years, 0 months, 10 days will be display a blank?
  
  Reply
To post as a guest, your comment is unpublished.
ganesh · 3 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?
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 3 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:
  
  1.png
  
  Reply
To post as a guest, your comment is unpublished.
Ozioma · 3 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")))))
- Reply
- To post as a guest, your comment is unpublished.
  Sunny · 3 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"))))))
  
  Reply

Login to your account

Password Reset