Excel dashboard

Een Excel dashboard is overzicht die Managers en Bedrijfsleiders helpt bij het volgen van belangrijke KPI’s of statistieken en het nemen van een beslissing. Excel dashboards zijn er in verschillende vormen. Zo kun je denken aan een management dashboard, een financieel dashboard, een customer service dashboard en een social media dashboard.

KPI dashboard in Excel inclusief: draaidiagrammen, stapeldiagrammen, kaartdiagrammen, pictogrammen en data slicer.

In mijn artikel “Je persoonlijke dashboard” heb ik mij al toegelegd op het onderwerp Excel Dashboard. De Excel-sjabloon hiervoor is een van de meest populaire met meer dan 500 downloads. Dus ik vond dat het onderwerp dashboards een nieuwe editie verdiende. Maar deze keer gaan we een heel andere weg in. We gebruiken draaitabellen, draaigrafieken en slicers om ons dashboard interactief te maken.

Daarnaast heb ik dit keer ook meer nadruk gelegd op design. Het dashboard moet er zo aantrekkelijk uitzien dat je er gewoon een warm gevoel bij krijgt. Om dit te bereiken, is er veel aandacht besteed aan opmaakwerk en grafiektypes.

Zo ziet het ultieme Excel dashboard eruit

Het beste Excel-dashboard met draaitabellen, draaigrafieken en slicers. Het Excel-dashboard is verdeeld in 5 delen die verschillende informatie bevatten:

1. Financiële kengetallen: Naast een verkooptrend (actueel en plan) in een combinatiegrafiek worden de verkopen per land weergegeven in een kaartdiagram.
2. Proceskengetallen: Naast de naleving van de leveringsdata zie je ook het retourpercentage, beide gevisualiseerd in een stapeldiagram.
3. Kengetallen medewerkers: Per locatie maken we gebruik van een pictogram om de medewerkers voor te stellen. Waarop per vestiging een duidelijke weergave van de medewerkers te zien is. Opgedeeld in mannen/vrouwen.
4. Klantkengetallen: Door slim gebruik te maken van gestapelde staven tonen we per product de klanttevredenheid.
   

De inhoud van het dashboard is gebaseerd op het balanced scorecard-concept. Dat precies deze vier weergaven (financiën, proces, medewerkers, klant) als geïntegreerd managementsysteem gebruikt. Ik heb het dashboard het Balanced Dashboard genoemd.

Het Excel-dashboard maakt een beknopte, overzichtelijke weergave van voor de besturing relevante kengetallen mogelijk. Het kan toegang krijgen tot verschillende statistieken en deze nauwkeurig en begrijpelijk presenteren. Vanwege het dynamische karakter is het perfect voor analyse ter voorbereiding op bijvoorbeeld maandelijkse besprekingen. Je kan het echter ook direct gebruiken voor de presentatie aan de manager of het management. Dankzij de compacte vorm kan je het dashboard printen op één pagina, op papier of als pdf voor verzending.

In 5 stappen naar het Excel dashboard

Als je het dashboard 1: 1 wilt bouwen, raad ik videozelfstudie aan. Omdat ik niet elke opmaak afzonderlijk kan of wil vastleggen bij het samenvatten van de tekst. In principe kan het proces worden onderverdeeld in 5 stappen, die elk natuurlijk meerdere acties omvatten.

1e stap: de gegevens voorbereiden

Zoals elk Excel-project begint dit met gegevensvoorbereiding. Omdat we in de volgende stap draaitabellen willen maken, hebben we onze gegevens in lijstvorm nodig. Waarbij elke kolom een unieke kop vereist. Zo ziet de gebruikte lijst er in ons geval uit:

Alle gegevens zijn gekoppeld, behalve de laatste 3 kolommen. Deze zijn niet logisch te koppelen aan de voorgaande kolommen. We moeten deze informatie dus onderaan de lijst opnemen.

De gegevens voor locatie, medewerker (M), medewerker (V) zijn gebaseerd op peildata die maandelijks plaatsvinden.

2e stap: draaitabellen maken

We hebben nu zes draaitabellen nodig. Dat wil zeggen één draaitabel voor elk diagram dat we op het dashboard tonen. In mijn Excel template heb ik vier werkbladen gebruikt om namelijk de vier views los van elkaar te kunnen bekijken. Financiën, proces, medewerkers en klanten.

Financiële statistieken

Aangezien het financiële overzicht uit twee diagrammen bestaat, hebben we hier ook twee draaitabellen nodig. We beginnen met de verkooptrend, die later in het combodiagram wordt weergegeven. Markeer de volledige gegevenslijst en ga vervolgens naar Invoegen en Draaitabel. Selecteer vervolgens het gewenste werkblad bij het doel en druk op ok. Vervolgens sorteer je de veldenlijst van de spil volgens het schema:

Het is niet verwonderlijk dat er vanaf augustus geen waarden worden opgeslagen. Ik heb hier bewust slechts een deel van het jaar weergegeven. Zoals dat meestal het geval is. We tonen de planwaarde voor het hele jaar om direct naar de komende maanden te kunnen kijken.

We doen nu hetzelfde voor de gegevens die later naar het kaartdiagram worden overgebracht. Hier ziet de veldlijst en de voltooide spil er als volgt uit:

Helaas kunnen de kaartdiagrammen geen gegevens rechtstreeks uit draaitabellen halen. Daarom hebben we een kleine hulplijst nodig die is gekoppeld aan de draaitabel.

Processtatistieken

Net als bij de financiële cijfers maken we twee draaitabellen, één voor elk diagram. Omdat de stapeldiagrammen een speciale structuur nodig hebben, maken we hier ook hulpcellen. Het resultaat ziet er als volgt uit:

De werkelijke waarde voor het naleven van leveringsdata en het retourpercentage berekenen we vervolgens door de twee waarden in de draaitabel te delen:

• Naleving van leveringsdata: op tijd / algemeen resultaat
• Retourpercentage: ja / algemeen resultaat

Werknemersstatistieken

Ook de kengetallen van de medewerkers voeden we vanuit dezelfde datalijst. De structuur van de draaitabel is hier te zien:

Klantstatistieken

Last, but not least bouwen we de draaitabel voor klanttevredenheid. Zelfde spel, ander resultaat.

3e stap: achtergrond en structuur van het Excel dashboard

Laten we nu beginnen met het maken van het formaat en de structuur voor het eigenlijke dashboard. Te beginnen met een blanco vel. Eerst verwijderen we de rasterlijnen. Hiervoor verwijderen we het vinkje in de weergave.

Laten we nu een mooie achtergrondafbeelding kiezen. Hierbij mag je natuurlijk afwijken van mijn foto. Om een achtergrondafbeelding netjes in te voegen, gaan we naar Pagina, Indeling, Achtergrond.

Als dat klaar is, plaatsen we de 6 vormen op het werkblad, afhankelijk van onze smaak. Ik gebruikte mijn beproefde groene (# 009f6d) voor mijn vormen en zette de transparantie op 65%. Voor de onderste vorm, waarin de data slicers later terug te vinden zijn, heb ik gekozen voor een zwart met een lichte transparantie. Je zou nu klaar moeten zijn:

Nu voegen we het bovenste logo in. Gewoon kopiëren en aanpassen. En onze vormen een passende belettering geven. Lettertype hier: Arial Nova Cond Light. Naast de etikettering voegen we ook iconen/symbolen toe die passen bij de verschillende weergaven. Deze vind je onder: Invoegen – Pictogrammen.

Met het grote aanbod zit er zeker ook iets voor jou tussen. Deze functie is alleen beschikbaar bij een Office 365-pakket of de nieuwe Excel-versie vanaf 2016. Nu hebben we een kant-en-klaar raamwerk om onze diagrammen in te voegen:

4e stap: Excel dashboard grafieken

Combinatiegrafiek

Het combodiagram is bedoeld om het verloop van de verkoop met werkelijke en geplande waarden weer te geven. De WERKELIJKE gegevens worden weergegeven door kolommen. En de planwaarden worden weergegeven met lijnen. De exacte opmaak is te vinden in de video.

De draaitabel in het tabblad Financiën, die de omzet per maand laat zien, dient als database voor het combodiagram. Je kunt met één klik de gewenste draaitabel maken.

Kaartdiagram

Helaas is het kaartdiagram niet beschikbaar voor oudere versies van Excel. Helaas momenteel alleen voor Excel 2019 of een Office 365 pakket. Daarnaast is het belangrijk dat je een internetverbinding hebt wanneer je het kaartdiagram voor het eerst maakt. Dit is nodig omdat de gegevens via Bing moeten worden benaderd.

Het kaartdiagram is bedoeld om de omzet per land in het dashboard te visualiseren. Hoe donkerder de kleur, hoe meer omzet er in dit land wordt gegenereerd. We maken het kaartdiagram op basis van de hulpcellen die we naast onze draaitabel hebben ingepakt:

Als u een oudere versie van Excel heeft, gebruikt u hiervoor gewoon een ander diagram. Bijvoorbeeld een staafdiagram dat de verkopen per land laat zien. Of een cumulatief overzicht van de WERKELIJKE versus PLAN-verkopen.

Stapeldiagram

We maken de stapeldiagrammen met behulp van de twee hulplijsten voor het naleven van leveringsdata en het rendement. Ik heb een aparte artikel voor het maken van opsommingstekens gemaakt dat je hier kunt bekijken. Hierbij dien je vooraf na te denken over welke segmenten je wilt invoegen en vooral hoe je deze wilt beoordelen. Natuurlijk kun je ook zonder segmentatie en alleen de werkelijke waarde vergelijken met het doel. Dit ontneemt het diagram echter zijn betekenis.

Pictogram

Met ons pictogram tonen we het aantal medewerkers per locatie. Onderverdeeld in mannen en vrouwen. Op een eenvoudige maar zeer esthetische manier. Het aantal pictogrammen geeft de waarde weer. Het pictogram is eigenlijk een opgemaakt gestapeld staafdiagram. We maken dit direct vanuit de draaitabel in het medewerkerstabblad als een draaitabel.

In de volgende stap kiest je je pictogrammen in de pictogrammencatalogus van Excel. Zodra je het juiste symbool hebt gevonden, hoef je het alleen nog maar te kopiëren naar de gegevensreeks van het staafdiagram. Bij het formatteren achteraf is het belangrijk dat je op de optie “Stack and share with unit/picture” klikt. Anders wordt je pictogram gewoon uitgerekt en dat ziet er niet goed uit. Deze aanpassing zorgt ervoor dat het aantal pictogrammen overeenkomt met de werkelijke eenheid die het zou moeten vertegenwoordigen. Bijvoorbeeld 6 werknemers = 6 pictogrammen.

Gestapeld staafdiagram (100%)

Het staafdiagram toont ons de klanttevredenheid uitgesplitst naar de 6 producten. De balk is opgedeeld in 5 eenheden: zeer ontevreden, ontevreden, neutraal, tevreden en zeer tevreden. Het diagram wordt rechtstreeks vanuit de draaitabel op het tabblad Klant gemaakt. Zorg ervoor dat de volgorde in de draaitabel direct overeenkomt met uw doelafbeelding. Anders moet je deze later in het diagram opnieuw aanpassen.

5e stap: gegevensslicers invoegen en opmaken

Met data slicers in Excel hebben we een praktische filteroptie voor tabellen en draaitabellen of draaigrafieken. Dankzij de data slicers in ons dashboard kan de gebruiker zijn dashboard zo aanpassen dat hij alleen de informatie ziet die voor hem relevant is. Bovendien ondersteunt deze functie de analyse van de bestaande gegevens. Aangezien een positief resultaat op algemeen niveau, bijvoorbeeld alle landen, niet uitsluit dat individuele deelgebieden onder de verwachtingen liggen. Het maken van slicers is eenvoudig. Klik gewoon op het diagram waarop je de slicer wilt laten beïnvloeden en kies Invoegen – Slicer.

We gebruiken 5 data slices voor ons dashboard. Product, land, klant, maanden en maanden voor medewerkers. Om ervoor te zorgen dat de filtering in de datasectie invloed heeft op meerdere diagrammen, moeten we ze nog aan de gewenste pivot koppelen. Klik hiervoor met de rechtermuisknop op de data slicer en ga naar rapportverbindingen. Daar kun je nu de pivots selecteren die gefilterd moeten worden.

Bij het bewerken van de gegevens kun je vervolgens kiezen of je meerdere posities kunt selecteren of slechts één. Dit is bijvoorbeeld handig voor het werknemersdiagram. Omdat een selectie van enkele maanden hier geen zin heeft.

Het resultaat van het Excel dashboard

Met het gekoppelde dashboard heb je nu de interactiviteit die je wilt. Je baas kan stoom afblazen en jij hebt je rust tot de volgende maand. Je kunt de Excel-dashboardsjabloon downloaden en eenvoudig aanpassen aan je gegevens. Daar heb je nu de kennis voor.

Ik wens je veel succes met Excellen!

Vragen over het Excel dashboard

Heb je naar aanleiding van dit artikel nog vragen over het maken van een Excel dashboard. Neem dan gerust vrijblijvend contact op met De Excel Specialist.