Bekijk video - Lege cellen markeren in Excel
Als ik een gegevensbestand van een klant/collega krijg of het uit een database download, doe ik een aantal basiscontroles van de gegevens. Ik doe dit om ervoor te zorgen dat er geen ontbrekende gegevenspunten, fouten of duplicaten zijn die later tot problemen kunnen leiden.
Een dergelijke controle is om lege cellen in Excel te vinden en te markeren.
Er zijn veel redenen die kunnen leiden tot lege cellen in een gegevensset:
- De gegevens zijn niet beschikbaar.
- Gegevenspunten zijn per ongeluk verwijderd.
- Een formule retourneert een lege tekenreeks die resulteert in een lege cel.
Hoewel het gemakkelijk is om deze lege cellen in een kleine dataset te herkennen, als je een enorme hebt met honderden rijen en kolommen, zou het zeer inefficiënt en foutgevoelig zijn om dit handmatig te doen.
In deze zelfstudie laat ik u verschillende manieren zien om lege cellen in Excel te vinden en te markeren.
Markeer lege cellen met voorwaardelijke opmaak
Voorwaardelijke opmaak is een geweldige manier om cellen te markeren op basis van hun waarde wanneer aan de gegeven voorwaarde is voldaan.
Ik ga deze voorbeelden presenteren met een kleine dataset. U kunt dezelfde technieken echter ook gebruiken met grote datasets.
Stel dat u een dataset heeft zoals hieronder weergegeven:
U kunt zien dat er lege cellen in deze dataset zijn.
Hier zijn de stappen om lege cellen in Excel te markeren (met voorwaardelijke opmaak):
- Selecteer de gegevens.
- Ga naar het tabblad Start.
- Klik in de vervolgkeuzelijst Voorwaardelijke opmaak op Nieuwe regel.
- Selecteer in het dialoogvenster 'Nieuwe opmaakregels' de optie 'Alleen cellen opmaken die bevatten'.
- Selecteer 'Blanken' in de vervolgkeuzelijst (zoals hieronder weergegeven):
- Geef de opmaak op (waarin u spaties wilt markeren).
- Klik OK.
Hierdoor worden alle lege cellen in de gegevensset gemarkeerd.
Houd er rekening mee dat voorwaardelijke opmaak dynamisch is. Dit betekent dat als voorwaardelijke opmaak wordt toegepast en u een gegevenspunt verwijdert, die cel automatisch wordt gemarkeerd.
Tegelijkertijd brengt dit dynamische gedrag overheadkosten met zich mee. Voorwaardelijke opmaak is vluchtig en kan uw werkmap vertragen als u het op grote gegevenssets gebruikt.
Selecteer en markeer lege cellen in Excel
Als u snel lege cellen wilt selecteren en markeren, kunt u de techniek 'Ga naar speciaal' gebruiken.
Dit zijn de stappen om lege cellen in Excel te selecteren en te markeren:
- Selecteer de gegevens.
- Druk op de F5-toets. Het opent het dialoogvenster Ga naar.
- Klik in het dialoogvenster Ga naar op de knop Speciaal.
- Selecteer Spaties in het dialoogvenster Ga naar speciaal.
- Klik OK. Hiermee worden alle lege cellen in de gegevensset geselecteerd.
- Met alle lege cellen geselecteerd, markeert u deze door er een celkleur aan te geven.
Zoals eerder vermeld, is deze methode handig als u snel alle lege cellen wilt selecteren en markeren. U kunt dezelfde stappen ook gebruiken om alle lege cellen te selecteren en vervolgens 0 of NA of een andere relevante tekst erin in te vullen.
Merk op dat deze methode, in tegenstelling tot voorwaardelijke opmaak, niet dynamisch is. Als u het één keer doet en vervolgens per ongeluk een gegevenspunt verwijdert, wordt het niet gemarkeerd.
VBA gebruiken om lege cellen in Excel te markeren
U kunt ook een korte VBA-code gebruiken om lege cellen in een geselecteerde gegevensset te markeren.
Deze methode is meer geschikt wanneer u vaak lege cellen in gegevenssets moet zoeken en markeren. U kunt de onderstaande code eenvoudig gebruiken en een invoegtoepassing maken of deze opslaan in uw persoonlijke macrowerkmap.
Hier is de VBA-code die lege cellen in de geselecteerde dataset markeert:
'Code door Sumit Bansal (https://trumpexcel.com) Sub HighlightBlankCells() Dim Dataset As Range Set Dataset = Selectie Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
Hier zijn de stappen om deze VBA-code in de backend te plaatsen en deze vervolgens te gebruiken om lege cellen in Excel te markeren:
- Ga naar het tabblad Ontwikkelaars en klik op Visual Basic (of druk op ALT + F11).
- Klik in de Vb-editor in de Projectverkenner met de rechtermuisknop op een van de bladnamen (als u Projectverkenner niet ziet, drukt u op CONTROL + R).
- Ga naar Invoegen en klik op Module.
- Kopieer en plak de VBA-code in het venster Modulecode.
- Sluit de VB-editor.
Hoe de VBA-code (macro) uit te voeren?
Nadat u deze macro hebt gekopieerd en geplakt, zijn er een aantal manieren waarop u deze macro kunt gebruiken.
Het dialoogvenster Macro gebruiken
Hier zijn de stappen om deze macro uit te voeren met behulp van het dialoogvenster Macro:
- Selecteer de gegevens.
- Ga naar het tabblad Ontwikkelaars en klik op Macro's.
- Selecteer in het dialoogvenster Macro de macro 'HighlightBlankCells' en klik op Uitvoeren.
De VB-editor gebruiken
Hier zijn de stappen om deze macro uit te voeren met behulp van de VB Editor:
- Selecteer de gegevens.
- Ga naar het tabblad Ontwikkelaars en klik op Visual Basic.
- Klik in de VB Editor ergens in de code.
- Klik op de groene driehoek-knop in de werkbalk (of druk op de F5-toets).
Zoals ik al zei, is het gebruik van VBA-macro om lege cellen te markeren de beste keuze als je dit vaak moet doen. Afgezien van de hierboven getoonde manieren om de macro uit te voeren, kunt u ook een invoegtoepassing maken of de code opslaan in de persoonlijke macrowerkmap. Hiermee hebt u toegang tot deze code vanuit elke werkmap in uw systeem.
