Een Excel-dashboard maken (uitgelegd met voorbeelden en sjablonen)

Een Excel-dashboard kan een geweldige tool zijn als het gaat om het bijhouden van KPIs, het vergelijken van gegevenspunten, en het verkrijgen van gegevensgestuurde weergaven die het management kunnen helpen bij het nemen van beslissingen.

In deze zelfstudie leert u hoe u een Excel-dashboard maakt, de beste praktijken die u moet volgen bij het maken van een dashboard, functies en tools die u in Excel kunt gebruiken , dingen die u koste wat het kost moet vermijden, en aanbevolen trainingsmateriaal.

Wat is een Excel-dashboard en hoe verschilt het van een rapport?

Laten we eerst begrijpen wat een Excel-dashboard is.

Een Excel-dashboard is één pagina (meestal, maar niet altijd noodzakelijk) die managers en bedrijfsleiders helpt bij het volgen van belangrijke KPIs of metrische gegevens en op basis daarvan een beslissing nemen. Het bevat grafieken / tabellen / weergaven die worden ondersteund door gegevens.

Een dashboard wordt vaak een rapport genoemd, maar niet alle rapporten zijn dashboards.

Hier is het verschil:

• Een rapport verzamelt en toont alleen gegevens op één enkele plaats. Als een manager bijvoorbeeld wil weten hoe de verkoop de afgelopen periode is gegroeid en welke regio het meest winstgevend was, zou een rapport daar geen antwoord op kunnen geven. Het zou eenvoudig alle relevante verkoopgegevens rapporteren. Deze rapporten worden vervolgens gebruikt om dashboards te maken (in Excel of PowerPoint) die helpen bij de besluitvorming.
• Aan de andere kant zou een dashboard onmiddellijk belangrijke vragen beantwoorden, zoals welke regios het beter doen en welke producten moet het management zich concentreren op. Deze dashboards kunnen statisch of interactief zijn (waar de gebruiker selecties kan maken en weergaven kan wijzigen en de gegevens dynamisch worden bijgewerkt).

Nu we begrijpen wat een dashboard is, laten we eens kijken in en leer hoe u een dashboard in Excel maakt.

Hoe u een Excel-dashboard maakt?

Het maken van een Excel-dashboard is een proces dat uit meerdere stappen bestaat en er zijn enkele belangrijke dingen die u in gedachten moet houden bij het maken ervan.

Zelfs voordat u Excel start, moet u duidelijk over de doelstellingen van het dashboard.

Als u bijvoorbeeld een KPI-dashboard maakt om de financiële KPIs van een bedrijf bij te houden, zou uw doel zijn om de vergelijking van de huidige periode met de afgelopen periode te tonen (s).

Evenzo, als u een dashboard maakt voor de personeelsafdeling om de opleiding van werknemers bij te houden, dan zou het doel zijn om te laten zien hoeveel werknemers zijn opgeleid en hoeveel er moeten worden opgeleid om het doel te bereiken.

Wat u moet doen voordat u zelfs maar een Excel-dashboard gaat maken

Veel mensen beginnen aan het dashboard te werken zodra ze de gegevens in handen hebben.

En in de meeste gevallen brengen ze de ellende van het herwerken op het dashboard met zich mee, aangezien de doelstellingen van de klant / belanghebbende niet worden gehaald.

Hieronder staan enkele van de wachtrijen vragen die u moet hebben beantwoord voordat u begint met het bouwen van een Excel-dashboard:

V: Wat is het doel van het dashboard?

Het eerste dat u moet doen zodra u de gegevens ( of zelfs voordat u de gegevens ontvangt), is om duidelijkheid te krijgen over wat uw stakeholder wil. Wees duidelijk voor welk doel het dashboard moet dienen.

Is het om KPIs eenmalig of op regelmatige basis bij te houden? Moet het de KPIs voor het hele bedrijf of divisie-gewijs volgen ?. Door de juiste vragen te stellen, kunt u beter begrijpen welke gegevens u nodig heeft en hoe u het dashboard moet ontwerpen.

V: Wat zijn de gegevensbronnen?

Weet altijd waar de gegevens vandaan komen en in welk formaat. In een van mijn projecten werden de gegevens geleverd als pdf-bestanden in de Spaanse taal. Dit veranderde de reikwijdte volledig en het grootste deel van onze tijd werd opgeslokt door het handmatig verwijderen van de gegevens. Hier zijn de vragen die u zou moeten stellen: Wie is de eigenaar van de gegevens? In welk formaat krijgt u de gegevens? Hoe vaak worden de gegevens bijgewerkt?

V: Wie gebruikt dit Excel-dashboard?

Een manager is waarschijnlijk alleen geïnteresseerd in de inzichten die uw dashboard biedt, maar sommige gegevensanalisten zijn in zijn team heeft misschien een meer gedetailleerd overzicht nodig. Op basis van wie uw dashboard gebruikt, moet u de gegevens en de uiteindelijke uitvoer structureren.

V: Hoe vaak moet het Excel-dashboard worden bijgewerkt?

Als uw dashboards wekelijks of maandelijks wordt bijgewerkt, kunt u beter een plug-and-play-model maken (waar u de gegevens gewoon kopieert en plakt en deze automatisch wordt bijgewerkt). Als het maar een eenmalige oefening is, kun je wat automatisering weglaten en dat handmatig doen.

V: Welke versie van Office gebruikt de klant / belanghebbende?

Het is beter om ga er niet vanuit dat de klant / belanghebbende de laatste versie van MS Office heeft. Ik heb ooit een dashboard gemaakt om te weten dat mijn belanghebbende Excel 2003 gebruikte.Dit leidde tot wat herwerk omdat de IFERROR-functie niet werkt in Excel 2003-versie (die ik uitgebreid had gebruikt bij het maken van het dashboard).

De gegevens ophalen in Excel

Zodra u een goed idee hebben van wat u moet maken, de volgende stappen zijn om de gegevens in handen te krijgen en deze in Excel te krijgen.

Uw leven is gemakkelijk als uw klant u gegevens in Excel geeft, als dat niet het geval is, moet u een efficiënte manier bedenken om het in Excel te krijgen.

Als u CSV-bestanden of tekstbestanden ontvangt, kunt u deze eenvoudig in Excel converteren. Als u toegang heeft tot een database waarin de gegevens worden opgeslagen, kunt u een verbinding maken en deze indirect bijwerken.

Zodra u de gegevens heeft, moet u deze opschonen en standaardiseren.

U moet bijvoorbeeld voorloop-, volg- of dubbele spaties verwijderen, duplicaten zoeken en verwijderen, spaties en fouten verwijderen, enzovoort.

In sommige gevallen moet u misschien zelfs herstructureren gegevens (stel bijvoorbeeld dat u een draaitabel moet maken).

Deze stappen zijn afhankelijk van het project en hoe uw gegevens eruitzien in Excel.

De structuur van het dashboard schetsen

Zodra u de gegevens in Excel heeft, weet u precies wat u wel en niet kunt gebruiken in uw Excel-dashboard.

In dit stadium is het een goed idee om terug te keren met uw belanghebbenden met een overzicht van het Excel-dashboard.

Als best practice maak ik een eenvoudig overzicht in PowerPoint samen met aanvullende opmerkingen. Het doel van deze stap is ervoor te zorgen dat uw stakeholder begrijpt wat voor soort dashboard hij / zij kan verwachten met de beschikbare gegevens.

Het helpt ook omdat de stakeholder veranderingen kan voorstellen die meer waarde voor hem zouden toevoegen.

Hier is een voorbeeld van een voorbeeldoverzicht dat ik heb gemaakt voor een van de KPI-dashboards:

Zodra u de opzet heeft uitgewerkt, is het tijd om het Excel-dashboard te maken.

Verdeel uw Excel-werkmap als best practice in drie delen (deze zijn de werkbladen die ik maak met dezelfde naam):

• Gegevens – Dit kunnen een of meer werkbladen zijn die de onbewerkte gegevens bevatten.
• Berekeningen – Dit is waar je alle berekeningen doet. Nogmaals, je hebt misschien een of meer dan een blad voor berekeningen.
• Dashboard – Dit is het blad met het dashboard. In de meeste gevallen is het een weergave van één pagina die analyse / inzichten laat zien die worden ondersteund door gegevens.

Excel-tabel – de geheime saus van een efficiënt Excel-dashboard

Het eerste dat ik doe met de onbewerkte gegevens, is deze omzetten in een Excel-tabel.

Excel-tabel biedt veel voordelen die zijn cruciaal bij het maken van een Excel-dashboard.

Om tabelgegevens om te zetten in een Excel-tabel, selecteert u de gegevens en gaat u naar het tabblad Invoegen en klikt u op het pictogram Tabel.

Dit zijn de voordelen van het gebruik van een Excel-tabel voor uw dashboard:

• Wanneer u een tabelgegevensset omzet in een Excel-tabel hoeft u zich geen zorgen te maken dat gegevens in een later stadium worden gewijzigd. Zelfs als u aanvullende gegevens krijgt, kunt u deze eenvoudig aan de tabel toevoegen zonder dat u zich zorgen hoeft te maken dat de formules in de war raken. Dit is erg handig als ik plug-and-play dashboards maak.
• Met een Excel-tabel kunt u de namen van de kolommen gebruiken in plaats van de verwijzing. In plaats daarvan is het bijvoorbeeld C2: C1000, u kunt Verkoop gebruiken.

Belangrijke Excel-functies voor dashboards

U kunt veel goede interactieve Excel-dashboards maken door alleen Excel-formules gebruiken.

Wanneer u een selectie maakt uit een vervolgkeuzelijst, of een schuifbalk gebruikt of een selectievakje selecteert, zijn er formules die worden bijgewerkt op basis van de resultaten en u de bijgewerkte gegevens / weergave in het dashboard.

Hier zijn mijn top vijf Excel-functies voor Excel Dashboards:

• SUMPRODUCT-functie: het is mijn favoriete functie bij het maken van een interactief Excel-dashboard. Het stelt me in staat om complexe berekeningen uit te voeren als er veel variabelen zijn. Stel dat ik een verkoopdashboard heb en ik wil weten wat de verkopen waren van de vertegenwoordiger Bob in het derde kwartaal in de regio Oost. Ik kan hiervoor eenvoudig een SOMPRODUCT-formule maken.
• INDEX / MATCH-functie: ik ben een groot voorstander van het gebruik van de combinatie van INDEX- en MATCH-formule voor het opzoeken van gegevens in Excel Dashboards. Je kunt ook de functie VERT.ZOEKEN gebruiken, maar ik vind INDEX / MATCH een betere keuze.
• IFERROR-functie: wanneer je berekeningen uitvoert op de onbewerkte gegevens, krijg je vaak fouten. Ik gebruik IFERROR uitgebreid om fouten in het dashboard te verbergen (en vaak ook in de onbewerkte gegevens).
• TEXT-functie: als je dynamische koppen of titels wilt maken, moet je de TEXT-functie gebruiken voor
• RIJEN / KOLOMMEN Functie: ik gebruik deze vaak als ik een formule moet kopiëren en een van de argumenten moet worden verhoogd als we naar beneden / rechts van de cel gaan.

Dingen om in gedachten te houden bij het werken met formules in een Excel Dashboard:

• Vermijd het gebruik van vluchtige formules (zoals OFFSET, NU, VANDAAG, enz. Aan..). Deze zullen uw werkmap vertragen.
• Verwijder onnodige formules. Als u enkele aanvullende formules in het berekeningsblad heeft, verwijdert u deze terwijl u het dashboard afrondt.
• Gebruik hulpkolommen omdat dit u kan helpen om lange matrixformules te vermijden.

Interactief Hulpmiddelen om uw Excel-dashboard geweldig te maken

Er zijn veel interactieve hulpmiddelen die u kunt gebruiken om uw Excel-dashboard dynamisch en gebruiksvriendelijk te maken.

Hier zijn enkele van deze die ik regelmatig gebruik:

• Schuifbalken: gebruik schuifbalken om het onroerend goed van uw werkmap op te slaan. Als u bijvoorbeeld 100 rijen met gegevens heeft, kunt u een schuifbalk gebruiken met slechts 10 rijen in het dashboard. Een gebruiker kan de schuifbalk gebruiken als hij / zij de volledige dataset wil bekijken.
• Selectievakjes: Met CheckBoxen kunt u selecties maken en het dashboard bijwerken. Stel dat ik een trainingsdashboard heb en ik ben de CEO van het bedrijf, dan zou ik naar het algemene bedrijfsdashboard willen kijken. Maar als ik hoofd verkoop ben, wil ik alleen kijken naar de prestaties van mijn afdeling. In dat geval kunt u een dashboard maken met selectievakjes voor verschillende afdelingen van het bedrijf.
• Vervolgkeuzelijst: met een vervolgkeuzelijst kan een gebruiker een exclusieve selectie maken. U kunt deze vervolgkeuzelijsten gebruiken om het dashboard bij te werken. Als u bijvoorbeeld gegevens per afdeling weergeeft, kunt u de afdelingsnamen in de vervolgkeuzelijst plaatsen.

Excel-grafieken gebruiken om gegevens in een Excel-dashboard te visualiseren

Grafieken maken uw Excel-dashboard niet alleen visueel aantrekkelijk, maar maken het ook gemakkelijk om te gebruiken en te interpreteren.

Hier zijn enkele tips voor het gebruik van grafieken in een Excel-dashboard:

• Selecteer de juiste grafiek: Excel biedt u veel grafiekopties en u moet de juiste grafiek gebruiken. Als u bijvoorbeeld een trend moet weergeven, moet u een lijndiagram gebruiken, maar als u de werkelijke waarden wilt markeren, kan een staaf- / kolomdiagram de juiste keuze zijn. Hoewel veel experts het gebruik van een cirkeldiagram afraden, raad ik u aan uw discretie te gebruiken. Als uw publiek gewend is om cirkeldiagrammen te zien, kunt u deze net zo goed gebruiken.
• Gebruik combinatiediagrammen: ik raad het gebruik van combinatiediagrammen ten zeerste aan, omdat deze de gebruiker in staat stellen waarden te vergelijken en betekenisvolle inzichten te verkrijgen. U kunt bijvoorbeeld het verkoopcijfer weergeven als een kolomdiagram en de groei als een lijndiagram.

• Gebruik dynamische grafieken: als u wilt dat de gebruiker selecties kan maken en u wilt dat de grafiek ermee wordt bijgewerkt, gebruikt u dynamische grafieken. Nu is een dynamisch diagram niets anders dan een gewoon diagram waarvan de gegevens in de back-end worden bijgewerkt wanneer u selecties maakt.

• Gebruik sparklines om uw gegevens zinvoller te maken: als u veel gegevens in uw dashboard / rapport heeft, kunt u overwegen om sparklines te gebruiken om deze visueel te maken. Een sparkline is een kleine grafiek die zich in een cel bevindt en kan worden gemaakt met behulp van een gegevensset. Deze zijn handig wanneer u een trend in de tijd wilt laten zien en tegelijkertijd ruimte wilt besparen op uw dashboard. U kunt hier meer lezen over Excel Sparklines.
• Gebruik contrasterende kleuren om gegevens te markeren: dit is een algemene grafiektip waarbij u gegevens in een grafiek moet markeren, zodat deze gemakkelijk te begrijpen zijn. Als u bijvoorbeeld verkoopgegevens heeft, kunt u het jaar met de laagste verkoopwaarde rood markeren.

Je kunt hier door een aantal van mijn tutorials over grafieken bladeren.

Ook als je meer gevorderd wilt worden in Excel-grafieken, raad ik je aan de blog van Excel-grafiekexpert Jon Peltier te bezoeken.

Wat u wel en niet moet doen in Excel Dashboards

Laten we eerst beginnen met wat u niet mag doen!

Hier zijn enkele dingen die ik u aanbeveel te vermijden bij het maken van een Excel-dashboard. Nogmaals, deze zouden variëren op basis van uw project en belanghebbende, maar zijn in de meeste gevallen geldig.

• Maak uw dashboards niet rommelig: alleen omdat u gegevens en grafieken heeft, betekent niet dat het in je dashboard. Onthoud dat het doel van het dashboard is om een probleem te helpen identificeren of om te helpen bij het nemen van beslissingen. Houd het dus relevant en verwijder alles wat daar niet thuishoort. Ik vraag me vaak af of iets gewoon goed is om absoluut te hebben. Dan ga ik door en verwijder ik alle good-to-haves.
• Gebruik geen vluchtige formules: omdat dit de berekeningen vertraagt.
• Bewaar geen extra gegevens in je werkmap: als je die gegevens nodig hebt, maak dan een kopie van het dashboard en bewaar deze als back-up.

Laten we nu eens kijken naar enkele dos (of best practices)

• Nummering van uw grafieken / sectie: uw dashboard is niet zomaar een willekeurige reeks grafieken en gegevenspunten. In plaats daarvan is het een verhaal waarin het een naar het ander leidt.U moet ervoor zorgen dat uw publiek de stappen in de juiste volgorde volgt, en daarom kunt u deze het beste nummeren. Hoewel u ze misschien kunt begeleiden wanneer u live presenteert, is het een grote hulp wanneer iemand uw dashboard in een later stadium gebruikt of er een afdruk van maakt.
• Beperk beweging in het dashboardgebied: Verberg alle rijen / kolommen om ervoor te zorgen dat de gebruiker niet per ongeluk wegschuift.
• Zet belangrijke rijen / kolommen vast: gebruik vastzetvensters als u wilt dat sommige rijen / kolommen altijd zichtbaar zijn op het dashboard.
• Vormen / grafieken laten plakken: zorg ervoor dat uw vormen / grafieken of interactieve bedieningselementen niet verbergen of de grootte ervan wijzigen wanneer iemand de cellen verbergt / vergroot of verkleint. U kunt ook de Excel Camera-tool gebruiken om een momentopname van grafieken / tabellen te maken en deze in het Excel-dashboard te plaatsen (deze afbeeldingen zijn dynamisch en worden bijgewerkt wanneer de back-endgrafiek / tabel wordt bijgewerkt).
• Geef een Gebruikershandleiding: Als u een complex dashboard heeft, is het een goed idee om een apart werkblad te maken en de stappen te markeren. Het zal mensen helpen uw dashboard te gebruiken, zelfs als u er niet bent.
• Ruimte besparen met combinatiediagrammen: gebruik combinatiediagrammen (zoals bullet-diagrammen, thermometergrafieken en werkelijke versus doeldiagrammen) om uw werkblad op te slaan spatie.
• Gebruik symbolen & Voorwaardelijke opmaak: gebruik symbolen (zoals pijlen omhoog / omlaag of vinkje / kruisje) en voorwaardelijke opmaak om een analyselaag naar uw dashboard (maar overdrijf het ook niet).

Wilt u professionele dashboards maken in Excel? Bekijk de Online Excel Dashboard-cursus waar ik je alles laat zien over het maken van een Excel-dashboard van wereldklasse.

Dashboard-voorbeelden

Hier zijn enkele coole Excel-dashboardvoorbeelden die je kunt downloaden en spelen met.

Excel KPI Dashboard

U kunt dit dashboard gebruiken om KPIs van verschillende bedrijven en gebruik vervolgens bullet-charts om diep in de prestaties van het individuele bedrijf te duiken.

Klik hier om dit KPI-dashboard te downloaden.

Als u geïnteresseerd bent in het maken van dit KPI-dashboard, klik dan op hier.

Call Center Performance Dashboard

U kunt dit dashboard gebruiken om de belangrijkste KPIs van een callcenter. Vanuit dit dashboard kunt u leren hoe u combinatiegrafieken maakt, hoe u specifieke gegevenspunten in grafieken markeert, hoe u sorteert met keuzerondjes, enz.

Klik hier om het dashboard te downloaden.

U kunt hier meer lezen over dit dashboard.

EPL-seizoen gevisualiseerd in een Excel-dashboard

In dit dashboard leert u hoe u VBA in Excel-dashboards gebruikt. In dit dashboard worden de details van de games bijgewerkt wanneer je dubbelklikt op de cellen aan de linkerkant. Het gebruikt ook VBA om een helpmenu weer te geven om de gebruiker te begeleiden bij het gebruik van dit dashboard.

Klik hier om het EPL-dashboard te downloaden.

U kunt hier meer over dit dashboard lezen.

Aanbevolen dashboardtraining

Als u wilt leren hoe u professionele dashboards van wereldklasse kunt maken in Excel, bekijk dan mijn GRATIS online Excel Dashboard-cursus.