In één oogopslag duidelijk: zo maak je heatmaps in Excel
Het kost moeite om een Excel-tabel met heel veel data snel te interpreteren. Het wordt heel wat makkelijker als het programma automatisch kleuren genereert op basis van de waarden in de cellen. Met zo’n heatmap verandert een tabel in een overzichtelijke afbeelding waardoor je een goed beeld krijgt van de problemen of prestaties.
Na het lezen van dit artikel weet je hoe je in Excel een heatmap kunt maken om in één oogopslag de gegevens in een tabel te duiden.
- Voeg voorwaardelijke opmaak toe
- Grafiek maken
- 3D-globe met gegevens
- Dynamische heatmap
Handig om te weten: Weet jij al hóé handig Excel is met data ordenen?
Een heatmap of hittekaart geeft op een visuele manier de gegevens weer. De getalwaarden komen overeen met bepaalde kleuren. Daarmee zie je onmiddellijk waar het in het rood gaat en waar het veilig in groen zit. Deze techniek komt vooral van pas als je resultaten in een spreadsheet wilt tonen met veel data.
Voorwaardelijke opmaak
De eenvoudigste manier om een heatmap in Excel te maken, is gebruikmaken van de voorwaardelijke opmaak. Hiermee zie je verschillende kleurtinten op basis van de waarden in de cellen. Om te beginnen selecteer je het celbereik in de heatmap. Daarbij neem je de kolom- en rijkoppen niet op in de selectie. Daarna ga je naar het tabblad Start. Open nu het vervolgkeuzemenu Voorwaardelijke opmaak en kies voor Kleurschalen. Als je de muisaanwijzer over de twaalf opties beweegt, krijg je een voorvertoning.
Selecteer de kleurenschaal die je wilt gebruiken. In ons voorbeeld gebruiken we een tabel van verkeersslachtoffers, afhankelijk van de maximumsnelheid op de plek van het ongeval (bron: Rijkswaterstaat). Omdat een hoog cijfer in dit geval kritiek is, kiezen we voor een kleurschaal waarbij de hoge waarden rood en de laagste waarden groen zijn. In een oogopslag zie je hoe de zaken ervoor staan.
Hier kiezen we voor de kleurenschaal rood-geel-groen.
Zelf kleuren bepalen
Wat als je geen kleurverloop wilt en alleen puur rood, geel en groen wilt weergeven? Of als je andere kleuren wilt gebruiken? Je kunt zelf ook een bepaalde kleurenset of slechts twee of drie kleuren gebruiken via een nieuwe opmaakregel. Selecteer opnieuw de gegevens waar het om draait, ga naar het tabblad Start, open het vervolgkeuzemenu Voorwaardelijkeopmaak en kies Nieuwe regel.
Wanneer het dialoogvenster Nieuwe opmaakregel opent, kies je de eerste optie bovenaan: Alle cellen opmaken op basis van de celwaarden. Daarna kun je onderaan in de vervolgkeuzelijst Opmaakstijl de opties 2-kleurenschaal of 3-kleurenschaal selecteren. Verder naar onder kies je bij Type: Minimum / Laagstewaarde een kleurtint. Bij Maximum / Hoogstewaarde selecteer je de kleurtint die hoogste waarden krijgt. Wanneer je klaar bent, klik je op OK om de heatmap met voorwaardelijke opmaak op de set gegevens toe te passen.
Het is mogelijk om zelf een kleurenschaal te bepalen.
Zonder getalwaarden
Het is mogelijk om alleen de kleuren te gebruiken en de getallen uit beeld te halen. Je doet dit wanneer de waarden minder betekenisvol zijn dan de kleuren. Selecteer weer de cellen die getallen bevatten. Klik er dan met de rechtermuisknop op de selectie en kies Celeigenschappen. In het tabblad Getal selecteer je bij Categorie de onderste optie: Aangepast. Vervolgens typ je aan de rechterkant in het veld Type drie keer een puntkomma (dus ;;;). Daarna klik je op OK. Wanneer je nu terugkeert naar de spreadsheet zie je dat de gegevens verdwenen zijn en dat alleen de kleuren van de heatmap overblijven. Houd er rekening mee dat de voorwaardelijke opmaak opnieuw wordt berekend als er een wijziging in het werkblad plaatsvindt. In kleine datasets is dit verwaarloosbaar, maar met grote datasets kan dit leiden tot een trage Excel-werkmap.
Met drie puntkomma’s verdwijnen de getallen.
Op de kaart
Met geografische gegevens kun je de heatmap een extra dimensie geven. Hiermee verschijnen de waarden op de locaties als kleur gecodeerde indicatoren. Selecteer eerst de gegevens en neem daarin de locatienamen mee. In dit voorbeeld hebben we de landen van Europa opgesomd en in de tweede kolom staat het aantal inwoners x1000. Nadat je de dataset hebt geselecteerd, ga je naar het tabblad Invoegen. Daar selecteer je Kaarten en daaronder kies je de optie Choropletenkaart. Met de choropletenkaart kun je de waarden vergelijken tussen regio’s en landen. Wanneer het kaartdiagram in beeld komt, zie je de waarden weergegeven met kleuren.
De landen met de donkerste kleuren hebben de meeste inwoners.
Grafiekontwerp
Afhankelijk van de gegevens die je weergeeft, kun je onder meer het kaartgebied, de kleurschaal en de legende aanpassen. Als je de kaartgrafiek selecteert, komt er in het lint een nieuw tabblad bij: Grafiekontwerp. Open dit om een andere lay-out te kiezen. Klik op de plusknop naast de kaart om te bepalen welke grafiekelementen je wilt zien: Grafiektitel, Gegevenslabels en Legenda.
In dit voorbeeld zijn de gegevenslabels het aantal inwoners per land. Als je de gegevenslabels in beeld hebt gebracht, kun je op zo’n label klikken en het lettertype, de lettergrootte en letterkleur in het tabblad Start aanpassen. Als je de opmaak van één label wijzigt, nemen de andere labels deze opmaak vanzelf over. Klik je op de penseelknop naast de kaart, dan kun je de stijl van de kaart en de kleurenschaal van de heatmap aanpassen.
Bedenk welke kleurenschaal je wilt gebruiken.
Driekleurenschaal
Soms is het duidelijker om een driekleurenschaal te gebruiken op de kaart. Klik daarvoor op de kaart zodat in de rechterbalk de optie Gegevensreeks opmaken verschijnt. Deze zijbalk bestaat uit drie tabbladen. Je hebt het derde tabblad nodig, dat heet Opties voor reeks. Houd de Kaartprojectie rustig op Automatisch. Ook het Kaartgebied dat je in beeld wilt brengen, zet je op Automatisch. Tenzij je de gegevens op een volledige wereldkaart wilt bekijken, dan kies je de optie Wereld in het uitklapmenu.
De derde optie zijn de Labels. Hier tonen we zowel de landen als het aantal inwoners. Klap daarna de functie Reekskleur open. Hier kun je kiezen tussen Sequentieel (2 kleuren) of Uiteenlopend (3 kleuren).Bij Minimum, Middelpunt en Maximum kun je telkens een andere kleur bepalen die Excel als maatstaf neemt om alle tussenliggende tinten te produceren.
In drie kleuren wordt alles nog duidelijker.
Geografische 3D-heatmap
Excel heeft ook een optie om in 3D een geografische hittekaart te maken met geavanceerde opties. Het resultaat is een 3D-wereldbol die je kunt roteren en waarop je kunt inzoomen. Begin met de gegevens te selecteren van de kaart, inclusief de locatienamen en de kolom- en rijkoppen. Daarna ga je naar het tabblad Invoegen en in de groep Rondleiding klik je op de knop 3D-kaart en vervolgens op 3D-kaart opencelen. Als je op deze manier ooit een 3D-kaart hebt gemaakt, dan kun je die nu selecteren. Wil je een nieuwe presentatie maken, dan klik je onderaan dit menu op de plusknop en kies je Nieuwe tour.
Start een nieuwe tour.
Thema’s en kaartlabels
Hierdoor kom je in een nieuwe werkomgeving waarin je de aarde ziet met de landsgrenzen. Met de pijltjes onderaan kun je de globe roteren en met het plus- en minteken zoom je in en uit. Je kunt de aardbol ook gewoon vastgrijpen met de muisaanwijzer en op die manier roteren en voor de zoomfunctie gebruik je net zo gemakkelijk het muiswieltje.
Als je op Kaartlabels klikt, verschijnen de namen van de landen. Op dit moment zie je ook blauwe punten op de wereldbol. Dat zijn de locaties van de cellen die data bevatten. We hebben in dit voorbeeld trouwens dezelfde gegevens gebruikt als daarnet: het aantal inwoners van de Europese landen.
De blauwe punten geven de locaties aan die gegevens bevatten.
Heatmap toepassen
Het lagenvenster is automatisch geopend aan de rechterkant. Als dat niet het geval is dan kun je hier in het tabblad Start (dat is een ander tabblad Start dan normaal in Excel) de knop Deelvenster Laag klikken. In dit deelvenster zie je onder Gegevens vijf pictogrammen. Hiermee bepaal je hoe je de gegevens wilt zien: als gestapelde kolommen, gegroepeerde kolommen, bubbels, heatmap of in gekleurde regio’s.
Selecteer Heatmap. Bij Locatie klik je op +Veldtoevoegen en in ons voorbeeld is dat het veld Land. De veldnaam is gewoon de kolomkop van de Excel-tabel waarmee we vertrokken. Bij Waarde klik je opnieuw op +Veld toevoegen. In ons voorbeeld selecteren we het veld Inwoners (x1000). Daardoor verschijnt de heatmap op de wereldbol. In de optiebalk bovenaan selecteer je Legenda. Hiermee krijg je in een balkje te zien hoe de kleuren bepaalde waarden weergeven.
In het begin ziet de heatmap er nog bescheiden uit.
Hittevlekken
Vind je die gekleurde bubbels van de heatmap wat te klein? Ga dan in het rechterdeelvenster een beetje naar beneden en open de Laagopties. Met de schuifregelaar Invloedradius vergroot je de bubbels traploos en laat je ze zelfs in elkaar overlopen. Met de regelaar Kleurenschaal zorg je dat je duidelijker de kleurwaarden ziet. Ben je tevreden? Plaats dan een vinkje bij Huidige schaal vergrendelen.
Ook nu zijn de kleuren die Excel gebruikt om de heatmap samen te stellen aanpasbaar. In het deelvenster staat de optie Kleuren op Standaard. Wanneer je die instelling wijzigt in Aangepast kun je een kleur selecteren voor Laag en een andere kleur voor Hoog. Op die manier verkrijg je verloop van twee kleuren. Met de knop Kleur toevoegen kies je een derde kleur en zo kun je meerdere tinten aan de kleurenschaal toevoegen. In het vak Legenda lees je voor welke waarden die kleuren staan. Ben je klaar, dan gebruik je bovenaan de knop Scherm vastleggen om een afbeelding te maken van de heatmap.
Bepaal de grootte van de hittevlekken en kleuren waarmee ze zijn samengesteld.
Dynamische heatmap
Een dynamische heatmap is een heatmap die een kleurencode toepast op data die het uit een ander Excel-blad ophaalt. Dit is vooral nuttig als de dataset op werkblad 1 veel gegevens bevat, terwijl je op werkblad 2 een dashboard wilt tonen waar je met een schuifbalkje door de volledige dataset kunt scrollen in een beperkte weergave.
In dit voorbeeld beginnen we op blad 1 met de twaalf maanden van het jaar die de resultaten van zeven jaar weergeven. Kopieer de maanden van kolom A en open een nieuw werkblad. Op blad 2 plak je de gekopieerde gegevens in de eerste kolom.
Vervolgens heb je het tabblad Ontwikkelaars nodig. Als dat tabblad bij jou niet in Excel te zien is, moet je dat nog toestaan in Bestand / Opties / Lint aanpassen. In het tabblad Ontwikkelaar klik je op het item Schuifbalk en daarna selecteer je de plaats waar je de schuifbalk wilt zien. Hier is die balk drie kolommen breed en komt hij onder de cellen waar de straks de gegevens zullen opgehaald worden.
Bovenaan blad 1 met de data, onderaan blad 2 met de schuifbalk.
Besturingselement opmaken
Klik met rechts op de schuifbalk en open de opdracht Besturingselement opmaken. Wijzig Minimumwaarde in 1, Maximumwaarde in 5, Stapsgewijzewijziging in 1 en Paginawijziging in 3. Bij Koppeling met cel klik je op het pictogram in het invulvak om een cel te selecteren. Ga dan naar Blad 1 en selecteer een lege cel naast de dataset. In ons voorbeeld is dat cel Blad1!$J$1. De dollartekens geven aan dat dit een absolute waarde is. Druk op Enter.
Maak het besturingselement op.
Dynamisch dashboard
We zijn er bijna … Terug naar blad 2. In cel B1 moet je de formule typen of plakken die er in ons geval uitziet als:
=INDEX(Blad1!$B$1:$H$13;RIJ();Blad1!$J$1+KOLOMMEN(Blad2!$B$1:B1)-1)Hierdoor haalt deze cel de informatie uit blad 1 op. Druk op Enter. Je ziet dat hierdoor de gegevens van deze cel inderdaad verschijnen. Klik op cel B1 en sleep deze formule ook naar de onderstaande rijen en de volgende drie kolommen. Vanaf nu kun je via de pijltjes van de schuifbalk doorheen de volledige dataset bladeren.
Tot slot selecteer je de inhoud van deze cellen in de drie kolommen en pas je in blad 2 de voorwaardelijke opmaak toe, zoals we in het begin van het artikel hebben uitgelegd. Hierdoor verschijnen de kleurschalen in het scrollvenster, terwijl die in de oorspronkelijke dataset op blad 1 niet zichtbaar zijn.
De gegevens verschijnen in een scrollvenster met de heatmap-kleuren.
