Met voorwaardelijke opmaak kunt u belangrijke gegevens markeren en specifieke opmaak toepassen op cellen die aan bepaalde criteria voldoen.
De voorwaardelijke opmaak van Excel is een krachtige functie waarmee u de opmaak van cellen kunt wijzigen op basis van een voorwaarde (of criteria). Voorwaardelijke opmaak helpt bij het markeren, benadrukken of differentiëren van gegevens die zijn opgeslagen in een Excel-spreadsheet.
Alle voorwaardelijke opmaakregels zijn gebaseerd op eenvoudige logica: als de voorwaarden waar zijn, wordt specifieke opmaak toegepast; als de voorwaarden onwaar zijn, wordt de opmaak niet toegepast.
Met voorwaardelijke opmaak kunt u belangrijke gegevens in een gegevensset markeren, afwijkingen benadrukken en gegevens visualiseren met behulp van gegevensbalken, kleuren en pictogrammensets. In deze zelfstudie bekijken we hoe u voorwaardelijke opmaak kunt toepassen om cellen of celbereiken te markeren op basis van een voorwaarde.
Hoe Excel voorwaardelijke opmaak toe te passen
U bent bijvoorbeeld verantwoordelijk voor het beheer van een grote voorraadlijst met verschillende artikelen en hun respectievelijke hoeveelheid op voorraad. En als de hoeveelheid op voorraad van een bepaald artikel lager is dan 50, laten we zeggen, is het belangrijk dat u dat weet, zodat u het kunt aanvullen.
Als die inventarislijst een dataset heeft met honderden rijen, is het niet erg effectief om rij voor rij te zoeken om te zien of er getallen in die kolom staan die lager zijn dan '50'. Dat is waar de voorwaardelijke opmaakfunctie van pas komt. Met een paar muisklikken kunt u alle waarden markeren in een kolom die kleiner is dan '50'.
Voorwaardelijke opmaak met markeringsregels
In ons voorbeeld hebben we een werkblad met verkooprecords van sommige producten. we willen het bedrag benadrukken dat minder dan 500 in verkoop is.
Om dat te doen, selecteert u eerst het celbereik waarop u een regel (voorwaarde) wilt toepassen. In ons voorbeeld willen we het bedrag dat kleiner is dan 500 markeren in de kolom 'Bedrag', dus selecteer kolom D. U kunt waarden markeren in een celbereik of meerdere celbereiken, of zelfs een heel blad.
Ga dan naar het tabblad ‘Home’ en klik op ‘Voorwaardelijke opmaak’. Selecteer 'Markeer regels voor cellen' in de vervolgkeuzelijst en aangezien we minder dan 500 waarden willen vinden, klikt u op de optie 'Minder dan'. Excel biedt zeven vooraf ingestelde markeringsregels. U kunt ze allemaal gebruiken om gegevens te markeren.
Vervolgens verschijnt een dialoogvenster 'Minder dan'. Voer daarin '500' in het vak 'Cellen opmaken die MINDER DAN zijn' in en selecteer opmaak voor de markering in de vervolgkeuzelijst ernaast.
Nu worden de cellen die minder dan 500 in hun waarden bevatten, gemarkeerd in de gekozen opmaak.
Laten we eens kijken hoe u waarden kunt markeren die een specifieke tekenreeks bevatten. In het volgende voorbeeld willen we alle werknemers uit New South Wales (NSW) markeren.
Ga hiervoor naar het tabblad Start -> Voorwaardelijke opmaak -> Markeer regels voor cellen -> Tekst die bevat.
U kunt andere opties gebruiken in het horizontale vervolgkeuzemenu 'Regels voor cellen markeren' om Dubbele waarden, Datums, Groter dan, Gelijk aan of Tussen waarden te markeren.
Typ in het dialoogvenster Tekst die bevat 'NSW' in het vak en kies de opmaak en klik op 'OK'.
Het resultaat:
Voorwaardelijke opmaak met regels voor boven/onder
Boven-/onderregels zijn andere handige ingebouwde vooraf ingestelde voorwaardelijke opmaakregels die beschikbaar zijn in Excel. Met deze regels kunt u de aandacht vestigen op het bovenste (n) aantal items, het bovenste (n) aantal procent, het onderste (n) aantal items, het onderste (n) aantal procent of celwaarden die hoger zijn dan gemiddeld of onder het gemiddelde.
Stel dat u het cijferrecord van een leerling in een spreadsheet hebt staan en u wilt de top 10 artiesten (top 10 rangen) uit die lijst weten. Met voorwaardelijke opmaak kunt u de top 10 of top 15 markeren, of een willekeurig (n) aantal topitems. Om de Top 10 items in een celbereik te markeren, selecteert u eerst het bereik (Totaal) in de tabel.
Ga vervolgens naar 'Voorwaardelijke opmaak', vouw 'Top/Bottom-regels' uit en selecteer de optie 'Top 10 items..'.
Wijzig in het dialoogvenster 'Top 10 items' het aantal rangen met behulp van kleine pijlen in het linkerveld. Als je de top 20 rangen (totaalcijfer) in je cijferlijst wilt markeren, stel het aantal dan in op 20. De standaardwaarde is al 10, dus we houden het. Kies de celopmaak in het rechterveld en klik op 'OK'
De top 10 punten uit de kolom 'Totaal' zijn gemarkeerd zoals hieronder weergegeven.
Stel dat u benedengemiddelde cijfers wilt vinden in de kolom 'Examen 1' in datzelfde cijferblad. Ga hiervoor naar het tabblad Start -> Voorwaardelijke opmaak -> Boven-/onderregels -> Onder het gemiddelde.
U kunt uw eigen voorwaardelijke opmaak instellen in plaats van een van de vooraf gedefinieerde voorwaarden te kiezen. Om uw eigen opmaak te maken, selecteert u de optie 'Aangepast formaat' in het venster 'Onder het gemiddelde'.
Er wordt een nieuw venster Cellen opmaken geopend, hier kunt u uw opmaakcellen aanpassen. We selecteren een oranje kleur om de markeringen onder het gemiddelde te markeren. Als u klaar bent, klikt u tweemaal op 'OK' om het resultaat te zien.
Het resultaat:
Gegevensbalken toepassen
Gegevensbalken zijn gewoon horizontale balken in uw cellen. De grootte van de balk is relatief ten opzichte van de waarde van de cel, respectievelijk ten opzichte van de waarde van andere cellen in het geselecteerde bereik. Dat betekent dat de waarde van de korte balk laag is in vergelijking met andere celwaarden en een lange balk betekent dat de waarde hoog is in vergelijking met andere celwaarden.
Selecteer eerst het cellenbereik dat u wilt visualiseren met de gegevensbalken.
Ga vervolgens naar 'Voorwaardelijke opmaak' op het tabblad 'Start', vouw vervolgens 'Databalken' uit in de vervolgkeuzelijst en kies uw keuze voor de gegevensbalk.
Het verschil tussen de gegevensbalkindeling en andere indelingen is dat deze op alle cellen wordt weergegeven in plaats van aan een specifieke voorwaarde te voldoen.
Kleurschalen toepassen
Kleurschalen lijken erg op gegevensbalken omdat ze beide de waarde van de afzonderlijke cel correleren met de waarde van andere cellen in het geselecteerde bereik. Gegevensbalken visualiseren de celwaarde echter met de lengte van een balk, terwijl kleurenschalen dit doen met kleurovergangen.
Elke kleurschaaloptie gebruikt twee- of driekleurenverlooppatronen. Eén kleur wordt toegewezen aan de hoogste waarden, een andere kleur wordt toegewezen aan de laagste waarden en alle andere waarden daartussen krijgen een mix van die twee kleuren.
Hiervoor gebruiken we hetzelfde voorbeeld dat we voor gegevensbalken hebben gebruikt. Selecteer het celbereik, ga naar Home -> Conditionele opmaak -> Kleurschalen. Kies vervolgens een kleurbereik in het vervolgkeuzemenu Kleurschalen horizontaal.
Wanneer we het eerste kleurbereik hebben gekozen, wordt rode kleur toegewezen aan de laagste waarde, groene kleur wordt toegewezen aan de hoogste waarde en alle waarden daartussen zijn toegewezen kleuren die een mix zijn van rode en groene kleuren (zoals hieronder weergegeven). Omdat geel wordt gemaakt door rood en groen met gelijke intensiteit te combineren, worden de cellen met gemiddelde waarden eraan toegewezen.
Pictogramsets toepassen
Icon Sets zijn een andere methode om de gegevens in elke cel te visualiseren en cellen met elkaar te correleren.
Selecteer de cellen en klik op Home -> Voorwaardelijke opmaak -> Icon Sets. U kunt elk van deze pictogrammensets kiezen die meer geschikt zijn voor uw behoefte. Voor ons voorbeeld kiezen we de eerste optie onder Directional.
Rode, gele en groene pijlen die respectievelijk lage, gemiddelde of dure artikelen aangeven.
Voorwaardelijke opmaak verwijderen
Om voorwaardelijke opmaak te verwijderen, gaat u terug naar de optie 'Voorwaardelijke opmaak' op het tabblad 'Start' en klikt u op 'Regels wissen'. Kies vervolgens welke regels u wilt wissen.
We kiezen 'Regels wissen van volledig blad' om alle voorwaardelijke opmaak van het werkblad te verwijderen.
Nu hebt u geleerd hoe u voorwaardelijke opmaak kunt gebruiken om cellen op te maken met de vooraf ingestelde Excel-voorwaarde.