Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe de cel leeg te houden bij het toepassen van de formule totdat gegevens in Excel zijn ingevoerd?

Als u in Excel een formule toepast op een kolombereik, wordt het resultaat weergegeven als nul terwijl de referentiecellen leeg zijn in de formule. Maar in dit geval wil ik de cel leeg houden wanneer ik de formule toepas totdat de referentiecel met gegevens is ingevoerd, als er trucs zijn om ermee om te gaan?
doc blanco blijven tot 1

Houd cel leeg totdat gegevens zijn ingevoerd

pijl blauw rechts bel Houd cel leeg totdat gegevens zijn ingevoerd

Er is eigenlijk een formule die u kan helpen de formulecel leeg te houden totdat gegevens in referentiecellen worden ingevoerd.

Hier kunt u bijvoorbeeld het verschil berekenen tussen kolom Waarde 1 en kolom Waarde 2 in kolom Verschillen, en u wilt de cel leeg houden als er enkele lege cellen zijn in de kolom Waarde 1 en kolom Waarde 2.

Selecteer de eerste cel waarin u het berekende resultaat wilt plaatsen, typ deze formule = ALS (OF (ISLEEG (A2), ISLEEG (B2)), "", A2-B2)en sleep de vulgreep naar beneden om deze formule toe te passen op de cellen die je nodig hebt.
doc blanco blijven tot 2

In de formule zijn A2 en B2 de referentiecellen in de formule die u wilt toepassen, A2-B2 is de berekening die u wilt gebruiken.

Batch lege rijen of kolommen invoegen in een specifiek interval in Excel-bereik
Als u om de rij lege rijen wilt invoegen, moet u ze mogelijk een voor een invoegen, maar de Voeg lege rijen en kolommen in of Kutools for Excel kan deze klus binnen enkele seconden oplossen. Klik voor een gratis proefperiode van 30 dagen!
doc lege rij kolom invoegen
Kutools for Excel: met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen.

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
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.
    Hooman · 1 years ago
    Thanks a lot!
    It was absolutely helpful!
  • To post as a guest, your comment is unpublished.
    Larry · 1 years 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
  • To post as a guest, your comment is unpublished.
    CK · 1 years 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?
  • To post as a guest, your comment is unpublished.
    Jason · 2 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
  • To post as a guest, your comment is unpublished.
    DEE · 2 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
  • To post as a guest, your comment is unpublished.
    john · 2 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.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Yes, that is trud, but could you provide any better sulotions?
  • To post as a guest, your comment is unpublished.
    rohima · 2 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?
  • To post as a guest, your comment is unpublished.
    bhushan KB · 2 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.
  • To post as a guest, your comment is unpublished.
    Angela · 2 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.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years 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 · 2 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!
    • To post as a guest, your comment is unpublished.
      Tim Smith · 2 years ago
      You would have to do nested IF's. One IF statement in another.
    • To post as a guest, your comment is unpublished.
      Sunny · 2 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.
  • To post as a guest, your comment is unpublished.
    Jason · 2 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 · 3 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 · 3 years ago
      Sorry, George, I do not know the formula can help you.
  • To post as a guest, your comment is unpublished.
    Guy · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 4 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 · 4 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 · 4 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 · 4 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"))))))