Probleem: Je wil de inhoud van cellen verbergen omdat je bijvoorbeeld het worksheet met je data op gaat afdrukken. Het zou handig zijn dat je dit kan doen op basis van een waarde van een cel (1=gegevens verbergen, 0=gegevens tonen), zodat je kan omschakelen wanneer je wil.
Oplossing: Ook hier: conditional formatting op basis van een formule.
Probleem: Je hebt een database die verschillende lijnen omvat, met natuurlijk verschillende waarden. Je hebt een lijst van referentiewaarden en zou visueel willen voorstellen hoeveel keer de referentiewaarden (bijvoorbeeld de lotto-cijfers) in je database voorkomt.
Oplossing: Conditional formatting op basis van een formule!
Probleem: Je hebt een enorme tabel in excel met een heel aantal lijnen. Je zou maar al te graag de leesbaarheid willen verhogen.
Oplossing: Via conditional formatting je rijen afwisselend blanco en een kleurtje geven.
More after the jump!
Het moet zeker al gebeurd zijn: je hebt je tabellen gelinkt met de vlookup functie zoals we in één van onze vorige blogs (zie Hoe 2 tabellen linken met Excel) hebben uitgelegd. En hoera hoera, het werkt! Na een tijdje merk je dat plots het resultaat is veranderd en de vlookup functie naar de verkeerde kolom verwijst. Hoe is dit in godsnaam mogelijk?
Een typische oorzaak van dit probleem is het invoegen kolommen, zodat de verwijzing in de vlookup niet meer klopt (typisch door de nietsvermoedende collega die daardoor alles in de war stuurt, grrr…).
Er is relatief eenvoudige oplossing die dit probleem voor eens en voor altijd uit de wereld helpt: combineer de ‘vlookup’ functie met een ‘match’ functie.
Om de principes van de vorige post duidelijk te maken (en aan te tonen dat we met concrete oplossingen komen :-)) hieronder voorbeeld van hoe je een bestaand excel model kan omtoveren in een gebruiksvriendelijk model met een waanzinnig dashboard output. Het volgt dezelfde structuur en geeft een stap per stap handleiding van hoe we te werk zijn gegaan.
We gebruiken een bestaand model (download hier van www.exinfm.com). Het model evalueert wat het meest voordelig is: een auto aankopen dan wel te leasen. Het is een redelijk eenvoudig model en dient enkel om een aantal principes te illustreren. De methode die wordt voorgesteld kan echter zonder problemen toegepast worden om de meest complexe modellen.
Hier gaan we dan… (de resulterende excel staat in de ‘nuttige excels’ lijst voor download)
De wereld wordt steeds sneller en complexer - niettemin wordt er verlangd dat beslissingen kordaat en onderbouwd genomen worden. Zeker in de bedrijfswereld gaan deze belangrijke beslissingen gepaard met steeds meer verfijnde analyses door ingewikkelde financiële en operationele (excel) modellen. Niet zelden overdonderen deze modellen ons met een zondvloed van gegevens en data, waardoor we de bomen niet meer door het bos zien. Als gevolg is efficiente rapportering dikwijls een ramp.
De oplossing van dit probleem is het invoeren van een doordacht dashboard systeem, waarbij data wordt gestructuurd in verschillende niveaus en representaties. Dashboards kunnen opbouwd worden op zeer verschillende manieren (grafisch, Key Perfomance Indicators, verklikker lichtjes, enz). In andere posts geven we specifiek advies hoe je efficiente dashboards kan ontwerpen. Hieronder geven we aan hoe eenvoudig het is om een dashboard in te voeren in een bestaand excel model. Het is een zeer eenvoudige manier om je manager te verrassen met een krachtig beslissingstool!
In deze post geven we 5 basis principes die cruciaal zijn om een dashboard te maken voor een bestaand excel workbook zodat efficient kan gerapporteerd worden.
1. Weet wat je wil: waarom een dashboard? wie zal het gebruiken? wie kan data interpreteren?
2. Denk output: vergeet niet dat je bestaande model een tool is om te helpen bij een bepaald probleem/beslissing. Wat wil je eigenlijk echt weten?
3. Gebruik black box methode: splits je excel op in duidelijke model inputs, model outputs (dashboard) en het model dat de berekeningen uitvoert.
4. Test test test test: we kunnen het niet vaak genoeg herhalen: test de resultaten die uit het dashboard komen tot je voldoende comfort hebt.
5. Verhoog gebruiksvriendelijkheid: zorg ervoor dat het dashboard kan gebruikt worden door de iets minder tech-savvy collega.
Probleem: Je hebt een lijst in excel, waar een aantal foutmeldingen inzitten van het type #N/A of #Value, etc… Hoe kan je deze foutmeldingen onzichtbaar maken, om bijvoorbeeld je sheet af te drukken?
Oplossing: gebruik conditional formatting op basis van een formule!
Probleem: Je hebt een heleboel data in een kolom, met scores. Je wil dit op een overzichtelijke manier weergeven, zonder meteen naar draaitabellen te grijpen.
Oplossing: Zorg ervoor dat je grafieken in een cel maakt! Zo ziet ‘t eruit:
Probleem: je hebt een erg lange excel lijst met records van een database. Je wil hier nu de duplicaten uithalen en verwijderen, met andere woorden de tabel ontdubbelen. Hoe moet dit?
Oplossing: een combinatie van de IF() functie en de COUNTIF() functie.
Probleem: Je wil bepaalde cellen in je excel workbook er laten uitspringen door ze een kleur te geven afhankelijk van de waarde van de cel.
Oplossing: Conditional formatting in excel!