Excel-Dashboards - Bedingte Formatierung

Bedingte Formatierung für die Datenvisualisierung

Wenn Sie Excel zum Erstellen eines Dashboards ausgewählt haben, versuchen Sie, Excel-Tabellen zu verwenden, wenn diese dem Zweck dienen. Mit bedingter Formatierung und Sparklines sind Excel-Tabellen die beste und einfachste Wahl für Ihr Dashboard.

In Excel können Sie die bedingte Formatierung für die Datenvisualisierung verwenden. In einer Tabelle mit den Verkaufszahlen für das vergangene Quartal nach Regionen können Sie beispielsweise die oberen 5% -Werte hervorheben.

Sie können eine beliebige Anzahl von Formatierungsbedingungen angeben, indem Sie Regeln angeben. Sie können die in Excel integrierten Regeln, die Ihren Bedingungen entsprechen, aus den Regeln für hervorgehobene Zellen oder den Regeln für oben / unten abrufen. Sie können auch Ihre eigenen Regeln definieren.

Sie wählen die Formatierungsoptionen aus, die für Ihre Datenvisualisierung geeignet sind - Datenleisten, Farbskalen oder Symbolsätze.

In diesem Kapitel lernen Sie bedingte Formatierungsregeln, Formatierungsoptionen und das Hinzufügen / Verwalten von Regeln kennen.

Zellen hervorheben

Mit den Regeln für Hervorhebungszellen können Sie den Zellen, die die Daten enthalten, die eines der folgenden Kriterien erfüllen, ein Format zuweisen:

Zahlen innerhalb eines bestimmten Zahlenbereichs: Größer als, Kleiner als, Zwischen und Gleich.
Doppelte oder eindeutige Werte.

Betrachten Sie die folgende Zusammenfassung der Ergebnisse, die Sie präsentieren möchten:

Angenommen, Sie möchten die Gesamtbetragswerte hervorheben, die mehr als 1000000 betragen.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Zellenregeln hervorheben.
Klicken Sie in der zweiten angezeigten Dropdown-Liste auf Größer als.

Das Dialogfeld Größer als wird angezeigt.

Geben Sie im Feld Zellen formatieren, die größer als: sind, die Bedingung als 1000000 an.
Wählen Sie im Feld mit die Formatierungsoption Grün mit dunkelgrünem Text aus.

Klicken Sie auf die Schaltfläche OK.

Wie Sie sehen können, werden die Werte, die die angegebene Bedingung erfüllen, mit dem angegebenen Format hervorgehoben.

Top / Bottom Regeln

Sie können Top / Bottom Rules verwenden, um den Werten, die eines der folgenden Kriterien erfüllen, ein Format zuzuweisen:

Top 10 Items - Zellen, die im oberen N rangieren, wobei 1 <= N <= 1000.
Top 10% - Zellen, die in den oberen n% rangieren, wobei 1 <= n <= 100 ist.
Bottom 10 Items - Zellen, die im unteren N rangieren, wobei 1 <= N <= 1000.
Bottom 10% - Zellen, die im unteren n% rangieren, wobei 1 <= n <= 100.
Above Average - Zellen, die für den ausgewählten Bereich überdurchschnittlich sind.
Below Average - Zellen, die für den ausgewählten Bereich unter dem Durchschnitt liegen.

Angenommen, Sie möchten die Werte für den Gesamtbetrag in den oberen 5% hervorheben.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Top / Bottom Rules.
Klicken Sie in der zweiten angezeigten Dropdown-Liste auf Top Ten%.

Das Dialogfeld Top Ten% wird angezeigt.

Geben Sie in den Formatzellen, die im Feld TOP: rangieren, die Bedingung als 5% an.
Wählen Sie im Feld mit die Formatierungsoption Grün mit dunkelgrünem Text aus.

Klicken Sie auf die Schaltfläche OK. Die oberen 5% -Werte werden mit dem angegebenen Format hervorgehoben.

Datenleisten

Sie können farbige Datenleisten verwenden, um den Wert relativ zu den anderen Werten anzuzeigen. Die Länge der Datenleiste repräsentiert den Wert. Ein längerer Balken repräsentiert einen höheren Wert und ein kürzerer Balken repräsentiert einen niedrigeren Wert. Sie können entweder Volltonfarben oder Verlaufsfarben für Datenleisten verwenden.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Datenleisten.
Klicken Sie in der zweiten angezeigten Dropdown-Liste unter Verlaufsfüllung auf Blaue Datenleiste.

Die Werte in der Spalte werden hervorgehoben und zeigen kleine, mittlere und große Werte mit blau gefärbten Verlaufsfüllleisten.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Datenleisten.
Klicken Sie in der zweiten angezeigten Dropdown-Liste unter Vollständige Füllung auf Orange Datenleiste.

Die Werte in der Spalte werden hervorgehoben und zeigen kleine, mittlere und große Werte nach Balkenhöhe mit orangefarbenen Balken.

Angenommen, Sie möchten den Umsatz im Vergleich zu einem Umsatzziel hervorheben, z. B. 800000.

Erstellen Sie eine Spalte mit den Werten = [@ [Gesamtbetrag]] - 800000.
Wählen Sie die neue Spalte aus.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Datenleisten.
Klicken Sie in der zweiten angezeigten Dropdown-Liste unter Verlaufsfüllung auf Grüne Datenleiste.

Die Datenbalken beginnen in der Mitte jeder Zelle und erstrecken sich für negative Werte nach links und für positive Werte nach rechts.

Wie Sie sehen können, sind die nach rechts gestreckten Balken grün und zeigen positive Werte an. Die nach links gestreckten Balken sind rot und zeigen negative Werte an.

Farbskalen

Sie können Farbskalen verwenden, um den Wert in einer Zelle relativ zu den Werten in den anderen Zellen in einer Spalte anzuzeigen. Die Farbe gibt an, wo jeder Zellenwert in diesen Bereich fällt. Sie können entweder eine 3-Farbskala oder eine 2-Farbskala verwenden.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Farbskalen.
Klicken Sie in der zweiten angezeigten Dropdown-Liste auf Grün-Gelb-Rot-Farbskala.

Wie im Fall von Hervorhebungszellenregeln verwendet eine Farbskala eine Zellschattierung, um die Unterschiede in den Zellenwerten anzuzeigen. Wie Sie in der Vorschau sehen können, sind die Farbunterschiede für diesen Datensatz nicht auffällig.

Klicken Sie in der zweiten Dropdown-Liste auf Weitere Regeln.

Das Dialogfeld Neue Formatierungsregel wird angezeigt.

Klicken Sie im Feld Regeltyp auswählen auf Alle Zellen basierend auf ihren Werten formatieren.
Wählen Sie im Feld Regelbeschreibung bearbeiten Folgendes aus:
- Wählen Sie im Feld Formatstil die Option 3-Farbskala aus.
- Geben Sie unter Mittelpunkt für Wert 75 ein.

Klicken Sie auf die Schaltfläche OK.

Wie Sie sehen können, sind bei der definierten Farbskala die Werte deutlich schattiert, um den Datenbereich darzustellen.

Symbolsätze

Sie können Symbolsätze verwenden, um numerische Unterschiede zu visualisieren. In Excel haben Sie eine Reihe von Symbolsätzen -

Symbolsatztyp	Symbolsätze
Richtung
Formen
Indikatoren
Bewertungen

Wie Sie sehen können, besteht ein Symbolsatz aus drei bis fünf Symbolen. Sie können Kriterien definieren, um den Werten in einem Zellbereich ein Symbol zuzuordnen. ZB ein roter Abwärtspfeil für kleine Zahlen, ein grüner Aufwärtspfeil für große Zahlen und ein gelber horizontaler Pfeil für Zwischenwerte.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Symbolsätze.
Klicken Sie in der zweiten angezeigten Dropdown-Liste in der Gruppe Richtung auf 3 Pfeile (farbig).

In der ausgewählten Spalte werden basierend auf den Werten farbige Pfeile angezeigt.

Benutzerdefinierte Regeln verwenden

Sie können Ihre eigenen Regeln definieren und einen Bereich von Zellen formatieren, die eine bestimmte Bedingung erfüllen.

Wählen Sie die Spalte - Gesamtbetrag.
Klicken Sie in der Gruppe "Stile" auf der Registerkarte "Startseite" auf "Bedingte Formatierung".
Klicken Sie in der Dropdown-Liste auf Neue Regel.

Das Dialogfeld Neue Formatierungsregel wird angezeigt.

Klicken Sie im Feld Regeltyp auswählen auf Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.
Gehen Sie im Feld Regelbeschreibung bearbeiten wie folgt vor:
- Geben Sie eine Formel in das Feld - Formatieren Sie Werte ein, bei denen diese Formel wahr ist. Zum Beispiel =PercentRank.INC($E$3:$E$13,E3)>=0.7
- Klicken Sie auf die Schaltfläche Format.
- Wählen Sie das Format. ZB Schrift - fett und Füll - orange.
- Klicken Sie auf OK.
Überprüfen Sie die Vorschau.

Klicken Sie auf OK, wenn die Vorschau in Ordnung ist. Die Werte im Datensatz, die der Formel entsprechen, werden mit dem von Ihnen gewählten Format hervorgehoben.

Verwalten von Regeln für die bedingte Formatierung

Sie können die Regeln für die bedingte Formatierung im Dialogfeld Manager für Regeln für die bedingte Formatierung verwalten.

Klicken Conditional Formattingin der Gruppe Stile auf der Registerkarte Startseite. Klicken Sie in der Dropdown-Liste auf Regeln verwalten.

Das Dialogfeld "Manager für bedingte Formatierungsregeln" wird angezeigt. Sie können alle vorhandenen Regeln anzeigen. Sie können eine neue Regel hinzufügen, eine Regel löschen und / oder eine Regel bearbeiten, um sie zu ändern.