Vier coole manieren om conditional formatting in Excel te gebruiken

IT & Projectmanagement

Conditional formatting (voorwaardelijke opmaak) biedt een waaier aan mogelijkheden. De voorwaardelijke opmaak helpt je om belangrijke data in een oogopslag te visualiseren en je gegevens gemakkelijker leesbaar te maken. Het is één van de krachtigste én belangrijkste functionaliteiten in Excel. Wij zetten enkele mogelijkheden op een rij.

1. Markeer lege cellen in minder dan 10 seconden

Wat?

Als je een bestand ontvangt of downloadt uit een database, is het soms noodzakelijk om enkele basiscontroles uit te voeren op de gegevens. Een van deze controles is het vinden en markeren van lege cellen. Er zijn immers veel redenen die lege cellen veroorzaken in een dataset:

  • Gegevens zijn niet beschikbaar
  • Een formule resulteert in een lege cel

Als je een grote dataset met honderden rijen en kolommen hebt, zou het handmatig controleren hiervan inefficiënt en foutgevoelig zijn.

Hoe?

Er bestaan 2 manieren om lege cellen te markeren:

  • Conditional formatting (dynamisch)
  • Go to (statisch)

Conditional formatting

 

Stap voor stap

  • Selecteer de gegevens
  • Ga in het lint naar het tabblad ‘Home’, kies daar voor ‘Conditional Formatting’ en in de dropdownlijst kies je voor ‘New Rule’
  • Selecteer in het dialoogvenster de 2e optie ‘Format only cells that contain’.
  • Selecteer ‘Blanks’ in de vervolgkeuzelijst
  • Kies bij ‘Format’ de opmaak of kleur die je wil gebruiken om de blanco cellen te markeren. In ons voorbeeld hebben we voor groen gekozen.
  • Klik op OK. Dit zal alle lege cellen in de dataset in groen markeren.

Weet wel dat voorwaardelijke opmaak dynamisch is. Dit betekent dat als je een cel leegmaakt, die cel automatisch wordt gemarkeerd.

Go to

 

Stap voor stap

  • Selecteer de gegevens.
  • Druk op de F5-toets. Hiermee wordt het dialoogvenster ‘Go To’ geopend.
  • Klik in het dialoogvenster op de knop ‘Special’.
  • Selecteer Blanks in het dialoogvenster ‘Go to special’.
  • Klik OK. Hiermee worden alle lege cellen in de geselecteerde lijst geselecteerd.
  • Als alle lege cellen zijn geselecteerd, markeer je deze door een celkleur te geven via de ‘Fill’ optie bij Font.

Zoals vermeld, is deze methode handig als je snel alle lege cellen wil selecteren en markeren. Je kan dezelfde stappen ook gebruiken om alle lege cellen te selecteren en vervolgens 0 of NA of andere relevante tekst erin te vullen.

Merk op dat deze methode in tegenstelling tot voorwaardelijke opmaak niet dynamisch is. Als je dit eenmaal doet en vervolgens per ongeluk een gegevenspunt verwijdert, wordt de cel niet gemarkeerd.

 

2. Negatieve getallen in rood markeren

Wat?

Als je in Excel veel getallen gebruikt, is het een goede gewoonte om negatieve getallen in rood op te lichten. Dit maakt het gemakkelijker om de gegevens te lezen. Er zijn verschillende technieken die je kan gebruiken om negatieve getallen in rood in Excel te markeren:

  • Voorwaardelijke opmaak gebruiken
  • De standaard cijferopmaak (Number format) gebruiken
  • Aangepaste cijferopmaak (Number format) gebruiken

Wij geven hier de voorkeur om de eerste techniek verder toe te lichten.

Hoe?

 

Stap voor stap

  • Selecteer de cellen waarin je de negatieve getallen in rood wil markeren.
  • Ga in het lint naar het tabblad ‘Home’, kies daar voor ‘Conditional Formatting’ en in de dropdownlijst kies je voor ‘Highlight Cells Rules’ en daar ‘Less Than’.
  • Geef in het dialoogvenster de waarde op waaronder de opmaak moet worden toegepast. Als je andere opmaak wil gebruiken dan die wordt voorgesteld (rode letters, rode schaduw), kies je een andere optie in de dropdownlijst.
  • Klik OK. Alle cellen met een waarde kleiner dan 0 worden gemarkeerd in lichtrode kleur met donkerrode tekst erin.

3. Hoe een vinkje ✓ symbool toevoegen in Excel

Wat?

Je kan voorwaardelijke opmaak gebruiken om een ​​vinkje of kruisje in te voegen op basis van de celwaarde. Stel dat je een lijstje hebt zoals in ons voorbeeld wordt gebruikt en dat je een vinkje wil invoegen als de omzet groter is dan 50 000€ en een kruisje als het minder dan 50 000€ is.

Hoe?

Stap voor stap

  • Voer in cel C2 = B2 in en kopieer vervolgens deze formule door naar de rest van de cellen. Dit zorgt ervoor dat je nu dezelfde waarde in de aangrenzende cel hebt. Als je de waarde in kolom B wijzigt, wordt deze automatisch gewijzigd in kolom C.
  • Selecteer alle cellen in kolom C (waarin je het vinkje wil invoegen).
  • Ga naar het tabblad ‘Home’, kies daar voor ‘Conditional Formatting’ en in de dropdownlijst kies je voor ‘New Rule’.
  • Klik in het dialoogvenster ‘New Formatting Rule’ op het dropdown-menu ‘Format Style’ en klik op ‘Icon Sets’.
  • Selecteer in de vervolgkeuzelijst ‘Icon Style’ de stijl met het vinkje en kruisteken
  • Vink het selectievakje ‘Show Icon only’ aan. Dit zorgt ervoor dat alleen de symbolen zichtbaar zijn en dat de cijfers verborgen zijn.
  • Verander bij type het ‘percentage’ in ‘Number’ en maak de instellingen zoals hieronder nogmaals getoond.
  • Klik OK.

De bovenstaande stappen zullen een groen vinkje invoegen wanneer de waarde groter is dan of gelijk is aan 50 000 en een rood kruis wanneer de waarde lager is dan 50 000.

In dit geval heb ik alleen deze twee pictogrammen gebruikt, maar je kan ook het gele uitroepteken gebruiken als je dat wil.

 

4. Nulwaarden verbergen

Wat?

Hiervoor wordt conditional formatting het vaakst gebruikt. Nochtans bestaan er ook alternatieven voor (zoals de Excel-instellingen wijzigen zodat alle nulwaarden worden verborgen). Maar het aanpassen in de instellingen heeft vaak nadelen:

  • Als je het bestand naar iemand anders stuurt die zijn/haar Excel-instellingen niet heeft gewijzigd, zien deze de nulwaarden.
  • Je kan niet beslissen of je alleen de nullen in bepaalde delen van je werkmap of spreadsheets wil verbergen. Het is alles of niets.

Gelukkig bestaat er dus een simpel alternatief via de conditional formatting.

Hoe?

Stap voor stap

  • Selecteer het bereik waarin je de nulwaarden wil verbergen.
  • Ga in het lint naar het tabblad ‘Home’, kies daar voor ‘Conditional Formatting’ en in de picklist kies je voor ‘Equal To’.
  • In het dialoogvenster zet je 0 bij de voorwaarden en in de dropdownlijst kies je voor ‘Custom Format’.
  • Bij het dialoogvenster van ‘Format Cells’ ga je naar het tabblad ‘Font’ en kies je de kleur wit.
  • Nu verdwijnen alle nullen uit de lijst, omdat de lettertypekleur van alle nullen eigenlijk in wit staan. Je kan nu probleemloos berekeningen en bewerkingen doen met de lijst, zonder dat de nulwaarden worden getoond.

 

Bron: https://trumpexcel.com/

Relevante opleidingen

Functionele analyse: opleiding

Vlot werkende software en applicaties zijn onmisbaar in je business. Je wil dan ook – als functioneel analist (in spe) – garanderen dat nieuwe softwareapplicaties de juiste functionaliteiten omvatten. Je basis? Deze opleiding 'Functionele analyse'. De opleiding begeleidt je stap voor stap door het analyseproces.
Bekijk deze opleiding Bekijk de opleiding "Functionele analyse: opleiding"

Opleiding: Projectmanagement van a tot z (volledige reeks)

Ben je regelmatig betrokken bij het opstarten en uitwerken van nieuwe projecten en wil je steviger in je schoenen staan en met meer kennis van zaken werken? Dan is er de 6-daagse opleiding 'Projectmanagement van a tot z': deze bestaat uit 4 modules en biedt je een goede basis op verschillende vlakken, van planning tot budgetten, van organisatie tot communicatie, enz.
Bekijk deze opleiding Bekijk de opleiding "Opleiding: Projectmanagement van a tot z (volledige reeks)"