Heading

Voorwaardelijke opmaak is een fantastische manier om snel gegevens in een spreadsheet te visualiseren. Met voorwaardelijke opmaak kun je bijvoorbeeld data in de komende 30 dagen markeren, problemen met gegevensinvoer markeren, rijen met topklanten markeren, duplicaten weergeven, en nog veel meer.

Excel wordt geleverd met een groot aantal “voorinstellingen” waarmee je eenvoudig nieuwe regels kunt maken zonder formules. U kunt echter ook regels maken met uw eigen aangepaste formules. Door uw eigen formule te gebruiken, neemt u de voorwaarde over die een regel triggert, en kunt u precies de logica toepassen die u nodig hebt. Formules geven u maximale macht en flexibiliteit.

Met de voorinstelling “Gelijk aan” is het bijvoorbeeld heel eenvoudig om cellen te markeren die gelijk zijn aan “appel”.

Maar wat als u cellen wilt markeren die gelijk zijn aan “appel” of “kiwi” of “limoen”? Natuurlijk, je kunt voor elke waarde een regel maken, maar dat is een hoop moeite. In plaats daarvan kunt u gewoon één regel gebruiken die is gebaseerd op een formule met de OR-functie:

Hier ziet u het resultaat van de regel die is toegepast op het bereik B4:F8 in dit spreadsheet:

Hier ziet u de exacte formule die is gebruikt:

=OR(B4="apple",B4="kiwi",B4="lime")

Snel aan de slag
U kunt in vier eenvoudige stappen een voorwaardelijke opmaakregel op basis van een formule maken:
1. Selecteer de cellen die u wilt opmaken.

2. Maak een voorwaardelijke opmaakregel en selecteer de optie Formule

3. Voer een formule in die WAAR of ONWAAR retourneert.

4. Stel opmaakopties in en sla de regel op.

De ISODD-functie retourneert alleen WAAR voor oneven getallen, waardoor de regel in werking treedt:

Video: Voorwaardelijke opmaak toepassen met een formule
Wij bieden ook videotraining over dit onderwerp.
Formulalogica
Formules die voorwaardelijke opmaak toepassen, moeten WAAR of ONWAAR retourneren, of numerieke equivalenten. Hier volgen enkele voorbeelden:
=ISODD(A1)=ISNUMBER(A1)=A1>100=AND(A1>100,B1<50)=OR(F1="MN",F1="WI")

De bovenstaande formules geven allemaal WAAR of ONWAAR terug, dus ze werken perfect als trigger voor voorwaardelijke opmaak.
Wanneer voorwaardelijke opmaak wordt toegepast op een bereik van cellen, voer dan celverwijzingen in met betrekking tot de eerste rij en kolom in de selectie (d.w.z. de cel linksboven). De truc om te begrijpen hoe voorwaardelijke opmaak formules werken is om dezelfde formule te visualiseren die wordt toegepast op elke cel in de selectie, met celverwijzingen bijgewerkt zoals gebruikelijk. Stel u voor dat u de formule in de cel linksboven van de selectie invoert, en vervolgens de formule over de gehele selectie kopieert. Als u hier moeite mee heeft, zie dan het gedeelte over Dummy Formules hieronder.

Voorbeeld formules
Hieronder staan voorbeelden van aangepaste formules die u kunt gebruiken om voorwaardelijke opmaak toe te passen. Sommige van deze voorbeelden kunnen worden gemaakt met behulp van Excel’s ingebouwde voorinstellingen voor het markeren van cellen, maar aangepaste formules kunnen veel verder gaan dan voorinstellingen, zoals u hieronder kunt zien.
Zie ook: Meer dan 30 formules voor voorwaardelijke opmaak 
Opvallende orders uit Texas
Om rijen te markeren die orders uit Texas (afgekort TX) vertegenwoordigen, gebruikt u een formule die de verwijzing naar kolom F vergrendelt:
=$F5="TX"


Voor meer details, zie dit artikel: Rijen markeren met voorwaardelijke opmaak.
Video: Hoe rijen markeren met voorwaardelijke opmaak
Data in de komende 30 dagen markeren 
Om data te markeren die in de komende 30 dagen voorkomen, hebben we een formule nodig die (1) ervoor zorgt dat data in de toekomst liggen en (2) ervoor zorgt dat data 30 dagen of minder vanaf vandaag liggen. Een manier om dit te doen is door de functie AND samen met de functie NOW als volgt te gebruiken:
=AND(B4>NOW(),B4<=(NOW()+30))

Met een huidige datum van 18 augustus 2016, markeert de voorwaardelijke opmaak datums als volgt:

De functie NOW retourneert de huidige datum en tijd. Voor details over hoe deze formule werkt, zie dit artikel: Markeer datums in de komende N dagen.
Help kolomverschillen 
Gegeven twee kolommen die vergelijkbare informatie bevatten, kunt u voorwaardelijke opmaak gebruiken om subtiele verschillen op te merken. De formule die wordt gebruikt om de onderstaande opmaak te activeren is:
=$B4<>$C4


Zie ook: een versie van deze formule die de EXACT-functie gebruikt om een hoofdlettergevoelige vergelijking uit te voeren.
Belicht ontbrekende waarden
Om waarden in de ene lijst te belichten die in een andere ontbreken, kunt u een formule gebruiken die is gebaseerd op de COUNTIF-functie:
=COUNTIF(list,B5)=0


Deze formule controleert eenvoudig elke waarde in lijst A met waarden in het genoemde bereik “lijst” (D5:D10). Als de telling nul is, geeft de formule WAAR terug en wordt de regel geactiveerd, waardoor waarden in lijst A worden gemarkeerd die ontbreken in lijst B.
Video: Hoe ontbrekende waarden te vinden met COUNTIF
Highlight eigenschappen met 3+ slaapkamers onder $350k
Om eigenschappen in deze lijst te vinden die ten minste 3 slaapkamers hebben maar minder dan $300.000 kosten, kunt u een formule gebruiken die is gebaseerd op de AND-functie:
=AND($C5<350000,$D5>=3)

De dollartekens ($) vergrendelen de verwijzing naar de kolommen C en D, en de AND-functie wordt gebruikt om ervoor te zorgen dat beide voorwaarden TRUE zijn. In rijen waar de AND-functie WAAR retourneert, wordt de voorwaardelijke opmaak toegepast:


Hoogste waarden markeren (dynamisch voorbeeld)
Hoewel Excel voorinstellingen heeft voor “hoogste waarden”, laat dit voorbeeld zien hoe hetzelfde kan worden gedaan met een formule, en hoe formules flexibeler kunnen zijn. Door een formule te gebruiken, kunnen we het werkblad interactief maken – wanneer de waarde in F2 wordt bijgewerkt, reageert de regel onmiddellijk en worden nieuwe waarden gemarkeerd.

De formule die voor deze regel wordt gebruikt, is:
=B4>=LARGE(data,input)

Waarbij “gegevens” het genoemde bereik B4:G11 is, en “invoer” het genoemde bereik F2 is. Deze pagina bevat details en een volledige uitleg.
Gantt-diagrammen
Geloof het of niet, u kunt zelfs formules gebruiken om eenvoudige Gantt-diagrammen te maken met voorwaardelijke opmaak zoals deze:

Dit werkblad gebruikt twee regels, een voor de balken, en een voor de arcering van het weekend:
=AND(D$4>=$B5,D$4<=$C5) // bars=WEEKDAY(D$4,2)>5 // weekends

In dit artikel wordt de formule voor de balken uitgelegd, en in dit artikel wordt de formule voor de weekend arcering uitgelegd.
Een eenvoudig zoekvak
Een leuke truc die je met voorwaardelijke opmaak kunt toepassen, is het bouwen van een eenvoudig zoekvak. In dit voorbeeld markeert een regel cellen in kolom B die tekst bevatten die in cel F2 is getypt:

De gebruikte formule is:
=ISNUMBER(SEARCH($F$2,B2))

Voor meer details en een volledige uitleg, zie:

Artikel: Hoe cellen te markeren die specifieke tekst bevatten 
Artikel: Hoe rijen te markeren die specifieke tekst bevatten 
Video: Hoe bouw je een zoekvak om gegevens te markeren 

Troubleshooting
Als je je voorwaardelijke opmaakregels niet correct kunt laten afgaan, is er hoogstwaarschijnlijk een probleem met je formule. Controleer eerst of u de formule bent begonnen met een gelijkteken (=). Als je deze stap vergeet, zal Excel de hele formule stilzwijgend omzetten in tekst, waardoor deze onbruikbaar wordt. Om dit te verhelpen, verwijdert u de dubbele aanhalingstekens die Excel aan beide zijden heeft toegevoegd en zorgt u ervoor dat de formule begint met een gelijkheidsteken (=).
Als uw formule correct is ingevoerd, maar de regel niet activeert, moet u wellicht iets dieper graven. Normaal gesproken kunt u de F9-toets gebruiken om de resultaten in een formule te controleren, of de functie Evalueren gebruiken om een formule te doorlopen. Helaas kun je deze hulpmiddelen niet gebruiken bij voorwaardelijke opmaak formules, maar je kunt wel een techniek gebruiken die “dummy formules” heet.
Dummy Formules
Dummy formules zijn een manier om je voorwaardelijke opmaak formules direct op het werkblad te testen, zodat je kunt zien wat ze eigenlijk doen. Dit kan je veel tijd besparen als je moeite hebt om celverwijzingen goed te laten werken.
In een notendop, je voert dezelfde formule in over een reeks cellen die overeenkomt met de vorm van je gegevens. Dit laat je de waarden zien die door elke formule worden geretourneerd, en het is een geweldige manier om te visualiseren en te begrijpen hoe formule-gebaseerde voorwaardelijke opmaak werkt. Zie dit artikel voor een gedetailleerde uitleg.

Video: Voorwaardelijke opmaak testen met dummy-formules
Beperkingen
Er zijn enkele beperkingen bij voorwaardelijke opmaak op basis van formules:

U kunt geen pictogrammen, kleurenschalen of gegevensbalken toepassen met een aangepaste formule. U bent beperkt tot standaard celopmaak, inclusief getalnotaties, lettertype, vulkleur en randopties.
U kunt bepaalde formuleconstructies zoals unies, kruisingen of matrixconstanten niet gebruiken voor criteria voor voorwaardelijke opmaak.
U kunt niet verwijzen naar andere werkmappen in een formule voor voorwaardelijke opmaak.

U kunt soms om # 2 en # 3 heen werken. Je kunt misschien de logica van de formule verplaatsen naar een cel in het werkblad, en dan in plaats daarvan naar die cel verwijzen in de formule. Als u probeert om een matrixconstante te gebruiken, probeer dan een benoemd bereik te maken.