Nullwerte in Excel ausblenden - Machen Sie die Zellen leer, wenn der Wert 0 ist

Video ansehen - So verbergen Sie Nullwerte in Excel + So entfernen Sie Nullwerte

Falls Sie lieber lesen als ein Video ansehen, finden Sie unten das vollständige schriftliche Tutorial.

Manchmal möchten Sie in Excel vielleicht Nullwerte ausblenden in Ihrem Dataset und zeigen Sie diese Zellen als Leerzeichen an.

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

Während Sie dies mit einem kleinen Dataset manuell tun können, benötigen Sie bessere Möglichkeiten, wenn Sie mit großen Datasets arbeiten.

In diesem Tutorial zeige ich Ihnen mehrere Möglichkeiten zum Ausblenden von Nullwerten in Excel und eine Methode zum Auswählen und Entfernen aller Nullwerte aus dem Datensatz.

Wichtiger Hinweis: Wenn Sie mit den in diesem Tutorial gezeigten Methoden eine 0 in einer Zelle ausblenden, wird die 0 nur ausgeblendet und nicht entfernt. Auch wenn die Zellen leer aussehen können, enthalten diese Zellen immer noch die Nullen. Und falls Sie diese Zellen (mit versteckten Nullen) in einer Formel verwenden, werden diese in der Formel verwendet.

Fangen wir an und tauchen wir in jede dieser Methoden ein!

Nullwert in Zellen automatisch ausblenden

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

Alles, was Sie tun müssen, ist, ein Kontrollkästchen in den Excel-Optionen zu deaktivieren, und die Änderung wird auf das gesamte Arbeitsblatt angewendet.

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

Nachfolgend finden Sie die Schritte zum Ausblenden von Nullen aus allen Zellen in einer Arbeitsmappe in Excel:

1. Öffnen Sie die Arbeitsmappe, in der Sie alle Nullen ausblenden möchten
2. Klicken Sie auf die Registerkarte Datei
3. Klicken Sie auf Optionen
4. Klicken Sie im sich öffnenden Dialogfeld „Excel-Optionen“ auf die Option „Erweitert“ im linken Bereich
5. Scrollen Sie nach unten zu dem Abschnitt "Anzeigeoption für dieses Arbeitsblatt" und wählen Sie das Arbeitsblatt aus, in dem Sie die Nullen ausblenden möchten.
6. Deaktivieren Sie die Option "Zeige eine Null in Zellen mit einem Wert von Null"
7. OK klicken

Die obigen Schritte würden Nullen in allen Zellen im ausgewählten Arbeitsblatt sofort ausblenden.

Diese Änderung wird auch auf Zellen angewendet, bei denen Null das Ergebnis einer Formel ist.

Denken Sie daran, dass diese Methode nur den 0-Wert in den Zellen ausblendet und diese nicht entfernt. Die 0 ist immer noch da, sie ist nur versteckt.

Dies ist die beste und schnellste Methode, um Nullwerte in Excel auszublenden. Dies ist jedoch nur nützlich, wenn Sie Nullwerte im gesamten Arbeitsblatt ausblenden möchten. Falls Sie nur Nullwerte in einem bestimmten Zellbereich ausblenden möchten, ist es besser, andere Methoden zu verwenden, die als nächstes in diesem Tutorial behandelt werden.

Nullwert in Zellen mit bedingter Formatierung ausblenden

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

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

Bei der bedingten Formatierung können Sie basierend auf dem Wert in den Zellen ein bestimmtes Format anwenden. Wenn der Wert in einigen Zellen also 0 ist, können Sie einfach die bedingte Formatierung programmieren, um ihn auszublenden (oder sogar hervorzuheben, wenn Sie möchten).

Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten Nullen in den Zellen ausblenden.

Im Folgenden finden Sie die Schritte zum Ausblenden von Nullen in Excel mithilfe der bedingten Formatierung:

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

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

Diese Problemumgehung funktioniert nur, wenn Ihr Arbeitsblatt einen weißen Hintergrund enthält. Falls sich in den Zellen eine andere Hintergrundfarbe befindet, kann die weiße Farbe die Nullen dennoch sichtbar halten.

Wenn Sie die Nullen in den Zellen wirklich ausblenden möchten, in denen die Zellen unabhängig von der Hintergrundfarbe der Zellen leer aussehen, können Sie die folgenden Schritte ausführen.

1. Wählen Sie den Datensatz aus
2. Klicken Sie auf die Registerkarte „Startseite“
3. Klicken Sie in der Gruppe „Stile“ auf „Bedingte Formatierung“.
4. Platzieren Sie den Cursor über den Optionen "Zellen hervorheben" und klicken Sie in den angezeigten Optionen auf die Option "Gleich".
5. Geben Sie im Dialogfeld „Gleich zu“ 0 in das linke Feld 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 "Zahl" und klicken Sie im linken Bereich auf die Option "Benutzerdefiniert".
8. Geben Sie in der Option Benutzerdefiniertes Format Folgendes in das Feld Typ ein: ;;; (drei Semikolons ohne Leerzeichen)
9. OK klicken.
10. OK klicken.

Die obigen Schritte ändern die benutzerdefinierte Formatierung von Zellen mit dem Wert 0.

Wenn Sie drei Semikolons als Format verwenden, wird alles in der Zelle ausgeblendet (sei es numerisch oder Text). Und da dieses Format nur auf die Zellen angewendet wird, die den Wert 0 enthalten, blendet es nur die Nullen und nicht den Rest des Datensatzes aus.

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 im Dialogfeld "Zellen formatieren" die Registerkarte "Füllen" auswählen und dann die Farbe auswählen müssen, in der Sie die Zellen hervorheben möchten.

Beide oben beschriebenen Methoden funktionieren auch, wenn 0 das Ergebnis einer Formel in einer Zelle ist.

Hinweis: Beide in diesem Abschnitt behandelten Methoden blenden nur die Nullwerte aus. Es entfernt diese Nullen nicht. Das bedeutet, dass, falls Sie diese Zellen in Berechnungen verwenden, auch alle Nullen verwendet werden.

Nullwert mit benutzerdefinierter Formatierung ausblenden

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

Im Folgenden finden Sie die Schritte zum Verwenden eines benutzerdefinierten Formats zum Ausblenden von Nullen in Zellen:

1. Wählen Sie den gesamten Datensatz aus, für den Sie dieses Format anwenden möchten. Wenn Sie dies für das gesamte Arbeitsblatt wünschen, wählen Sie alle Zellen aus
2. Klicken Sie bei ausgewählten Zellen auf die Registerkarte Start
3. Klicken Sie in der Gruppe Zellen auf die Option Format
4. Klicken Sie in der Dropdown-Option auf Zellen formatieren. Dies öffnet das Dialogfeld Zellen formatieren
5. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahl
6. Klicken Sie in der Option im linken Bereich auf die Option Benutzerdefiniert.
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 würden ein benutzerdefiniertes Format auf die ausgewählten Zellen anwenden, wobei nur die Zellen mit dem Wert 0 formatiert werden, um den Wert auszublenden, während der Rest der Zellen die Daten wie erwartet anzeigt.

Wie funktioniert dieses benutzerdefinierte Format?

In Excel gibt es vier Arten von Datenformaten, die Sie angeben:

1. Positive Zahlen
2. Negative Zahlen
3. Nullen
4. Text

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

In den meisten Fällen gibt es ein Standardformat - Allgemein, das alle diese Datentypen sichtbar hält. Sie haben jedoch die Flexibilität, für jeden dieser Datentypen ein bestimmtes Format zu erstellen.

Nachfolgend finden Sie die benutzerdefinierte Formatstruktur, die Sie befolgen müssen, falls Sie ein benutzerdefiniertes Format auf jeden dieser Datentypen anwenden:

;;;

In der oben beschriebenen Methode habe ich das Format angegeben für:

0;-0;;@

Das obige Format macht Folgendes:

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

Profi-Tipp: Wenn negative Zahlen in Ihrem Dataset rot angezeigt werden sollen, verwenden Sie das folgende benutzerdefinierte Format: 0;[Rot]0;0;@

Ersetzen Sie Nullen durch Bindestrich oder einen bestimmten Text

In einigen Fällen möchten Sie vielleicht die Nullen in den Zellen nicht nur ausblenden, sondern diese durch etwas aussagekräftigeres ersetzen.

Sie können beispielsweise die Nullen entfernen und durch einen Bindestrich oder einen Text wie „Nicht verfügbar“ oder „Daten noch zu kommen“ ersetzen.

Dies wird wiederum durch die benutzerdefinierte Zahlenformatierung ermöglicht, bei der Sie angeben können, was eine Zelle anstelle einer Null anzeigen soll.

Unten sind die Schritte zum Konvertieren einer 0 in einen Bindestrich in Excel:

1. Wählen Sie den gesamten Datensatz aus, für den Sie dieses Format anwenden möchten. Wenn Sie dies für das gesamte Arbeitsblatt wünschen, wählen Sie alle Zellen aus
2. Klicken Sie bei ausgewählten Zellen auf die Registerkarte Start
3. Klicken Sie in der Gruppe Zellen auf die Option Format
4. Klicken Sie in der Dropdown-Option auf Zellen formatieren. Dies öffnet das Dialogfeld Zellen formatieren
5. Wählen Sie im Dialogfeld Zellen formatieren die Registerkarte Zahl
6. Klicken Sie in der Option im linken Bereich auf die Option Benutzerdefiniert.
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 würden alle anderen Zellen unverändert lassen und die Zellen mit dem Wert 0 ändern, um einen Bindestrich (-) anzuzeigen.

Notiz: In diesem Beispiel habe ich mich dafür entschieden, die Null durch einen Bindestrich zu ersetzen, aber Sie können auch jeden beliebigen Text verwenden. Wenn Sie beispielsweise die NA in den Zellen anzeigen möchten, können Sie das folgende Format verwenden: 0;-0;”NA”;@Profi-Tipp: Sie können auch einige Textfarben in der benutzerdefinierten Zahlenformatierung angeben. Wenn Sie beispielsweise möchten, dass die Zellen mit Nullen den Text NA in roter Farbe anzeigen, können Sie das folgende Format verwenden: 0;-0;[Rot]”NA”;@

Nullwerte in Pivot-Tabellen ausblenden

Es kann zwei Szenarien geben, in denen eine Pivot-Tabelle den Wert als 0 anzeigt:

1. Die in der Pivot-Tabelle zusammengefassten Quelldatenzellen haben 0 Werte
2. Die in der Pivot-Tabelle zusammengefassten Quelldatenzellen sind leer und die Pivot-Tabelle wurde so formatiert, dass die leeren Zellen als Null angezeigt werden

Sehen wir uns an, wie Sie die Nullen in der Pivot-Tabelle in jedem Szenario ausblenden:

Wenn Quelldatenzellen 0s haben

Unten ist ein Beispiel-Dataset, das ich verwendet habe, um eine Pivot-Tabelle zu erstellen. Beachten Sie, dass die Mengen-/Umsatz-/Gewinnzahlen für alle Datensätze für „West“ 0 sind.

Die mit diesem Datensatz erstellte Pivot-Tabelle sieht wie folgt aus:

Da das Dataset für alle Datensätze für die Region West 0s enthielt, sehen Sie hier eine 0 in der Pivot-Tabelle für West.

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

Die bedingte Formatierung in der Pivot-Tabelle in Excel funktioniert jedoch etwas anders als normale Daten.

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

1. Wählen Sie eine beliebige Zelle in der Spalte aus, die 0s in der Pivot-Tabellenzusammenfassung enthält (Wählen Sie nicht alle Zellen aus, sondern nur eine Zelle)
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe „Stile“ auf „Bedingte Formatierung“.
4. Platzieren Sie den Cursor über den Optionen "Zellen hervorheben" und klicken Sie in den angezeigten Optionen auf die Option "Gleich".
5. Geben Sie im Dialogfeld „Gleich zu“ 0 in das linke Feld 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 „Zahl“ und klicken Sie im linken Bereich auf die Option „Benutzerdefiniert“.
8. Geben Sie in der Option Benutzerdefiniertes Format Folgendes in das Feld Typ ein: ;;; (drei Semikolons ohne Leerzeichen)
9. OK klicken. Rechts neben der ausgewählten Zelle wird ein kleines Symbol für Formatierungsoptionen angezeigt.
10. Klicken Sie auf dieses Symbol für die Formatierungsoptionen
11. Wählen Sie in den verfügbaren Optionen - Alle Zellen mit den Werten "Umsatzsumme" für "Region" aus.

Die obigen Schritte blenden die Null in der Pivot-Tabelle so aus, dass die Nullen weiterhin ausgeblendet werden, wenn Sie die Struktur der Pivot-Tabelle ändern (indem Sie weitere Zeilen/Spalten-Header hinzufügen).

Wenn Sie mehr darüber erfahren möchten, wie das funktioniert, finden Sie hier ein ausführliches Tutorial zum richtigen Weg bedingte Formatierung auf Pivot-Tabellen anwenden.

Wenn Quelldatenzellen leere Zellen haben

Unten ist ein Beispiel, in dem ich ein Dataset habe, das leere Zellen für alle Datensätze für West enthält.

Wenn ich mit diesen Daten eine Pivot-Tabelle erstelle, erhalte ich das Ergebnis wie unten gezeigt.

Standardmäßig zeigt die Pivot-Tabelle leere Zellen an, wenn alle Zellen in den Quelldaten leer sind. Falls Sie in diesem Fall jedoch immer noch eine 0 sehen, führen Sie die folgenden Schritte aus, um die Null auszublenden und leere Zellen anzuzeigen:

1. Klicken Sie mit der rechten Maustaste auf eine der Zellen in der Pivot-Tabelle
2. Klicken Sie auf „Pivot-Tabellenoptionen“
3. Klicken Sie im Dialogfeld "PivotTable-Optionen" auf die Registerkarte "Layout & Format".
4. Überprüfen Sie in den Formatoptionen die Optionen und "Für leere Zellen anzeigen:" und lassen Sie es leer.
5. OK klicken.

Die obigen Schritte würden die Nullen in der Pivot-Tabelle ausblenden und stattdessen eine leere Zelle anzeigen.

Falls Sie möchten, dass die Pivot-Tabelle statt der 0 etwas anzeigt, können Sie dies in Schritt 4 angeben. Wenn Sie beispielsweise den Text "Daten nicht verfügbar" anzeigen möchten, statt der 0, geben Sie diesen Text in das ein Feld (wie unten gezeigt).

Suchen und Entfernen von Nullen in Excel

In allen oben genannten Methoden habe ich Ihnen Möglichkeiten gezeigt, den 0-Wert in der Zelle auszublenden, aber der Wert bleibt immer noch in der Zelle.

Falls Sie die Nullwerte aus dem Dataset entfernen möchten (was leere Zellen hinterlassen würde), führen Sie die folgenden Schritte aus:

1. Wählen Sie den Datensatz (oder das gesamte Arbeitsblatt) aus.
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe „Bearbeiten“ auf „Suchen & 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 Zellinhalt abgleichen".
6. Klicken Sie auf Alle ersetzen

Die obigen Schritte würden sofort alle Zellen mit einem Nullwert durch ein Leerzeichen ersetzen.

Falls Sie die Nullen nicht sofort entfernen und zuerst die Zellen auswählen möchten, gehen Sie wie folgt vor:

1. Wählen Sie den Datensatz (oder das gesamte Arbeitsblatt) aus.
2. Klicken Sie auf die Registerkarte Start
3. Klicken Sie in der Gruppe „Bearbeiten“ auf „Suchen & 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 Zellinhalt 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 ausgewählt, die 0 enthalten.

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

Verstecken der Nullen vs. Entfernen der Nullen

Wenn Sie Zellen mit Nullen behandeln, müssen Sie den Unterschied zwischen dem Ausblenden und dem Entfernen dieser Zellen kennen.

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

Im Gegenteil, wenn Sie eine leere Zelle haben, kann die Verwendung in Formeln dazu führen, dass Excel diese Zellen automatisch ignoriert (je nachdem, welche Formel Sie verwenden).

Im Folgenden sehen Sie beispielsweise, was ich erhalte, wenn ich die AVERAGE-Formel verwende, um den Durchschnitt für zwei Spalten zu erhalten (das Ergebnis befindet sich in Zeile 14). Spalte A enthält Zellen, in denen der Wert 0 ausgeblendet wurde (aber immer noch in der Zelle vorhanden ist), und Spalte B enthält Zellen, in denen der Wert 0 entfernt wurde.

Sie können den Unterschied im Ergebnis sehen, obwohl der Datensatz für beide Datensätze gleich aussieht.

Dies geschieht, da die MITTELWERT-Formel leere Zellen in Spalte B ignoriert, aber die in Spalte A verwendet (da die in Spalte A immer noch den Wert 0 enthalten).