Nullwert in Excel ausblenden (Zelle leer machen, wenn Wert 0 ist)

Manchmal möchten Sie in ExcelausblendenNullwerte im Datensatz und zeigen diese Zellen als leer an.

Angenommen, Sie haben einen Datensatz wie den folgenden und Sie möchten den Wert 0 in all diesen Zellen ausblenden (oder ihn durch so etwas wie einen Bindestrich oder den Text „Nicht verfügbar“ ersetzen).

Während Sie dies bei kleinen Datensätzen manuell tun können, benötigen Sie einen besseren Ansatz, wenn Sie mit großen Datensätzen arbeiten.

In diesem Lernprogramm zeige ich Ihnen mehrere Möglichkeiten zum Ausblenden von Nullen in Excel und eine Möglichkeit zum Auswählen und Entfernen aller Nullen aus einem Datensatz.

wichtiger Hinweis: Wenn Sie mit der in diesem Tutorial gezeigten Methode eine 0 in einer Zelle ausblenden, wird die 0 nur ausgeblendet, ohne sie zu entfernen.Obwohl die Zellen möglicherweise leer erscheinen, enthalten diese Zellen immer noch Nullen.Wenn Sie diese Zellen (versteckte Nullen) in einer Formel verwenden, werden diese Zellen in der Formel verwendet.

Beginnen wir mit der Arbeit an diesen Methoden!

Nullwerte in Zellen automatisch ausblenden

Excel verfügt über eine integrierte Funktion, mit der Sie automatisch alle Nullwerte für das gesamte Arbeitsblatt ausblenden können.

Alles, was Sie tun müssen, ist ein Kontrollkästchen in den Excel-Optionen zu deaktivieren, und die Änderungen gelten für das gesamte Arbeitsblatt.

Angenommen, Sie haben den unten gezeigten Verkaufsdatensatz und möchten alle Nullwerte ausblenden und eine leere Zelle anzeigen.

Hier sind die Schritte zum Ausblenden von Nullen in allen Zellen in einer Excel-Arbeitsmappe:

1. Öffnen Sie die Arbeitsmappe, in der Sie alle Nullen ausblenden möchten
2. Klicken Sie auf die Registerkarte Datei
3. Option anklicken
4. Klicken Sie im sich öffnenden Dialogfeld Excel-Optionen im linken Bereich auf die Option Erweitert
5. Scrollen Sie nach unten zum Abschnitt "Anzeigeoptionen für dieses Blatt" und wählen Sie das Blatt aus, in dem Sie Nullen ausblenden möchten.
6. Deaktivieren Sie die Option "Nullen in Zellen mit Nullwerten anzeigen".
7. OK klicken

Die obigen Schritte werden Nullen in allen Zellen im ausgewählten Arbeitsblatt auf einmal ausblenden.

Diese Änderung gilt auch für Zellen, in denen das Formelergebnis Null ist.

Denken Sie daran, dass diese Methode nur die 0-Werte in der Zelle verbirgt, sie nicht löscht.0 ist immer noch da, es ist nur versteckt.

Dies ist der beste und schnellste Weg, um Nullwerte in Excel auszublenden.Dies ist aber nur dann sinnvoll, wenn Sie Nullwerte im gesamten Blatt ausblenden möchten.Wenn Sie nur Nullwerte in einem bestimmten Zellbereich ausblenden möchten, verwenden Sie besser die anderen Methoden, die später in diesem Tutorial beschrieben werden.

Nullwerte in Zellen mit bedingter Formatierung ausblenden

Obwohl die obige Methode die bequemste ist, können Sie Nullen nicht nur in einem bestimmten Bereich ausblenden (sie verbirgt Nullwerte im gesamten Arbeitsblatt).

Wenn Sie nur Nullen für einen bestimmten Datensatz (oder mehrere Datensätze) ausblenden möchten, können Sie die bedingte Formatierung verwenden.

Mit der bedingten Formatierung können Sie basierend auf dem Wert in einer Zelle eine bestimmte Formatierung anwenden.Wenn also einige Zellen einen Wert von 0 haben, können Sie einfach eine bedingte Formatierung schreiben, um sie auszublenden (oder bei Bedarf sogar hervorzuheben).

Angenommen, Sie haben einen Datensatz wie unten und Sie möchten Nullen in Zellen ausblenden.

Hier sind die Schritte zum Ausblenden von Nullen in Excel mithilfe der bedingten Formatierung:

1. Wählen Sie einen Datensatz aus
2. Klicken Sie auf die Registerkarte Startseite
3. Klicken Sie in der Gruppe Stil auf Bedingte Formatierung.
4. Platzieren Sie den Cursor auf der Option „Zellenregeln hervorheben“ und klicken Sie in den angezeigten Optionen auf die Option „Gleich“.
5. Geben Sie im Gleich-Dialog im linken Feld "0" ein.
6. Wählen Sie das Dropdown-Menü Format und klicken Sie auf Benutzerdefiniertes Format.
7. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Schriftart aus.
8. Wählen Sie aus der Dropdown-Liste Farbe die Option Weiß aus.
9. OK klicken.
10. OK klicken.

Die obigen Schritte ändern die Schriftfarbe von Zellen mit Nullen in Weiß, wodurch diese Zellen leer aussehen.

Diese Problemumgehung funktioniert nur, wenn Sie einen weißen Hintergrund in Ihrem Blatt haben.Wenn es eine andere Hintergrundfarbe in der Zelle gibt, kann Weiß die Null immer noch sichtbar machen.

Wenn Sie die Nullen in den Zellen tatsächlich ausblenden möchten, sehen die Zellen unabhängig von ihrer Hintergrundfarbe leer aus, können Sie die folgenden Schritte ausführen.

1. Wählen Sie einen Datensatz aus
2. Klicken Sie auf die Registerkarte Startseite
3. Klicken Sie in der Gruppe Stil auf Bedingte Formatierung.
4. Platzieren Sie den Cursor auf der Option „Zellenregeln hervorheben“ und klicken Sie in den angezeigten Optionen auf die Option „Gleich“.
5. Geben Sie im Dialogfeld „Gleich“ im linken Feld 0 ein.
6. Wählen Sie das Dropdown-Menü Format und klicken Sie auf Benutzerdefiniertes Format.
7. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahlen aus, und klicken Sie dann im linken Bereich auf die Option Benutzerdefiniert.
8. Geben Sie unter Benutzerdefinierte Formatierungsoptionen Folgendes in das Feld Typ ein:;;; (drei Semikolons, keine Leerzeichen)
9. OK klicken.
10. OK klicken.

Die obigen Schritte ändern das benutzerdefinierte Format für Zellen mit einem Wert von 0.

Wenn Sie drei Semikolons als Formatierung verwenden, wird alles in der Zelle ausgeblendet (egal ob Zahlen oder Text).Und da dieses Format nur für Zellen mit einem Wert von 0 gilt, werden nur die Nullen ausgeblendet und nicht der Rest des Datensatzes.

Sie können diese Methode auch verwenden, um diese Zellen in einer anderen Farbe hervorzuheben, z. B. rot oder gelb.Fügen Sie dazu einfach einen weiteren Schritt (nach Schritt 8) hinzu, in dem Sie die Registerkarte „Füllung“ im Dialogfeld „Zellen formatieren“ auswählen und die Farbe auswählen müssen, mit der Sie die Zellen hervorheben möchten.

Die beiden oben genannten Methoden funktionieren auch, wenn 0 das Ergebnis der Formel in der Zelle ist.

Hinweis: Beide in diesem Abschnitt beschriebenen Methoden blenden nur Nullwerte aus.Diese Nullen werden nicht entfernt.Das bedeutet, wenn Sie diese Zellen in einer Berechnung verwenden, werden auch alle Nullen verwendet.

Nullwerte mit benutzerdefiniertem Format ausblenden

Eine andere Möglichkeit, Nullwerte aus einem Datensatz auszublenden, besteht darin, ein benutzerdefiniertes Format zu erstellen, das die Werte in Zellen mit 0 ausblendet, während alle anderen Werte wie erwartet angezeigt werden.

Hier sind die Schritte zum Ausblenden von Nullen in Zellen mit benutzerdefinierter Formatierung:

1. Wählen Sie das gesamte Dataset aus, auf das dieses Format angewendet werden soll.Wählen Sie alle Zellen aus, wenn Sie diese Option für das gesamte Blatt wünschen
2. Klicken Sie bei ausgewählter Zelle auf die Registerkarte Start
3. Klicken Sie in der Gruppe Zellen auf Formatierungsoptionen
4. Klicken Sie in den Dropdown-Optionen auf Zellen formatieren.Dies öffnet den Dialog Zellen formatieren
5. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahlen aus
6. Klicken Sie in den Optionen im linken Bereich auf Benutzerdefinierte Optionen.
7. Geben Sie in den Optionen auf der rechten Seite das folgende Format in das Feld Typ ein:0; -0 ;; @
8. OK klicken

Die obigen Schritte wenden ein benutzerdefiniertes Format auf die ausgewählten Zellen an, wobei nur Zellen mit einem Wert von 0 formatiert werden, um den Wert auszublenden, während die verbleibenden Zellen Daten wie erwartet anzeigen.

Wie funktioniert dieses benutzerdefinierte Format?

In Excel können Sie vier Arten von Datenformaten angeben:

1. Eine positive Zahl
2. negative Zahl
3. 零
4. Text

Für jeden dieser Datentypen können Sie ein Format angeben.

In den meisten Fällen gibt es ein Standardformat – generisch, das alle diese Datentypen sichtbar macht.Sie haben jedoch die Flexibilität, spezifische Formate für jeden Datentyp zu erstellen.

Hier ist die benutzerdefinierte Formatierungsstruktur, der Sie folgen müssen, falls Sie benutzerdefinierte Formatierungen auf jeden dieser Datentypen anwenden:

;;;

In der oben vorgestellten Methode habe ich das Format wie folgt angegeben:

0; -0 ;; @

Das obige Format bewirkt Folgendes:

1. Positive Zahlen werden unverändert angezeigt
2. Negative Zahlen werden mit einem Minuszeichen dargestellt
3. Nullen werden ausgeblendet (weil kein Format angegeben ist)
4. Der Text wird unverändert angezeigt

Ersetzen Sie Nullen durch Bindestriche oder einen bestimmten Text

In einigen Fällen möchten Sie möglicherweise nicht nur Nullen in Zellen ausblenden, sondern sie auch durch etwas Aussagekräftigeres ersetzen.

Beispielsweise möchten Sie möglicherweise Nullen entfernen und durch Bindestriche oder Text wie „nicht verfügbar“ oder „Daten noch nicht verfügbar“ ersetzen.

Dies wird wieder durch ein benutzerdefiniertes Zahlenformat ermöglicht, bei dem Sie angeben können, was die Zelle anstelle von Null anzeigen soll.

Im Folgenden sind die Schritte zum Konvertieren von 0 in Bindestrich in Excel aufgeführt:

1. Wählen Sie das gesamte Dataset aus, auf das dieses Format angewendet werden soll.Wählen Sie alle Zellen aus, wenn Sie diese Option für das gesamte Blatt wünschen
2. Klicken Sie bei ausgewählter Zelle auf die Registerkarte Start
3. Klicken Sie in der Gruppe Zellen auf Formatierungsoptionen
4. Klicken Sie in den Dropdown-Optionen auf Zellen formatieren.Dies öffnet den Dialog Zellen formatieren
5. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahlen aus
6. Klicken Sie in den Optionen im linken Bereich auf Benutzerdefinierte Optionen.
7. Geben Sie in den Optionen auf der rechten Seite das folgende Format in das Feld Typ ein:0;-0;–;@
8. OK klicken

Die obigen Schritte lassen alle anderen Zellen unverändert und ändern die Zellen mit einem Wert von 0, um einen Bindestrich (-) anzuzeigen.

注意: In diesem Beispiel habe ich mich dafür entschieden, Nullen durch Bindestriche zu ersetzen, aber Sie können jeden beliebigen Text verwenden.Wenn Sie beispielsweise NA in einer Zelle anzeigen möchten, können Sie das folgende Format verwenden:0;-0;"NA";@

Profi-Tipps: Sie können auch eine Textfarbe im benutzerdefinierten Zahlenformat angeben.Wenn Sie beispielsweise möchten, dass Zellen mit Nullen den Text NA in Rot anzeigen, können Sie das folgende Format verwenden:0;-0;[Rot]"NA";@

Nullwerte in Pivot-Tabelle ausblenden

Es gibt zwei mögliche Szenarien, in denen eine Pivot-Tabelle einen Wert als 0 anzeigt:

1. In der Pivot-Tabelle zusammengefasste Quelldatenzellen haben 0 Werte
2. Die Quelldatenzellen für die Zusammenfassung in der Pivot-Tabelle sind leer, die Pivot-Tabelle wurde so formatiert, dass leere Zellen als Nullen angezeigt werden

Mal sehen, wie man die Nullen jeweils in der Pivot-Tabelle ausblendet:

Wenn die Quelldatenzelle 0 ist

Unten ist der Beispieldatensatz, den ich zum Erstellen der Pivot-Tabelle verwendet habe.Beachten Sie, dass alle erfassten Mengen-/Einnahmen-/Gewinnzahlen für „Westen“ 0 sind.

Die mit diesem Datensatz erstellte Pivot-Tabelle sieht folgendermaßen aus:

Da alle Datensätze in der westlichen Region Nullen im Datensatz haben, sehen Sie hier Nullen in der westlichen Pivot-Tabelle.

Wenn Sie Nullen in dieser Pivot-Tabelle ausblenden möchten, können Sie dazu die bedingte Formatierung verwenden.

Die bedingte Formatierung in Excel-PivotTables funktioniert jedoch etwas anders als bei normalen Daten.

Hier sind die Schritte zum Anwenden einer bedingten Formatierung auf eine Pivot-Tabelle, um Nullen auszublenden:

1. Wählen Sie eine beliebige Zelle in der Spalte aus, die eine 0 in der PivotTable-Zusammenfassung enthält (wählen Sie nicht alle Zellen aus, sondern nur eine).
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Stil auf Bedingte Formatierung.
4. Platzieren Sie den Cursor auf der Option „Zellenregeln hervorheben“ und klicken Sie in den angezeigten Optionen auf die Option „Gleich“.
5. Geben Sie im Dialogfeld „Gleich“ im linken Feld 0 ein.
6. Wählen Sie das Dropdown-Menü Format und klicken Sie auf Benutzerdefiniertes Format.
7. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahlen aus, und klicken Sie dann im linken Bereich auf die Option Benutzerdefiniert.
8. Geben Sie unter Benutzerdefinierte Formatierungsoptionen Folgendes in das Feld Typ ein:;;; (drei Semikolons, keine Leerzeichen)
9. OK klicken.Rechts neben den ausgewählten Zellen werden kleine Symbole für Formatierungsoptionen angezeigt.
10. Klicken Sie auf dieses Symbol für Formatierungsoptionen
11. Wählen Sie unter den verfügbaren Optionen – Alle Zellen aus, die den Umsatzsummenwert für das Gebiet anzeigen.

Die obigen Schritte verbergen die Nullen in der Pivot-Tabelle, wenn Sie die Struktur der Pivot-Tabelle ändern (durch Hinzufügen weiterer Zeilen-/Spaltenüberschriften bleiben die Nullen weiterhin verborgen).

Wenn Sie mehr darüber erfahren möchten, wie es funktioniert, finden Sie hier ein ausführliches Tutorial, das es erklärtWenden Sie bedingte Formatierung auf eine Pivot-Tabelle ander richtige Weg.

Wenn die Quelldatenzelle leere Zellen enthält

Unten ist ein Beispiel, in dem ich einen Datensatz mit leeren Zellen für alle West-Datensätze habe.

Wenn ich mit diesen Daten eine Pivot-Tabelle erstelle, erhalte ich Ergebnisse wie unten.

Standardmäßig zeigt eine Pivot-Tabelle leere Zellen an, wenn alle Zellen in den Quelldaten leer sind.Wenn Sie in diesem Fall jedoch immer noch 0 sehen, gehen Sie folgendermaßen vor, um die Null auszublenden und die leere Zelle anzuzeigen:

1. Klicken Sie mit der rechten Maustaste auf eine beliebige Zelle in der Pivot-Tabelle
2. Klicken Sie auf PivotTable-Optionen
3. Klicken Sie im Dialogfeld PivotTable-Optionen auf die Registerkarte Layout und Formatierung
4. Aktivieren Sie in den Formatierungsoptionen die Option und "Anzeige: für leere Zellen" und lassen Sie sie leer.
5. OK klicken.

Die obigen Schritte werden die Nullen in der Pivot-Tabelle ausblenden und eine leere Zelle anzeigen.

Sie können in Schritt 0 angeben, ob die Pivot-Tabelle etwas anstelle von 4 anzeigen soll.Wenn Sie beispielsweise den Text "Daten nicht verfügbar" anstelle von 0 im Feld (siehe unten) anzeigen möchten.

Finden und entfernen Sie Nullen in Excel

Bei allen oben genannten Methoden habe ich Ihnen gezeigt, wie Sie den Wert 0 in der Zelle ausblenden, aber der Wert bleibt in der Zelle.

Wenn Sie Nullwerte aus dem Datensatz entfernen möchten (was Sie mit leeren Zellen zurücklässt), verwenden Sie die folgenden Schritte:

1. Wählen Sie einen Datensatz (oder ein ganzes Arbeitsblatt)
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf Suchen und auswählen
4. Klicken Sie in den Optionen, die in der Dropdown-Liste angezeigt werden, auf Ersetzen
5. Nehmen Sie im Dialogfeld Suchen und Ersetzen die folgenden Änderungen vor:
   • Geben Sie im Feld Suchen nach 0 ein
   • Geben Sie im Feld "Ersetzen durch" nichts ein (lassen Sie es leer)
   • Klicken Sie auf die Schaltfläche „Optionen“ und aktivieren Sie die Option „Gesamten Zelleninhalt abgleichen“.
6. Klicken Sie auf Alle ersetzen

Die obigen Schritte ersetzen sofort alle Zellen mit einem Wert von Null durch Leerzeichen.

Wenn Sie die Nullen nicht sofort entfernen und zuerst die Zellen auswählen möchten, verwenden Sie die folgenden Schritte:

1. Wählen Sie einen Datensatz (oder ein ganzes Arbeitsblatt)
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe Bearbeiten auf Suchen und auswählen
4. Klicken Sie in den Optionen, die in der Dropdown-Liste angezeigt werden, auf Ersetzen
5. Nehmen Sie im Dialogfeld Suchen und Ersetzen die folgenden Änderungen vor:
   • Geben Sie im Feld Suchen nach 0 ein
   • Geben Sie im Feld "Ersetzen durch" nichts ein (lassen Sie es leer)
   • Klicken Sie auf die Schaltfläche „Optionen“ und aktivieren Sie die Option „Gesamten Zelleninhalt abgleichen“.
6. Klicken Sie auf die Schaltfläche Alle suchen.Dadurch erhalten Sie eine Liste aller Zellen, die 0 enthalten
7. Drücken Sie Strg-A (halten Sie die Strg-Taste gedrückt und drücken Sie A).Dadurch werden alle Zellen mit 0 in ihnen ausgewählt.

Nachdem Sie nun alle 0-Zellen ausgewählt haben, können Sie sie löschen, durch etwas anderes ersetzen oder die Farbe ändern und sie hervorheben.

Null verstecken Nullen entfernen

Wenn Sie mit Zellen arbeiten, die Nullen enthalten, müssen Sie den Unterschied zwischen dem Ausblenden und Löschen dieser Zellen kennen.

Wenn Sie den Nullwert in einer Zelle ausblenden, ist er für Sie unsichtbar, verbleibt jedoch in der Zelle.Das bedeutet, wenn Sie einen Datensatz haben und ihn für Berechnungen verwenden, wird dieser 0-Wert auch für Berechnungen verwendet.

Umgekehrt kann die Verwendung einer leeren Zelle in einer Formel dazu führen, dass Excel diese Zellen automatisch ignoriert (abhängig von der verwendeten Formel).

Folgendes erhalte ich zum Beispiel, wenn ich die AVERAGE-Formel verwende, um den Durchschnitt von zwei Spalten zu erhalten (Ergebnis in Zeile 14).Die 0-Werte in den Zellen von Spalte A wurden ausgeblendet (aber noch in den Zellen vorhanden), und die 0-Werte in den Zellen von Spalte B wurden entfernt.

Obwohl die Datensätze für beide Datensätze gleich aussehen, können Sie den Unterschied in den Ergebnissen erkennen.

Dies liegt daran, dass die AVERAGE-Formel leere Zellen in Spalte B ignoriert, aber Zellen in Spalte A verwendet (weil Zellen in Spalte A immer noch den Wert 0 haben).