Voorwaardelijke opmaak in MS Excel (27/32)

Wat is voorwaardelijke opmaak?

Met voorwaardelijke opmaak laat je de opmaak van een cel (denk aan de achtergrondkleur, de tekstkleur, randen etc.) automatisch afhangen van de inhoud van de cel. Je geeft een cel dus niet manueel een opmaak, maar dat gaat automatisch, op basis van de inhoud van je cellen. “Opmaak” moet je ruim bekijken. Het gaat over meer dan kleuren. Je kan met voorwaardelijke opmaak bijv. ook kleine icoontjes toevoegen op basis van de celinhoud. Bijv: een groen licht bij een waarde onder 10, een rood stoplicht bij een waarde van 10 of meer.

Je stelt vooraf wat we noemen opmaakregels in die bepalen hoe de opmaak er moet uitzien, in welke gevallen. Verandert de inhoud van de cel? Dan zal ook de opmaak automatisch veranderen. Dat woordje automatisch is belangrijk. Je gaat de opmaak niet zelf toepassen, maar je laat die echt afhangen van de inhoud van cellen.

Hieronder zie je een voorbeeld: de kleur van de cellen verandert automatisch mee met de inhoud, van rood tot groen. Er verschijnen in de omzetkolom dankzij de toegevoegde voorwaardelijke opmaak ook vlaggetjes die aangeven of het een lage, medium of hoge waarde is.

Je ziet meteen hoe voorwaardelijke opmaak handig kan zijn wanneer je data snel wil analyseren: je data spreken veel meer en de analyse gaat sneller.

Hoe voeg je voorwaarde opmaak toe?

Alles begint met een goede selectie. Dat is echt héél belangrijk. Je selecteert eerst alles wat eventueel ooit van opmaak moet veranderen. Dus alle cellen die ooit eventueel van opmaak moeten veranderen.

Dan klik je op de tab start op de knop voorwaardelijke opmaak in de groep stijlen. Kies een van de voorgestelde opmaken en klik door.

De opmaak werd nu toegevoegd.

Hoe zie je aan welke cellen voorwaardelijke opmaak werd toegevoegd?

Je ziet niet goed het verschil tussen een cel die opgemaakt werd met direct opmaak (“deze cel moet rood zijn”) of met voorwaardelijke opmaak (“kleur alle cellen met een waarde groter dan 10 rood”)

Gelukkig kan je alle cellen die voorwaardelijke opmaak hebben selecteren. Klik daarvoor op de tab start in de groep bewerken op de knop zoeken en selecteren en kies voor voorwaardelijke opmaak.

Je ziet nu waar eerder voorwaardelijke opmaak werd toegevoegd.

Hoe wijzig je de voorwaardelijke opmaakregels?

De opmaak wordt ingesteld met zogenaamde regels. Dat zijn uitspraken die als ze waar zijn de voorwaardelijke opmaak aan de cel geven.

Heb je eerder voorwaardelijke opmaak ingesteld? Dan kan je die regels tonen. Het allerbelangrijkste is dat je eerst alle cellen selecteert waaraan de voorwaardelijke opmaak werd toegevoegd. Dus alles wat eventueel van opmaak kan veranderen.

Vervolgens klik je op de tab start op de knop voorwaardelijke opmaak in de groep stijlen. Klik onderaan op regels beheren.

In het venster dat verschijnt zie je de opmaakregels staan die jouw geselecteerd bereik voorwaardelijk opmaken. Door op een regel te dubbelklikken kan je deze wijzigen.

Ik dubbelklik bijv. op de kleurenschaal. Vervolgens zie je de details van deze opmaak.

Ik verander de kleurschaal van een 3-kleurenschaal in een 2-kleurenschaal en pas ook de kleuren aan. Als je dan op OK klikt wordt de wijziging toegepast.

Hoe verwijder je eerder ingestelde voorwaardelijke opmaak?

Heb je voorwaardelijke opmaak ingesteld en wil je deze verwijderen? Dan volg je alle stapjes zoals hierboven omschreven voor het wijzigen van voorwaardelijke opmaak. In plaats van te dubbelklikken op een opmaakregel, klik je echter bovenaan op de knop regel verwijderen. Klik daarna OK om het venster te sluiten

Vaak gebruikte types voorwaardelijke opmaak

Wanneer je voorwaardelijke opmaak toevoegt, kan je ofwel kiezen voor een kant en klare opmaak, of alles instellen.

Enkele kant en klaar beschikbare opmaken:

• Markeringsregels voor cellen: kies een opmaak en wijs die simpel toe aan bepaalde cellen. Maak bijv. alle cellen groter dan 10 rood, markeer lege cellen of duid die cellen aan waar een stukje tekst in zit.

• Bovenste of onderste in een reeks aanduiden: geef bijv. de laagste 10% van de waarden een opmaak, of de 2 laagste waarden.

• Gegevensbalk: kleur een deeltje van de cel. De cel loopt zo een stukje vol (meestal van links naar rechts)

• Kleurenschaal: definieer vaste kleuren voor bepaalde (bereiken van) waarden. Kleur bijv. waarden onder 10 geel, tussen 10 en 20 groen en vanaf 20 rood.

• Pictogrammen: toon pictogrammen in de cel op basis van de waarde. Bijvoorbeeld sterretjes, of pijltjes.

Alle mogelijkheden van voorwaardelijke opmaak benutten met een eigen nieuwe regel

In veel gevallen volstaan de standaard opmaakregels die hierboven aan bod kwamen. Maar soms wil je meer. Soms wil je echt in detail alles instellen.

Dat doe je met het venster nieuwe opmaakregel. Dat vind je op de tab start, en dan nieuwe regel bij voorwaardelijke opmaak in de groep stijlen.

Bij opmaakstijl (wat je in het screenshot hierboven ongeveer in het midden ziet) kan je bijvoorbeeld kiezen voor kleurenschalen, pictogrammen en gegevensbalken.

Bovenaan kan je veel andere types regels kiezen. Hieronder demonstreer ik hoe je de achtergrond van die cellen die groter zijn dan 10 000 geel kan maken.

Formule gebruiken om voorwaardelijke opmaak in te stellen op basis van waarden in andere cellen

Dit is een veelgevraagde functie: hoe kan je de opmaak van een cel laten afhangen van de waarde in een andere cel? Want standaard wordt een cel (voorwaardelijk) opgemaakt op basis van de eigen waarde. In de voorbeelden hierboven hing de opmaak altijd af van de eigen waarde.

Dit is nu iets anders.

Hoe kan je bijvoorbeeld de hele rij opmaken op basis van de waarde in kolom B? Laat ons zeggen dat de rij van een verkoper met meer dan 15.000 euro omzet automatisch volledig geel moet worden.

Daarvoor moet je een nieuwe regel maken met daarin een formule. Dat is dus de onderste optie in het venster voor een nieuwe opmaakregel.

Laat ons dat eens doen.

Ik toon het eerst, daarna leg ik uit wat ik doe.

Gezien? Hieronder doe ik het in stukjes, met wat uitleg.

Eerst moeten we natuurlijk weer selecteren. Op welk bereik willen we de voorwaardelijke opmaak toepassen? Dat is net als altijd alles wat eventueel opgemaakt moet worden. In ons voorbeeld is dat het bereik A2:C6.

Klik daarna op de knop voorwaardelijke opmaak en kies voor nieuwe regel. Kies voor een formule gebruiken om te bepalen welke cellen worden opgemaakt.

Bedoeling is dat je nu een logische formule schrijft, dus met een uitspraak die waar of onwaar kan zijn. Als de uitspraak waar is, wordt de voorwaardelijke opmaak toegepast. (Wil je meer weten over logische uitspraken in Microsoft Excel? Bekijk dan zeker het deel over de ALS-functies)

Je vraagt je nu misschien af hoe je die uitspraak in de formule moet formuleren vermits je de voorwaardelijke opmaak wil toepassen op je hele geselecteerde bereik. Wel; je doet alsof je een formule schrijft specifiek voor de cel die het meeste links en het meeste bovenaan staat in je bereik. We maken in ons voorbeeld dus een formule voor de cel waarin nu de inhoud “Jan” zit.

Wanneer moet de cel A2 (Jan) geel zijn? Als B2 (de omzet) > 15.000.

De formule wordt dus:

=B2>15000

We moeten bij formules voor voorwaardelijke opmaak echter altijd ook nadenken over de dollartekens.

Microsoft Excel zal immers elke cel in het bereik evalueren ten opzichte van je formule. Als je geen dollartekens typt, is de formule relatief waardoor de celverwijzingen mee opschuiven.

We moeten dus nadenken over de celadressering van B2. Moet de B absoluut zijn met een dollarteken? Moet de 2 absoluut zijn met een dollarteken?

• Kolom B moet absoluut zijn. De kleur van elke cel op elke rij hangt altijd af van de waarde van de cel in kolom B. De omzet zit immers in B.
• Rij 2 mag niet absoluut zijn. De kleur van een cel op rij 3 hangt niet af van de waarde op rij 2. Die 2 moet dus een 3 kunnen worden als je naar beneden doorvoert.

De formule ziet er dus zo uit:

=$B2>15000

Snap je het?

Bij opmaak kies je vervolgens de opmaak (gele achtergrond) en daarna moet je nog een paar keer op OK klikken.