Voorwaardelijke opmaak in Excel: instructies en gebruik

Gebruik deze instructies om verschillende formaten te maken

Voorwaardelijke opmaak in Microsoft Excel is een handig hulpmiddel om tabellen, rapporten of cellen prominenter te maken, een gebied of meerdere kolommen te markeren, gegevens te analyseren, verschillen te verduidelijken of trends te identificeren. Hiervoor worden databalken, symbolensets of gekleurde schalen gebruikt. Afhankelijk van de opgegeven voorwaarden, wordt de weergave van cellen gewijzigd of wordt het celbereik opgemaakt. U kunt uw eigen maken voor de vele geïntegreerde voorwaarden.

Voorwaardelijke opmaak is logisch en helpt u

Voorwaardelijke opmaak in Excel kan op veel manieren worden toegepast, of het nu gaat om een Excel-tabel, een specifiek celbereik of een draaitabelrapport. Er zijn verschillende acties en formaatsjablonen voor u beschikbaar. Het doorslaggevende voordeel voor Excel-gebruikers is dat speciale waarden of resultaten kunnen worden gemarkeerd door kleurcodering. Als een waarde verandert, wordt de opmaak automatisch aangepast - afhankelijk van de omstandigheden.

info: De PivotTable ("Pivot Table") is een speciale tabel waarmee een grote hoeveelheid gegevens kan worden berekend, samengevat en geanalyseerd. Daarnaast kun je snel vergelijkingen maken en trends in de onderzoeksgegevens signaleren.

Voorwaardelijke opmaak met bestaande regels voor getallen, tekstinhoud of datums

Als u opmaakt met een vergelijkingsoperator, kunnen cellen gemakkelijker worden gevonden. U kunt waarden zichtbaarder maken, bijvoorbeeld groter of kleiner dan een gedefinieerde waarde of bepaalde waarden die tussen een boven- en ondergrens liggen. Dit betekent dat de gewenste of gezochte gegevens in kleur worden weergegeven. Snelle opmaak of geavanceerde opmaak zijn beschikbaar voor bewerking. Met de uitgebreide opmaak kun je werken met formules om bijvoorbeeld een Excel-kolom automatisch in kleur te markeren.

Voorbeelden:

• Een magazijnbediende wil graag alle voorraden onder de 15 stuks markeren met een gekleurde achtergrond.
• Alle steden met meer dan 150.000 inwoners moeten in een tabel worden aangegeven.
• Een productiebedrijf wil alle goederen die tussen 1 januari 2022-2023 en 31 januari 2022-2023 zijn geproduceerd in het rood markeren.

Snel formaat:

Selectie van een of meer cellen.

Klik op "Start" op het tabblad.

Ga naar de pijl naast de knop "Voorwaardelijke opmaak" in "Stijl".

Klik op "Regels voor het markeren van cellen".

Selecteer het gewenste commando, b.v. B. groter dan, kleiner dan, tussen, datum etc.

Voer waarden in en selecteer formaat.

Uitgebreide opmaak:

1. Selecteer een of meer cellen in een tabel of draaitabelrapport.

2. Ga naar het tabblad "Start", in de groep Stijlen, klik op de pijl naast "Voorwaardelijke opmaak".

3. Klik op "Regels beheren": Het dialoogvenster "Beheerder voor voorwaardelijke opmaakregels" verschijnt.

4. Selecteer actie:

   • Klik op "Nieuwe regel": Het dialoogvenster "Nieuwe opmaakregel" verschijnt.
   • Als u een opmaak wilt wijzigen, moet u ervoor zorgen dat het juiste werkblad of de juiste tabel is geselecteerd in de keuzelijst "Opmaakregel weergeven voor".
   • Ga in het veld "Van toepassing op" naar "Dialoogvenster samenvouwen"; selecteer vervolgens het nieuwe celbereik op het werkblad.
   • Klik op de knop "Dialoogvenster uitvouwen".
   • Selecteer de regel en klik op "Regel bewerken": Het dialoogvenster "Opmaakregel bewerken" verschijnt.

5. Ga als volgt te werk om het bereik voor velden in het waardegebied 'Regel toepassen op' van een draaitabelrapport te wijzigen:

   • Selectie: Klik op "Alleen deze cellen"
   • Bijbehorend veld: Klik op "Alle cellen met dezelfde velden"
   • Waardeveld: Klik op "Alle cellen"

6. Klik onder "Selecteer regeltype" op "Alleen cellen opmaken die bevatten"

Nu kunt u acties uitvoeren onder "Regelbeschrijving bewerken" in het lijstveld "Cellen alleen opmaken met":

Formaat op nummer, datum of tijd:

Selecteer hiervoor een celwaarde en vergelijkingsoperator en voer het getal, de datum of de tijd in. voorbeeld: Voer voor "Tussen" 300 en 500 in of selecteer "Gelijk aan" en voer een datum in.

U kunt een formule gebruiken om een getal, datum of tijdwaarde terug te krijgen.

Formaat op tekst:

Selecteer "Specifieke tekst" en "Relationele operator".

Voer tekst in, b.v. B. op "bevat" het woord "goud" of op "begint met" een voorvoegsel waarnaar u wilt zoeken.

Er kunnen maximaal 255 tekens worden ingevoerd.

Waarden kunnen worden geretourneerd met behulp van formules. Om dit te doen, moet de formule beginnen met (=). Ongeldige formules zijn niet opgemaakt. Het is raadzaam om de formule eerst op fouten te testen.

Formaat op datum:

• Selecteer datum en vergelijkingsdatum, b.v. B. Gisteren, volgende week of deze maand

Vervolgens: Om een formaat op te geven, klikt u op "Formaat", het dialoogvenster "Cellen opmaken" verschijnt. Selecteer het gewenste getalformaat, vulformaat, kader of lettertype, klik op "OK".

Het is mogelijk om meerdere formaten te kiezen. In het veld "Voorbeeld" kunt u zien welke formaten zijn geselecteerd.

Voorwaardelijke opmaak met databases, kleurenschema's en symbolensets

Inhoud en waarden in cellen kunnen visueel worden opgewaardeerd met databases, kleurenschalen of symbolensets en aantrekkelijker en duidelijker worden gemaakt. Als waarden in een celbereik met elkaar vergeleken moeten worden, gaat dat makkelijker met deze voorwaardelijke opmaak.

Cellen opmaken met gegevensbalken

Gegevensbalken maken het gemakkelijker om cellen met elkaar te vergelijken. De waarde in de cel wordt aangegeven door de lengte van de staaf. Hoe hoger de waarde, hoe langer de gegevensbalk, hoe lager de waarde, hoe korter de balk. Hierdoor kunnen hoge of lage waarden snel worden bepaald.

Snel formaat:

Markeer het gewenste gebied (cel, tabel of heel blad).

Klik op het tabblad Start op de knop Voorwaardelijke opmaak.

Ga naar "databalk" en selecteer een vulling.

Cellen opmaken met gekleurde randen

Kleurschalen helpen om verschillende gegevensinformatie gemakkelijker te classificeren en om hun inhoud snel met elkaar te kunnen vergelijken. Afhankelijk van de kleurtint worden hoge of lage waarden weergegeven. U kunt cellen opmaken met een 2- of 3-kleurenschaal.

2-kleuren schaal

Snel formaat:

Selecteer cel, tabel of het hele blad.

Klik op het tabblad Start op Voorwaardelijke opmaak.

Ga naar "kleurenschaal" en klik op het gewenste 2-kleurenschaalformaat. De bovenste kleur staat voor hogere waarden, de onderste kleur voor lagere waarden.

3-kleuren schaal

De derde kleur maakt de visualisatie van gemiddelde waarden mogelijk - naast de kleurweergave van hoge en lage waarden zoals in de 2-kleurenschaal.

Snel formaat:

Selecteer cel, tabel of het hele blad.

Klik op het tabblad Start op Voorwaardelijke opmaak.

Ga naar "kleurenschaal" en klik op het gewenste 3-kleurenschaalformaat. De bovenste kleur staat voor hogere waarden, de onderste kleur voor lagere waarden.

Cellen opmaken met symbolensets

Met een set symbolen kunnen gegevens worden bekeken en in aparte categorieën worden ingedeeld. Een symbool staat voor een bepaald waardenbereik. De voorwaardelijke opmaak in Excel biedt symbolen in verschillende kleuren: pijlen, stippen, kruisjes, uitroeptekens en nog veel meer.

Snel formaat:

Selecteer cel, tabel of het hele blad.

Klik op het tabblad Start op Voorwaardelijke opmaak.

Ga naar "Symbolensets" en selecteer de gewenste symbolenset

Zelf opmaakregels maken

Als u in de voorwaardelijke opmaak van Excel geen geschikte optie kunt vinden, kunt u eenvoudig individuele regels voor opmaak maken.

Markeer het celbereik dat voorwaardelijk moet worden opgemaakt.

Ga naar "Voorwaardelijke opmaak" op het tabblad "Start".

Selecteer "Nieuwe regel" en bevestig met "OK".

Als u de gewenste optie voor uw eigen opmaakregel niet kunt vinden, kan een logische formule helpen. U kunt bijvoorbeeld een formule gebruiken om Excel-cellen automatisch te kleuren zodra de inhoud beschikbaar is. Omgekeerd kunnen lege cellen voorwaardelijk worden opgemaakt.

Voorbeeld: Door een formule toe te passen, kunt u gegevens buiten het geselecteerde bereik vergelijken. Dit omvat zelfs gegevens die op een ander werkblad in dezelfde werkmap staan. Of u kunt waarden vergelijken met een resultaat dat door een functie wordt geretourneerd. De formule moet logisch zijn en "true" of "false" retourneren.

Door cellen rechtstreeks op het werkblad te markeren, kunt u celverwijzingen in een formule invoeren. Dit zijn absolute celverwijzingen. Excel kan echter ook de celverwijzingen voor elke cel in het geselecteerde gebied aanpassen. Selecteer desgewenst relatieve celverwijzingen.

Stapsgewijze instructies voor het maken van een regel

1. Ga op het tabblad "Start", de groep "Stijlen", naar de pijl naast "Voorwaardelijke opmaak" en klik op "Regels beheren". Het dialoogvenster Beheer van voorwaardelijke opmaakregels wordt weergegeven.

2. De volgende acties zijn mogelijk:

   • Als u een voorwaardelijke opmaak wilt toevoegen, gaat u naar "Nieuwe regel".
   • Een voorwaardelijke opmaak wijzigen:
   • • Controleer of u op het juiste werkblad staat.
     • Klik eerst in Van toepassing op op Dialoogvenster samenvouwen. Selecteer vervolgens het nieuwe celbereik en ga naar "Dialoogvenster uitvouwen".
     • Selecteer de gewenste regel, klik op "Regel bewerken". "Opmaakregel bewerken" verschijnt.

3. Klik onder "Regeltype selecteren" op "Formule gebruiken om de op te maken cellen te bepalen".

   • Voer onder "Regelbeschrijving bewerken" een formule in bij "Waarden opmaken waarvoor deze regel waar is". De formule begint met (=) en moet "true" (1) of "false" (0) retourneren.
   • Klik op Opmaak om het dialoogvenster Cellen opmaken weer te geven.
   • Selecteer nu het nummer, lettertype, vulformaat enz. dat moet worden gebruikt als de celwaarde waar is en bevestig met "OK". De geselecteerde formaten worden weergegeven in "Preview".

De formule AANTAL.ALS retourneert bijvoorbeeld logische waarden. Als deze formule wordt gebruikt, kunnen dubbele waarden worden bepaald of kan inhoud die in een bepaalde frequentie voorkomt, in kleur worden gemarkeerd. Op deze manier kunnen ook unieke items worden gevonden en in kleur worden gemarkeerd. Andere formules die kunnen worden gebruikt voor voorwaardelijke opmaak zijn:

• REST: dus afwisselende lijnkleuren kunnen automatisch worden ingesteld
• IDENTIEK: Met deze formule kunnen de gegevens die u zoekt, worden gemarkeerd in een Excel-tabel die overeenkomt met een opgegeven waarde
• ISERROR: markeert automatisch lege cellen in de Excel-tabel of laat foutwaarden uit de werkmap verdwijnen
• NOT en ISNUMBER: formatteert alle Excel-cellen die geen getallen bevatten
• MAX: de grootste waarde van de cel wordt gemarkeerd in de tabel
• MIN: maakt de voorwaardelijke opmaak van de kleinste waarde in de Excel-lijst mogelijk, zodat het minimum in één oogopslag kan worden gezien
• LINKS: afhankelijk van een tekenreeks aan het begin, worden Excel-cellen automatisch opgemaakt en krijgen ze een uniforme tekstkleur
• LARGE en LEFT: laat een cel met nieuwe inhoud gekleurd worden
• RECHTS: Cellen kunnen worden opgemaakt op basis van het laatste teken van de tekenreeks
• VANDAAG: Hiermee kunnen afspraken in kleur worden opgemaakt met behulp van de stoplichtmethode of kunnen datumwaarden worden gemarkeerd die binnenkort up-to-date zijn
• MAAND of JAAR: hiermee kunt u de Excel-lijst in maanden of jaren verdelen; Met JAAR kunnen ook datumwaarden van het huidige jaar worden gemarkeerd en kunnen jaarwaarden worden gemarkeerd
• OF en WEEKDAY: markeert waarden volgens de dag van de week in kleur

Voorbeeld van het maken van een regel voor EN- en OF-controles:

Voor dit voorbeeld worden twee waarden gebruikt voor voorwaardelijke opmaak. Als de eerste regel niet van toepassing is, is de tweede regel automatisch van toepassing.

Eerste regel: Een huizenkoper wil controleren of de geplande aankoop van onroerend goed in lijn is met zijn budget. Hij kan maximaal € 80.000 eigen vermogen als aanbetaling inbrengen en maandelijks een aflossing van maximaal € 1.200 aflossen. Als zowel de aanbetaling als de leentermijn aan deze eisen voldoen, zijn de bijbehorende cellen groen gemarkeerd.

Tweede regel: Als de aanbetaling of de termijn niet aan de vereisten voldoet, worden de bijbehorende cellen rood gemarkeerd.

De formule om de eerste regel te maken met de kleur groen is als volgt:

= EN (IF ($ B $ 4 <= 80000,1); IF (ABS ($ B $ 5) <= 1200,1))

De formule om de tweede regel te maken met de kleur rood is als volgt:

= OF (ALS ($ B $ 4 = 1200,1))

Voorwaardelijke opmaak overzetten en verwijderen

Voorwaardelijke opmaak kan worden gekopieerd en toegepast op andere of nieuwe gegevens in het werkblad of weer worden verwijderd.

Voorwaardelijke opmaak overzetten

Om voorwaardelijke opmaak te kopiëren en in andere cellen of celbereiken te plakken, gebruikt u de knop "Opmaak overbrengen".

Selecteer de cellen met de voorwaardelijke opmaak die u wilt kopiëren en plakken in andere cellen en klik erop.

Ga nu naar "Start" en vervolgens naar "Overdrachtsindeling". De cursor verschijnt als een penseel.

U kunt voorwaardelijke opmaak aan een cel of celbereik toevoegen door het penseel over de cel te bewegen.

Met ESC beëindigt u het gebruik van de borstel.

U kunt nog steeds het penseel gebruiken om voorwaardelijke opmaak in te voegen door te dubbelklikken op "Overdrachtsindeling".

Als alternatief kan de voorwaardelijke opmaak worden gekopieerd met behulp van de sneltoetsen en in een andere Excel-cel worden geplakt.

Voorwaardelijke opmaak wissen

Er wordt onderscheid gemaakt tussen een verwijderingsproces op een werkblad en een verwijderingsproces in een reeks cellen.

Wissen op een werkblad of in het hele werkblad

Ga naar "Voorwaardelijke opmaak" op het tabblad "Start".

Klik vervolgens op "regels verwijderen"

Klik vervolgens op "Regels in heel blad verwijderen".

Als alternatief kunt u de voorwaardelijke opmaak verwijderen met behulp van het dialoogvenster "Inhoud selecteren".

Verwijderen in het celbereik

Markeer cellen die de gewenste voorwaardelijke opmaak bevatten.

Klik op de knop "Lezen analyse vergrootglas" (de knop bevindt zich rechtsonder naast de geselecteerde gegevens).

Ga naar "Formaat verwijderen".

Zoek en verwijder dezelfde voorwaardelijke opmaak in het hele werkblad

Klik op de cel met de juiste voorwaardelijke opmaak.

Klik op het tabblad "Start" op de pijl naast "Zoeken en selecteren" en klik vervolgens op "Inhoud selecteren".

Klik op "Voorwaardelijke formaten".

Ga onder "Gegevensverificatie" naar "Zelfde" (hierdoor worden alle cellen met dezelfde voorwaardelijke opmaak geselecteerd).

Ga op het tabblad "Start" naar "Voorwaardelijke opmaak", dan "Regels verwijderen" en "Regels in geselecteerde cellen verwijderen".

Voorwaardelijke opmaak in het draaitabelrapport

Met een draaitabelrapport kunnen gebruikers snel grote hoeveelheden gegevens samenvatten. Celbereik, Excel-spreadsheet of draaitabelrapport - voorwaardelijke opmaak werkt in principe overal hetzelfde. Alleen in het draaitabelrapport zijn er enkele speciale functies waarvan u op de hoogte moet zijn:

• Sommige voorwaardelijke opmaak werken niet voor velden in het waardebereik van een draaitabelrapport.
• Als u de indeling van het draaitabelrapport op enigerlei wijze wijzigt, blijft de voorwaardelijke opmaak behouden totdat de velden in de onderliggende gegevens worden verwijderd.
• Voorwaardelijke opmaak voor velden in het waardenbereik kan gebaseerd zijn op de gegevenshiërarchie. Het bereik van deze voorwaardelijke opmaak wordt bepaald door het eerstvolgende lagere of eerstvolgende hogere niveau van de hiërarchie in rijen voor een of meer kolommen of in kolommen voor een of meer rijen.
• Het bereik van voorwaardelijke opmaak voor velden in het waardebereik kan op drie manieren worden gedefinieerd:

• • selectie
  • Bijbehorend veld
  • Waardeveld

De standaardmethode is selectie.

Gebiedsdefinitie door selectie

Ga naar "Opmaakregel toepassen op".

Klik op het dialoogvenster Nieuwe opmaakregel of op het dialoogvenster Opmaakregel bewerken.

Deze methode is geschikt voor een aaneengesloten reeks velden in het waardebereik, b.v. B. onderzoeksgegevens voor een regio, maar ook voor een niet-aaneengesloten reeks velden in het waardebereik, zoals onderzoeksgegevens voor verschillende regio's op verschillende niveaus van de gegevenshiërarchie.

Gebiedsdefinitie via een bijbehorend veld

Deze methode werkt als je:

• wilt vermijden dat u veel niet-gerelateerde items moet selecteren
• meerdere velden voorwaardelijk wilt opmaken in het waardebereik voor een gegevenshiërarchieniveau
• Wil je gedeeltelijke resultaten uitsluiten

Gebiedsdefinitie via een waardeveld

Deze methode werkt als je:

• wilt vermijden dat u veel niet-gerelateerde items moet selecteren
• meerdere velden voorwaardelijk wilt opmaken in het waardebereik voor alle hiërarchieniveaus
• Totalen en gedeeltelijke resultaten wilt opnemen.

Conclusie: Voorwaardelijke opmaak in Excel maakt het gemakkelijker om gegevens bij te houden

Als je bepaalde gegevenswaarden in je Excel-tabel in kleur wilt uitlichten, is de voorwaardelijke opmaak in Excel ideaal. U hebt twee opties om de voorwaardelijke opmaak toe te passen. Of u gebruikt de regels van Excel om tekstinhoud, numerieke waarden of datums in kleur te markeren. Of u kunt zelf een regel definiëren met verschillende voorwaarden waaronder de relevante waarden gemarkeerd moeten worden. U kunt gegevens eenvoudig en effectief met elkaar vergelijken met behulp van gegevensbalken, kleurenbalken en symbolensets. Als de ontwerpopties niet langer nodig zijn, kunt u ze met een paar klikken verwijderen.

Meer artikelen over voorwaardelijke opmaak

• Automatisch pijlen genereren in Excel-lijsten
• Negatieve tijdwaarden weergeven in Excel-tabellen
• Kleur negatieve percentages rood
• Herken negatieve percentages in één oogopslag
• Getallen opmaken met een aangepast formaat in kleur
• Excel-cellen opmaken die niet de opgegeven waarde hebben
• Automatisch cellen opslaan met de bijbehorende valutanotaties
• Markeer cellen in kleur met een macrofunctie
• Gebruik voorwaardelijke opmaak en markeer gegevens die groter zijn dan of gelijk zijn aan de op twee na grootste cel
• Omgekeerde waarheidswaarden