Voorwaardelijke opmaak in Excel: zo gebruik je het (met formulevoorbeelden)

Om voorwaardelijke opmaak in Excel te gebruiken selecteer je je cellen, ga je naar Start → Voorwaardelijke opmaak en kies je een regel zoals Regels voor het markeren van cellen → Groter dan, Gegevensbalken of Kleurenschalen. Voor alles wat het menu niet dekt, zoals een hele rij markeren of twee kolommen vergelijken, kies je Nieuwe regel → Een formule gebruiken om te bepalen welke cellen worden opgemaakt en typ je een formule die WAAR teruggeeft voor de cellen die je wilt opmaken.

De meeste problemen komen door één detail: in een formuleregel bepalen de $-tekens naar welke cellen de formule kijkt. Zet ze goed (meestal $C2, met de kolom vast en de rij vrij) en de regel werkt op elke rij. Zet je ze fout, dan markeert Excel de verkeerde cellen of geen enkele.

Let op: de formulevoorbeelden hieronder gebruiken de Nederlandse functienamen (VANDAAG, EN, REST, RIJ) en puntkomma's als scheidingsteken, zoals in Nederlandstalige Excel. In Engelstalige Excel zijn dat TODAY, AND, MOD, ROW en komma's.

De ingebouwde regels voor voorwaardelijke opmaak gebruiken

Deze stappen zijn hetzelfde in Excel voor Microsoft 365, 2024, 2021 en 2019 in Windows.

1. Selecteer de cellen die je wilt opmaken, bijvoorbeeld de kolom Bedrag (D2:D10), zonder de kop.
2. Ga naar Start → Voorwaardelijke opmaak. (De toetsencombinatie Alt, H, L na elkaar geldt voor Engelstalige Excel; in de Nederlandse versie zijn de toegangstoetsen anders, die zie je als je Alt indrukt.)
3. Wijs Regels voor het markeren van cellen aan en kies een regel:
   • Groter dan, Kleiner dan, Tussen of Gelijk aan voor getallen
   • Tekst die bevat voor woorden zoals "Te laat"
   • Datum voor datums zoals Gisteren, Afgelopen 7 dagen, Volgende week of Deze maand
   • Dubbele waarden om herhalingen te markeren (of zet de keuzelijst op Uniek)
4. Typ de waarde en kies een opmaak in de lijst met (zoals Lichtrode vulling met donkerrode tekst) of Aangepaste notatie.
5. Klik op OK.

De rest van het menu werkt op dezelfde manier:

• Regels voor bovenste/onderste markeren de Bovenste 10 items, Bovenste 10%, Onderste 10 items, Onderste 10%, of waarden Boven gemiddelde en Onder gemiddelde. Je kunt die 10 veranderen in elk getal.
• Gegevensbalken tekenen een balk in elke cel, langer bij grotere waarden (Verloopvulling of Effen vulling).
• Kleurenschalen kleuren cellen van de ene kleur naar de andere, bijvoorbeeld rood voor laag en groen voor hoog.
• Pictogramseries voegen pijlen, stoplichten of beoordelingen toe op basis van waar elke waarde in het bereik valt.

Wil je dubbele waarden vinden of verwijderen, lees dan de handleiding over dubbele waarden verwijderen in Excel.

Een hele rij markeren op basis van een celwaarde

Ingebouwde regels maken alleen de cellen op die aan de voorwaarde voldoen. Om de hele rij te kleuren wanneer Status "Te laat" zegt, heb je een formuleregel nodig. Stel dat je gegevens in A1:D100 staan: Klant in A, Vervaldatum in B, Status in C, Bedrag in D.

1. Selecteer het hele gegevensbereik zonder de koprij, te beginnen bij de cel linksboven: klik op A2 en sleep naar D100.
2. Ga naar Start → Voorwaardelijke opmaak → Nieuwe regel.
3. Kies onder Selecteer een regeltype de optie Een formule gebruiken om te bepalen welke cellen worden opgemaakt.
4. Typ bij Waarden opmaken waarvoor deze formule waar is: =$C2="Te laat"
5. Klik op Opmaak, open het tabblad Opvulling, kies een lichtrode kleur en klik op OK.
6. Controleer of het Voorbeeld je opmaak toont en klik op OK.

Elke rij waarin kolom C "Te laat" zegt, wordt nu rood van A tot D, en de kleur verandert mee als de status wijzigt. De vergelijking is niet hoofdlettergevoelig. Typen mensen de status met de hand, dan houdt een vervolgkeuzelijst de spelling consistent, zodat de regel nooit een rij mist.

Waarom het $-teken ertoe doet (fout nummer 1)

Excel schrijft je formule voor de actieve cel, de eerste cel van je selectie (A2 hierboven), en past hem daarna aan voor elke andere cel, net als bij het kopiëren van een formule naar beneden en opzij.

Met =C2="Te laat" controleert cel A2 de cel C2, maar B2 controleert D2, dus slechts een deel van de rij licht op. Met =$C$2="Te laat" controleert elke cel C2, dus het hele bereik is óf helemaal rood óf helemaal niet.

Let op: klik je tijdens het typen van de formule op een cel, dan voegt Excel een absolute verwijzing in zoals $C$2. Druk op F4 (op een Mac Cmd+T of Fn+F4) om door $C$2, C$2, $C2 en C2 te schakelen totdat je de juiste hebt.

Meer formulevoorbeelden

Gebruik dezelfde stappen met deze formules. Ze gaan er allemaal van uit dat je selectie in rij 2 begint.

Een cel opmaken op basis van een andere cel. In een verkoopblad met Werkelijk in kolom D en Doel in kolom E selecteer je D2:D100 en gebruik je: =$D2<$E2 Elk werkelijk bedrag onder het doel wordt rood. Selecteer in plaats daarvan A2:E100 om de hele rij te markeren.

Vergelijken met één invoercel. Zet een drempelwaarde in H1, selecteer D2:D100 en gebruik =$D2>$H$1. $H$1 is volledig absoluut, zodat elke rij naar dezelfde cel kijkt.

Datums in het verleden markeren. Selecteer A2:D100 en gebruik: =$B2<VANDAAG() Lege cellen tellen als nul (een datum in 1900), dus ook lege vervaldatums lichten op. Om lege cellen en betaalde facturen over te slaan, gebruik je =EN($B2<>"";$B2<VANDAAG();$C2<>"Betaald").

Om de andere rij arceren. Selecteer je gegevens en gebruik: =REST(RIJ();2)=0 Hiermee worden even rijen gearceerd (=1 voor oneven rijen). Start → Opmaken als tabel met Gestreepte rijen doet hetzelfde zonder regel.

Voorwaarden combineren. Gebruik EN of OF, bijvoorbeeld =EN($C2="In behandeling";$D2>5000) voor grote facturen in behandeling. Je hebt geen ALS nodig: de formule hoeft alleen WAAR of ONWAAR terug te geven.

Regels beheren, volgorde van regels en Stoppen als waar

Ga naar Start → Voorwaardelijke opmaak → Regels beheren om de Beheer van regels voor voorwaardelijke opmaak te openen.

1. Kies bij Opmaakregels weergeven voor de optie Dit werkblad om elke regel op het blad te zien.
2. Gebruik Regel bewerken of Regel verwijderen, of pas het bereik van een regel aan bij Is van toepassing op (bijvoorbeeld =$A$2:$D$100) en klik op Toepassen.
3. Gebruik de pijlen Omhoog en Omlaag om regels te herordenen. Regels hoger in de lijst gaan voor.

Als twee regels hetzelfde verschillend instellen, zoals twee vulkleuren, wint de hogere regel. Botsen ze niet (de ene maakt het lettertype vet, de andere vult de cel rood), dan gelden beide. Opmaak door regels gaat ook voor op opmaak die je met de hand hebt toegepast.

Vink Stoppen als waar aan om te voorkomen dat Excel de regels eronder nog controleert zodra die regel waar is. Dat is handig voor uitzonderingen zoals "als Status Betaald is, toon dan niets anders". Het is niet beschikbaar voor regels met gegevensbalken, kleurenschalen of pictogramseries.

Om de opmaak te verwijderen gebruik je Start → Voorwaardelijke opmaak → Regels wissen → Regels wissen in geselecteerde cellen of Regels wissen in het hele blad.

Voorwaardelijke opmaak naar andere cellen kopiëren

• Opmaak kopiëren/plakken: selecteer een opgemaakte cel, klik op Start → Opmaak kopiëren/plakken en sleep over de nieuwe cellen. Dubbelklik erop om meerdere bereiken te verven; druk op Esc om te stoppen.
• Plakken special: kopieer de cel, selecteer het doel en kies Start → Plakken → Plakken special → Opmaak.
• Breid de regel liever uit: pas het bereik Is van toepassing op aan in Regels beheren. Zo houd je één nette regel in plaats van meerdere kopieën.

Excel voor Mac en Excel voor het web

Excel voor Mac heeft hetzelfde menu Start → Voorwaardelijke opmaak. Kies voor een formuleregel Nieuwe regel, zet Stijl op Klassiek, kies in het vak eronder Een formule gebruiken om te bepalen welke cellen worden opgemaakt, typ de formule en zet Opmaken met op Aangepaste notatie.

Excel voor het web gebruikt ook Start → Voorwaardelijke opmaak, maar regels openen in een paneel Voorwaardelijke opmaak in plaats van een dialoogvenster. Kies voor een formuleregel Nieuwe regel en stel het regeltype in op Formule. Regels die je op de desktop maakt, werken in de browser en andersom. (De exacte Nederlandse namen in de webversie kunnen afwijken.)

Waarom voorwaardelijke opmaak niet werkt

Het markeert de verkeerde rijen (een rij ernaast). Begint je selectie bij A2, dan moet de formule naar rij 2 verwijzen. Dit gebeurt meestal als je de koprij meeneemt of van onder naar boven selecteert.

Slechts één kolom wordt gemarkeerd, of alles. Controleer de dollartekens. Gebruik $C2, niet C2 of $C$2, voor regels voor hele rijen.

Getallen zijn opgeslagen als tekst. Geïmporteerde waarden kunnen op getallen lijken maar tekst zijn (vaak met een klein groen driehoekje in de hoek), waardoor regels zoals Groter dan zich vreemd gedragen. Selecteer de cellen, klik op het waarschuwingspictogram en kies Converteren naar getal.

Tekst komt niet overeen. Een spatie aan het eind ("Te laat ") of een andere spelling is niet gelijk aan "Te laat". Maak de gegevens schoon met SPATIES.WISSEN, of gebruik Tekst die bevat in plaats van Gelijk aan.

Regels overlappen of werken elkaar tegen. Open Regels beheren, toon Dit werkblad en controleer de volgorde en de bereiken bij Is van toepassing op. Door kopiëren, plakken en rijen invoegen wordt één regel vaak in veel fragmenten opgesplitst.

De werkmap werd traag. Honderden gefragmenteerde regels, regels op hele kolommen (=$A:$D) en vluchtige functies zoals VANDAAG() of INDIRECT zorgen voor extra herberekeningen. Verwijder dubbele regels en beperk Is van toepassing op tot de rijen die je gebruikt.

Veelgestelde vragen

Hoe markeer ik een rij op basis van een celwaarde in Excel? Selecteer de gegevens zonder kopregels, kies Start → Voorwaardelijke opmaak → Nieuwe regel → Een formule gebruiken om te bepalen welke cellen worden opgemaakt, voer =$C2="Te laat" in en kies een vulling. Door de $ voor de kolomletter verandert de hele rij van kleur.

Hoe pas ik voorwaardelijke opmaak toe op basis van een andere cel? Gebruik een formuleregel die naar die cel verwijst, bijvoorbeeld =$D2<$E2 om waarden onder hun doel te markeren, of =$D2>$H$1 om elke rij met één invoercel te vergelijken.

Kan voorwaardelijke opmaak naar een ander blad verwijzen? Ja, bijvoorbeeld =$D2>Instellingen!$B$1. Verwijzingen naar andere werkmappen zijn niet toegestaan.

Kan ik sorteren of filteren op de kleur van voorwaardelijke opmaak? Ja. Zet Gegevens → Filter aan, open de filterpijl van de kolom en kies Filteren op kleur of Sorteren op kleur. Het werkt met kleuren die door regels zijn toegepast.

Hoe verwijder ik voorwaardelijke opmaak zonder de gegevens te wissen? Selecteer de cellen en kies Start → Voorwaardelijke opmaak → Regels wissen → Regels wissen in geselecteerde cellen. Je waarden blijven staan.

Laat AI vertellen wat je moet markeren

Voorwaardelijke opmaak toont wat je al hebt besloten te zoeken. CoreGPT voor Excel werkt andersom: vraag "wat valt op in de verkoop van Q3?" en het leest je gegevens en wijst op topverkopers, ongewone dalingen en trends, zodat je weet welke regels het waard zijn om in te stellen. Je kunt gratis starten en het werkt in Excel voor Windows, Mac en het web.

Probeer het: CoreGPT voor Excel

Verder lezen:

• Dubbele waarden verwijderen in Excel: 5 manieren (met en zonder gegevens te wissen)
• Vervolgkeuzelijst maken in Excel (die zichzelf automatisch bijwerkt)
• XLOOKUP vs VLOOKUP (X.ZOEKEN vs VERT.ZOEKEN): verschillen, voorbeelden en welke je moet gebruiken