Voorwaardelijke opmaak in Excel combineren met formules

Het idee achter voorwaardelijke opmaak in Excel is dat je met kleuren en grafieken de trends op werkbladen kunt visualiseren. In combinatie met formules kan er nog veel meer met voorwaardelijke opmaak in Excel. Zo kun je bijvoorbeeld duplicaten ontdekken of gewaarschuwd worden als de magazijnvoorraad opraakt en er nabestellingen nodig zijn.

De voorwaardelijke opmaak in Excel is onder meer handig voor controllers om trends in bedrijfsresultaten op te laten vallen. Met een extra kolom kunnen data worden gevisualiseerd. Als een celwaarde verandert, past Excel de opmaak van de bijbehorende cellen meteen aan, zodat de opvulkleur, databalken en pictogrammen dat meteen weergeven.

Om getallen in een kolom ernaast als gegevensbalk weer te geven, voeg je eerst een nieuwe kolom in. Typ in de bovenste cel een verwijzing naar de kolom ernaast, bijvoorbeeld “=A1”. Selecteer deze cel en dubbelklik op de vulgreep die rechtsonder verschijnt om dit door te voeren in de hele kolom. De kolom is nu dubbel aanwezig. Selecteer de cellen van de nieuwe kolom en klik in het lint op de knop ‘Voorwaardelijke opmaak’. In het submenu ‘Gegevensbalken’ kies je bijvoorbeeld de opmaak ‘Groene gegevensbalk’. Die toont posi­tieve waarden in het groen, maar kleurt de balk rood bij negatieve waarden. Open daarna opnieuw het submenu ‘Gegevensbalken’ en kies ‘Meer regels’. Activeer in het dialoogvenster het selectievakje ‘Alleen balk weergeven’. De waarden in de cellen worden dan verborgen, zodat je alleen nog maar de gegevensbalken ziet. De waarden zelf zijn nog in de oorspronkelijke kolom ernaast zichtbaar.

Gegevensbalken maken de omzetstijgingen of -dalingen overzichtelijker dan met alleen getallen, zodat je bijvoorbeeld de resultaten van afzonderlijke filialen in één oogopslag kunt inschatten.

Trends kun je ook goed aangeven met pictogrammen. Kies via ‘Voorwaardelijke opmaak’ in het submenu ‘Pictogramseries’ de optie ‘Meer regels’. Stel voor elk pictogram de bijbehorende waarden in. Let erop dat je bij ‘Type’ de waarden in procenten of als absolute getallen definieert.

Kleurenschalen geven via een kleurverloop aan hoe dicht de waarden bij vooraf gedefinieerde grenzen liggen. De voorwaardelijke opmaak zorgt ervoor dat de achtergrond donkerder wordt naarmate die grens dichterbij komt. Selecteer het celbereik dat je wilt opmaken. Als je binnen een gegevensbereik op Ctrl+A drukt, wordt het hele bereik geselecteerd. Door nogmaals op A te drukken selecteer je het hele werkblad.

Open via het lint het menu voor de voorwaardelijke opmaak en kies ‘Nieuwe regel’. Selecteer in het dialoogvenster bij ‘Opmaakstijl’ de optie ‘3-kleurenschaal’. Je kunt daarna het minimum (‘Laagste waarde’) en maximum (‘Hoogste waarde’) aanpassen. Bij het middelpunt laat je Percentiel op de waarde 50 staan en vervolgens wijs je aan elk bereik een kleur toe. Kies bijvoorbeeld rood, geel en groen voor het minimum, middelpunt en maximum. Als je het dialoogvenster sluit, geeft Excel de geselecteerde cellen een opvulkleur volgens de gekozen instellingen.

Je kunt cellen ook zo opmaken dat Excel bij het overschrijden van een (onder)grens een waarschuwing toont in de naastgelegen cel. Zo kun je bijvoorbeeld in een werkblad met voorraadgegevens laten waarschuwen voor tekorten met de tekst ‘LET OP: nabestellen!’. Selecteer het bijbehorende celbereik en maak een nieuwe regel, selecteer in het dialoogvenster het type ‘Alleen cellen opmaken met’. Stel daaronder in ‘Celwaarde’ en ‘kleiner dan of gelijk aan’ een geef op bij welke waarde de waarschuwing moet verschijnen. Klik op de knop ‘Opmaak’ en ga naar het tabblad ‘Getal’. Kies hier ‘Aangepast’ en typ in het invoervak een tekst zoals “LET OP: nabestellen!”, inclusief de dubbele aanhalingstekens. Eventueel moet je de kolombreedte nog afstemmen op de tekstlengte. Dat kan automatisch met een dubbelklik op de scheidingslijn naast de kolomkop.

Het minimum en maximum van een reeks getallen kun je ook eenvoudig laten opvallen via voorwaardelijke opmaak. Kies hiervoor in het menu voor voorwaardelijke opmaak ‘Regels voor bovenste/onderste’ en daarna ‘Bovenste 10 items’. Stel vervolgens de waarde op 1. Als je dat wilt kun je bijvoorbeeld ook de bovenste drie waarden opmaken door de waarde op 3 te stellen. Op dezelfde manier maak je het ­minimum op via ‘Onderste 10 items’.

Bij de tot nu toe genoemde voorbeelden wordt de voorwaardelijke opmaak telkens alleen toegepast op de betreffende cel zelf. Wil je in plaats daarvan een hele rij laten opvallen, dan moet je het hele gegevensbereik selecteren. Kies bij de voorwaardelijke opmaak via ‘Nieuwe regel’ voor het type ‘Een formule gebruiken om te bepalen welke cellen worden opgemaakt’. Typ je voor een rij of kolom in een celverwijzing het dollarteken, dan bekijkt Excel de hele rij of kolom. Bij een tabel waarin de omzetcijfers in kolom B vanaf rij 1 staan, kun je in het invoervak als formule =MAX($B:$B)=$B1 invoeren en de opmaak bepalen.

Wil je andere regels opstellen, dan kun je alle mogelijkheden van formules gebruiken. Zo bepaal je met =GROOTSTE($B:$B;1) of =KLEINSTE($B:$B;1) niet alleen de grootste of kleinste waarde, maar ook opvolgende waarden. Voor de een-na-grootste waarde geef je als tweede argument het getal 2 op, enzovoort.

De functie =REST(getal;deler) geeft het restgetal bij het delen van een getal door een deler. Je kunt dus bijvoorbeeld elke tweede rij inkleuren met behulp van de volgende formule =REST(RIJ(A1);2)=0.

Met behulp van de functie =ISFOUT(B1) kun je foutwaarden eenvoudig verbergen. Combineer je dit met een voorwaardelijke opmaak met een witte tekstkleur (wit op wit), dan is de celinhoud alleen nog zichtbaar in de formulebalk.

Er is nog veel meer mogelijk met voorwaardelijke opmaak in Excel. Zo kun je bijvoorbeeld datums opmaken om achterstallige facturen te markeren, duplicaten laten opvallen en relaties zoeken tussen data op verschillende werkbladen.

(Rainer Schwabe, c’t magazine)