Note: The other languages of the website are Google-translated. Back to English

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


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.


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!
doc insert blank row column
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.
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
Comments (33)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
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")))))
This comment was minimized by the moderator on the site
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"))))))
This comment was minimized by the moderator on the site
How can a no data inputed will still be blank for Max function concerning 3 non adjacent cells

The function is working
=Max(A1,C1,E1) with its value
But when i drag the data
The blank data becomes 0 automatically and i just want it to remain blank until I put a data on A,C&E
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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:
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
The cell is blank, so why not the result is blank? I am confused
This comment was minimized by the moderator on the site
How do you make the 119years, 0 months , 10 days just show a blank ?
This comment was minimized by the moderator on the site
Hello, Kay, what is your condition? All cells that contain 119 years, 0 months, 10 days will be display a blank?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Thanks for posting. This helped me =IF(OR(ISBLANK(A2),ISBLANK(B2)), "", A2-B2) to condition cells to be blank.
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
Sorry, George, I do not know the formula can help you.
This comment was minimized by the moderator on the site
You sir/ma'am, are a genius.... thank you

NOTE: this also works with Google Sheets ^_^
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
You would have to do nested IF's. One IF statement in another.
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Hi, Angela, could you describe your question with more details? I could not understand it.
This comment was minimized by the moderator on the site
=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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
Yes, that is trud, but could you provide any better sulotions?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
Thanks a lot!
It was absolutely helpful!
This comment was minimized by the moderator on the site
I have created simple ss to calculated days from a set date in this case E2 with G2 = E2 +30

How do I hide the result in G2 until a date is entered into E2?

Is there a standard way of doing this in excel for multiple cells at one time?
This comment was minimized by the moderator on the site
Hi, HAMISH, take A1 is the cell used to enter date, C1 is the cell that used to add 30, now in cell C1, type the formula =IF(ISBLANK(A1), "", A1+30), then the result in C1 will be shown date + 30 if a date entered in cell A1, otherwise, it keep blank. Please see the gif attached below.
This comment was minimized by the moderator on the site
Olá amigos, quero parabenizar a página pelas informações que me ajudaram e resolver um problema, porem agora preciso que um campo de data seja atualizada quando o campo renovação estiver como SIM, abaixo segue uma descrição e dados para ajudar, já tentei criar essa formula mais não consegui:

Celular Formato Conteúdo Descrição
A1 Data Abreviada 12/11/2020 Dt. Assinatura contrato
B1 Geral 12 Vigência (Meses)
C1 Geral Sim ou Não Renovação Automática
D1 Data Abreviada Formula Data Termino Contrato

Objetivo: fazer com que o Excel atualize a data de vencimento do contrato, somando a quantidade de meses da vigência, caso a renovação automática esteja "SIM" e se tiver "NÂO" ele não somará, assim um contrato que foi assinado em janeiro de 2021 e já teve sua renovação automática de 12 meses aplicada deve ter a data de vencimento atualizada para janeiro de 2023, se tivermos visualizando a planilha a partir de fevereiro de 2022, como seria essa formula?
Atualmente essa célula já tem a seguinte formula que deve permanecer:
=SE(OU(ÉCÉL.VAZIA(A1);ÉCÉL.VAZIA(B1));"";(DATAM(A1;B1)))
Essa formula além de somar a data de assinatura do contrato e a vigência ela só apresenta o resultado se houver informações nas células A1 e B1, se não deixa vazia

Se puderem me ajudar agradeço
This comment was minimized by the moderator on the site
Olá pessoal, parabéns pelo site, tem me ajudado bastante, segue abaixo um problema que ainda não consegui resolver, são várias situações em uma formula, segue descrição do que preciso e dados para criar o ambiente para que possa validar e quem sabe me ajudar com essa função:

Objetivo: fazer com que o Excel atualize a data de vencimento do contrato, somando a quantidade de meses da vigência, caso a renovação automática esteja "SIM" e se tiver "NÂO" ele não somará, assim um contrato que foi assinado em janeiro de 2021 e já teve sua renovação automática de 12 meses aplicada deve ter a data de vencimento atualizada para janeiro de 2023, se tivermos visualizando a planilha a partir de fevereiro de 2022, como seria essa formula?
Atualmente essa célula D1 já tem a seguinte formula que deve permanecer:
=SE(OU(ÉCÉL.VAZIA(A1);ÉCÉL.VAZIA(B1));"";(DATAM(A1;B1)))
Essa formula além de somar a data de assinatura do contrato e a vigência ela só apresenta o resultado se houver informações nas células A1 e B1, se não deixa vazia

Celular Formato Conteúdo Descrição
A1 Data Abreviada 12/11/2020 Dt Assinatura contrato
B1 Geral 12 Vigência (Meses)
C1 Geral Sim ou Não Renovação Automática
D1 Data Abreviada Formula Data Termino Contrato
This comment was minimized by the moderator on the site
Hi, Andre Oliveria, if cell A1 contains date and contract name, you need this formula
=MID(A1,MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),LEN(A1)+1)),LOOKUP(1,0*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))) + 1 - MIN(IFERROR(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789",1)),LEN(A1)+1)))
to extract the date only, then use below formula
=IF(OR(ISBLANK(B1),ISBLANK(C1)),"",IF(EDATE(B1,C1)<=NOW(),EDATE(B1,C1+1), IF(LOWER(D1)="yes", EDATE(B1,C1*2+1), EDATE(B1,C1+1))))
to get the final date.
B1 is the extracted start date, C1 is the months, D1 is "yes"or"no".
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations