Defined names controleren
Defined names helpen je model variabelen te herkennen en te gebruiken. Maar het kan een chaos worden. Gekopieerde tabbladen en VBA-acties kunnen dubbele en verborgen namen tot gevolg hebben. Hoe weet je of alle gedefinieerde namen daadwerkelijk gebruikt worden en waar? PerfectXL onthult al deze geheimen in je model, zodat je ze kunt verbeteren waar nodig en alles kunt opruimen wat weg mag.
- Download het voorbeeldbestand om de stappen te volgen.
- Werk je al met PerfectXL? Ga dan direct naar de oplossing met PerfectXL.
Stappenplan zonder PerfectXL
Stap 1: Open de Name Manager in Excel
Het is niet moeilijk om uit te zoeken welke namen er allemaal in je model voorkomen. Je klikt op “Name Manager” onder “Formulas” in het hoofdmenu van Excel en vervolgens verschijnt er een pop-up met daarin een overzicht van alle namen en de cellen waar ze naar verwijzen.
Stap 2: “Show Formulas”
Nu weten we dat er zeven namen in ons model gedefinieerd zijn, waarvan de laatste slechts de titel van een grafiek op ons tabblad “Summary” betreft. Die kunnen we eenvoudig verwijderen, want een titel kan prima direct worden ingevoegd. Maar van de overige zes namen weten niet of en waar ze gebruikt worden en dus ook niet of we ze weg kunnen gooien. Dat wordt een zoektocht!
Door op ieder tabblad te kiezen voor de optie “Show Formulas” onder “Formulas” in het hoofdmenu van Excel maken we formules zichtbaar, waarvan normaalgesproken alleen de waarden getoond worden. Verwijzingen naar defined names worden zo ook zichtbaar.
In ons voorbeeldbestand vinden we alleen de naam “Exp_est” op tabblad “Summary” en de naam “Opt_est” op tabblad “Expenses”, maar meer kunnen we met het blote oog niet vinden.
Kunnen we nu concluderen dat de overige vier namen niet gebruikt worden? Laten we kijken wat er gebeurt als we ze weggooien…
Stap 3: Verwijder ongebruikte Defined Names
Voordat je iets verwijdert, maak je uiteraard eerst een reservekopie van je bestand, want als je niet zeker bent van je zaak is het niet slim om met het origineel aan de slag te gaan.
Ga in je reservekopie naar het tabblad “Summary” en kijk goed naar de resultaten.
Nu roepen we de Name Manager weer op (Formulas -> Name Manager) om alle namen die vermoedelijk niet gebruikt worden weg te gooien. Let bij iedere verwijdering goed op of er iets verandert in de resultaten op het dashboard.
Na het verwijderen van de naam “Opt_act” verandert het verschil tussen inkomsten en uitgaven van -2.980 naar 32.550. Deze naam werd dus toch ergens gebruikt!
Het verschil lijkt veroorzaakt de worden door de waarde in cel C5: Actual Expenses.
De reservekopie mag dus weg, want daarin hebben we namen weggegooid die blijkbaar wel gebruikt werden. Creëer een nieuwe reservekopie van het origineel om op zoek te gaan naar de verborgen Defined Name.
Stap 4: Spoor verborgen namen op
Als we cel C5 selecteren dan zie we een dropdown met daarin slechts 1 list-item: 35.530. Dit list-item is actief en we zien in de formulebalk de verwijzing =Operating!C24. Het geselecteerde list-item verwijst dus naar een cel op het tabblad “Operating”.
Als we de betreffende cel op tabblad “Operating” selecteren dan zien we een optelsom van de data in de kolom, maar in de naambox (linksboven vóór de formulebalk) zien we de Defined Name die we zochten: Opt_act. Maar hoe kan deze naam dan van invloed zijn op cel C5 in tabblad “Expenses”, waarin slechts verwezen wordt naar de cel-locatie?
Stap 5: Onderzoek en verbeter
We selecteren cel C5 op tabblad “Expenses” en gaan vervolgens in het Excel menu naar “Data Validation” onder menu-item “Data”. Daar zien we dat de drop-down lijst inderdaad de naam “Opt_act” bevat.
We kunnen in dit model concluderen dat de lijst in cel C5 nergens voor nodig is.
Door in de Data Validation pop-up op ‘Clear all’ te klikken verwijderen we de lijst uit cel C5.
De waarde in cel C5 is nu vervangen door een vaste waarde:
Dat was niet de bedoeling, dus we vervangen de vaste waarde handmatig door een verwijzing naar cel Operating!C24.
Stap 6: Controleer de resultaten
Als we nu in tabblad “Expenses” voor de optie “Show Formulas” (onder “Formulas” in het hoofdmenu) kiezen, dan zien we dat de namen “Opt_est” en “Opt_act” netjes naast elkaar staan in de cellen B5 en C5. Het gebruik van deze namen is nu transparant.
Nu kunnen we drie van de zes overgebleven namen in ons model herleiden. Als we de overige drie uit dit model verwijderen, dan zal dat inderdaad zonder consequenties zijn; de overige drie worden nergens gebruikt. Maar je kunt je voorstellen dat dit proces in het geval van omvangrijkere modellen enorm tijdrovend is!
Stappenplan met PerfectXL
Stap 1: Open het bestand in PerfectXL Explore
Open PerfectXL Explore en selecteer het bestand dat je wilt controleren op Defined Names. De tool zal direct een visualisatie van het model laden.
Stap 2: Overzicht van namen in je bestand
Klik in het menu rechts, onder “Excel Objects”, op “Defined Names” voor een overzicht van alle namen die gebruikt worden in het model.
Rechts in de tabel, in de kolom “references” kun je direct zien hoe vaak er naar iedere naam wordt verwezen in het model. Je kunt de tabel ook sorteren op basis van het aantal verwijzingen. In deze specifieke tabel zien we in één oogopslag dat sommige namen helemaal niet worden gebruikt; deze kunnen we dus zonder consequenties verwijderen! Klik op het groene pijltje helemaal rechts om direct naar de Name Manager in Excel te springen.
Stap 3: Verwijder ongebruikte namen
In de Name Manager kun je de ongebruikte namen verwijderen, dus in het geval van dit voorbeeldbestand zijn dat “act,” “Chart_table” en “Diff”.
Veel modellen barsten van de ongebruikte namen, dus deze methode scheelt een mijl op zeven!
Stap 4a: Onderzoek de gebruikte namen
Nu nemen we het model verder onder de loep, om er zeker van te zijn dat de namen helder worden gebruikt en dat het voor iedereen duidelijk is wat ze representeren. Hiervoor kun je ook de PerfectXL Explore interface gebruiken.
Hierboven hebben we al beschreven dat het groene pijltje aan de rechterkant van een regel je naar de Excel Name Manager brengt, maar je kunt ook op het groene pijltje onder “References” klikken om meer informatie over een naam te krijgen. Klik op het pijltje naast “Exp_est”.
Hier zie je het overzicht van alle verwijzingen naar de naam “Exp_est”. In dit geval is dat er maar één. Met het groene pijltje aan de rechterkant van de kolom springen we direct naar de locatie van deze verwijzing in Excel.
Stap 4b: Evalueer de gebruikte namen
Door de titels van de rijen en kolommen te lezen kunnen we concluderen dat de betreffende cel verwijst naar de geschatte kosten (estimated expenses). De naam “Exp_est” is dus een passende naam.
Het op deze manier handmatige controleren van de Defined Names die gebruikt worden in je bestand verzekert je ervan dat ze logisch, leesbaar en transparant zijn. Daarom is het goed om dit proces te doorlopen voor alle actieve namen in je model.
Conclusie
Defined Names controleren met PerfectXL zal je veel tijd besparen, omdat je niet hoeft te raden of en waar de namen gebruikt worden. Je loopt ook niet het risico dat je namen (of verwijzingen er naartoe) per ongeluk over het hoofd ziet, omdat PerfectXL Explore ze gegarandeerd allemaal voor je vindt en op een rijtje zet.