Verberg nulwaarden in Excel - Cellen leeg maken als de waarde 0 . is

Bekijk video - Nulwaarden verbergen in Excel + nulwaarden verwijderen

Als u liever leest dan een video bekijkt, vindt u hieronder de volledige schriftelijke tutorial.

Soms wil je in Excel misschien: verberg nulwaarden in uw dataset en toon deze cellen als blanco's.

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt de waarde 0 in al deze cellen verbergen (of deze wilt vervangen door bijvoorbeeld een streepje of de tekst 'Niet beschikbaar').

Hoewel je dit handmatig kunt doen met een kleine dataset, heb je betere manieren nodig als je met grote datasets werkt.

In deze zelfstudie laat ik u meerdere manieren zien om nulwaarden in Excel te verbergen en één methode om alle nulwaarden uit de gegevensset te selecteren en te verwijderen.

Belangrijke notitie: wanneer u een 0 in een cel verbergt met behulp van de methoden die in deze zelfstudie worden getoond, wordt alleen de 0 verborgen en niet verwijderd. Hoewel de cellen er leeg uitzien, bevatten die cellen nog steeds de nullen. En als u deze cellen (met verborgen nullen) in een formule gebruikt, worden deze in de formule gebruikt.

Laten we aan de slag gaan en in elk van deze methoden duiken!

Nulwaarde automatisch verbergen in cellen

Excel heeft een ingebouwde functionaliteit waarmee u automatisch alle nulwaarden voor het hele werkblad kunt verbergen.

Het enige dat u hoeft te doen, is een vakje in de Excel-opties uitschakelen en de wijziging wordt toegepast op het hele werkblad.

Stel dat u de verkoopgegevensset hebt zoals hieronder weergegeven en dat u alle nulwaarden wilt verbergen en in plaats daarvan lege cellen wilt weergeven.

Hieronder vindt u de stappen om nullen te verbergen voor alle cellen in een werkmap in Excel:

1. Open de werkmap waarin u alle nullen wilt verbergen
2. Klik op het tabblad Bestand
3. Klik op Opties
4. Klik in het dialoogvenster Excel-opties dat wordt geopend op de optie 'Geavanceerd' in het linkerdeelvenster
5. Scrol omlaag naar het gedeelte met de tekst 'Weergaveoptie voor dit werkblad' en selecteer het werkblad waarin u de nullen wilt verbergen.
6. Schakel de optie 'Toon een nul in cellen met een nulwaarde' uit
7. Klik OK

De bovenstaande stappen zouden nullen in alle cellen in het geselecteerde werkblad onmiddellijk verbergen.

Deze wijziging wordt ook toegepast op cellen waar nul het resultaat is van een formule.

Onthoud dat deze methode alleen de waarde 0 in de cellen verbergt en deze niet verwijdert. De 0 is er nog steeds, hij is alleen verborgen.

Dit is de beste en snelste methode om nulwaarden in Excel te verbergen. Maar dit is alleen handig als u nulwaarden in het hele werkblad wilt verbergen. Als u wilt dat dit alleen nulwaarden in een specifiek cellenbereik verbergt, is het beter om andere methoden te gebruiken die in deze zelfstudie worden behandeld.

Verberg nulwaarde in cellen met voorwaardelijke opmaak

Hoewel de bovenstaande methode de handigste is, kunt u nullen niet alleen in een specifiek bereik verbergen (het verbergt eerder nulwaarden in het hele werkblad).

Als u alleen nullen voor een specifieke dataset (of meerdere datasets) wilt verbergen, kunt u voorwaardelijke opmaak gebruiken.

Met voorwaardelijke opmaak kunt u een specifieke opmaak toepassen op basis van de waarde in de cellen. Dus als de waarde in sommige cellen 0 is, kunt u eenvoudig voorwaardelijke opmaak programmeren om deze te verbergen (of zelfs markeren als u dat wilt).

Stel je hebt een dataset zoals hieronder weergegeven en je wilt nullen in de cellen verbergen.

Hieronder vindt u de stappen om nullen in Excel te verbergen met behulp van voorwaardelijke opmaak:

1. Selecteer de dataset
2. Klik op het tabblad 'Home'
3. Klik in de groep 'Stijlen' op 'Voorwaardelijke opmaak'.
4. Plaats de cursor op de opties voor 'Markeer cellenregels' en klik in de opties die verschijnen op de optie 'Gelijk aan'.
5. Voer in het dialoogvenster 'Gelijk aan' '0' in het linkerveld in.
6. Selecteer de vervolgkeuzelijst Formaat en klik op 'Aangepast formaat'.
7. Selecteer in het dialoogvenster 'Cellen opmaken' het tabblad 'Lettertype'.
8. Kies in de vervolgkeuzelijst Kleur de witte kleur.
9. Klik OK.
10. Klik OK.

De bovenstaande stappen veranderen de letterkleur van de cellen met de nullen in wit, waardoor deze cellen er blanco uitzien.

Deze tijdelijke oplossing werkt alleen als uw werkblad een witte achtergrond heeft. Als er een andere achtergrondkleur in de cellen zit, kan de witte kleur de nullen nog steeds zichtbaar houden.

Als u de nullen in de cellen echt wilt verbergen, waar de cellen er leeg uitzien, ongeacht de achtergrondkleur die aan de cellen wordt gegeven, kunt u de onderstaande stappen gebruiken.

1. Selecteer de dataset
2. Klik op het tabblad 'Home'
3. Klik in de groep ‘Stijlen’ op ‘Voorwaardelijke opmaak’.
4. Plaats de cursor op de opties voor 'Markeer cellenregels' en klik in de opties die verschijnen op de optie 'Gelijk aan'.
5. Voer in het dialoogvenster 'Gelijk aan' 0 in het linkerveld in.
6. Selecteer de vervolgkeuzelijst Formaat en klik op Aangepast formaat.
7. Selecteer in het dialoogvenster 'Cellen opmaken' het tabblad 'Nummer' en klik op de optie 'Aangepast' in het linkerdeelvenster.
8. Voer in de optie Aangepast formaat het volgende in het veld Type in: ;;; (drie puntkomma's zonder spatie)
9. Klik OK.
10. Klik OK.

De bovenstaande stappen wijzigen de aangepaste opmaak van cellen met de waarde 0.

Wanneer u drie puntkomma's als indeling gebruikt, verbergt het alles in de cel (of het nu numeriek of tekst is). En aangezien dit formaat alleen wordt toegepast op die cellen die de waarde 0 hebben, verbergt het alleen de nullen en niet de rest van de dataset.

U kunt deze methode ook gebruiken om deze cellen in een andere kleur te markeren (zoals rood of geel). Om dit te doen, voegt u gewoon nog een stap toe (na stap 8) waar u het tabblad Vullen moet selecteren in het dialoogvenster 'Cellen opmaken' en vervolgens de kleur selecteert waarin u de cellen wilt markeren.

Beide hierboven beschreven methoden werken ook wanneer 0 het resultaat is van een formule in een cel.

Opmerking: beide methoden die in deze sectie worden behandeld, verbergen alleen de nulwaarden. Het verwijdert deze nullen niet. Dit betekent dat als u deze cellen in berekeningen gebruikt, alle nullen ook worden gebruikt.

Nulwaarde verbergen met aangepaste opmaak

Een andere manier om nulwaarden uit een gegevensset te verbergen, is door een aangepaste indeling te maken die de waarde verbergt in cellen met nullen, terwijl elke andere waarde wordt weergegeven zoals verwacht.

Hieronder staan ​​de stappen om een ​​aangepast formaat te gebruiken om nullen in cellen te verbergen:

1. Selecteer de gehele dataset waarvoor u dit formaat wilt toepassen. Als je dit voor het hele werkblad wilt, selecteer dan alle cellen
2. Klik met de cellen geselecteerd op het tabblad Start
3. Klik in de groep Cellen op de optie Opmaak
4. Klik in de vervolgkeuzelijst op Cellen opmaken. Dit opent het dialoogvenster Cellen opmaken
5. Selecteer in het dialoogvenster Cellen opmaken het tabblad Getal
6. Klik in de optie in het linkerdeelvenster op de optie Aangepast.
7. Voer in de opties aan de rechterkant het volgende formaat in het veld Type in: 0;-0;;@
8. Klik OK

De bovenstaande stappen zouden een aangepast formaat toepassen op de geselecteerde cellen, waarbij alleen die cellen met de waarde 0 worden opgemaakt om de waarde te verbergen, terwijl de rest van de cellen de gegevens weergeven zoals verwacht.

Hoe werkt dit aangepaste formaat?

In Excel zijn er vier soorten gegevensindelingen die u opgeeft:

1. Positieve getallen
2. Negatieve getallen
3. nullen
4. Tekst

En voor elk van deze gegevenstypen kunt u een indeling opgeven.

In de meeste gevallen is er een standaardindeling - Algemeen, die al deze gegevenstypen zichtbaar houdt. Maar u hebt de flexibiliteit om voor elk van deze gegevenstypen een specifiek formaat te maken.

Hieronder vindt u de aangepaste indelingsstructuur die u moet volgen voor het geval u een aangepaste indeling toepast op elk van deze gegevenstypen:

;;;

In de hierboven beschreven methode heb ik het formaat gespecificeerd om:

0;-0;;@

Het bovenstaande formaat doet het volgende:

1. Positieve getallen worden weergegeven zoals ze zijn
2. Negatieve getallen worden weergegeven met een minteken
3. Nullen zijn verborgen (omdat er geen formaat is opgegeven)
4. Tekst wordt weergegeven zoals deze is

Pro-tip: Als u wilt dat negatieve getallen rood worden weergegeven in uw dataset, gebruikt u de volgende aangepaste indeling: 0;[Rood]0;0;@

Vervang nullen door streepjes of een specifieke tekst

In sommige gevallen wilt u misschien niet alleen de nullen in de cellen verbergen, maar deze vervangen door iets meer betekenis.

U wilt bijvoorbeeld de nullen verwijderen en deze vervangen door een streepje of een tekst zoals "Niet beschikbaar" of "Gegevens moeten nog komen"

Dit wordt opnieuw mogelijk gemaakt met behulp van aangepaste getalnotatie, waarbij u kunt specificeren wat een cel moet weergeven in plaats van een nul.

Hieronder vindt u de stappen om een ​​0 naar een streepje in Excel te converteren:

1. Selecteer de gehele dataset waarvoor u dit formaat wilt toepassen. Als je dit voor het hele werkblad wilt, selecteer dan alle cellen
2. Klik met de cellen geselecteerd op het tabblad Start
3. Klik in de groep Cellen op de optie Opmaak
4. Klik in de vervolgkeuzelijst op Cellen opmaken. Dit opent het dialoogvenster Cellen opmaken
5. Selecteer in het dialoogvenster Cellen opmaken het tabblad Getal
6. Klik in de optie in het linkerdeelvenster op de optie Aangepast.
7. Voer in de opties aan de rechterkant het volgende formaat in het veld Type in: 0;-0;-;@
8. Klik OK

De bovenstaande stappen zouden alle andere cellen behouden zoals ze zijn en de cellen met de waarde 0 veranderen om een ​​streepje (-) weer te geven.

Opmerking: In dit voorbeeld heb ik ervoor gekozen om de nul te vervangen door een streepje, maar je kunt ook elke willekeurige tekst gebruiken. Als u bijvoorbeeld de NA in de cellen wilt weergeven, kunt u de volgende indeling gebruiken: 0;-0;”NA”;@Pro-tip: U kunt ook enkele tekstkleuren opgeven in aangepaste getalnotatie. Als u bijvoorbeeld wilt dat de cellen met nullen de tekst NA in rode kleur weergeven, kunt u de volgende indeling gebruiken: 0;-0;[Rood]”NA”;@

Nulwaarden verbergen in draaitabellen

Er kunnen twee scenario's zijn waarin een draaitabel de waarde als 0 weergeeft:

1. De brongegevenscellen die zijn samengevat in de draaitabel hebben 0 waarden
2. De brongegevenscellen die zijn samengevat in de draaitabel zijn blanco en de draaitabel is zo opgemaakt dat de lege cellen als nul worden weergegeven

Laten we eens kijken hoe we de nullen in de draaitabel in elk scenario kunnen verbergen:

Wanneer Brongegevenscellen 0s hebben

Hieronder ziet u een voorbeeldgegevensset die ik heb gebruikt om een ​​draaitabel te maken. Merk op dat de aantallen/omzet/winstgetallen voor alle records voor 'West' 0 zijn.

De draaitabel die met deze dataset is gemaakt, ziet er als volgt uit:

Aangezien er nullen in de dataset waren voor alle records voor regio West, ziet u hier een 0 in de draaitabel voor west.

Als u nul in deze draaitabel wilt verbergen, kunt u hiervoor voorwaardelijke opmaak gebruiken.

Voorwaardelijke opmaak in de draaitabel in Excel werkt echter iets anders dan gewone gegevens.

Hier zijn de stappen om voorwaardelijke opmaak toe te passen op draaitabel om nullen te verbergen:

1. Selecteer een cel in de kolom met nullen in het draaitabeloverzicht (selecteer niet alle cellen, maar slechts één cel)
2. Klik op het tabblad Start
3. Klik in de groep 'Stijlen' op 'Voorwaardelijke opmaak'.
4. Plaats de cursor op de opties voor 'Markeer cellenregels' en klik in de opties die verschijnen op de optie 'Gelijk aan'.
5. Voer in het dialoogvenster 'Gelijk aan' 0 in het linkerveld in.
6. Selecteer de vervolgkeuzelijst Formaat en klik op Aangepast formaat.
7. Selecteer in het dialoogvenster 'Cellen opmaken' het tabblad Getal en klik op de optie 'Aangepast' in het linkerdeelvenster.
8. Voer in de optie Aangepast formaat het volgende in het veld Type in: ;;; (drie puntkomma's zonder spatie)
9. Klik OK. U ziet een klein pictogram Opmaakopties rechts van de geselecteerde cel.
10. Klik op dit pictogram 'Opmaakopties'
11. Kies in de opties die beschikbaar komen - Alle cellen met de waarden "Som van inkomsten" voor "Regio".

De bovenstaande stappen verbergen de nul in de draaitabel op zo'n manier dat als u de structuur van de draaitabel wijzigt (door er meer rijen/kolommen aan toe te voegen, de nullen verborgen blijven).

Als u meer wilt weten over hoe dit werkt, vindt u hier een gedetailleerde zelfstudie over de juiste manier om: voorwaardelijke opmaak toepassen op draaitabellen.

Wanneer Brongegevenscellen lege cellen hebben

Hieronder ziet u een voorbeeld waarin ik een dataset heb met lege cellen voor alle records voor West.

Als ik een draaitabel maak met deze gegevens, krijg ik het resultaat zoals hieronder weergegeven.

Standaard toont de draaitabel lege cellen wanneer alle cellen in de brongegevens leeg zijn. Maar als u in dit geval nog steeds een 0 ziet, volg dan de onderstaande stappen om de nul te verbergen en lege cellen weer te geven:

1. Klik met de rechtermuisknop op een van de cellen in de draaitabel
2. Klik op 'Opties draaitabel'
3. Klik in het dialoogvenster 'PivotTable Options' op het tabblad 'Layout & Format'
4. Vink in de Opmaakopties de opties en 'Voor lege cellen weergeven:' aan en laat deze leeg.
5. Klik OK.

De bovenstaande stappen zouden de nullen in de draaitabel verbergen en in plaats daarvan een lege cel weergeven.

Als u wilt dat de draaitabel iets toont in plaats van de 0, kunt u dat in stap 4 specificeren. Als u bijvoorbeeld de tekst - 'Gegevens niet beschikbaar' wilt weergeven, in plaats van de 0, typt u deze tekst in de veld (zoals hieronder weergegeven).

Nullen zoeken en verwijderen in Excel

In alle bovenstaande methoden heb ik je manieren laten zien om de 0-waarde in de cel te verbergen, maar de waarde blijft nog steeds in de cel.

Als u de nulwaarden uit de dataset wilt verwijderen (waardoor u lege cellen overhoudt), gebruikt u de onderstaande stappen:

1. Selecteer de dataset (of het hele werkblad)
2. Klik op het tabblad Start
3. Klik in de groep 'Bewerken' op 'Zoeken & Selecteren'
4. Klik in de opties die worden weergegeven in de vervolgkeuzelijst op 'Vervangen'
5. Breng in het dialoogvenster 'Zoeken en vervangen' de volgende wijzigingen aan:
   • Voer in het veld 'Zoeken wat' 0 . in
   • In het veld 'Vervangen door' niets invullen (leeg laten)
   • Klik op de knop Opties en vink de optie 'Volledige celinhoud afstemmen' aan
6. Klik op Alles vervangen

De bovenstaande stappen zouden onmiddellijk alle cellen met een nulwaarde vervangen door een blanco.

Als u de nullen niet meteen wilt verwijderen en eerst de cellen wilt selecteren, gebruikt u de volgende stappen:

1. Selecteer de dataset (of het hele werkblad)
2. Klik op het tabblad Start
3. Klik in de groep 'Bewerken' op 'Zoeken & Selecteren'
4. Klik in de opties die worden weergegeven in de vervolgkeuzelijst op 'Vervangen'
5. Breng in het dialoogvenster 'Zoeken en vervangen' de volgende wijzigingen aan:
   • Voer in het veld 'Zoeken wat' 0 . in
   • In het veld 'Vervangen door' niets invullen (leeg laten)
   • Klik op de knop Opties en vink de optie 'Volledige celinhoud afstemmen' aan
6. Klik op de knop 'Alles zoeken'. Dit geeft je een lijst met alle cellen met 0 erin
7. Druk op Control-A (houd de control-toets ingedrukt en druk op A). Hiermee worden alle cellen met 0 geselecteerd.

Nu u alle cellen met 0 hebt geselecteerd, kunt u deze verwijderen, vervangen door iets anders of de kleur wijzigen en deze markeren.

Het verbergen van de nullen vs. De nullen verwijderen

Bij het behandelen van de cellen met nullen erin, moet u het verschil weten tussen deze verbergen en deze verwijderen.

Wanneer u de nulwaarde in een cel verbergt, is deze niet zichtbaar voor u, maar blijft deze wel in de cel staan. Dit betekent dat als u een dataset heeft en deze gebruikt voor berekeningen, die 0-waarde ook wordt gebruikt in de berekening.

Integendeel, als u een lege cel hebt, kan het gebruik ervan in formules betekenen dat Excel deze cellen automatisch negeert (afhankelijk van de formule die u gebruikt).

Hieronder is bijvoorbeeld wat ik krijg als ik de formule GEMIDDELDE gebruik om het gemiddelde voor twee kolommen te krijgen (het resultaat staat in rij 14). Kolom A heeft cellen waar de 0-waarde is verborgen (maar staat nog steeds in de cel) en kolom B heeft cellen waar de 0-waarde is verwijderd.

U kunt het verschil in het resultaat zien ondanks het feit dat de dataset voor beide datasets er hetzelfde uitziet.

Dit gebeurt omdat de GEMIDDELDE formule lege cellen in kolom B negeert, maar die in kolom A gebruikt (omdat die in kolom A nog steeds de waarde 0 bevatten).