So erstellen Sie eine Heatmap in Excel – Eine Schritt-für-Schritt-Anleitung

Eine Heatmap in Excel ist eine visuelle Darstellung, die Ihnen schnell eine vergleichende Ansicht eines Datensatzes zeigt.

Im folgenden Datensatz kann ich beispielsweise leicht erkennen, in welchen Monaten die Verkäufe im Vergleich zu anderen Monaten niedrig waren (rot hervorgehoben).

Im obigen Datensatz werden die Farben basierend auf dem Wert in der Zelle zugewiesen. Die Farbskala ist Grün über Gelb bis Rot, wobei hohe Werte die grüne Farbe und niedrige Werte die rote Farbe erhalten.

Erstellen einer Heatmap in Excel

Während Sie eine Heatmap in Excel erstellen können, indem Sie die Zellen manuell farbcodieren. Sie müssen es jedoch wiederholen, wenn sich die Werte ändern.

Anstelle der manuellen Arbeit können Sie die bedingte Formatierung verwenden, um Zellen basierend auf dem Wert hervorzuheben. Wenn Sie die Werte in den Zellen ändern, wird die Heatmap auf diese Weise automatisch durch die Farbe / das Format der Zelle basierend auf den in der bedingten Formatierung festgelegten Regeln aktualisiert.

In diesem Tutorial erfahren Sie, wie Sie:

• Erstellen Sie schnell eine Heatmap in Excel mit bedingter Formatierung.
• Erstellen Sie eine dynamische Heatmap in Excel.
• Erstellen Sie eine Heatmap in Excel-Pivot-Tabellen.

Los geht’s!

Erstellen einer Heatmap in Excel mit bedingter Formatierung

Wenn Sie einen Datensatz in Excel haben, können Sie Datenpunkte manuell markieren und eine Heatmap erstellen.

Dies wäre jedoch eine statische Heatmap, da sich die Farbe nicht ändern würde, wenn Sie den Wert in einer Zelle ändern.

Daher ist die bedingte Formatierung der richtige Weg, da sich die Farbe in einer Zelle ändert, wenn Sie den Wert darin ändern.

Angenommen, Sie haben einen Datensatz wie unten gezeigt:

Hier sind die Schritte zum Erstellen einer Heatmap mit diesen Daten:

• Wählen Sie den Datensatz aus. In diesem Beispiel wäre es B2: D13.
• Gehe zu Home –> Bedingte Formatierung –> Farbskalen. Es werden verschiedene Farbkombinationen angezeigt, mit denen die Daten hervorgehoben werden können. Die gebräuchlichste Farbskala ist die erste, bei der Zellen mit hohen Werten grün und niedrig rot hervorgehoben werden. Beachten Sie, dass Sie beim Bewegen der Maus über diese Farbskalen die Live-Vorschau im Datensatz sehen können.
  

Dadurch erhalten Sie eine Heatmap wie unten gezeigt:

Standardmäßig weist Excel dem niedrigsten Wert die rote Farbe und dem höchsten Wert die grüne Farbe zu, und alle verbleibenden Werte erhalten eine Farbe basierend auf dem Wert. Es gibt also einen Farbverlauf mit verschiedenen Schattierungen der drei Farben basierend auf dem Wert.

Was nun, wenn Sie keinen Farbverlauf wünschen und nur Rot, Gelb und Grün anzeigen möchten. Sie möchten beispielsweise alle Werte unter 700 unabhängig vom Wert rot hervorheben. So erhalten 500 und 650 beide die gleiche rote Farbe, da es weniger als 700 ist.

Um dies zu tun:

• Gehe zu Home -> Bedingte Formatierung –> Farbskalen –> Weitere Optionen.
• Wählen Sie im Dialogfeld Neue Formatierungsregel die Option „3-Farbskala“ aus der Dropdown-Liste Formatstil.
• Jetzt können Sie den Mindest-, Mittel- und Maximalwert angeben und ihm die Farbe zuweisen. Da wir alle Zellen mit einem Wert unter 700 rot hervorheben möchten, ändern Sie den Typ in Nummer und den Wert in 700.
• Klicken Sie auf OK.

Nun erhalten Sie das Ergebnis wie unten gezeigt. Beachten Sie, dass alle Werte unter 700 den gleichen Rotton erhalten.

Ein Wort der Vorsicht: Während bedingte Formatierung ein wunderbares Werkzeug ist, ist es leider flüchtig. Dies bedeutet, dass bei jeder Änderung des Arbeitsblatts die bedingte Formatierung neu berechnet wird. Während die Auswirkungen bei kleinen Datensätzen vernachlässigbar sein können, kann dies bei der Arbeit mit großen Datensätzen zu einer langsamen Excel-Arbeitsmappe führen.

Erstellen einer dynamischen Heatmap in Excel

Da die bedingte Formatierung vom Wert in einer Zelle abhängt, wird die bedingte Formatierung neu berechnet und geändert, sobald Sie den Wert ändern.

Dies ermöglicht die Erstellung einer dynamischen Heatmap.

Schauen wir uns zwei Beispiele für die Erstellung von Heatmaps mit interaktiven Steuerelementen in Excel an.

Beispiel 1: Heatmap mit Bildlaufleiste

Hier ist ein Beispiel, in dem sich die Heatmap ändert, sobald Sie die Bildlaufleiste verwenden, um das Jahr zu ändern.

Diese Art von dynamischen Heatmaps kann in Dashboards verwendet werden, in denen Sie Platzbeschränkungen haben, aber dennoch möchten, dass der Benutzer auf den gesamten Datensatz zugreift.

Klicken Sie hier, um die Heatmap-Vorlage herunterzuladen

Wie erstelle ich diese dynamische Heatmap?

Hier ist der vollständige Datensatz, der zum Erstellen dieser dynamischen Heatmap verwendet wird.

Hier sind die Schritte:

• Geben Sie in einem neuen Blatt (oder im selben Blatt) die Monatsnamen ein (kopieren Sie sie einfach aus den Originaldaten).
• Gehe zu Entwickler -> Steuerelemente –> Einfügen –> Bildlaufleiste. Klicken Sie nun auf eine beliebige Stelle im Arbeitsblatt, und es wird eine Bildlaufleiste eingefügt. (klicken Sie hier, wenn Sie die Registerkarte Entwickler nicht finden können).
• Klicken Sie mit der rechten Maustaste auf die Bildlaufleiste und klicken Sie auf Formatsteuerung.
• Nehmen Sie im Dialogfeld Formatsteuerung die folgenden Änderungen vor:
  • Minimalwert: 1
  • Maximalwert 5
  • Cell Link: Sheet1!$ J $ 1 (Sie können auf das Symbol rechts klicken und dann manuell die Zelle auswählen, die Sie mit der Bildlaufleiste verknüpfen möchten).
• Klicken Sie auf OK.
• Geben Sie in Zelle B1 die Formel ein: =INDEX(Sheet1!$ B $ 1: $ H $ 13, ZEILE (), Blatt1!$ J$ 1+ SPALTEN (Blatt2!$B$1:B1)-1)
• Ändern Sie die Größe und platzieren Sie die Bildlaufleiste am unteren Rand des Datensatzes.

Wenn Sie nun die Bildlaufleiste ändern, wird der Wert in Sheet1!$ J $ 1 würde sich ändern, und da die Formeln mit dieser Zelle verknüpft sind, würden die korrekten Werte angezeigt.

Da die bedingte Formatierung flüchtig ist, wird sie auch aktualisiert, sobald sich der Wert ändert.

Video ansehen – Dynamische Heatmap in Excel

Beispiel 2: Erstellen einer dynamischen Heatmap in Excel mithilfe von Optionsfeldern

Hier ist ein weiteres Beispiel, in dem Sie die Heatmap durch Auswahl eines Optionsfelds ändern können:

In diesem Beispiel können Sie die oberen / unteren 10 Werte basierend auf der Auswahl des Optionsfelds / der Optionstaste hervorheben.

Klicken Sie hier, um die Heatmap-Vorlage herunterzuladen

Erstellen einer Heatmap in einer Excel-Pivot-Tabelle

Die bedingte Formatierung in Pivot-Tabellen funktioniert genauso wie bei normalen Daten.

Aber es gibt etwas Wichtiges, das Sie wissen müssen.

Lassen Sie mich ein Beispiel nehmen und es Ihnen zeigen.Angenommen, Sie haben eine Pivot-Tabelle wie unten gezeigt:

So erstellen Sie eine Heatmap in dieser Excel-Pivot-Tabelle:

• Wählen Sie die Zellen aus (B5:D14).
• Gehen Sie zu Home -> Bedingte Formatierung –> Farbskalen und wählen Sie die Farbskala aus, die Sie anwenden möchten.

Dies würde sofort die Heatmap in der Pivot-Tabelle erstellen.

Das Problem bei dieser Methode besteht darin, dass beim Hinzufügen neuer Daten im Backend und Aktualisieren dieser Pivot-Tabelle die bedingte Formatierung nicht auf die neuen Daten angewendet wird.Wenn ich beispielsweise neue Daten im Backend hinzugefügt, die Quelldaten angepasst und die Pivot-Tabelle aktualisiert habe, können Sie sehen, dass die bedingte Formatierung nicht darauf angewendet wird.

Dies geschieht, da wir die bedingte Formatierung nur auf die Zellen B5:D14 angewendet haben.

Wenn diese Heatmap dynamisch sein soll, sodass sie aktualisiert wird, wenn neue Daten hinzugefügt werden, gehen Sie wie folgt vor:

• Wählen Sie die Zellen aus (B5:D14).
• Gehen Sie zu Home -> Bedingte Formatierung –> Farbskalen und wählen Sie die Farbskala aus, die Sie anwenden möchten.
• Gehen Sie erneut zu Home –> Bedingte Formatierung -> Regeln verwalten.
• Klicken Sie im Regelmanager für bedingte Formatierung auf die Schaltfläche Bearbeiten.
• Wählen Sie im Dialogfeld Formatierungsregel bearbeiten die dritte Option: Alle Zellen mit den Werten „Sales“ für „Date“ und „Customer“.

Jetzt wird die bedingte Formatierung aktualisiert, wenn Sie die Backend-Daten ändern.

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.

Sie können auch die folgenden Excel-Tutorials mögen:

• So markieren Sie jede zweite Zeile in Excel.
• Erstellen Sie 100% Stack-Säulendiagramm mit bedingter Formatierung.
• Wie man farbige Zellen in Excel zählt.
• So markieren Sie leere Zellen in Excel.
• Excel Sparklines – Eine vollständige Anleitung