Tips voor voorwaardelijke opmaak in Excel
Tips voor een voorwaardelijke opmaak in Excel 2007 en nieuwer
Het idee achter voorwaardelijke opmaak is dat je met kleuren en grafieken de trends op werkbladen kunt visualiseren. In combinatie met formules kan er nog veel meer mee. Zo kun je bijvoorbeeld duplicaten ontdekken of gewaarschuwd worden als de magazijnvoorraad opraakt en er nabestellingen nodig zijn.
De voorwaardelijke opmaak van Excel is onder meer handig voor controllers om trends in bedrijfs resultaten 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 'Voorwaarde lijke opmaak'. In het submenu 'Gegevensbalken' kies je bijvoorbeeld de opmaak 'Groene gegevensbalk'. Die toont positieve 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.
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.
Kleur bekennen
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 op A 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 '3kleurenschaal'. 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.
Zeg het met tekst
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 aanhalingste kens. 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'.
Alles geregeld
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 eennagrootste 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.
Regels voor datums
Om alleen bepaalde datums op te maken, voeg je een nieuwe regel toe van het type 'Alleen cellen opmaken met' en kies je in het dialoogvenster 'Datums op of in'. Je kunt dan kiezen voor bepaalde dagen, weken of maanden. Het is ook mogelijk een nieuwe regel met een formule te definiëren, zoals =B2=VANDAAG(). Op dezelfde manier kun je datums die in de toekomst liggen met de formule =B2>VANDAAG() selecteren en groen markeren.
Achterstallige facturen kun je op een vergelijkbare manier makkelijk markeren met voorwaardelijke opmaak. De formule =(VANDAAG()-C1>30) vergelijkt de datum in kolom C met de huidige datum en past bijvoorbeeld een rode opmaak toe als die datum meer dan 30 dagen geleden is. Wil je in een agenda juist de zaterdagen en zondagen markeren, dan gebruik je
een formule als =WEEKDAG($A2:$A26;2)>5. Om alleen zaterdagen te selecteren typ je =WEEKDAG($A2:$A26;2)=6 en voor zondagen wijzig je het getal 6 in een 7.
Zoeken en vinden
Om een uitgebreid werkblad te doorzoeken naar een bepaalde waarde, gebruik je de functie =VIND.ALLES(). Selecteer het gewenste celbereik, start de voorwaardelijke opmaak en typ bijvoorbeeld de formule =VIND.ALLES($E$1;$B1). Kies vervolgens de gewenste opmaak. Alle cellen die het zoekbegrip bevatten worden dan gemarkeerd.
Duplicaten
Als je dubbele waarden meteen wilt zien, kun je op een leeg werkblad voorwaardelijke opmaak toepassen om gebruikers bij de invoer al te waarschuwen voor duplicaten. Dubbele waarden in een kolom markeer je via 'Voorwaardelijke opmaak / Markeringsregels voor cellen / Dubbele waarden'.
Meer mogelijkheden heb je met een regel waarbij je een formule gebruikt, zoals =AANTAL. ALS ($A$1:$C $10; A 1)>1. De formule =SOM PRODUCT (($E$1:$E1=$ E 1)*($ F$1:$ F 1= $F1))>1 selecteert alle duplicaten in de ko lommen E en F. Je kunt bij de vergelijking >1 aanpassen en bijvoorbeeld >2 gebruiken om duplicaten pas te markeren vanaf drie identieke waarden.
In plaats van de duplicaten aan te geven met een opvulkleur, kun je ze bijvoorbeeld ook doorhalen. Klik bij maken van de regel op de knop Opmaak en ga je naar het tabblad Lettertype. Zet daar een vinkje in het selectievakje Doorhalen.
Tussen werkbladen
Het is ook mogelijk om te zoeken naar relaties tussen data op verschillende werkbladen. Vanaf Excel versie 2010 vergelijkt de voorwaardelijke opmaak cellen ook als die zich bevinden op twee verschillende werkbladen. Daarvoor selecteer je eerst de tabel waarop je de voorwaardelijke opmaak wilt toepassen. Start via 'Voorwaardelijke opmaak' een 'Nieuwe regel' en kies 'Een formule gebruiken om te bepalen welke cellen worden opgemaakt'. Typ daarna bijvoorbeeld de formule =A1<>Blad2!A1 en kies de gewenste opmaak. Hiermee krijgt elke niet identieke cel op het eerste werkblad de voorwaardelijke opmaak.
Snel aanpassen
Een voorwaardelijke opmaak blijft bestaan totdat de betreffende regels worden gewist. Dat kan bijvoorbeeld door de opmaak van een cel zonder voorwaardelijke opmaak met de kwast te kopiëren en plakken. Je kunt ook via 'Voorwaardelijke opmaak / Regels wissen' de opmaak verwijderen uit de geselecteerde cellen of het hele werkblad.
Wil je de opmaak niet wissen maar aanpassen, dan selecteer je de betreffende cellen en wijzig je de opmaak via de menuopdracht 'Regels beheren'. Om cellen met een conditionele opmaak snel te kunnen vinden, druk je op de toets F5 om het dialoogvenster 'Ga naar' te openen. Klik daarin op 'Speciaal' en kies 'Voorwaardelijke opmaak'.
Selecteer het keuzerondje 'Alles' voor alle cellen met voorwaardelijke opmaak. Selecteer je 'Zelfde', dan worden alleen de cellen geselecteerd met dezelfde voorwaardelijke opmaak als de huidige cel. Je kunt vervolgens voor alle geselecteerde cellen de voorwaardelijke opmaak wissen of indien van toepassing naar wens een nieuwe regel maken.