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

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!
Ozioma
Ozioma
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")))))
Reply
Sunny
Sunny     Ozioma
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"))))))
Reply
Report
Vinz
Vinz     Sunny
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
Reply
ganesh
ganesh
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?
Reply
Sunny
Sunny     ganesh
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:
View Attachments (1 / 5)
Reply
Report
Ola
Ola
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?
Reply
Sunny
Sunny     Ola
This comment was minimized by the moderator on the site
The cell is blank, so why not the result is blank? I am confused
Reply
Report
Kay
Kay     Sunny
This comment was minimized by the moderator on the site
How do you make the 119years, 0 months , 10 days just show a blank ?
Reply
Sunny
Sunny     Kay
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?
Reply
Report
Dan Thomas
Dan Thomas
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!
Reply
Guy
Guy
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.
Reply
George
George
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!
Reply
Sunny
Sunny     George
This comment was minimized by the moderator on the site
Sorry, George, I do not know the formula can help you.
Reply
Report
Jason
Jason
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 ^_^
Reply
DAVE
DAVE
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!
Reply
Sunny
Sunny     DAVE
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.
Reply
Report
Tim Smith
Tim Smith     DAVE
This comment was minimized by the moderator on the site
You would have to do nested IF's. One IF statement in another.
Reply
Angela
Angela
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.
Reply
Sunny
Sunny     Angela
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.
Reply
Report
bhushan KB
bhushan KB
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.
Reply
rohima
rohima
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?
Reply
john
john
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.
Reply
Sunny
Sunny     john
This comment was minimized by the moderator on the site
Yes, that is trud, but could you provide any better sulotions?
Reply
Report
DEE
DEE
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
Reply
Jason
Jason
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
Reply
CK
CK
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?
Reply
Larry
Larry
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
Reply
Hooman
Hooman
This comment was minimized by the moderator on the site
Thanks a lot!
It was absolutely helpful!
Reply
HAMISH
HAMISH
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?
Reply
Sunny
Sunny     HAMISH
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.
View Attachments (1 / 5)
Reply
Report
Andre Oliveira
Andre Oliveira
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
Reply
Andre Oliveira
Andre Oliveira
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
Reply
Report
Sunny
Sunny     Andre Oliveira
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".
Reply
Report
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations