Excel #VERW! (#REF!) fout oplossen: oorzaak en herstel

De #VERW!-fout is een van de vervelendste foutmeldingen in Excel, omdat hij vaak pas opduikt nadat je iets hebt verwijderd of verplaatst. Anders dan een tikfout wijst hij op een verwijzing die letterlijk niet meer bestaat. Hieronder lees je waar dat vandaan komt, hoe je het herstelt en hoe je voorkomt dat het opnieuw gebeurt.

De #VERW!-fout betekent dat een formule naar een cel wijst die niet meer bestaat

De #VERW!-fout (in het Engels #REF!, een afkorting van reference) verschijnt zodra een formule verwijst naar een cel, rij, kolom of blad die niet langer geldig is. Excel kan de verwijzing niet vervangen door een andere cel en zet daarom de tekst #VERW! op de plek waar eerst een celadres stond.

Je ziet dat terug in de formulebalk. Waar eerst bijvoorbeeld =B2*C2 stond, lees je na het verwijderen van kolom C ineens =B2*#VERW!. De formule bestaat nog, maar mist een geldig adres om mee te rekenen.

De fout plant zich voort. Verwijst een tweede formule naar de cel met de #VERW!, dan neemt die de fout over. In een uitgebreid model kan één verwijderde kolom zo tientallen cellen op rood zetten. Daarom loont het om de bron van de fout op te sporen in plaats van elke cel apart te repareren.

Verwar #VERW! niet met andere meldingen. #NAAM? betekent dat Excel een functienaam of een benoemd bereik niet herkent, #WAARDE! wijst op een verkeerd type gegevens, en #N/B geeft aan dat een opzoekfunctie niets vond. #VERW! staat specifiek voor een verwijzing die zijn doel is kwijtgeraakt. Dat onderscheid helpt je de juiste oplossing te kiezen: bij #VERW! herstel je de verwijzing, niet de functienaam of het gegevenstype.

Vier situaties veroorzaken bijna elke #VERW!

De fout ontstaat altijd doordat het doel van een verwijzing verdwijnt of buiten bereik valt. Deze tabel zet de vier meest voorkomende oorzaken op een rij.

Vooral knippen (Ctrl+X) en dan plakken is verraderlijk. Knip je een cel en plak je die over een cel waar een formule naar verwees, dan verdwijnt het oorspronkelijke doel en resteert een #VERW!. Kopiëren (Ctrl+C) heeft dat effect niet, omdat de bron blijft bestaan.

In opzoek- en verwijsfuncties duikt #VERW! het snelst op

Bepaalde functies zijn extra gevoelig voor deze fout, omdat ze met vaste posities werken.

• VERT.ZOEKEN. De fout ontstaat als het kolomindexgetal groter is dan het aantal kolommen in de tabelmatrix. Verwijder je een kolom binnen die matrix, dan verschuift de telling en valt de index buiten de tabel. Kies je liever een methode die naar de kolom zelf verwijst, lees dan het verschil in VERT.ZOEKEN vs X.ZOEKEN.
• INDEX. Vraag je met INDEX om rij 12 in een bereik van 10 rijen, dan geeft de functie #VERW! omdat die rij niet bestaat.
• INDIRECT. Deze functie bouwt een verwijzing op uit tekst. Klopt de tekst niet, bijvoorbeeld door een verwijderd blad, dan volgt direct een #VERW!.
• 3D-verwijzingen. Verwijst een formule naar een reeks bladen (=SOM(Jan:Dec!B2)) en verwijder je een van die bladen, dan kan de reeks breken.

Het patroon is telkens hetzelfde: de functie rekent met een positie die door een wijziging is weggevallen.

Direct na een verwijdering herstel je de fout met Ctrl+Z

Ontstaat de #VERW! doordat je net iets hebt gewist, dan is de snelste oplossing terugdraaien.

1. Druk direct op Ctrl+Z, of klik op Ongedaan maken in de werkbalk Snelle toegang. De verwijderde rij of kolom komt terug en de verwijzing herstelt zich.
2. Is terugdraaien niet meer mogelijk, klik dan de foutcel aan en bekijk de formule in de formulebalk.
3. Selecteer het stukje #VERW! in de formule en typ het juiste celadres of bereik erover.
4. Bevestig met Enter en controleer of de uitkomst klopt.

Herstel je een opzoekfunctie, pas dan het kolomindexgetal aan naar de nieuwe positie van de gewenste kolom, of vervang de vaste index door een die de kolom zelf opzoekt. Zit de fout in een INDEX-formule, controleer dan of het gevraagde rij- of kolomnummer nog binnen de grenzen van het bereik valt.

In een grote werkmap spoor je elke #VERW! op met Zoeken en vervangen

In een uitgebreid bestand staan de fouten soms verspreid over meerdere bladen. Zoek ze in één keer op.

1. Druk op Ctrl+H om Zoeken en vervangen te openen.
2. Typ bij Zoeken naar de tekst #VERW!.
3. Klik op Opties en zet Binnen op Werkmap zodat alle bladen worden doorzocht.
4. Kies Alles zoeken. Excel toont een lijst met elke cel die de fout bevat; klik een regel aan om er direct heen te springen.

Wil je begrijpen hoe een formule tot de fout komt, gebruik dan Formules > Formule evalueren. Excel loopt de berekening stap voor stap door, zodat je precies ziet op welk moment de verwijzing wegvalt. Een tweede handig hulpmiddel is Formules > Precedenten aanwijzen, dat met pijlen laat zien welke cellen een formule voedt.

Een gebroken VERT.ZOEKEN herstellen laat de aanpak zien

Een veelvoorkomend geval: je had =VERT.ZOEKEN(A2; Prijs!A:E; 5; ONWAAR) om de prijs uit de vijfde kolom te halen. Iemand verwijdert kolom C in het blad Prijs, waardoor de tabel nog maar vier kolommen telt. De formule blijft 5 tellen, die kolom bestaat niet meer en het resultaat wordt #VERW!.

1. Klik op de foutcel en lees in de formulebalk of het indexgetal (5) nog past bij de nieuwe tabelbreedte.
2. Tel de kolommen in het blad Prijs opnieuw. De prijs staat nu in de vierde kolom.
3. Pas het indexgetal aan naar 4: =VERT.ZOEKEN(A2; Prijs!A:D; 4; ONWAAR).
4. Kopieer de gecorrigeerde formule door naar de overige rijen.

Wil je dit soort correcties in de toekomst vermijden, vervang de vaste index dan door X.ZOEKEN of INDEX met VERGELIJKEN, die naar de kolom zelf verwijzen. Het volledige verschil staat in VERT.ZOEKEN vs X.ZOEKEN.

Verwijderde namen en validatielijsten geven ook een #VERW!

Niet alleen celverwijzingen breken. Gebruik je een benoemd bereik in een formule en verwijder je die naam via Formules > Namen beheren, dan verandert elke formule die de naam gebruikte in een fout. Excel toont dan meestal #NAAM?, maar bij een bereikverwijzing binnen die naam kan het ook #VERW! worden.

Ook gegevensvalidatie is gevoelig. Verwijst een keuzelijst (Gegevens > Gegevensvalidatie) naar een bereik dat je later weghaalt, dan werkt de lijst niet meer. Controleer bij zulke fouten of de bron nog bestaat via Formules > Namen beheren, en herstel de naam of wijs een nieuw bereik aan. Zo houd je niet alleen je formules, maar ook je invoerlijsten en draaitabellen heel.

Tabellen en namen voorkomen dat verwijzingen breken

Je voorkomt de #VERW!-fout door verwijzingen te gebruiken die meebewegen als de structuur verandert.

• Werk met Excel-tabellen. Maak van je bereik een tabel met Ctrl+T. Gestructureerde verwijzingen zoals Verkoop[Omzet] passen zich automatisch aan wanneer je rijen of kolommen toevoegt of verwijdert.
• Gebruik namen voor bereiken. Geef een bereik een naam via Formules > Naam definiëren. Verwijs je met die naam, dan blijft de formule leesbaar en verplaatst hij mee.
• Kies INDEX en VERGELIJKEN of X.ZOEKEN in plaats van een vast kolomindexgetal. Zulke opzoekmethoden verwijzen naar de kolom zelf en breken niet als je er eentje tussenvoegt.
• Verwijder kolommen bewust. Wil je een kolom weghalen, controleer dan eerst met Precedenten aanwijzen of er formules van afhankelijk zijn.
• Verberg in plaats van verwijderen. Heb je een hulpkolom alleen visueel in de weg, verberg die dan (rechtsklik, Verbergen) in plaats van hem te wissen; verborgen kolommen breken geen verwijzingen.

Draaitabellen zijn eveneens gevoelig voor verwijderde bronkolommen; hoe je die opbouwt en vernieuwt, staat in hoe je een draaitabel in Excel maakt.

Met ALS.FOUT verberg je een resterende fout netjes

Soms is een verwijzing tijdelijk leeg of hoort de fout gewoon niet in je rapport thuis. Vang hem dan af met ALS.FOUT, zodat een lege waarde of eigen tekst verschijnt in plaats van #VERW!:

=ALS.FOUT(B2*C2; "")

Gebruik ALS.FOUT bewust. Het verbergt niet alleen #VERW!, maar ook andere fouten zoals #DEEL/0! en #N/B. Los de onderliggende verwijzing dus eerst op en zet ALS.FOUT er pas omheen als je zeker weet dat de fout onschadelijk is. Anders maskeer je een echt probleem in je berekening en merk je pas laat dat er cijfers ontbreken.

Wil je specifiek alleen bepaalde fouten opvangen, gebruik dan ALS.NB voor uitsluitend #N/B, of controleer vooraf met IS.FOUT. Zo houd je zicht op de fouten die je wél wilt zien. Een goede werkwijze is om tijdens het bouwen de fouten juist zichtbaar te laten en ALS.FOUT pas toe te voegen als het model af is en klaar om te delen. Zo maskeer je geen problemen die je nog moet oplossen. Meer over veelvoorkomende meldingen en het voorkomen ervan staat in de uitleg van Microsoft over de #VERW!-fout.