Excel #OVERLOOP! (#SPILL!) fout oplossen: hoe doe je dat?

Sinds Excel dynamische matrices ondersteunt (Microsoft 365 en Excel 2021 en nieuwer) kan één formule meerdere resultaten teruggeven die zich vanzelf over de omliggende cellen verspreiden. Dat verspreiden heet overlopen. Lukt dat niet, dan verschijnt de #OVERLOOP!-fout en blijft de rest van je berekening leeg. In deze handleiding lees je precies waarom dat gebeurt en hoe je het per situatie oplost.

De #OVERLOOP!-fout betekent dat Excel het resultaat nergens kwijt kan

De #OVERLOOP!-fout (in het Engels #SPILL!, in sommige oudere Nederlandse builds #OVERLOPEN!) verschijnt wanneer een formule meerdere waarden produceert, maar het gebied waar die waarden terecht moeten komen niet volledig leeg is. Excel noemt dat gebied het overloopbereik.

Een concreet voorbeeld. Zet je in cel D2 de formule =A2:A100, dan wil Excel 99 waarden neerzetten in D2 tot en met D100. Staat er in D50 al iets, dan kan de formule niet volledig overlopen en toont D2 de melding #OVERLOOP! in plaats van de rij getallen.

Klik je de foutcel aan, dan tekent Excel een gestippelde blauwe rand rond de plek waar de waarden hadden moeten komen. Zo zie je meteen hoe groot het overloopbereik is en waar de blokkade zit. De foutmelding is dus geen bug in je formule, maar een botsing met iets dat al in de weg staat.

Belangrijk om te weten: alleen de eerste cel van het bereik bevat de echte formule. De overige cellen zijn een echo van die ene formule en herken je aan de lichtgrijze weergave in de formulebalk. Verwijder je de bovenste cel, dan verdwijnt het hele bereik. Dit gedrag verklaart waarom je een overlopende formule alleen in de linkerbovenhoek kunt bewerken.

De fout treedt op zodra Excel de formule berekent, dus meteen na het invoeren of na een wijziging in de brondata. Groeit je lijst en botst de matrix daardoor tegen een cel die eerder wel vrij was, dan verandert een werkende formule alsnog in #OVERLOOP!. Dat maakt de fout lastig te reproduceren: het bestand werkte gisteren, maar vandaag heeft iemand een aantekening onder de formule gezet.

Enkele functies veroorzaken veruit de meeste overloopfouten

De #OVERLOOP!-fout hoort bij formules die per definitie meerdere waarden teruggeven. Herken je die functies, dan weet je meteen waar je op moet letten.

• FILTER geeft alle rijen terug die aan een voorwaarde voldoen. Het aantal rijen wisselt met de data, dus het overloopbereik groeit en krimpt mee.
• UNIEK haalt de unieke waarden uit een lijst. Bij nieuwe categorieën wordt het resultaat langer.
• SORTEREN en SORTEREN.OP geven een hele lijst gesorteerd terug.
• REEKS genereert een reeks getallen, bijvoorbeeld =REEKS(10) voor 1 tot en met 10.
• X.ZOEKEN kan meerdere kolommen tegelijk teruggeven en overloopt dan naar rechts.

Omdat het resultaat van deze functies van tevoren niet vaststaat, is het lastiger om precies genoeg ruimte vrij te houden. Juist daarom lopen ze het vaakst tegen een bezette cel aan. Wie regelmatig met opzoekformules werkt die een matrix teruggeven, vindt in VERT.ZOEKEN vs X.ZOEKEN uitleg over wanneer X.ZOEKEN een heel bereik oplevert.

Een blokkerende cel verwijderen lost verreweg de meeste gevallen op

De meest voorkomende oorzaak is simpel: er staat iets in het overloopbereik. Excel helpt je die cel direct te vinden.

1. Selecteer de cel met de #OVERLOOP!-melding.
2. Klik op het gele waarschuwingsdriehoekje dat links van de cel verschijnt.
3. Kies in het menu Cellen met problemen selecteren. Excel springt nu direct naar de cel of cellen die het overlopen tegenhouden.
4. Verwijder of verplaats de inhoud van die cellen. Zodra het bereik leeg is, vult de formule zichzelf aan.

Let op onzichtbare inhoud. Een spatie, een handmatige regeleinde of een cel met witte tekst telt ook als bezet. Twijfel je, selecteer dan het hele overloopbereik en druk op Delete voordat je de formule opnieuw invoert.

Soms zit de blokkade niet in gewone tekst maar in opmaak, zoals een cel die eerder een formule bevatte en nu een lege string ("") teruggeeft. Zo'n cel oogt leeg maar telt niet als leeg. Controleer dat met de functie =ISLEEG(D50): geeft die ONWAAR, dan staat er iets wat je eerst moet opruimen.

Elke #OVERLOOP! valt onder één van zes oorzaken

Excel vermeldt de precieze reden in de tekst achter het foutdriehoekje. Deze tabel koppelt elke reden aan de bijbehorende oplossing.

Verwijzen naar een hele kolom laat het bereik buiten het werkblad lopen

Een veelgemaakte fout is een dynamische functie loslaten op een hele kolom. Neem =UNIEK(A:A) in cel C1. Excel wil de unieke waarden onder C1 plaatsen, maar omdat de bron uit ruim een miljoen rijen bestaat, zou het resultaat voorbij de onderste rij van het werkblad reiken. Dat kan niet, en dus verschijnt #OVERLOOP! met de melding dat het bereik buiten het werkblad valt.

De oplossing is een begrensd bereik gebruiken. Weet je dat je data tot rij 5000 loopt, schrijf dan:

=UNIEK(A2:A5000)

Wil je toch met de hele kolom werken zodat nieuwe rijen automatisch meetellen, maak van de brongegevens dan een Excel-tabel (Ctrl+T) en verwijs naar de tabelkolom. Die groeit alleen tot de laatste gevulde rij en niet tot het einde van het werkblad, waardoor de fout uitblijft.

Samengevoegde cellen en Excel-tabellen blokkeren het overlopen altijd

Twee oorzaken uit de tabel komen zo vaak voor dat ze een aparte behandeling verdienen.

Samengevoegde cellen. Een overlopende formule kan niet in een samengevoegde cel schrijven. Selecteer het overloopbereik, ga naar Start en zet de knop Samenvoegen en centreren uit. Wil je de visuele opmaak houden, gebruik dan Celeigenschappen (Ctrl+1) met de uitlijning Centreren over selectie in plaats van samenvoegen. Zo behoud je het gecentreerde uiterlijk zonder de cellen fysiek samen te voegen.

Excel-tabellen. Binnen een tabel die je met Ctrl+T hebt gemaakt, kan een dynamische matrixformule niet overlopen. Je hebt twee opties: klik in de tabel, ga naar Tabelontwerp en kies Converteren naar bereik, of plaats de matrixformule in een cel buiten de tabel. Zet de formule niet in een tabelkolom als je meerdere resultaten verwacht. Een tabel is prima als bron voor een overlopende formule, maar niet als plek waar het resultaat terechtkomt.

Met het @-teken dwing je één waarde af, met # verwijs je naar het hele bereik

Soms wil je juist geen overloop. Het at-teken (de operator voor impliciete doorsnede) beperkt een formule tot één enkele waarde. Zet je een @ voor de verwijzing, dan pakt Excel alleen de waarde op dezelfde rij:

=@A2:A100

Deze aanpak is handig als je de oude, niet-overlopende werking van vroegere Excel-versies wilt nabootsen, bijvoorbeeld bij het overzetten van een bestaand bestand waarin de formule per rij één resultaat hoorde te geven.

Andersom kun je een geslaagd overloopbereik in één keer aanspreken met de overloopverwijzing, het hekje. Staat je matrix in D2, dan verwijst D2# naar het volledige bereik dat de formule vult. Handig voor vervolgberekeningen die automatisch meegroeien:

=SOM(D2#)
=AANTAL(D2#)

Voeg je later rijen toe aan de bron, dan groeit ook de som mee, zonder dat je het bereik hoeft aan te passen. Deze overloopverwijzing werkt ook in grafieken en gegevensvalidatie, zodat een keuzelijst automatisch de nieuwste items toont.

Een FILTER-formule herstellen laat de aanpak in de praktijk zien

Neem een concreet geval. In cel F2 staat =FILTER(A2:C500; B2:B500="Noord") om alle rijen van regio Noord te tonen. De formule geeft #OVERLOOP! omdat er in kolom G en H, binnen het overloopbereik, nog oude aantekeningen staan.

1. Klik op F2 en daarna op het gele driehoekje; kies Cellen met problemen selecteren. Excel selecteert de gevulde cellen in G en H.
2. Verplaats die aantekeningen naar een leeg gebied verderop, bijvoorbeeld kolom M.
3. Zodra het bereik vrij is, vult FILTER alle Noord-rijen in vanaf F2 naar rechts en beneden.

Twijfel je of het resultaat te groot wordt, tel dan eerst hoeveel rijen aan de voorwaarde voldoen met =AANTAL.ALS(B2:B500; "Noord"). Weet je dat aantal, dan reserveer je precies genoeg lege rijen onder F2 en voorkom je een herhaling van de fout.

Ruimte vrijhouden voorkomt #OVERLOOP! voordat hij verschijnt

Je voorkomt de fout het makkelijkst door je werkblad zo in te richten dat matrixformules altijd ruimte hebben.

• Geef elke overlopende formule een eigen kolom en houd de cellen eronder leeg.
• Reserveer minstens zoveel lege rijen als je bronbereik telt.
• Verwijs niet naar hele kolommen (A:A) naast een overlopende formule; gebruik een begrensd bereik of een tabelkolom.
• Vermijd vluchtige functies binnen een matrix als de omvang moet vastliggen.
• Zet niet meerdere overlopende formules vlak onder elkaar; laat ruimte voor de langste variant.
• Bewaak dat collega's die het bestand delen niet in het overloopbereik typen; een korte instructie of een gekleurde markering onder de formule helpt daarbij.

Krijg je toch een foutmelding die op een verwijzingsprobleem lijkt in plaats van op ruimtegebrek, kijk dan bij de aanpak in de #VERW!-fout oplossen: oorzaak en herstel. Meer achtergrond over het overloopbereik en de precieze meldingen vind je in de officiële uitleg van Microsoft.