Anwenden der bedingten Formatierung in einer Pivot-Tabelle in Excel

Das Anwenden der bedingten Formatierung in einer Pivot-Tabelle kann etwas schwierig sein.

Da Pivot-Tabellen so dynamisch sind und sich die Daten im Backend häufig ändern können, müssen Sie wissen, wie Sie die bedingte Formatierung in einer Pivot-Tabelle in Excel richtig verwenden.

Die falsche Methode zum Anwenden der bedingten Formatierung auf eine Pivot-Tabelle

Schauen wir uns zunächst die reguläre Methode zum Anwenden der bedingten Formatierung in einer Pivot-Tabelle an.

Angenommen, Sie haben eine Pivot-Tabelle wie unten gezeigt:

Im obigen Datensatz befindet sich das Datum in den Zeilen und wir haben Verkaufsdaten in Spalten gespeichert.

Hier ist die reguläre Methode zum Anwenden der bedingten Formatierung auf einen Datensatz:

• Wählen Sie die Daten aus (in diesem Fall wenden wir die bedingte Formatierung auf B5:D14 an).
• Gehe zu Home -> Bedingte Formatierung -> Obere / untere Regeln -> Überdurchschnittlich.
• Geben Sie das Format an (ich verwende “Grüne Füllung mit Dard Green Text”).
• Klicken Sie auf OK.

Dadurch wird die bedingte Formatierung wie unten gezeigt angewendet:

Alle Datenpunkte, die über dem Durchschnitt des gesamten Datensatzes liegen, wurden hervorgehoben.

Das Problem bei dieser Methode besteht darin, dass das bedingte Format auf einen festen Zellbereich angewendet wurde (B5: D14). Wenn Sie Daten im Backend hinzufügen und diese Pivot-Tabelle aktualisieren, wird die bedingte Formatierung nicht darauf angewendet.

Zum Beispiel gehe ich zurück zum Datensatz und füge Daten für ein weiteres Datum hinzu (11 Januar 2015). Dies ist, was ich bekomme, wenn ich die Pivot-Tabelle aktualisiere.

Wie Sie im obigen Bild sehen können, werden die Daten für den 11. Januar 2015 nicht hervorgehoben (obwohl dies der Fall sein sollte, da die Werte für Speicher 1 und Speicher 3 über dem Durchschnitt liegen).

Der Grund ist, wie oben erwähnt, dass die bedingte Formatierung auf einen festen Bereich (B5: D14) angewendet wurde und nicht auf neue Daten in der Pivot-Tabelle erweitert wird.

Der richtige Weg, um bedingte Formatierung auf eine Pivot-Tabelle anzuwenden

Hier sind zwei Methoden, um sicherzustellen, dass die bedingte Formatierung auch dann funktioniert, wenn neue Daten im Backend vorhanden sind.

Methode 1 – Symbol für die Pivottabellenformatierung verwenden

Bei dieser Methode wird das Symbol für die Pivottabellenformatierungsoptionen verwendet, das angezeigt wird, sobald Sie die bedingte Formatierung in einer Pivottabelle anwenden.

Hier sind die Schritte dazu:

• Wählen Sie die Daten aus, auf die Sie die bedingte Formatierung anwenden möchten.
• Gehe zu Home -> Bedingte Formatierung -> Obere / untere Regeln -> Überdurchschnittlich.
• Geben Sie das Format an (ich verwende “Grüne Füllung mit Dard Green Text”).
• Klicken Sie auf OK.
  • Wenn Sie die obigen Schritte ausführen, wird die bedingte Formatierung auf den Datensatz angewendet. Unten rechts im Datensatz sehen Sie das Symbol Formatierungsoptionen:

• Klicken Sie auf das Symbol. Es werden drei Optionen in einer Dropdown-Liste angezeigt:
  • Ausgewählte Zellen (die standardmäßig ausgewählt wären).
  • Alle Zellen mit den Werten “Summe der Einnahmen”.
  • Alle Zellen mit den Werten “Summe der Einnahmen” für “Datum” und “Geschäft”.
• Wählen Sie die dritte Option – Alle Zellen mit den Werten “Umsatzsumme” für “Datum” und “Geschäft”.

Wenn Sie nun Daten im Backend hinzufügen und die Pivot-Tabelle aktualisieren, werden die zusätzlichen Daten automatisch durch die bedingte Formatierung abgedeckt.

Die drei Optionen verstehen:

• Ausgewählte Zellen: Dies ist die Standardoption, bei der die bedingte Formatierung nur auf die ausgewählten Zellen angewendet wird.
• Alle Zellen mit den Werten “Summe der Einnahmen”: Bei dieser Option werden alle Zellen berücksichtigt, die die Summe der Umsatzwerte anzeigen (oder die Daten, die Sie im Abschnitt Werte der Pivot-Tabelle haben).
  • Das Problem mit dieser Option besteht darin, dass sie auch die Gesamtsummenwerte abdeckt und eine bedingte Formatierung anwendet.
• Alle Zellen mit den Werten “Umsatzsumme” für “Datum” und “Geschäft”: Dies ist in diesem Fall die beste Option. Es wendet die bedingte Formatierung auf alle Werte (ohne Gesamtsummen) für die Kombination von Datum und Speicher an. Selbst wenn Sie im Backend weitere Daten hinzufügen, kümmert sich diese Option darum.

Hinweis:

• Das Symbol Formatierungsoptionen wird direkt nach dem Anwenden der bedingten Formatierung auf den Datensatz angezeigt. Wenn geht weg, wenn Sie etwas anderes tun (eine Zelle bearbeiten oder Schriftart / Ausrichtung ändern, etc.).
• Die bedingte Formatierung entfällt, wenn Sie die Zeilen- / Spaltenfelder ändern. Wenn Sie beispielsweise das Datumsfeld entfernen und erneut anwenden, geht die bedingte Formatierung verloren.

Methode 2 – Verwenden des Regelmanagers für bedingte Formatierung

Neben dem Symbol für Formatierungsoptionen können Sie auch das Dialogfeld Regelmanager für bedingte Formatierung verwenden, um bedingte Formatierungen in einer Pivottabelle anzuwenden.

Diese Methode ist nützlich, wenn Sie die bedingte Formatierung bereits angewendet haben und die Regeln ändern möchten.

Hier ist, wie es geht:

• Wählen Sie die Daten aus, auf die Sie die bedingte Formatierung anwenden möchten.
• Gehe zu Home -> Bedingte Formatierung -> Obere / untere Regeln -> Überdurchschnittlich.
• Geben Sie das Format an (ich verwende “Grüne Füllung mit Dard Green Text”).
• Klicken Sie auf OK. Dadurch wird die bedingte Formatierung auf die ausgewählten Zellen angewendet.
• Gehe zu Home -> Bedingte Formatierung -> Regeln verwalten.
• Wählen Sie im Regelmanager für die bedingte Formatierung die Regel aus, die Sie bearbeiten möchten, und klicken Sie auf die Schaltfläche Regel bearbeiten.
• Im Dialogfeld Regel bearbeiten werden dieselben drei Optionen angezeigt:
  • Ausgewählte Zellen.
  • Alle Zellen mit den Werten “Summe der Einnahmen”.
  • Alle Zellen mit den Werten “Summe der Einnahmen” für “Datum” und “Geschäft”.
• Wählen Sie die dritte Option und klicken Sie auf OK.

Dadurch wird die bedingte Formatierung auf alle Zellen für die Felder ‘Date’ und ‘Store’ angewendet. Selbst wenn Sie die Backend-Daten ändern (weitere Speicherdaten oder Datumsdaten hinzufügen), ist die bedingte Formatierung funktionsfähig.

Hinweis: Die bedingte Formatierung entfällt, wenn Sie die Zeilen-/Spaltenfelder ändern. Wenn Sie beispielsweise das Datumsfeld entfernen und erneut anwenden, geht die bedingte Formatierung verloren.

Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:

• Markieren Sie jede zweite Zeile in Excel mithilfe der bedingten Formatierung.
• Erstellen einer Pivot-Tabelle in Excel – Eine Schritt-für-Schritt-Anleitung.
• Quelldaten für Pivot-Tabelle vorbereiten.
• So gruppieren Sie Zahlen in der Pivot-Tabelle in Excel.
• So gruppieren Sie Daten in Pivot-Tabellen in Excel.
• So aktualisieren Sie die Pivot-Tabelle in Excel.
• Pivot-Tabelle in Excel löschen.
• Verwenden von Slicern in der Excel-Pivot-Tabelle.
• Hinzufügen und Verwenden eines berechneten Felds für eine Excel-Pivot-Tabelle.
• Pivot–Cache in Excel – Was ist das und wie kann man es am besten verwenden?
• So ersetzen Sie leere Zellen durch Nullen in Excel-Pivot-Tabellen.