Maak een heatmapgrafiek in Excel

In Excel lijkt een heatmapgrafiek op een tabel die een visuele weergave biedt en een vergelijkend overzicht van een dataset toont. Als uw werkblad een grote dataset bevat, is het moeilijk om direct lagere of hogere waarden te herkennen — maar in een heatmap worden celwaarden weergegeven met verschillende kleuren, zodat u grotere of kleinere waarden snel en gemakkelijk kunt zien, zoals in de onderstaande afbeelding wordt getoond.

• Maak een eenvoudige heatmapgrafiek met Voorwaardelijke opmaak gebruiken
• Maak een dynamische heatmapgrafiek in Excel
• Voorbeeld 1: Maak een dynamische heatmap met behulp van een schuifbalk
• Voorbeeld 2: Maak een dynamische heatmap met behulp van Keuzerondje
• Voorbeeld 3: Maak een dynamische heatmap met behulp van Selectievakje
• Download het voorbeeldbestand van de heatmapgrafiek
• Video: Maak een heatmapgrafiek in Excel

Maak een eenvoudige heatmapgrafiek met Voorwaardelijke opmaak gebruiken

Er is geen ingebouwde heatmapfunctie in Excel, maar met de krachtige functie Voorwaardelijke opmaak kunt u razendsnel een heatmap maken. Volg hiervoor deze eenvoudige stappen:

1. Selecteer het gegevensbereik waarop u de voorwaardelijke opmaak wilt toepassen.

2. Klik vervolgens op Start > Voorwaardelijke opmaak gebruiken > Kleurschalenen kies daarna een gewenste stijl uit de uitgevouwen vervolgkeuzelijst aan de rechterkant (in dit geval kies ik)Groen – Geel – Rood kleurschaal). Zie afbeelding:

3. De heatmap is nu klaar en markeert de cellen op basis van hun waarden: groen staat voor de hoogste waarden, rood voor de laagste, en de waarden daartussen worden weergegeven met een vloeiende kleurovergang van groen naar rood. Zie afbeelding:

4. Als u de getallen wilt verbergen en alleen de kleuren wilt behouden, selecteert u het gegevensbereik en drukt u op Ctrl + 1 om het dialoogvenster Celopmaak instellen te openen.

5. In het dialoogvenster Celopmaak instellen, klikt u onder het tabblad Getal op de optie Aangepast in het linkerdeelvenster Categorie en voert u vervolgens ;;; in het tekstvak Type in. Zie afbeelding:

6. Klik daarna op de knop OK, waarna alle getallen worden verborgen, zoals in de onderstaande afbeelding wordt getoond:

Opmerking: Om cellen met andere gewenste kleuren te markeren, selecteer je het gegevensbereik en klik je vervolgens op Start > Voorwaardelijke opmaak gebruiken > Regels beheren om het dialoogvenster Voorwaardelijke opmaak gebruiken – Regels beheren te openen.

Dubbelklik vervolgens op de bestaande regel om het dialoogvenster Opmaakregel bewerkente openen en pas de regel naar wens aan, zie afbeelding:

Maak een dynamische heatmapgrafiek in Excel

Voorbeeld 1: Maak een dynamische heatmap met behulp van een schuifbalk

Als uw werkblad meerdere kolommen met gegevens bevat maar u deze binnen een beperkte ruimte wilt weergeven, kunt u een schuifbalk toevoegen om de heatmap dynamisch te maken, zoals in de onderstaande demo wordt getoond.

Volg de volgende stappen om dit type dynamische heatmapgrafiek te maken:

1. Voeg een nieuw werkblad in en kopieer vervolgens de eerste kolom met maanden van het oorspronkelijke blad naar dit nieuwe blad.

2. Klik vervolgens op Ontwikkelaar > Invoegen > Schuifbalk, zie afbeelding:

3. Sleep vervolgens met de muis een schuifbalk onder de gekopieerde gegevens, klik er met de rechtermuisknop op en selecteer Besturingselement opmaken, zie afbeelding:

4. In het dialoogvenster Object opmaken stelt u onder het tabblad Besturingselement de minimale waarde, maximale waarde, stapgrootte, paginagrootte en de gekoppelde cel in op basis van uw gegevensbereik, zoals in de onderstaande afbeelding wordt getoond:

5. Klik vervolgens op OK om dit dialoogvenster te sluiten.

6. Voer nu in cel B1 van dit nieuwe blad de volgende formule in en druk op Enter om het eerste resultaat te verkrijgen:

Opmerking: In de bovenstaande formule is data1!$B$1:$I$13 het oorspronkelijke werkblad met het gegevensbereik zonder de rijkop (maanden), $I$1 de cel waaraan de schuifbalk is gekoppeld, en $B$1:B1 de cel waarin u de formule invoert.

7. Sleep vervolgens deze formulecel naar de overige cellen. Als u slechts 3 jaar in het werkblad wilt weergeven, sleept u de formule van B1 naar D13, zie afbeelding:

8. Pas vervolgens de Kleurschaal van de functie Voorwaardelijke opmaak gebruiken toe op het nieuwe gegevensbereik om de heatmap te maken. Wanneer u de schuifbalk versleept, beweegt de heatmap nu dynamisch — zie afbeelding:

Voorbeeld 2: Maak een dynamische heatmap met behulp van Keuzerondje

U kunt ook een dynamische heatmap maken met behulp van Keuzerondje. Door één Keuzerondje te selecteren, worden de grootste n waarden gemarkeerd, en door een andere Keuzerondje te selecteren, worden de kleinste n waarden gemarkeerd, zoals in de onderstaande demo wordt getoond:

Volg deze stappen om dit type dynamische heatmap te maken:

1. Klik op Ontwikkelaar > Invoegen > Keuzerondje (formulierbesturingselement) en sleep vervolgens met de muis om twee keuzerondjes te tekenen. Bewerk de tekst naar wens, zie afbeelding:

2. Klik met de rechtermuisknop op het eerste keuzerondje nadat u dit hebt ingevoegd en selecteer Besturingselement opmaken. In het dialoogvenster Besturingselement opmaken, onder het tabblad Besturingselement, selecteert u een cel die is gekoppeld aan het keuzerondje. Zie de schermafbeelding:

3. Klik op de knop OK om het dialoogvenster te sluiten en herhaal vervolgens bovenstaande stap (stap 2) om het tweede keuzerondje ook aan dezelfde cel (cel M1) te koppelen.

4. Pas daarna voorwaardelijke opmaak toe op het gegevensbereik. Selecteer het gegevensbereik en klik op Start > Voorwaardelijke opmaak > Nieuwe regel. Zie de schermafbeelding:

5. In het dialoogvenster Nieuwe opmaakregel selecteert u Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt in het vak Selecteer een regeltype en voert u vervolgens de volgende formule in: =IF($M$1=1,IF(B2>=LARGE($B$2:$I$13,15),TRUE,FALSE)) in het tekstvak Opmaakwaarden waarvoor deze formule WAAR is. Klik daarna op de knop Opmaak om een kleur te kiezen. Zie de schermafbeelding:

6. Klik op de knop OK. Zodra u het eerste keuzerondje selecteert, worden de 15 grootste waarden rood gemarkeerd.

7. Om de kleinste 15 waarden te markeren, houdt u de gegevens geselecteerd, opent u het dialoogvenster Nieuwe opmaakregel en typt u vervolgens deze formule: =IF($M$1=2,IF(B2<=SMALL($B$2:$I$13,15),TRUE,FALSE)) in het tekstvak Opmaakwaarden waarvoor deze formule WAAR is. Klik daarna op de knop Opmaak om een andere gewenste kleur te kiezen. Zie de schermafbeelding:

Opmerking: In de bovenstaande formules is $M$1 de cel die is gekoppeld aan het keuzerondje, $B$2:$I$13 het gegevensbereik waarop u voorwaardelijke opmaak wilt toepassen, B2 de eerste cel van het gegevensbereik en het getal 15 het specifieke aantal dat u wilt markeren.

8. Klik op OK om het dialoogvenster te sluiten. Wanneer u nu het eerste keuzerondje selecteert, worden de 15 grootste waarden gemarkeerd; wanneer u het tweede keuzerondje selecteert, worden de 15 kleinste waarden gemarkeerd, zoals in de onderstaande demo wordt weergegeven:

Voorbeeld 3: Maak een dynamische heatmap met behulp van Selectievakje

In dit gedeelte introduceer ik een dynamische heatmapgrafiek met behulp van een selectievakje, waarmee u de heatmap naar wens kunt tonen of verbergen. Zodra u het selectievakje aanvinkt, wordt de heatmap direct weergegeven; zodra u het uitvinkt, verdwijnt deze onmiddellijk. Zie de onderstaande demo:

1. Converteer eerst uw gegevensbereik naar een tabelindeling, zodat voorwaardelijke opmaak automatisch wordt toegepast wanneer u een nieuwe rij met gegevens invoegt. Selecteer het gegevensbereik en druk vervolgens tegelijkertijd op Ctrl + T om het dialoogvenster Tabel maken te openen. Zie de schermafbeelding:

2. Klik op OK om het dialoogvenster te sluiten en klik vervolgens op Ontwikkelaar > Invoegen > Selectievakje (besturingselement). Sleep daarna met de muis om een selectievakje te tekenen en bewerk de tekst naar wens, zoals in onderstaande schermafbeeldingen wordt weergegeven:

3. Klik met de rechtermuisknop op het selectievakje en kies Besturingselement opmaken. Selecteer in het dialoogvenster Object opmaken, onder het tabblad Besturingselement, een cel die is gekoppeld aan het selectievakje. Zie de schermafbeelding:

4. Klik op OK om het dialoogvenster te sluiten. Selecteer daarna het gegevensbereik waarvoor u een heatmap wilt maken en klik op Start > Voorwaardelijke opmaak gebruiken > Nieuwe regel om naar het dialoogvenster Nieuwe opmaakregel te gaan.

5. Voer in het dialoogvenster Nieuwe opmaakregel de volgende handelingen uit:

• Selecteer Alle cellen opmaken op basis van hun waardenin de lijst Selecteer een regeltype;
• Kies 3-kleurenschaalin de vervolgkeuzelijst Opmaakstijl;
• Selecteer Formulein de vakken Typeonder de vervolgkeuzelijsten Minimum,Middenpunten Maximumafzonderlijk;
• Voer vervolgens de volgende formules in de drie WaardeTekstvak:
• Minimum:=ALS($M$1=WAAR;MIN($B$2:$I$13);ONWAAR)
• Middenpunt:=ALS($M$1=WAAR;GEMIDDELDE($B$2:$I$13);ONWAAR)
• Maximum:=ALS($M$1=WAAR;MAX($B$2:$I$13);ONWAAR)
• Geef vervolgens de markeringskleuren op uit het gedeelte Kleur naar uw wens.

Opmerking: In de bovenstaande formules is $M$1 de cel die is gekoppeld aan het selectievakje, en $B$2:$I$13 het gegevensbereik waarop u voorwaardelijke opmaak wilt toepassen.

6. Nadat u de instellingen hebt voltooid, klikt u op de knop OK om het dialoogvenster te sluiten. Wanneer u nu het selectievakje aanvinkt, wordt de heatmap weergegeven; vinkt u het uit, dan verdwijnt deze. Zie de onderstaande demo:

Download het voorbeeldbestand van de heatmapgrafiek

Video: Heatmapgrafiek maken in Excel