Exceltips

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)

(more…)

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.

(more…)

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!

(more…)

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:

(more…)

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!

(more…)

Uit verschillende reacties en commentaren blijkt dat er interesse is om ‘contextual’ menus te creeëren in excel.

Wel het kan en het is niet eens zo moeilijk!!!

“Contextual” menus zijn menus die veranderen afhankelijk van andere data input in de sheet. Een voorbeeld maakt het onmiddellijk duidelijk: stel je hebt een sheet waarbij je een een stuk taart wil beschrijven aan de hand van de kleur, de vorm en de smaak. Voor elk van deze eigenschappen heb je een menu van mogelijkheden. Je wil nu echter ervoor zorgen dat als de gebruiker de optie ‘kleur’ zou kiezen, het smaakmenu verschijnt en voor de optie ‘vorm’, het vormmenu, enz.

Hieronder wordt dit voorbeeldje uitgewerkt. Je kan het ook vinden in de lijst om te downloaden.

(more…)

Uit verschillende reacties en commentaren blijkt dat er interesse is om ‘contextual’ menus te creeëren in excel.

Wel het kan en het is niet eens zo moeilijk!!!

“Contextual” menus zijn menus die veranderen afhankelijk van andere data input in de sheet. Een voorbeeld maakt het onmiddellijk duidelijk: stel je hebt een sheet waarbij je een een stuk taart wil beschrijven aan de hand van de kleur, de vorm en de smaak. Voor elk van deze eigenschappen heb je een menu van mogelijkheden. Je wil nu echter ervoor zorgen dat als de gebruiker de optie ‘kleur’ zou kiezen, het smaakmenu verschijnt en voor de optie ‘vorm’, het vormmenu, enz.

Hieronder wordt dit voorbeeldje uitgewerkt. Je kan het ook vinden in de lijst om te downloaden.

(more…)

Probleem: je wil een cel, of een aantal cellen formatteren op basis van de waarde van een andere cel in je excel worksheet. vb. Als een cel op ‘ok’ staat, de hele rij (10 cellen breed) grijs formatteren.

Oplossing: binnen ‘conditional formatting’ de voorwaarde baseren op een formule.

Je kan de video bekijken ‘after the jump’

(more…)

Probleem: Je wil je excel workbook een beetje sexier er doen uitzien door een pulldown, of dropdown box toe te voegen aan een bepaalde cel.

Oplossing: Data validation!

(more…)

Probleem: je wil een excel file maken waarin teamleden informatie moeten invullen, maar ze mogen enkel vooraf bepaalde categorieën gebruiken. Bovendien staat het aantal categorieën nog niet vast en kunnen er nog bij komen. Je hebt ook geen zin om de validatie in hetzelfde sheet te doen als je input.

Oplossing:

1. Om je validatiewaarden in een andere sheet te steken, gebruik je “Names”. Je benoemd een range in je “dropdown-sheet”, en gebruikt deze “Name” om je range te definiëren in je Data Validation.
2. Als je wil dat deze range automatisch uitzet als er nieuwe waarden worden toegevoegd in je dropdown, gebruik dan “Dynamic Ranges” door middel van de “Offset” functie: =offset($A$1,0,0,counta($A:$A),1). Het eerste argument is de referentiecell in je range.

(more…)