Door voorwaardelijke opmaak toe te voegen in Excel kunt u verschillende opmaakopties toepassen op een cel of celbereik die voldoen aan specifieke voorwaarden die u instelt. Door dergelijke voorwaarden in te stellen, kunt u uw spreadsheet beter organiseren en gemakkelijker scannen. De opmaakopties die u kunt gebruiken, zijn onder meer wijzigingen in lettertype- en achtergrondkleur, letterstijlen, celranden en het toevoegen van getalopmaak aan gegevens.
Excel heeft ingebouwde opties voor veelgebruikte voorwaarden, zoals het vinden van getallen die groter of kleiner zijn dan een bepaalde waarde of het vinden van getallen die boven of onder de gemiddelde waarde liggen. Naast deze vooraf ingestelde opties kunt u ook aangepaste voorwaardelijke opmaakregels maken met behulp van Excel-formules.
Deze instructies zijn van toepassing op Excel 2019, 2016, 2013, 2010 en Excel voor Microsoft 365.
Meerdere voorwaarden toepassen in Excel
U kunt meer dan één regel toepassen op dezelfde gegevens om op verschillende voorwaarden te testen. Voor budgetgegevens kunnen bijvoorbeeld voorwaarden zijn ingesteld die opmaakwijzigingen toepassen wanneer bepaalde uitgavenniveaus zijn bereikt, zoals 50%, 75% en 100% van het totale budget.
In dergelijke omstandigheden bepa alt Excel eerst of de verschillende regels conflicteren, en als dat het geval is, volgt het programma een vaste prioriteitsvolgorde om te bepalen welke voorwaardelijke opmaakregel op de gegevens moet worden toegepast.
Gegevens vinden die meer dan 25% en 50% meer bedragen
In het volgende voorbeeld worden twee aangepaste voorwaardelijke opmaakregels toegepast op het bereik van cellen B2 tot B5.
- De eerste regel controleert of de gegevens in cellen A2:A5 groter zijn dan de corresponderende waarde in B2:B5 door meer dan 25%.
- De tweede regel controleert of dezelfde gegevens in A2:A5 de corresponderende waarde in B2:B5 met meer dan 50%.
Zoals te zien is in de afbeelding hierboven, zal de achtergrondkleur van de cel of cellen in het bereik B1:B4 veranderen als een van de bovenstaande voorwaarden waar is.
- Voor gegevens waarbij het verschil meer dan 25% is, verandert de achtergrondkleur van de cel in groen.
- Als het verschil groter is dan 50%, verandert de achtergrondkleur van de cel in rood.
De regels die worden gebruikt om deze taak uit te voeren, worden ingevoerd met behulp van het Nieuwe opmaakregel dialoogvenster. Begin met het invoeren van de voorbeeldgegevens in cellen A1 tot C5 zoals te zien is in de afbeelding hierboven.
In het laatste deel van de tutorial zullen we formules toevoegen aan cellen C2:C4 die het exacte procentuele verschil tussen de waarden in cellen laten zien A2:A5 en B2:B5; dit stelt ons in staat om de nauwkeurigheid van de voorwaardelijke opmaakregels te controleren.
Voorwaardelijke opmaakregels instellen
Eerst passen we voorwaardelijke opmaak toe om een significante toename van 25 procent of meer te vinden.
De functie ziet er als volgt uit:
=(A2-B2)/A2>25%
- Markeer cellen B2 tot B5 in het werkblad.
- Klik op het Home-tabblad van het lint.
- Klik op het Voorwaardelijke opmaak pictogram in het lint om de vervolgkeuzelijst te openen.
-
Kies Nieuwe regel om het dialoogvenster Nieuwe opmaakregel te openen.
- Klik onder Selecteer een regeltype op de laatste optie: Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt.
- Typ de formule hierboven vermeld in de ruimte eronder Maak waarden op waar deze formule waar is:
- Klik op de Format knop om het dialoogvenster te openen. Klik op de Fill tab en kies een kleur.
- Klik op OK om de dialoogvensters te sluiten en terug te keren naar het werkblad.
- De achtergrondkleur van cellen B3 en B5 moet veranderen in de kleur die je hebt geselecteerd.
Nu passen we voorwaardelijke opmaak toe om een toename van 50 procent of meer te vinden. De formule ziet er als volgt uit:
- Herhaal de eerste vijf stappen hierboven.
-
Typ de formule hierboven in de ruimte eronder Maak waarden op waar deze formule waar is:
- Klik op de Format knop om het dialoogvenster te openen. Klik op de Fill-tab en kies een andere kleur dan in de vorige reeks stappen.
- Klik op OK om de dialoogvensters te sluiten en terug te keren naar het werkblad.
De achtergrondkleur van cel B3 moet hetzelfde blijven, wat aangeeft dat het procentuele verschil tussen de getallen in cellen A3 enB3 is groter dan 25 procent maar kleiner dan of gelijk aan 50 procent. De achtergrondkleur van cel B5 moet veranderen in de nieuwe kleur die u hebt geselecteerd, wat aangeeft dat het procentuele verschil tussen de getallen in cellen A5 en B5 is groter dan 50 procent.
Voorwaardelijke opmaakregels controleren
Om te controleren of de ingevoerde voorwaardelijke opmaakregels correct zijn, kunnen we formules invoeren in cellen C2:C5 die het exacte procentuele verschil berekenen tussen de getallen in de bereikenA2:A5 en B2:B5.
De formule in cel C2 ziet er als volgt uit:
=(A2-B2)/A2
- Klik op cell C2 om het de actieve cel te maken.
- Typ de bovenstaande formule en druk op de Enter toets op het toetsenbord.
- Het antwoord 10% moet verschijnen in cel C2, wat aangeeft dat het getal in cel A2 10% groter is dan het getal in cel B2.
- Het kan nodig zijn om de opmaak op cel C2 te wijzigen om het antwoord als een percentage weer te geven.
- Gebruik de vulgreep om de formule te kopiëren van cell C2 naar cells C3 naar C5.
- De antwoorden voor cellen C3 tot C5 moeten 30%, 25% en 60% zijn.
De antwoorden in deze cellen laten zien dat de voorwaardelijke opmaakregels juist zijn, aangezien het verschil tussen cellen A3 en B3 groter is dan 25 procent, en het verschil tussen cellen A5 en B5 is groter dan 50 procent.
Cel B4 veranderde niet van kleur omdat het verschil tussen cellen A4 en B4 gelijk is aan 25 procent, en onze voorwaardelijke opmaakregel specificeerde dat een percentage van meer dan 25 procent nodig was om de achtergrondkleur te veranderen.
Voorrangsvolgorde voor voorwaardelijke opmaak
Wanneer u meerdere regels toepast op hetzelfde gegevensbereik, bepa alt Excel eerst of de regels conflicteren. Tegenstrijdige regels zijn regels waarbij de opmaakopties niet beide kunnen worden toegepast op dezelfde gegevens.
In ons voorbeeld zijn de regels conflicterend omdat beide dezelfde opmaakoptie gebruiken - de celkleur op de achtergrond wijzigen.
In de situatie waarin de tweede regel waar is (het verschil in waarde is meer dan 50 procent tussen twee cellen), dan is de eerste regel (het verschil in waarde is groter dan 25 procent) ook waar.
Aangezien een cel niet tegelijkertijd twee verschillende kleurenachtergronden kan hebben, moet Excel weten welke voorwaardelijke opmaakregel het moet toepassen.
De prioriteitsvolgorde van Excel stelt dat de regel die hoger is in de lijst in het dialoogvenster Beheer van conditionele opmaakregels als eerste wordt toegepast.
Zoals in de afbeelding hierboven te zien is, staat de tweede regel die in deze zelfstudie wordt gebruikt hoger in de lijst en heeft daarom voorrang op de eerste regel. Als gevolg hiervan is de achtergrondkleur van cel B5 groen.
Standaard staan nieuwe regels bovenaan de lijst; om de volgorde te wijzigen, gebruikt u de Omhoog en Omlaag pijlknoppen in het dialoogvenster.
Niet-conflicterende regels toepassen
Als twee of meer voorwaardelijke opmaakregels niet conflicteren, worden beide toegepast wanneer de voorwaarde die elke regel test waar wordt.
Als de eerste voorwaardelijke opmaakregel in ons voorbeeld het celbereik B2:B5 opmaakte met een oranje rand in plaats van een oranje achtergrondkleur, zouden de twee voorwaardelijke opmaakregels niet conflict omdat beide formaten kunnen worden toegepast zonder de andere te verstoren.
Voorwaardelijke opmaak versus reguliere opmaak
In het geval van conflicten tussen voorwaardelijke opmaakregels en handmatig toegepaste opmaakopties, heeft de voorwaardelijke opmaakregel altijd voorrang en wordt deze toegepast in plaats van handmatig toegevoegde opmaakopties.
