Excel-foutmeldingen natrekken en oplossen
Als een spreadsheet vol met foutmeldingen staat kun je deze niet zonder zorgen overdragen aan een klant of collega. Het handmatig natrekken van elke fout, zoals #VALUE!, is echter veel werk en doorgaans geen geliefde taak. Met PerfectXL zie je direct waar de foutmeldingen staan, welke cellen ernaar verwijzen en wat het risiconiveau van elke foutmelding is. Zo heb je ze in no-time weggewerkt.
- Download het voorbeeldbestand om de stappen te volgen.
- Heb je PerfectXL? Ga dan direct naar de oplossing mét PerfectXL.
Stappenplan zonder PerfectXL
Stap 1: Zoeken met het zoekvenster
Als je de verantwoordelijkheid krijgt over een Excelmodel dat vol met errors staat, dan wil je die natuurlijk graag nalopen en waar mogelijk oplossen of afvangen. Met foutmeldingen bedoelen we hier de waarden in een cel van een spreadsheet die beginnen met een # en eindigen met een uitroepteken, bijvoorbeeld #VALUE!
De meest voor de hand liggende manier om dergelijke errors na te lopen in Excel zonder PerfectXL, is om ze handmatig een voor een op te zoeken in je Excel model. Daarvoor gebruik je het zoekvenster in Excel (CTRL+F).
Stap 2a: De juiste zoekopdracht
Alle foutmeldingen in Excel hebben de vorm #[naam_foutmelding]!, op #NAME? en #N/A na. De eerste vorm kun je in bulk opzoeken door in het zoekvenster te zoeken naar “#*!“. De hashtag staat in deze zoekopdracht voor het begin van een foutmelding, het sterretje staat voor een willekeurig aantal tekens en het uitroepteken staat voor het einde van de foutmelding.
Stap 2b: Zoeken in waarden
Klik voordat je begint met zoeken op de button “Options >>” en stel de zoekopdracht bij “Within” in op “Workbook” (om in het gehele model te zoeken) en bij “Look in” op “Values”.
Stap 2c: Alles zoeken
Klik vervolgens op “Find all”.
Stap 3: Loop alle foutmeldingen langs
Excel selecteert nu automatisch de eerstgevonden foutmelding in de werkmap, zodat je deze kunt evalueren en, waar nodig, verbeteren. Navigeer langs alle individuele foutmeldingen met de button “Volgende zoeken” in het zoekvenster, of door de volgende foutmelding te selecteren vanuit het overzicht in het zoekvenster.
Stap 4: Zoeken in formules
Vervolgens pas je de “Look in” waarde in je zoekopties aan naar “Formulas”, want in “Values” vind je alleen de directe foutmeldingen en niet de foutmeldingen die verstopt zijn in formules. Zoals in het onderstaande voorbeeld; de formule in C7 is =IF(C4>10000;C4+C5+#REF!+C6;C4+C5+C6) toont als resultaat keurig de som van de bovenliggende cellen: 18.500, omdat de waarde in C4 lager is dan 10.000. Foutmelding #REF! zou alleen als waarde gevonden worden als de waarde van cel C4 groter dan 10.000 was, maar hij staat wel in de formule en moet dus worden opgelost!
Stap 5: Herhaal alle stappen voor #NAME? en #N/A
Ten slotte herhaal je alle bovenstaande stappen voor #NAME? en voor #N/A, omdat deze niet voldoen aan de vorm “#*!”.
Focus je als laatste op de #REF! foutmeldingen. Als je alle overige foutmeldingen hebt opgelost, dan zullen deze waarschijnlijk (grotendeels) verdwenen zijn.
Een model dat vol met errors staat is uit den boze, vooral wanneer de berekeningen over grote sommen geld beslissen. Het mag duidelijk zijn dat het natrekken en oplossen van alle foutmeldingen op de handmatige manier, vooral in een omvangrijk model, een enorm tijdrovende klus is. Wanneer je een deadline hebt om een model aan te leveren aan een klant, dan kan dit nogal wat stress veroorzaken! Gebruik PerfectXL als je grondig en snel korte metten wilt maken met Excel-foutmeldingen!
Stappenplan met PerfectXL
Stap 1: Open PerfectXL Risk Finder (evt via de add-in)
Een eenvoudigere oplossing dan PerfectXL Risk Finder zul je niet vinden! Open de tool direct of via de PerfectXL Add-in in Excel. Als je de add-in gebruikt, ga dan naar “Detect Problems,” en selecteer (je raadt het al) “Excel Errors.”
Stap 2: Onderscheid tussen foutmeldingen en verwijzingen naar foutmeldingen
In het linkermenu van PerfectXL Risk Finder zie je dat er twee categorieen Excel Errors zijn: “Excel Errors” en “References to Excel Errors.” Dit vormt een groot voordeel ten opzichte van de handmatige methode, omdat de verwijzingen naar Excel-foutmeldingen automatisch zijn opgelost wanneer je de foutmeldingen in categorie “Excel errors” hebt aangepakt.
PerfectXL Risk Finder selecteert standaard de categorie “Excel Error” en in het overzicht verschijnt een lijst met locaties waar dergelijke foutmeldingen gevonden worden.
Stap 3: Oplossen
Wanneer je op een van deze ‘cases’ klikt verschijnt er een screenshot van de relevante locatie in je Excel file met een rode markering rondom de cel met de foutmelding en daaronder de details van de betreffende formule en een uitleg over hoe je het probleem zou kunnen oplossen. Je kunt op het fragment klikken om direct naar de relevante locatie in je originele bestand te springen en het probleem op te lossen. Dit alles in een fractie van de tijd die het je zou kosten zonder PerfectXL!
