---

---

Misschien is dat ook de reden waarom je heatmaps in de praktijk niet zo veel tegenkomt: ze roepen soms meer vragen op dan ze antwoorden geven.

Maar bij mij kwam wel direct de vraag op: hoe zou je een heatmap in Excel maken? Och ja, natuurlijk: met voorwaardelijke opmaak. Daarom in dit artikel maar eens de schijnwerper op diverse mogelijkheden van de, in Excel ingebouwde, opties.

Voorwaardelijke opmaak

Markeringsregels

Kiest u voor Markeringsregels dan ziet u diverse opties: de opmaak regelen van cellen die een waarde bevatten groter dan een bepaalde target, of juist kleiner of die tussen 2 targets in liggen etc.

Om alle cellen, die een waarde groter dan 7 bevatten, te accentueren doorlopen we de volgende stappen:

1. selecteer alle cellen, die een opmaak moeten krijgen; in het voorbeeld de cellen C3:C33
2. kies Voorwaardelijke opmaak/Markeringsregels/Groter dan
3. vul in het vervolgscherm de targetwaarde in (hier 7) en kies als opmaak Groene opvulling met donkergroene tekst
4. klik dan op OK

Om de cellen, waarvan de waarde kleiner is dan 3, te benadrukken doen we iets vergelijkbaars, alleen kiezen we in stap 2 de optie Kleiner dan.

NB door op F9 te drukken, wordt de Excel-map opnieuw berekend. Alle waarden in kolom C worden dan ververst en de opmaak zal zich daaraan aanpassen.

Voorwaardelijke opmaak aanpassen

Soms voldoet de op deze manier standaard ingestelde opmaak niet (helemaal). Volg dan de volgende stappen:

1. selecteer één van de cellen, waarvan de opmaak gewijzigd moet worden, in dit geval bijvoorbeeld C3
2. kies Voorwaardelijke opmaak en dan de optie Regels beheren…
3. bovenstaand scherm komt dan tevoorschijn. Standaard worden alleen de regels van de huidige selectie getoond. Dit kunt u makkelijk wijzigen in het eerste keuzevak.
4. klik op de regel die u wilt wijzigen (of bekijken) en kies Regel bewerken
5. afhankelijk van het soort regel kunnen nu diverse parameters ingesteld worden, inclusief de gewenste opmaak

Bij iedere regel is ook te zien voor welke cellen de regel geldt; in dit geval allebei de keren voor C3:C33.

NB Dit bereik kan hier aangepast worden. Uitermate handig als u in de eerste stap bij het instellen van opmaak vergeten bent een reeks cellen te selecteren.

Ook als er nieuwe waarden aan kolom B en C worden toegevoegd zal het bereik voor de opmaak-regels moeten worden aangepast.

Opmaak in een Excel-tabel

1. selecteert u één van de cellen van de tabel en u kiest dan Regels beheren, dan zal Excel direct alle opmaakregels van die tabel tonen
2. voegt u nieuwe gegevens onder aan de tabel toe, dan zal Excel automatisch ook de voorwaardelijke opmaak aan die cellen toevoegen

In kolom E wordt via Voorwaardelijke opmaak/Markering/Datum de datum van vandaag met een gele achtergrondkleur weergegeven.

Kolom F kent dezelfde opmaak als in het vorige overzicht.

Opmaak ten opzichte van het gemiddelde

Kolom I heeft op de volgende manier een voorwaardelijke opmaak gekregen:

1. selecteer de cellen I3:I33
2. kies Voorwaardelijke opmaak/Markering/Groter dan
3. als target geven we nu niet een harde grens op, maar selecteren met de muis cel K3 of drukken op de functietoets F3 en kiezen dan de naam GemOpbr (de laatste optie is het mooiste, omdat dan duidelijker te zien is wat deze opmaakregel doet)
4. kies de gewenste opmaak
5. klik OK
6. herhaal het vorige maar nu met de optie Kleiner dan

Opmaak voor unieke of dubbele waarden

1. selecteer de cellen, die een opmaak moeten krijgen (in dit geval B3:B9)
2. kies Voorwaardelijke opmaak/Markering/Dubbele waarden
3. kies de optie Dubbele of Unieke
4. geef de gewenste opmaak op en klik op OK

Opmaak voor bovenste/onderste

Soms wilt u zien welke cellen de grootste of kleinste waarden bevatten. Dat gaat als volgt (zie het tabblad BovenOnder in het Voorbeeldbestand):

1. selecteer de cellen, die een opmaak moeten krijgen
2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Bovenste 10 items
3. dan geeft u aan of u echt 10 items wilt opmaken; u kunt bijvoorbeeld ook voor de top-3 kiezen
4. kies de gewenste opmaak en klik op OK

NB1 u ziet ook de optie Bovenste 10%; de 10 is weer apart in te stellen. Excel zal dan zelf, aan de hand van het geselecteerde bereik, bepalen hoeveel cellen er ‘gekleurd’ moeten worden.

NB2 uiteraard kunt u ook de cellen met de laagste waarden accentueren (Onderste in plaats van Bovenste).

Opmaak ten opzichte van het gemiddelde 2

Hiervoor hebben we via Markering een methode laten zien om cellen boven of onder het gemiddelde op te maken. Excel kent standaard een alternatief (zie tabblad BovenOnder van het Voorbeeldbestand, kolommen E en F):

1. selecteer de cellen, die een opmaak moeten krijgen
2. kies Voorwaardelijke opmaak, dan de optie Bovenste/Onderste en vervolgens de optie Boven gemiddelde
3. geef een opmaak op en klik op OK

NB nadeel van deze methode is, dat u nergens zelf het gemiddelde ziet.

Bovenste/onderste interactief

1. leg ergens vast hoeveel cellen er ‘gekleurd’ moeten worden; in het voorbeeld gebruiken we cel L2 daarvoor met de naam BovOnderAantal
2. plaats in een cel de tekst Boven of Onder; in het voorbeeld cel L3 met de naam BovOnder. Via gegevensvalidatie kan uit die 2 woorden worden gekozen en is de invoer beperkt. 
3. selecteer de cellen, die een opmaak moeten krijgen (beginnend bij I3)
4. kies Voorwaardelijke opmaak, dan de optie Nieuwe regel en daarna de optie Een formule gebruiken
5. plaats dan de volgende formule =EN(BovOnder=”Boven”; I3>=GROOTSTE($I$3:$I$33;BovOnderAantal))
6. geef een gewenste opmaak en klik OK

De formule levert alleen als waarde WAAR (en dan zal de opmaak worden doorgevoerd) als aan allebei de voorwaarden wordt voldaan: cel L2 moet de waarde Boven bevatten EN de waarde in cel I3 moet groter of gelijk zijn aan de grootste waarde in het bereik van geselecteerde cellen (als BovOnderAantal gelijk is aan 1, aan de één na grootste als BovOnderAantal gelijk is aan 2 etcetera).

LET OP bij het maken van een formule voor voorwaardelijke opmaak met relatieve verwijzingen.
In dit geval bevat de formule I3, omdat dat het startpunt van onze selectie was.
Selecteer cel I4 en bekijk nu de regel die bij de voorwaardelijke opmaak hoort; Excel heeft de relatieve verwijzing in de formule automatisch aangepast.

NB de andere gebruikte formule (in het geval dat we de laagste waarden zoeken):
=EN(BovOnder=”Onder”;I3<=KLEINSTE($I$3:$I$33;BovOnderAantal))

Gegevensbalken

Sommige mensen willen getallen zien in overzichten, anderen zijn meer grafisch ingesteld.

Gegevensbalken als voorwaardelijke opmaak zijn in dit geval een uitkomst. Hierboven (zie het tabblad GegBalk in het Voorbeeldbestand) zijn per kolom de waarden ‘vertaald’ naar kleine grafiekjes:

1. selecteer de cellen van de Uit-kolom. Wijs daartoe met de muis de bovenrand van de Uit-cel aan; de cursor wordt dan een pijl-naar-beneden. Klik dan met de muis.
2. kies Voorwaardelijke opmaak en dan de optie Gegevensbalken. In dit geval kiezen we de derde kleurovergang (Rood)
3. om de kleuren-balk en de getallen te scheiden: kies opnieuw Voorwaardelijke opmaak, maar dan de optie Regels beheren en Regel bewerken. Klik op de knop Negatieve waarden en as en zorg dat bij Asinstellingen de optie Middelpunt is geselecteerd.
4. voor de In-kolom geldt een vergelijkbare procedure
5. de Result– en ResCum-kolom zijn iets ingewikkelder: in eerste instantie kiezen we voor een Groene gegevensbalk. Daarna moet er nog een aanpassing doorgevoerd worden: kies bij Regel bewerken/Negatieve waarden en as een Rode opvulkleur (ook voor de rand van de balk). Zet meteen ook Middelpunt aan.

Gegevensbalken 2

Bijna hetzelfde overzicht als hiervoor (wel heb ik de kolom met cumulatieve resultaten weggelaten; zie het tabblad GegBalk in het Voorbeeldbestand, bereik B17:E29).
Het verschil zit hem er in dat de grootte van de gegevensbalken nu relatief ten opzichte van de totale tabel bepaald zijn en niet per kolom.

LET OP zoals uit de diverse voorbeelden mag blijken, moeten de overzichten wel altijd van een goede legenda worden voorzien anders kan de lezer snel de verkeerde conclusies trekken.

Gegevensbalken 3

Wanneer de exacte details van de in- en uitgaande stromen niet van belang zijn kunt u er ook voor kiezen om alleen de gegevensbalken te laten zien (zie het tabblad GegBalk in het Voorbeeldbestand, bereik H2:K14):

1. selecteer één van de cellen waarvan de opmaak moet worden gewijzigd
2. kies Voorwaardelijke opmaak/Regels beheren
3. selecteer de betreffende regel en klik op Regel bewerken
4. plaats een vinkje voor de optie Alleen balk weergeven

Gegevensbalken 4

LET OP wanneer er aan de brongegevens regels worden toegevoegd dan dient de draaitabel te worden vernieuwd. Komt er in de draaitabel dan ook een nieuwe regel bij, dan zal de opmaak niet automatisch worden doorgevoerd. Dit kunt u als volgt oplossen:

Heatmap

We gaan als volgt te werk:

1. selecteer alle cellen, die bij de heatmap betrokken moeten worden
2. kies Voorwaardelijke opmaak en dan de optie Kleurenschalen. In dit geval kiezen we de eerste kleurovergang (groen-geel-rood)

Nog meer opmaak

Voor diegene die de smaak te pakken hebben gekregen: het Voorbeeldbestand bevat nog diverse andere voorbeelden van opmaak. Met het bovenstaande in het achterhoofd moeten de daarbij gemaakte keuzes duidelijk zijn.

---