Het komt wel eens voor dat je in Excel alvast allemaal formules maakt, zodat wanneer je gegevens invult Excel alles gelijk berekend.
Het komt dan geregeld voor dat je eerst allemaal foutmeldingen te zien krijgt omdat er nog geen gegevens zijn ingevoerd waarmee Excel de berekening kan uitvoeren.
Op zich is dat geen probleem. Zodra je wel de gegevens invoert, werken je formules gewoon en verdwijnen de foutmeldingen.
Ik vind al die foutmeldingen echter slordig staan, daarom verberg ik ze meestal. Er zijn meerdere manieren waarop dit kan. In dit artikel deel ik de twee manieren die ik het eenvoudigste vind en het meeste gebruik.
Manier 1: de ALS.FOUT functie
Indien het om een slechts een paar cellen gaat, dan gebruik ik graag de ALS.FOUT functie om foutmeldingen te verbergen.
Met deze functie kan je Excel laten weten wat je wilt dat Excel doet, en wat het moet doen indien dit een foutmelding oplevert. De schrijfwijze van deze functie is:
ALS.FOUT(waarde;waarde_indien_fout)
Een simpel voorbeeld hoe dit werkt is als volgt:
Stel je wilt een getal in cel A1 delen door een getal dat staat in cel B1. Wanneer cel B1 bijvoorbeeld leeg is of de waarde 0 heeft, krijg je een foutmelding, omdat je een getal niet door nul kan delen.
Dit kun je eenvoudig oplossen met de functie ALS.FOUT.
Het eerste argument (waarde) is hetgeen dat je wilt dat Excel gaat doen. In dit geval is dat A1/B1.
En het tweede argument (waarde_indien_fout) is wat Excel moet doen als er fout ontstaat.
Je kunt bijvoorbeeld een spatie typen, zodat er niets gebeurt en de cel leeg blijft. De formule wordt dan:
=ALS.FOUT(A1/B1;"")
Maar je kunt ook je eigen foutmelding bedenken, bijvoorbeeld dat Excel de tekst ‘Geen waarde’ moet doen. In dat geval wordt de formule:
=ALS.FOUT(A1/B1;"Geen waarde")
Ik zal nog een concreet voorbeeld geven wanneer deze functie bijvoorbeeld handig is.
In mijn kasboek template, heb ik een formule bedacht waarmee het procentuele verandering in saldo wordt berekent. De som hiervoor is (eindsaldo-beginsaldo)/beginsaldo. Het probleem is dat wanneer er nog geen gegevens zijn ingevoerd, dit eerst een foutmelding oplevert.
Ik heb daarom gekozen om deze som als ‘waarde’ in te voeren bij de ALS.FOUT functie en gekozen om een spatie te tonen indien deze som een fout oplevert. Zodra er op een later moment alsnog gegevens worden ingevoerd, zal de procentuele verandering gewoon worden berekent.
Manier 2: Voorwaardelijke opmaak
Je kunt natuurlijk altijd manier 1 gebruiken, maar als het allemaal verschillende situaties zijn die de foutmeldingen opleveren, dan heb ik zelf geen zin om daar steeds de argumenten bij ALS.FOUT functie voor in te moeten voeren.
In dat geval kies liever om gebruik te maken van voorwaardelijke opmaak. Hiermee kun je bepalen hoe cellen worden opgemaakt, indien ze aan het een bepaald criterium voldoen.
Om gebruik te maken van voorwaardelijke opmaak, selecteer je de cellen waar je dit op wilt toe passen en daarna ga je in het lint naar het tabblad ‘Start‘ en vind je in de groep ‘Stijlen‘ de knop Voorwaardelijke opmaak. Klik vervolgens op Nieuwe regel.
Vervolgens kun je twee manieren kiezen om fouten te verbergen. We beginnen met de makkelijkste, die is de meeste gevallen voldoende is.
Alleen cellen opmaken die fouten bevatten
Kies bij Selecteer een type regel (1) voor Alleen cellen opmaken met. Daarna selecteer je bij Alleen cellen opmaken met (2) voor Fouten.
Vervolgens klik je op Opmaak (3) en stel je de tekstkleur als dezelfde kleur als de celachtergrond. In dit voorbeels is dat wit.
De foutmelding blijft wel aanwezig, maar wordt minder storend doordat de tekst wit wordt. Als je de cel selecteert, zal je nog wel zien dat er een fout is, het wordt dus niet volledig verborgen zoals bij manier 1.
Een formule gebruiken om te bepalen welke cellen worden opgemaakt
Een andere manier is om in het venster ‘Nieuwe opmaak regel’ te kiezen voor ‘Een formule gebruiken om te bepalen welke cellen worden opgemaakt‘.
Dit levert exact hetzelfde resultaat op als net met alleen cellen opmaken die fouten bevatten. Het verschil is dat, indien je wilt, je meer flexibiliteit krijgt als je een formule gebruikt.
Stel je hebt een werkblad met gegevens waarin je foutmeldingen wilt opmaken, maar alleen als in kolom D de tekst “Uitverkocht” staat. De standaardoptie van voorwaardelijke opmaak (alleen fouten) kan dit niet doen, maar met een formule kan het wél.
Zodra ik in cel D2 in bovenstaand voorbeeld de tekst verander in ‘Uitverkocht’, wordt de voorwaardelijke opmaak toegepast. Bij iedere andere tekst, blijft de foutmelding. Je kunt hiermee dus meer maatwerk toepassen met aanvullende voorwaarden. Dit kost wel meer werk, maar kan handig zijn voor specifieke scenario’s.
Deze formule doet twee dingen tegelijkertijd:
- Eerst wordt er gekeken of er een fout staat in cel C2. Dit wordt gedaan door het gedeelte
ISFOUT(C2). Als er een fout staat, dan geeft deze formule de waarde ‘WAAR’ terug. - Daarna wordt er gekeken of er in cel D2 de status ‘Uitverkocht’ staat. Dit wordt gecontroleerd met het gedeetle
D2="Uitverkocht". Als dit klopt, geeft het ook de waarde ‘WAAR’ terug.
De formule gebruikt de functie EN, wat betekent dat beide voorwaarden moeten kloppen:
- Er moet een fout zijn in cel C2.
- In cel D2 moet “Uitverkocht” staan.
Als beide kloppen, zal de cel worden opgemaakt volgens de regels die je hebt ingesteld in voorwaardelijke opmaak.
Zelf gebruik ik meestal manier 1, omdat dit voor mij voldoende is, maar inden je specifieke criteria hebt, kan het gebruik van een formule je dus meer opties bieden.
Foutcontrole uitschakelen
Zoals je hebt kunnen zien wordt de tekst door voorwaardelijke opmaak verborgen, maar markeert Excel de cellen nog wel als een fout met een groen driehoekje in de hoek van de cel (zoals hieronder in cel C1):
Indien je dit niet wilt, kan je dat uitschakelen met de volgende stappen:
- Ga naar Bestand > Opties > Formules.
- Zoek het gedeelte Foutcontrole.
- Haal het vinkje weg bij Foutcontrole op de achtergrond inschakelen.
Let wel op dat deze instelling geldt voor al je Excel bestanden!
De foutmeldingen (zoals #DIV/0!) blijven nog steeds zichtbaar in de cellen. Tenzij je voorwaardelijke opmaak gebruikt om deze tekst te verbergen. Deze optie helpt dus alleen om de foutmarkering (groene driehoekjes) te verbergen.
Hoewel dit een handige optie kan zijn, kies ik er zelf liever niet voor. Het verbergen van foutmarkeringen kan er namelijk voor zorgen dat fouten in andere bestanden over het hoofd worden gezien. En zelf vind ik alleen een groen driehoekje ook niet zo storend.
Als je dit artikel leerzaam vond en je jouw Excel vaardigheden op een gestructureerde manier verder wilt ontwikkelen, zou je een kijkje kunnen nemen naar een van mijn online Excel cursussen.
Mocht je nog vragen hebben over verbergen van foutmeldingen dan kan je dat hieronder doen.
