Skjul nullverdier i Excel - Gjør cellene tomme Hvis verdien er 0

Se video - Slik skjuler du nullverdier i Excel + hvordan du fjerner nullverdier

Hvis du foretrekker å lese fremfor å se på en video, er den komplette skriftlige opplæringen nedenfor.

Noen ganger i Excel kan det være lurt skjul nullverdier i datasettet ditt, og vis disse cellene som blanke.

Anta at du har et datasett som vist nedenfor, og du vil skjule verdien 0 i alle disse cellene (eller vil erstatte det med noe som en bindestrek eller teksten ‘Ikke tilgjengelig’).

Selv om du kan gjøre dette manuelt med et lite datasett, trenger du bedre måter når du jobber med store datasett.

I denne opplæringen vil jeg vise deg flere måter å skjule nullverdier i Excel og en metode for å velge og fjerne alle nullverdiene fra datasettet.

Viktig notat: Når du gjemmer en 0 i en celle ved hjelp av metodene som er vist i denne opplæringen, vil den bare skjule 0 og ikke fjerne den. Selv om cellene kan se tomme ut, inneholder disse cellene fortsatt 0 -tallet. Og hvis du bruker disse cellene (som har skjulte nuller) i en hvilken som helst formel, vil disse bli brukt i formelen.

La oss komme i gang og dykke ned i hver av disse metodene!

Skjul nullverdi i celler automatisk

Excel har en innebygd funksjonalitet som lar deg automatisk skjule alle nullverdiene for hele regnearket.

Alt du trenger å gjøre er å fjerne merket for en boks i Excel -alternativer, og endringen vil bli brukt på hele regnearket.

Anta at du har salgsdatasettet som vist nedenfor, og at du vil skjule alle nullverdiene og vise tomme celler i stedet.

Nedenfor er trinnene for å skjule nuller fra alle cellene i en arbeidsbok i Excel:

1. Åpne arbeidsboken der du vil skjule alle nuller
2. Klikk kategorien Fil
3. Klikk på Alternativer
4. Klikk på "Avansert" -alternativet i venstre rute i dialogboksen Excel -alternativer som åpnes
5. Rull ned til delen som sier "Visningsalternativ for dette regnearket", og velg regnearket du vil skjule nullene i.
6. Fjern merket for "Vis et null i celler som har nullverdi"
7. Klikk Ok

Trinnene ovenfor vil umiddelbart skjule nuller i alle cellene i det valgte regnearket.

Denne endringen brukes også på celler der null er et resultat av en formel.

Husk at denne metoden bare skjuler 0 -verdien i cellene og ikke fjerner disse. 0 er fortsatt der, den er bare skjult.

Dette er den beste og raskeste metoden for å skjule nullverdier i Excel. Men dette er bare nyttig hvis du vil skjule nullverdier i hele regnearket. Hvis du bare vil at dette skal skjule nullverdier i et bestemt celleområde, er det bedre å bruke andre metoder som dekkes i denne opplæringen.

Skjul nullverdi i celler ved hjelp av betinget formatering

Selv om metoden ovenfor er den mest praktiske, tillater den deg ikke bare å skjule nuller i et bestemt område (den skjuler heller nullverdier i hele regnearket).

Hvis du bare vil skjule nuller for et bestemt datasett (eller flere datasett), kan du bruke betinget formatering.

Med betinget formatering kan du bruke et bestemt format basert på verdien i cellene. Så hvis verdien i noen celler er 0, kan du ganske enkelt programmere betinget formatering for å skjule den (eller til og med markere den hvis du vil).

Anta at du har et datasett som vist nedenfor, og du vil skjule nuller i cellene.

Nedenfor er trinnene for å skjule nuller i Excel ved bruk av betinget formatering:

1. Velg datasettet
2. Klikk på "Hjem" -fanen
3. Klikk på "Betinget formatering" i gruppen "Stiler".
4. Plasser markøren over alternativene "Marker celleregler", og klikk på alternativet "Tilsvarende" i alternativene som vises.
5. I dialogboksen "Tilsvarende" skriver du inn "0" i feltet til venstre.
6. Velg rullegardinlisten Format, og klikk på 'Egendefinert format'.
7. Velg "Font" -fanen i dialogboksen "Format celler".
8. Velg den hvite fargen fra rullegardinmenyen Farge.
9. Klikk OK.
10. Klikk OK.

Trinnene ovenfor endrer skriftfargen på cellene som har nuller til hvitt, noe som får disse cellene til å se blanke ut.

Denne løsningen fungerer bare når du har en hvit bakgrunn i regnearket. Hvis det er en annen bakgrunnsfarge i cellene, kan den hvite fargen fortsatt holde nullene synlige.

Hvis du virkelig vil gjemme nullene i cellene, der cellene ser blanke ut uansett hvilken bakgrunnsfarge cellene får, kan du bruke trinnene nedenfor.

1. Velg datasettet
2. Klikk på "Hjem" -fanen
3. Klikk på "Betinget formatering" i gruppen "Stiler".
4. Plasser markøren over alternativene "Marker celleregler", og klikk på alternativet "Tilsvarende" i alternativene som vises.
5. I dialogboksen 'Lik til' skriver du inn 0 i venstre felt.
6. Velg rullegardinlisten Format, og klikk på Egendefinert format.
7. I dialogboksen "Formater celler" velger du kategorien "Nummer" og klikker på "Tilpasset" i venstre rute.
8. I alternativet Egendefinert format skriver du inn følgende i Type -feltet: ;;; (tre semikolon uten plass)
9. Klikk OK.
10. Klikk OK.

Trinnene ovenfor endrer tilpasset formatering av celler som har verdien 0.

Når du bruker tre semikolon som format, skjuler det alt i cellen (det være seg numerisk eller tekst). Og siden dette formatet bare brukes på cellene som har verdien 0 i det, skjuler det bare nullene og ikke resten av datasettet.

Du kan også bruke denne metoden til å markere disse cellene i en annen farge (for eksempel rød eller gul). For å gjøre dette, bare legg til et nytt trinn (etter trinn 8) der du må velge Fyll -fanen i dialogboksen "Formater celler" og deretter velge fargen du vil markere cellene i.

Begge metodene som dekkes ovenfor fungerer også når 0 er et resultat av en formel i en celle.

Merk: Begge metoden som dekkes i denne delen, skjuler bare nullverdiene. Det fjerner ikke disse nullene. Dette betyr at hvis du bruker disse cellene i beregninger, vil alle nullene også bli brukt.

Skjul nullverdi ved hjelp av tilpasset formatering

En annen måte å skjule nullverdier fra et datasett er ved å lage et egendefinert format som skjuler verdien i celler som har 0s, mens en annen verdi vises som forventet.

Nedenfor er trinnene for å bruke et tilpasset format for å skjule nuller i celler:

1. Velg hele datasettet du vil bruke dette formatet for. Hvis du vil ha dette for hele regnearket, velger du alle cellene
2. Når cellene er markert, klikker du på Hjem -fanen
3. Klikk på alternativet Format i gruppen Celler
4. Klikk på Format celler fra rullegardinmenyen. Dette åpner dialogboksen Format celler
5. I kategorien Formatceller velger du kategorien Nummer
6. Klikk alternativet Tilpasset i alternativet i venstre rute.
7. I alternativene til høyre skriver du inn følgende format i feltet Type: 0;-0;;@
8. Klikk OK

De ovennevnte trinnene vil bruke et tilpasset format på de valgte cellene der bare de cellene som har verdien 0 er formatert for å skjule verdien, mens resten av cellene viser dataene som forventet.

Hvordan fungerer dette tilpassede formatet?

I Excel er det fire typer dataformater du angir:

1. Positive tall
2. Negative tall
3. Nuller
4. Tekst

Og for hver av disse datatypene kan du angi et format.

I de fleste tilfeller er det et standardformat - Generelt, som holder alle disse datatypene synlige. Men du har fleksibiliteten til å lage et bestemt format for hver av disse datatypene.

Nedenfor er den tilpassede formatstrukturen du må følge hvis du bruker et tilpasset format til hver av disse datatypene:

;;;

I metoden som er dekket ovenfor, har jeg spesifisert formatet til:

0;-0;;@

Ovennevnte format gjør følgende:

1. Positive tall vises som de er
2. Negative tall vises med et negativt tegn
3. Nuller er skjult (ettersom det ikke er angitt noe format)
4. Teksten vises som den er

Pro tips: Hvis du vil at negative tall skal vises som røde i datasettet, bruker du følgende tilpassede format: 0; [Rød] 0; 0;@

Erstatt nuller med bindestrek eller noe spesifikk tekst

I noen tilfeller vil du kanskje ikke bare gjemme nullene i cellene, men erstatte disse med noe mer mening.

For eksempel kan det være lurt å fjerne nullpunktene og erstatte det med en bindestrek eller en tekst som “Ikke tilgjengelig” eller “Data ennå”

Dette er igjen mulig ved bruk av tilpasset tallformatering, der du kan angi hva en celle skal vise i stedet for en null.

Nedenfor er trinnene for å konvertere en 0 til en bindestrek i Excel:

1. Velg hele datasettet du vil bruke dette formatet for. Hvis du vil ha dette for hele regnearket, velger du alle cellene
2. Når cellene er markert, klikker du på Hjem -fanen
3. Klikk på alternativet Format i gruppen Celler
4. Klikk på Format celler fra rullegardinmenyen. Dette åpner dialogboksen Format celler
5. I kategorien Formatceller velger du kategorien Nummer
6. Klikk alternativet Tilpasset i alternativet i venstre rute.
7. I alternativene til høyre skriver du inn følgende format i feltet Type: 0;-0;-;@
8. Klikk OK

De ovennevnte trinnene vil beholde alle de andre cellene som de er og endre cellene med verdien 0 for å vise en bindestrek (-).

Merk: I dette eksemplet har jeg valgt å erstatte nullpunktet med en bindestrek, men du kan også bruke hvilken som helst tekst. For eksempel, hvis du vil vise NA i cellene, kan du bruke følgende format: 0; -0; ”NA”;@Pro tips: Du kan også angi noen få tekstfarger i tilpasset nummerformatering. For eksempel, hvis du vil at cellene med nuller skal vise teksten NA i rød farge, kan du bruke følgende format: 0; -0; [Rød] ”NA”;@

Skjul nullverdier i pivottabeller

Det kan være to scenarier der en pivottabell viser verdien som 0:

1. Kildedatacellene som er oppsummert i pivottabellen har 0 verdier
2. Kildedatacellene som er oppsummert i pivottabellen er blanke og pivottabellen er formatert for å vise de tomme cellene som null

La oss se hvordan vi gjemmer nullpunktene i pivottabellen i hvert scenario:

Når kildedataceller har 0s

Nedenfor er et eksempel på datasett som jeg har brukt til å lage et pivottabell. Vær oppmerksom på at antall/inntekter/fortjeneste for alle postene for ‘West’ er 0.

Pivottabellen som er opprettet ved hjelp av dette datasettet ser ut som vist nedenfor:

Siden det var 0s i datasettet for alle postene for West -regionen, ser du en 0 i pivottabellen for vest her.

Hvis du vil skjule null i denne pivottabellen, kan du bruke betinget formatering for å gjøre dette.

Betinget formatering i pivottabellen i Excel fungerer imidlertid litt annerledes enn vanlige data.

Her er trinnene for å bruke betinget formatering på pivottabell for å skjule nuller:

1. Velg en celle i kolonnen som har 0s i Pivot Table -sammendraget (Ikke velg alle cellene, men bare én celle)
2. Klikk på kategorien Hjem
3. Klikk på "Betinget formatering" i gruppen "Stiler".
4. Plasser markøren over alternativene "Marker celleregler", og klikk på alternativet "Tilsvarende" i alternativene som vises.
5. I dialogboksen 'Lik til' skriver du inn 0 i venstre felt.
6. Velg rullegardinlisten Format, og klikk på Egendefinert format.
7. I dialogboksen "Formater celler" velger du kategorien Nummer og klikker på "Tilpasset" i venstre rute.
8. I alternativet Egendefinert format skriver du inn følgende i Type -feltet: ;;; (tre semikolon uten plass)
9. Klikk OK. Du vil se det lille formateringsalternativikonet til høyre for den valgte cellen.
10. Klikk på dette ikonet "Formateringsalternativer"
11. I alternativene som blir tilgjengelige, velg - Alle celler som viser "Sum of Revenue" -verdier for "Region".

Trinnene ovenfor skjuler nullpunktet i pivottabellen på en slik måte at hvis du endrer pivottabellstrukturen (ved å legge til flere rader/kolonneoverskrifter i den, vil nullene fortsatt bli skjult).

Hvis du vil lære mer om hvordan dette fungerer, er det en detaljert opplæring om hvordan du gjør det bruke betinget formatering på pivottabeller.

Når kildedataceller har tomme celler

Nedenfor er et eksempel der jeg har et datasett som har tomme celler for alle postene for West.

Hvis jeg lager en pivottabell med disse dataene, får jeg resultatet som vist nedenfor.

Som standard viser pivottabellen deg tomme celler når alle cellene i kildedataene er tomme. Men hvis du fortsatt ser en 0 i dette tilfellet, følg trinnene nedenfor for å skjule nullpunktet og vise tomme celler:

1. Høyreklikk på en av cellene i pivottabellen
2. Klikk på 'Pivot Table Options'
3. I dialogboksen "Pivottabellalternativer" klikker du på "Layout og format" -fanen
4. I alternativene Format, sjekk alternativene og "For tomme celler vises:" og la det stå tomt.
5. Klikk OK.

Trinnene ovenfor vil skjule nullpunktene i pivottabellen og i stedet vise en tom celle.

Hvis du vil at pivottabellen skal vise noe i stedet for 0, kan du angi det i trinn 4. Hvis du for eksempel vil vise teksten - 'Data ikke tilgjengelig', i stedet for 0, skriver du denne teksten i feltet (som vist nedenfor).

Finn og fjern nuller i Excel

I alle metodene ovenfor har jeg vist deg måter å skjule 0 -verdien i cellen, men verdien forblir fortsatt i cellen.

Hvis du vil fjerne nullverdiene fra datasettet (som vil etterlate deg med tomme celler), bruker du trinnene nedenfor:

1. Velg datasettet (eller hele regnearket)
2. Klikk på kategorien Hjem
3. I gruppen "Redigering" klikker du på "Finn og velg"
4. Klikk på "Erstatt" i alternativene som vises i rullegardinlisten
5. Gjør følgende endringer i dialogboksen "Finn og erstatt":
   • I feltet "Finn hva" skriver du inn 0
   • I feltet "Erstatt med" skriver du ingenting (la det stå tomt)
   • Klikk på Alternativer -knappen og merk av for alternativet "Match hele celleinnholdet"
6. Klikk på Erstatt alle

De ovennevnte trinnene ville umiddelbart erstatte alle cellene som hadde nullverdi med et tomt.

Hvis du ikke vil fjerne nullene med en gang og først velge cellene, bruker du følgende trinn:

1. Velg datasettet (eller hele regnearket)
2. Klikk på kategorien Hjem
3. I gruppen "Redigering" klikker du på "Finn og velg"
4. Klikk på "Erstatt" i alternativene som vises i rullegardinlisten
5. Gjør følgende endringer i dialogboksen "Finn og erstatt":
   • I feltet "Finn hva" skriver du inn 0
   • I feltet "Erstatt med" skriver du ingenting (la det stå tomt)
   • Klikk på Alternativer -knappen og merk av for "Match hele celleinnholdet"
6. Klikk på "Finn alle" -knappen. Dette vil gi deg en liste over alle cellene som har 0 i den
7. Trykk på Control-A (hold inne kontrolltasten og trykk på A). Dette vil velge alle cellene som har 0 i den.

Når du har valgt alle cellene med 0, kan du slette disse, erstatte disse med noe annet, eller endre fargen og markere disse.

Skjule nullene vs. Fjerne nullpunktene

Når du behandler cellene med 0s i, må du vite forskjellen mellom å skjule disse og fjerne disse.

Når du skjuler nullverdien i en celle, er den ikke synlig for deg, men den forblir fortsatt i cellen. Dette betyr at hvis du har et datasett og du bruker det til beregning, vil den 0 -verdien også bli brukt i beregningen.

Tvert imot, når du har en tom celle, kan bruk av den i formler bety at Excel automatisk ignorerer disse cellene (avhengig av hvilken formel du bruker).

For eksempel er det jeg får når jeg bruker formelen GJENNOMGANG for å få gjennomsnittet for to kolonner (resultatet er i rad 14). Kolonne A har celler der 0 -verdien er skjult (men fortsatt er der i cellen) og kolonne B har celler der 0 -verdien er fjernet.

Du kan se forskjellen i resultatet til tross for at datasettet for begge datasettene ser likt ut.

Dette skjer ettersom gjennomsnittsformelen ignorerer tomme celler i kolonne B, men den bruker de i kolonne A (ettersom de i kolonne A fortsatt har verdien 0 i den).