Een heatmap maken in Excel: stapsgewijze handleiding

Om een heatmap in Excel te maken, gebruik je Voorwaardelijke opmaak. Selecteer je gegevens, pas een kleurenschaal toe en Excel kleurt elke cel in op basis van de waarde.

Het resultaat is een raster met kleuren in plaats van cijfers. Donkere of fellere tinten markeren je hoogste en laagste waarden, zodat je patronen ziet door te kijken in plaats van elke cel te lezen.

In deze gids bouw je vanaf nul een heatmap, pas je de kleuren aan en gebruik je dezelfde techniek op draaitabellen. We behandelen ook de fouten die de meeste beginners in de war brengen.

Wat is een heatmap in Excel?

Een heatmap is een raster van cellen met kleuren in plaats van kale cijfers. Hoge cijfers krijgen één kleur. Lage cijfers een andere. Excel vult zelf elke tussentint in. Je doet het inkleuren dus niet handmatig. 

Dit gebeurt via een functie genaamd Voorwaardelijke opmaak, specifiek de optie Kleurenschaal. 

Kies een celbereik, pas een kleurenschaal toe en Excel bekijkt elke waarde in dat bereik. Vervolgens kleurt het elke cel op basis van hoe hoog of laag dat getal is ten opzichte van de rest. Pas je later een getal aan, dan werkt de kleur ook bij.

Je ziet patronen in plaats van ernaar te moeten zoeken. Zodra je pieken, dalen en uitschieters zichtbaar zijn, kun je regio's of maanden in seconden vergelijken in plaats van minuten. 

Laten we dit begrijpen met een voorbeeld van een verkoopdataset: 

Verkoopdataset om een heatmap te maken. Afbeelding door auteur.

Hoe maak je een heatmap in Excel

Om een heatmap te maken:

1. Markeer het celbereik dat je wilt kleuren. In ons geval selecteren we alleen de cijfers, niet de regiosnamen of maandkoppen
2. Ga naar Start > Voorwaardelijke opmaak
3. Klik op het vervolgkeuzemenu
4. Beweeg over Kleurenschaal en er verschijnt een submenu met verschillende vooringestelde kleurcombinaties. Elke preset toont als een klein gekleurd raster, zodat je ongeveer ziet hoe het eruitziet voordat je het toepast

Maak een heatmap met Voorwaardelijke opmaak. Afbeelding door auteur.

Onze verkooptabel heeft nu kleur. Sterke maanden in Zuid neigen naar groen. Zwakkere in Oost neigen naar rood of oranje, en de rest zit daar tussenin.

Let op: de kleuren passen zich aan op basis van de nieuwe pieken en dalen zodra je nieuwe waarden toevoegt.

Hoe pas je een heatmap aan

De standaard kleurenschaal werkt, maar is zelden de beste keuze voor elke dataset. Excel biedt een paar manieren om deze aan te passen, elk voor een ander probleem.

Verander de kleurenschaal

Om de kleurenschaal te wijzigen: 

1. Selecteer je gegevensbereik
2. Open opnieuw Kleurenschaal 
3. Beweeg over elke preset om een voorbeeld te zien op je eigen gegevens

Sommige schalen gebruiken twee kleuren, andere drie. 

• Een tweekleurenschaal (bijvoorbeeld Wit naar Rood) is ideaal als je maar één richting belangrijk vindt, zoals het uitlichten van je toppresteerders. 
• Een driekleurenschaal werkt beter wanneer beide extremen ertoe doen, zoals het markeren van zowel onder- als overpresterende regio's in de verkooptabel.

Kleuren uit de bibliotheek met kleurenschalen voor een heatmap. Afbeelding door auteur.

Draai de kleuren om

Om kleuren om te keren: 

1. Selecteer het gegevensbereik 
2. Ga naar Voorwaardelijke opmaak > Regels beheren 
3. Selecteer in het dialoogvenster Regels voor voorwaardelijke opmaak beheren je kleurenschaalregel, klik op Regel bewerken en je ziet opties voor de minimale en maximale kleuren. 
4. Wissel ze om en de schaal draait om. Wat blauw was, wordt rood, en wat rood was, wordt blauw.

Dit is handig in situaties waarin lage cijfers goed nieuws kunnen zijn, niet slecht.

Wissel de kleuren van de heatmap om. Afbeelding door auteur.

Maak een aangepaste kleurenschaal

Als geen van de presets past, bouw dan je eigen kleurenschaal. Zo doe je dat: 

1. Selecteer je gegevensbereik
2. Ga naar Voorwaardelijke opmaak > Nieuwe regel > Alle cellen opmaken op basis van hun waarden
3. Kies je eigen kleuren voor minimum, middelpunt en maximum, in plaats van een vooringestelde combinatie te kiezen

Je kunt deze aanpak gebruiken om een heatmap te laten aansluiten bij een specifieke huisstijlkleur.

Voeg aangepaste kleuren toe aan je heatmap. Afbeelding door auteur.

Pas de minimale, middelste en maximale waarden aan

Standaard baseert Excel de schaal op de laagste en hoogste getallen in je geselecteerde bereik. Dat is meestal prima, maar kan vertekenen als je één extreme uitschieter hebt.

Om dit aan te passen: 

1. Selecteer je gegevensbereik
2. Ga naar Voorwaardelijke opmaak > Regels beheren > Regels bewerken
3. Wijzig in het dialoogvenster Regel voor opmaak bewerken via het vervolgkeuzemenu het waardetype voor minimum, middelpunt of maximum van Laagste waarde en Hoogste waarde naar Getal, Percentage of Percentiel via het vervolgkeuzemenu
4. Kies de minimale, middelste en maximale waarde naar wens 

Pas de minimale, maximale en middelste waarden in de heatmap aan. Afbeelding door auteur.

Geen van deze aanpassingen is vereist om een werkende heatmap te bouwen. Maar hiermee maak je het verschil tussen een heatmap die alleen kleur geeft en een die je gegevens echt beter leesbaar maakt. 

Vervolgens passen we deze hulpmiddelen toe in een paar verschillende scenario's, te beginnen met verkoopgegevens.

Hoe maak je verschillende soorten heatmaps

Om verschillende soorten heatmaps te maken, volg je telkens dezelfde vijf stappen:

1. Selecteer de gegevens
2. Ga naar Start 
3. Ga naar Voorwaardelijke opmaak 
4. Klik op Kleurenschaal
5. Kies een schaal

Laten we dit bekijken aan de hand van vier verschillende use-cases: 

Heatmaps voor verkoopgegevens

Stel dat je de verkoop per maand bijhoudt voor vier regio's en je wilt zien waar het bedrijf het sterkst is zonder zestien afzonderlijke cijfers te hoeven scannen.

• Selecteer de tabel
• Ga naar Start > Voorwaardelijke opmaak > Kleurenschaal en kies een driekleurenschaal

Daarna licht Oost elke maand groen op, want dat is je sterkste regio. West blijft rood omdat die aandacht nodig heeft. 

Een driekleurenschaal werkt hier omdat je beide uiteinden tegelijk zichtbaar wilt: de beste en de zwakste presteerder naast elkaar.

Pas een heatmap toe op een verkoopdataset. Afbeelding door auteur.

Heatmaps voor prestatiedashboards

Stel dat je een klein team aanstuurt en in plaats van verkoop kijk je naar prestatiescores over verschillende vaardigheden.

Pas dit keer een tweekleurenschaal toe omdat je alleen wilt zien wie sterk is en wie achterblijft. In de resultaten zie je:  

• Lee is bijna overal groen
• Priya is bijna overal wit
• Sam is gemengd, sterk in follow-ups maar zwak in deals 

Pas een heatmap toe om de dashboardprestaties te volgen. Afbeelding door auteur.

Heatmaps voor enquêteresultaten

Misschien heb je net een klantenenquête uitgevoerd en heb je gemiddelde beoordelingen op een schaal van 1 tot 5 over enkele vragen en perioden.

Stel hier het minimum en maximum handmatig in — 1 en 5 — in plaats van Excel ze uit de gegevens te laten berekenen. 

Enquêtescores gebruiken zelden het volledige bereik, dus een automatische schaal zou kleine verschillen uitvergroten. Met een vaste schaal komt "Prijs-kwaliteitverhouding" over als een echt zwak punt in plaats van alleen het laagste getal in een krap cluster.

Pas een heatmap toe om de klantenenquête te volgen. Afbeelding door auteur.

Heatmaps voor projecttracking

Stel dat je een project met een handvol taken bijhoudt en in één oogopslag wilt zien welke achterlopen.

Stel de schaal zo in dat lage procentages rood zijn en hoge percentages wit. Zo betekent rood altijd "achterstand." 

Een tweekleurenschaal werkt hier omdat de vraag binair is: op schema of niet.

• Documentatie blijft rood in elke fase, want dat is in elke stap het laagste voltooingspercentage in de tabel
• API-integratie ziet er goed uit in de planning, maar zakt daarna snel weg

Dat is hier de waarde van de heatmap: hij laat zowel de trage taak zien als wanneer die achterop begon te raken.

Pas een heatmap toe om de projecttaken te volgen. Afbeelding door auteur. 

Hoe gebruik je heatmaps met draaitabellen

Pas Voorwaardelijke opmaak toe op een draaitabel op dezelfde manier als je dat zou doen op elk regulier bereik. 

Eerst heb je een draaitabel nodig om dit op toe te passen. Om er een in te voegen:

1. Selecteer je tabel
2. Ga naar Invoegen > Draaitabel 
3. Kies Nieuw werkblad en klik op OK

Maak een draaitabel. Afbeelding door auteur.

4. Er opent rechts een leeg draaitabelgebied, samen met een veldenlijst met Regio, Maand en Verkoop
5. Sleep Regio naar het vak Rijen, Maand naar het vak Kolommen en Verkoop naar het vak Waarden

Stel de draaitabelvelden in. Afbeelding door auteur.

Nu heb je een draaitabel om te kleuren. Om deze te kleuren:

1. Selecteer de waarden erin, niet de rij- of kolomlabels
2. Ga naar Start > Voorwaardelijke opmaak > Kleurenschaal en kies zoals eerder een schaal

Voorwaardelijke opmaak toepassen met een draaitabel. Afbeelding door auteur.

Heatmaps vs kleurenschalen vs voorwaardelijke opmaak

Heatmaps, kleurenschalen en voorwaardelijke opmaak worden door elkaar gebruikt, maar zijn niet helemaal hetzelfde:

• Voorwaardelijke opmaak is de overkoepelende tool die de weergave van cellen aanpast op basis van hun waarden, en omvat veel meer dan kleur. Gegevensbalken, pictogramsets en markeerregels vallen allemaal onder Voorwaardelijke opmaak.
• Kleurenschalen zijn één optie binnen Voorwaardelijke opmaak. Dit kleurt cellen in over een verloop op basis van hun waarden. Je gebruikt één functie van meerdere die onder datzelfde menu beschikbaar zijn.
• Heatmap is het resultaat dat je ziet. Het is hoe de gegevens eruitzien nadat er een kleurenschaal op is toegepast. 

Dus als iemand zegt "voeg een heatmap toe", bedoelt die eigenlijk: "pas een kleurenschaal toe met Voorwaardelijke opmaak." 

Veelgemaakte fouten bij heatmaps

De meeste heatmaps lopen tegen dezelfde handvol problemen aan. Dit veroorzaakt ze en zo los je het op.

Kleuren komen niet overeen met de gegevens

Dit gebeurt meestal wanneer minimum en maximum van de kleurenschaal blijven staan op "Laagste waarde" en "Hoogste waarde", maar je dataset één extreme uitschieter bevat. Dat ene getal rekt de hele schaal op, waardoor de rest samengedrukt wordt tot een smalle band vergelijkbare kleuren.

Oplossing: Stel vaste waarden in voor minimum en maximum in plaats van Excel ze automatisch te laten berekenen. Open Voorwaardelijke opmaak > Regels beheren > Regel bewerken en wijzig het waardetype van Laagste waarde naar Getal.

Blanco cellen beïnvloeden de resultaten

Lege cellen binnen een kleurenschaalbereik kunnen beïnvloeden hoe Excel het minimum of middelpunt berekent, zeker bij percentielen in plaats van vaste getallen.

Oplossing: Verwijder lege cellen uit het bereik, of vul ze met nul of een placeholderwaarde voordat je de kleurenschaal toepast. Controleer je bereik goed als de gegevens uit een rapport komen dat gaten laat voor ontbrekende waarden.

Opmaak werkt niet bij

Voorwaardelijke opmaak die op een vaste selectie is toegepast, blijft vast. Het bereik wordt niet groter alleen omdat je getallen in de volgende rij typt.

Oplossing: Zet je bereik om in een Excel-tabel voordat je de kleurenschaal toepast. Excel-tabellen breiden automatisch uit als je rijen toevoegt, en regels voor voorwaardelijke opmaak groeien mee. 

Als je met een draaitabel werkt, pas de kleurenschaal dan rechtstreeks toe op de waarden, zodat deze alle "cellen die" dat veld tonen dekt, zoals eerder besproken.

Te veel kleuren maken de gegevens onleesbaar

Dit gebeurt wanneer meerdere kleurenschalen boven op hetzelfde bereik worden gestapeld, of wanneer een schaal te veel scherpe kleurwisselingen tegelijk gebruikt. In plaats van een strak verloop krijg je een rommelige mix die moeilijker te lezen is dan kale cijfers.

Oplossing: Beperk je tot één kleurenschaal per bereik, en houd het bij maximaal twee of drie kleuren. Als een tabel meer dan één soort opmaak nodig heeft, pas dan kleurenschalen toe op kleinere, afzonderlijke secties in plaats van regels over elkaar heen te leggen.

Best practices voor Excel-heatmaps

Een heatmap is alleen nuttig als hij makkelijk te lezen is. Deze gewoontes helpen daarbij:

• Gebruik consistente kleurenschalen in verwante rapporten. Wisselen tussen rood-groen de ene maand en blauw-wit de volgende maakt rapporten lastiger naast elkaar te vergelijken, ook als elk rapport op zichzelf prima oogt.
• Vermijd te felle of afleidende kleuren omdat die het moeilijker maken om je op de onderliggende gegevens te concentreren.
• Kies kleuren die werken voor kleurenblinde lezers, zoals een blauw-oranje of blauw-gele schaal, en vermijd rood-groen, want dat is de lastigste combinatie voor kleurenblinden om te onderscheiden. 
• Verwijder losse lege cellen, corrigeer inconsistente getalnotaties en controleer op tekst in een numeriek bereik om je dataset schoon te houden voordat je een heatmap toepast.
• Combineer heatmaps met filters of draaitabellen voor grotere datasets, zodat lezers de weergave kunnen verfijnen nog voordat de kleuren echt helpen.

Tot slot 

Je kent nu de vijf stappen. Probeer ze vervolgens op je eigen gegevens in plaats van onze demotabel. Werk je met grotere datasets, leer dan ook draaitabellen, want heatmaps en draaitabellen gaan goed samen en de meeste echte datasets zijn te groot om handmatig te kleuren.

Onze cursus Data-analyse in Excel behandelt beide onderwerpen uitgebreider als je verder wilt gaan.