Bekijk video - Markeer rijen op basis van celwaarden in Excel
Als u liever schriftelijke instructies leest, vindt u hieronder de tutorial.
Met voorwaardelijke opmaak kunt u een cel (of een celbereik) opmaken op basis van de waarde erin.
Maar soms wilt u, in plaats van alleen de cel te markeren, de hele rij (of kolom) markeren op basis van de waarde in één cel.
Om u een voorbeeld te geven, hieronder heb ik een dataset waarin ik alle rijen heb gemarkeerd waar de naam van de verkoper Bob is.
In deze zelfstudie laat ik u zien hoe u rijen markeert op basis van een celwaarde met behulp van voorwaardelijke opmaak met behulp van verschillende criteria.
Klik hier om het voorbeeldbestand te downloaden en te volgen.
Markeer rijen op basis van tekstcriteria
Stel dat u een gegevensset hebt zoals hieronder weergegeven en dat u alle records wilt markeren waarin de naam van de verkoper Bob is.
Hier zijn de stappen om dit te doen:
- Selecteer de volledige dataset (A2:F17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =$C2="Bob"
- Klik op de knop 'Formaat'.
- Stel in het dialoogvenster dat wordt geopend de kleur in waarin u wilt dat de rij wordt gemarkeerd.
- Klik OK.
Hiermee worden alle rijen gemarkeerd waar de naam van de verkoper 'Bob' is.
Klik hier om het voorbeeldbestand te downloaden en te volgen.
Hoe werkt het?
Voorwaardelijke opmaak controleert elke cel op de voorwaarde die we hebben opgegeven, namelijk: =$C2="Bob"
Dus wanneer het elke cel in rij A2 analyseert, zal het controleren of cel C2 de naam Bob heeft of niet. Als dit het geval is, wordt die cel gemarkeerd, anders niet.
Merk op dat de truc hier is om een dollarteken ($) te gebruiken voor het kolomalfabet ($C1). Door dit te doen, hebben we de kolom vergrendeld zodat deze altijd C is. Dus zelfs wanneer cel A2 wordt gecontroleerd op de formule, wordt C2 gecontroleerd en wanneer A3 wordt gecontroleerd op de voorwaarde, wordt C3 gecontroleerd.
Hierdoor kunnen we de hele rij markeren door voorwaardelijke opmaak.
Markeer rijen op basis van een numeriek criterium
In het bovenstaande voorbeeld hebben we gezien hoe u op een naam kunt controleren en de hele rij kunt markeren.
We kunnen dezelfde methode gebruiken om ook te controleren op numerieke waarden en rijen te markeren op basis van een voorwaarde.
Stel dat ik dezelfde gegevens heb (zoals hieronder weergegeven), en ik wil alle rijen markeren waar de hoeveelheid meer dan 15 is.
Hier zijn de stappen om dit te doen:
- Selecteer de volledige dataset (A2:F17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =$D2>=15
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in waarin u wilt dat de rij wordt gemarkeerd.
- Klik OK.
Hiermee worden alle rijen gemarkeerd waar de hoeveelheid groter is dan of gelijk is aan 15.
Op dezelfde manier kunnen we dit ook gebruiken om ook criteria voor de datum te hebben.
Als u bijvoorbeeld alle rijen met de datum na 10 juli 2021-2022 wilt markeren, kunt u de onderstaande datumformule gebruiken:
=$A2>DATUM(2018,7,10)
Markeer rijen op basis van meerdere criteria (EN/OF)
U kunt ook meerdere criteria gebruiken om rijen te markeren met behulp van voorwaardelijke opmaak.
Als u bijvoorbeeld alle rijen wilt markeren waar de naam van de vertegenwoordiger 'Bob' is en het aantal groter is dan 10, kunt u dat doen met behulp van de volgende stappen:
- Selecteer de volledige dataset (A2:F17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =EN($C2=”Bob”,$D2>10)
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in waarin u wilt dat de rij wordt gemarkeerd.
- Klik OK.
In dit voorbeeld worden alleen die rijen gemarkeerd waar aan beide voorwaarden is voldaan (dit wordt gedaan met behulp van de EN-formule).
Op dezelfde manier kunt u ook de OF-voorwaarde gebruiken. Als u bijvoorbeeld rijen wilt markeren waarin de verkoper Bob is of het aantal groter is dan 15, kunt u de onderstaande formule gebruiken:
=OF($C2="Bob",$D2>15)
Klik hier om het voorbeeldbestand te downloaden en te volgen.
Markeer rijen in verschillende kleuren op basis van meerdere voorwaarden
Soms wilt u misschien rijen in een kleur markeren op basis van de voorwaarde.
U wilt bijvoorbeeld alle rijen markeren waar het aantal groter is dan 20 in groen en waar het aantal groter is dan 15 (maar minder dan 20) in oranje.
Om dit te doen, moet u twee voorwaardelijke opmaakregels maken en de prioriteit instellen.
Hier zijn de stappen om dit te doen:
- Selecteer de volledige dataset (A2:F17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =$D2>15
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in op Oranje.
- Klik OK.
- Klik in het dialoogvenster 'Voorwaardelijke opmaakregels' op 'Nieuwe regel'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =$D2>20
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in op Groen.
- Klik OK.
- Klik op Toepassen (of OK).
De bovenstaande stappen zouden alle rijen maken met een hoeveelheid van meer dan 20 in groen en die met meer dan 15 (maar minder dan gelijk aan 20 in oranje).
De volgorde van regels begrijpen:
Bij gebruik van meerdere voorwaarden is het belangrijk dat de volgorde van de voorwaarden klopt.
In het bovenstaande voorbeeld staat de groene kleurvoorwaarde boven de oranje kleurvoorwaarde.
Als het andersom is, zijn alle rijen alleen oranje gekleurd.
Waarom?
Omdat een rij waar de hoeveelheid groter is dan 20 (zeg 23) voldoet aan onze beide voorwaarden (=$D2>15 en =$D2>20). En aangezien Oranje conditie bovenaan staat, krijgt het de voorkeur.
U kunt de volgorde van de voorwaarden wijzigen door de knoppen Omhoog/Omlaag te gebruiken.
Klik hier om het voorbeeldbestand te downloaden en te volgen.
Markeer rijen waar elke cel leeg is
Als u alle rijen wilt markeren waar een van de cellen erin leeg is, moet u voor elke cel controleren met behulp van voorwaardelijke opmaak.
Hier zijn de stappen om dit te doen:
- Selecteer de volledige dataset (A2:F17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =AANTAL.ALS($A2:$F2,””)>0
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in op Oranje.
- Klik OK.
De bovenstaande formule telt het aantal lege cellen. Als het resultaat meer dan 0 is, betekent dit dat er lege cellen in die rij zijn.
Als een van de cellen leeg is, wordt de hele rij gemarkeerd.
Markeer rijen op basis van vervolgkeuzelijst
In de voorbeelden die tot nu toe zijn behandeld, zijn alle voorwaarden gespecificeerd met het dialoogvenster voorwaardelijke opmaak.
In dit deel van de tutorial laat ik je zien hoe je het dynamisch kunt maken (zodat je de voorwaarde in een cel in Excel kunt invoeren en automatisch de daarop gebaseerde rijen markeert).
Hieronder ziet u een voorbeeld, waarbij ik een naam selecteer in de vervolgkeuzelijst en alle rijen met die naam worden gemarkeerd:
Dit zijn de stappen om dit te maken:
- Maak een vervolgkeuzelijst in cel A2. Hier heb ik de namen van de verkoper gebruikt om de vervolgkeuzelijst te maken. Hier is een gedetailleerde handleiding voor het maken van een vervolgkeuzelijst in Excel.
- Selecteer de volledige dataset (C2:H17 in dit voorbeeld).
- Klik op het tabblad Start.
- Klik in de groep Stijlen op Voorwaardelijke opmaak.
- Klik op 'Nieuwe regels'.
- Klik in het dialoogvenster 'Nieuwe opmaakregel' op 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =$E2=$A$2
- Klik op de knop 'Formaat'. Stel in het dialoogvenster dat wordt geopend de kleur in op Oranje.
- Klik OK.
Wanneer u nu een naam in de vervolgkeuzelijst selecteert, worden automatisch de rijen gemarkeerd waar de naam dezelfde is als die u in de vervolgkeuzelijst hebt geselecteerd.
Wilt u meer weten over zoeken en markeren in Excel? Bekijk de onderstaande video's.
