Voorwaardelijke opmaak in Excel: de ultieme gids met voorbeelden

Voorwaardelijke opmaak is een van de meest eenvoudige maar krachtige functies in Excel-spreadsheets.

Zoals de naam al doet vermoeden, kunt u voorwaardelijke opmaak in Excel gebruiken wanneer u cellen wilt markeren die aan een opgegeven voorwaarde voldoen.

Het geeft je de mogelijkheid om snel een visuele analyselaag toe te voegen aan je dataset. U kunt warmtekaarten maken, toenemende/afnemende pictogrammen, Harvey-bubbels en nog veel meer weergeven met behulp van voorwaardelijke opmaak in Excel.

Voorwaardelijke opmaak gebruiken in Excel (voorbeelden)

In deze zelfstudie laat ik je zeven geweldige voorbeelden zien van het gebruik van voorwaardelijke opmaak in Excel:

• Identificeer snel duplicaten met behulp van voorwaardelijke opmaak in Excel.
• Markeer cellen met een waarde groter/kleiner dan een getal in een gegevensset.
• Markering van de bovenste/onderste 10 (of 10%) waarden in een gegevensset.
• Markeren van fouten / blanco's met behulp van voorwaardelijke opmaak in Excel.
• Heatmaps maken met voorwaardelijke opmaak in Excel.
• Markeer elke N-de rij/kolom met behulp van voorwaardelijke opmaak.
• Zoek en markeer met behulp van voorwaardelijke opmaak in Excel.

1. Identificeer snel duplicaten

Voorwaardelijke opmaak in Excel kan worden gebruikt om duplicaten in een gegevensset te identificeren.

Hier is hoe u dit kunt doen:

• Selecteer de dataset waarin u duplicaten wilt markeren.
• Ga naar Start -> Voorwaardelijke opmaak -> Celregels markeren -> Dubbele waarden.
• Zorg ervoor dat in het dialoogvenster Waarden dupliceren Dupliceren is geselecteerd in de vervolgkeuzelijst links. U kunt het toe te passen formaat specificeren door de rechter vervolgkeuzelijst te gebruiken. Er zijn enkele bestaande indelingen die u kunt gebruiken, of u kunt uw eigen indeling opgeven met de optie Aangepast formaat.
• Klik OK.

Dit zou onmiddellijk alle cellen markeren die een duplicaat hebben in de geselecteerde dataset. Uw gegevensset kan zich in een enkele kolom, meerdere kolommen of in een niet-aangrenzend celbereik bevinden.

Zie ook: De ultieme gids voor het zoeken en verwijderen van duplicaten in Excel.

2. Markeer cellen met een waarde groter/kleiner dan een getal

U kunt voorwaardelijke opmaak in Excel gebruiken om snel cellen te markeren die waarden bevatten die groter/kleiner zijn dan een opgegeven waarde. Bijvoorbeeld het markeren van alle cellen met een verkoopwaarde van minder dan 100 miljoen, of het markeren van cellen met tekens die lager zijn dan de overschrijdingsdrempel.

Hier zijn de stappen om dit te doen:

• Selecteer de volledige dataset.
• Ga naar Start -> Voorwaardelijke opmaak -> Celregels markeren -> Groter dan… / Kleiner dan…
• Op basis van de optie die u selecteert (groter dan of kleiner dan), wordt een dialoogvenster geopend. Stel dat u de optie 'Groter dan' selecteert. Voer in het dialoogvenster het nummer in het veld aan de linkerkant in. De bedoeling is om cellen te markeren die een getal hebben dat groter is dan dit opgegeven getal.

• Geef de opmaak op die moet worden toegepast op de cellen die aan de voorwaarde voldoen met behulp van de vervolgkeuzelijst aan de rechterkant. Er zijn enkele bestaande indelingen die u kunt gebruiken, of u kunt uw eigen indeling opgeven met de optie Aangepast formaat.
• Klik OK.

Dit zou onmiddellijk alle cellen markeren met waarden groter dan 5 in een gegevensset.Opmerking: als u waarden groter dan 5 wilt markeren, moet u voorwaardelijke opmaak opnieuw toepassen met de criteria "Gelijk aan".

Hetzelfde proces kan worden gevolgd om cellen te markeren met een waarde die kleiner is dan een opgegeven waarde.

3. Markering boven/onder 10 (of 10%)

Voorwaardelijke opmaak in Excel kan snel top 10 items of top 10% uit een dataset identificeren. Dit kan handig zijn in situaties waarin u snel de beste kandidaten wilt zien aan de hand van scores of topdealwaarden in de verkoopgegevens.

Op dezelfde manier kunt u ook snel de onderste 10 items of de onderste 10% in een dataset identificeren.

Hier zijn de stappen om dit te doen:

• Selecteer de volledige dataset.
• Ga naar Start -> Voorwaardelijke opmaak -> Regels boven/onder -> Top 10 items (of %) / Onderste 10 items (of %).
• Op basis van wat u selecteert, wordt het dialoogvenster geopend. Laten we zeggen dat je Top 10 items hebt geselecteerd, dan zou het een dialoogvenster openen zoals hieronder getoond:
• Geef de opmaak op die moet worden toegepast op de cellen die aan de voorwaarde voldoen met behulp van de vervolgkeuzelijst aan de rechterkant. Er zijn enkele bestaande indelingen die u kunt gebruiken, of u kunt uw eigen indeling opgeven met de optie Aangepast formaat.
• Klik OK.

Dit zou onmiddellijk de top 10 items in de geselecteerde dataset markeren. Merk op dat dit alleen werkt voor cellen die een numerieke waarde bevatten.

Als u minder dan 10 cellen in de dataset hebt en u selecteert de opties om Top 10 items/Onderste 10 items te markeren, dan worden alle cellen gemarkeerd.

Hier zijn enkele voorbeelden van hoe de voorwaardelijke opmaak zou werken:

4. Markeren van fouten/spaties

Als u in Excel met veel numerieke gegevens en berekeningen werkt, weet u hoe belangrijk het is om cellen met fouten of lege cellen te identificeren en te behandelen. Als deze cellen worden gebruikt in verdere berekeningen, kan dit leiden tot foutieve resultaten.

Voorwaardelijke opmaak in Excel kan u helpen snel cellen te identificeren en te markeren die fouten bevatten of leeg zijn.

Stel dat we een dataset hebben zoals hieronder weergegeven:

Deze dataset heeft een lege cel (A4) en fouten (A5 en A6).

Hier zijn stappen om de cellen te markeren die leeg zijn of fouten bevatten:

• Selecteer de dataset waarin u lege cellen en cellen met fouten wilt markeren.
• Ga naar Home -> Conditionele opmaak -> Nieuwe regel.
• Selecteer in het dialoogvenster Nieuwe opmaakregel de optie Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt.
• Voer de volgende formule in het veld in het gedeelte 'Bewerk de regelbeschrijving' in:
  =OF(ISBLANK(A1),ISFOUT(A1))
  • De bovenstaande formule controleert alle cellen op twee voorwaarden - of deze leeg is of niet en of deze een fout bevat of niet. Als een van de voorwaarden WAAR is, wordt WAAR geretourneerd.
• Stel de opmaak in die u wilt toepassen op de cellen die leeg zijn of fouten bevatten. Klik hiervoor op de knop Opmaak. Het opent het dialoogvenster 'Cellen opmaken', waar u het formaat kunt specificeren.
• Klik OK.

Dit zou onmiddellijk alle cellen markeren die leeg zijn of fouten bevatten.

Opmerking: U hoeft niet het volledige bereik A1:A7 in de formule te gebruiken in voorwaardelijke opmaak. De bovengenoemde formule gebruikt alleen A1. Wanneer u deze formule op het hele bereik toepast, controleert Excel één cel per keer en past de verwijzing aan. Wanneer het bijvoorbeeld A1 controleert, gebruikt het de formule =OR(ISBLANK(A1),ISERROR(A1)). Wanneer het cel A2 controleert, gebruikt het vervolgens de formule =OR(ISBLANK(A2),ISERROR(A2)). Het past automatisch de verwijzing aan (aangezien dit relatieve verwijzingen zijn) afhankelijk van de cel die wordt geanalyseerd. U hoeft dus niet voor elke cel een aparte formule te schrijven. Excel is slim genoeg om de celverwijzing helemaal zelf te wijzigen 🙂

Zie ook: IFERROR en ISERROR gebruiken om fouten in Excel af te handelen.

5. Heatmaps maken

Een warmtekaart is een visuele weergave van gegevens waarbij de kleur de waarde in een cel vertegenwoordigt. U kunt bijvoorbeeld een warmtekaart maken waarbij een cel met de hoogste waarde groen gekleurd is en er een verschuiving naar rode kleur plaatsvindt naarmate de waarde afneemt.

Iets zoals hieronder weergegeven:

De bovenstaande dataset heeft waarden tussen 1 en 100. Cellen worden gemarkeerd op basis van de waarde erin. 100 krijgt de groene kleur, 1 krijgt de rode kleur.

Hier zijn de stappen om heatmaps te maken met voorwaardelijke opmaak in Excel.

• Selecteer de dataset.
• Ga naar Home -> Voorwaardelijke opmaak -> Kleurschalen en kies een van de kleurenschema's.

Zodra u op het heatmap-pictogram klikt, wordt de opmaak toegepast op de dataset. Er zijn meerdere kleurovergangen waaruit u kunt kiezen. Als u niet tevreden bent met de bestaande kleuropties, kunt u meer regels selecteren en de gewenste kleur opgeven.

Opmerking: op een vergelijkbare manier kunt u ook gegevensbalk en pictogrammensets toepassen.

6. Markeer elke andere rij/kolom

Misschien wilt u alternatieve rijen markeren om de leesbaarheid van de gegevens te vergroten.

Dit worden de zebralijnen genoemd en kunnen vooral handig zijn als u de gegevens afdrukt.

Er zijn nu twee manieren om deze zebralijnen te maken. De snelste manier is om uw tabelgegevens om te zetten in een Excel-tabel. Het paste automatisch een kleur toe op afwisselende rijen. Je kunt er hier meer over lezen.

Een andere manier is het gebruik van voorwaardelijke opmaak.

Stel dat u een dataset heeft zoals hieronder weergegeven:

Hier zijn de stappen om alternatieve rijen te markeren met behulp van voorwaardelijke opmaak in Excel.

• Selecteer de gegevensset. Selecteer in het bovenstaande voorbeeld A2:C13 (exclusief de koptekst). Als u ook de koptekst wilt opnemen, selecteert u de volledige dataset.
• Open het dialoogvenster Voorwaardelijke opmaak (Home-> Conditionele opmaak-> Nieuwe regel) [Toetsenbordsnelkoppeling - Alt + O + D].
• Selecteer in het dialoogvenster het dialoogvenster 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
• Voer de volgende formule in het veld in het gedeelte 'Bewerk de regelbeschrijving' in:
  =ISODD(RIJ())

• De bovenstaande formule controleert alle cellen en als het RIJ-nummer van een cel oneven is, wordt WAAR geretourneerd. De opgegeven voorwaardelijke opmaak wordt toegepast op alle cellen die TRUE retourneren.
• Stel de opmaak in die u wilt toepassen op de cellen die leeg zijn of fouten bevatten. Klik hiervoor op de knop Opmaak. Het opent het dialoogvenster 'Cellen opmaken', waar u het formaat kunt specificeren.
• Klik OK.

Dat is het! De alternatieve rijen in de dataset worden gemarkeerd.

U kunt in veel gevallen dezelfde techniek gebruiken. Het enige dat u hoeft te doen, is de relevante formule gebruiken in de voorwaardelijke opmaak. Hier zijn enkele voorbeelden:

• Markeer alternatieve even rijen: =ISEVEN(ROW())
• Markeer alternatieve rijen toevoegen: =ISODD(ROW())
• Markeer elke 3e rij: =MOD(ROW(),3)=0

7. Zoek en markeer gegevens met voorwaardelijke opmaak

Deze is een beetje geavanceerd gebruik van voorwaardelijke opmaak. Het zou je eruit laten zien als een Excel-rockstar.

Stel dat u een dataset heeft zoals hieronder weergegeven, met productnaam, verkoper en geografie. Het idee is om een ​​string in cel C2 te typen, en als deze overeenkomt met de gegevens in een cel(len), dan moet die worden gemarkeerd. Iets zoals hieronder weergegeven:

Dit zijn de stappen om deze zoek- en markeringsfunctionaliteit te maken:

• Selecteer de gegevensset.
• Ga naar Home -> Conditionele opmaak -> Nieuwe regel (sneltoets - Alt + O + D).
• Selecteer in het dialoogvenster Nieuwe opmaakregel de optie 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
• Voer de volgende formule in het veld in het gedeelte 'Bewerk de regelbeschrijving' in:
  =EN($C$2””,$C$2=B5)
• Stel de opmaak in die u wilt toepassen op de cellen die leeg zijn of fouten bevatten. Klik hiervoor op de knop Opmaak. Het opent het dialoogvenster 'Cellen opmaken', waar u het formaat kunt specificeren.
• Klik OK.

Dat is het! Als u nu iets invoert in cel C2 en op enter drukt, worden alle overeenkomende cellen gemarkeerd.

Hoe werkt dit?

De formule die bij voorwaardelijke opmaak wordt gebruikt, evalueert alle cellen in de gegevensset. Stel dat u Japan invoert in cel C2. Nu zou Excel de formule voor elke cel evalueren.

De formule retourneert TRUE voor een cel wanneer aan twee voorwaarden wordt voldaan:

• Cel C2 is niet leeg.
• De inhoud van cel C2 komt exact overeen met de inhoud van de cel in de dataset.

Daarom worden alle cellen die de tekst Japan bevatten gemarkeerd.

Download het voorbeeldbestand

U kunt dezelfde logica gebruiken om variaties te maken zoals:

• Markeer de hele rij in plaats van een cel.
• Markeer zelfs als er een gedeeltelijke overeenkomst is.
• Markeer de cellen/rijen terwijl u typt (dynamisch) [Je gaat deze truc geweldig vinden :)].

Voorwaardelijke opmaak verwijderen in Excel

Eenmaal toegepast, blijft voorwaardelijke opmaak behouden, tenzij u deze handmatig verwijdert. Als best practice houdt u de voorwaardelijke opmaak alleen toegepast op die cellen waar u deze nodig hebt.

Omdat het vluchtig is, kan het leiden tot een trage Excel-werkmap.

Voorwaardelijke opmaak verwijderen:

• Selecteer de cellen waaruit u voorwaardelijke opmaak wilt verwijderen.
• Ga naar Start -> Voorwaardelijke opmaak -> Regels wissen -> Regels wissen van geselecteerde cellen.
  • Als u voorwaardelijke opmaak uit het hele werkblad wilt verwijderen, selecteert u Regels van volledig werkblad wissen.

Belangrijke dingen om te weten over voorwaardelijke opmaak in Excel

• Voorwaardelijke opmaak in vluchtig. Het kan leiden tot een trage werkmap. Gebruik het alleen als het nodig is.
• Wanneer u cellen kopieert die voorwaardelijke opmaak bevatten, wordt voorwaardelijke opmaak ook gekopieerd.
• Als u meerdere regels op dezelfde set cellen toepast, blijven alle regels actief. Bij eventuele overlap krijgt de laatst toegepaste regel de voorkeur. U kunt de volgorde echter wijzigen door de volgorde te wijzigen in het dialoogvenster Regels beheren.