Heatmap-Diagramm in Excel erstellen

In Excel sieht ein Heatmap-Diagramm wie eine Tabelle aus, die eine visuelle Darstellung bietet und einen Vergleichsüberblick über einen Datensatz ermöglicht. Bei einem großen Datensatz in Ihrem Arbeitsblatt ist es sehr schwierig, auf einen Blick niedrigere oder höhere Werte zu erkennen. In einer Heatmap hingegen werden die Zellwerte durch unterschiedliche Farbmuster dargestellt, sodass größere oder kleinere Datenwerte schnell und einfach erkannt werden können, wie im folgenden Screenshot gezeigt.

• Erstellen Sie ein einfaches Heatmap-Diagramm mit Bedingte Formatierung verwenden
• Erstellen Sie ein dynamisches Heatmap-Diagramm in Excel
• Beispiel 1: Erstellen Sie eine dynamische Heatmap mithilfe einer Bildlaufleiste
• Beispiel 2: Erstellen Sie eine dynamische Heatmap mithilfe von Optionsfeld
• Beispiel 3: Erstellen Sie eine dynamische Heatmap mithilfe von Kontrollkästchen
• Laden Sie die Beispieldatei für das Heatmap-Diagramm herunter
• Video: Heatmap-Diagramm in Excel erstellen

Erstellen Sie ein einfaches Heatmap-Diagramm mit Bedingte Formatierung verwenden

Excel bietet keine direkte Heatmap-Diagrammfunktion, aber mit der leistungsstarken Funktion Bedingte Formatierung verwenden können Sie im Handumdrehen eine Heatmap erstellen. So geht’s:

1. Markieren Sie den Datenbereich, auf den Sie die bedingte Formatierung anwenden möchten.

2. Klicken Sie anschließend auf Start > Bedingte Formatierung verwenden > Farbskalen, und wählen Sie dann im rechts erscheinenden Dropdown-Menü den gewünschten Stil aus (in diesem Fall wähle ich)Grüne – Gelbe – Rote Farbskala). Siehe Screenshot:

3. Die Heatmap wird nun erstellt und hebt die Zellen basierend auf ihren Werten hervor: Grüne Farben kennzeichnen die höchsten Werte, rote die niedrigsten – alle übrigen Werte erscheinen als sanfter Farbverlauf zwischen Grün und Rot. Siehe Screenshot:

4. Wenn Sie die Zahlen ausblenden und nur die Farben anzeigen möchten, markieren Sie den Datenbereich und drücken Sie die Tastenkombination Strg + 1, um das Dialogfeld Zellenformat festlegen zu öffnen.

5. Im Dialogfeld Zellenformat festlegen wechseln Sie zum Register Zahl, klicken Sie in der Liste unter Kategorie links auf Benutzerdefiniert und geben Sie anschließend ;;; in das Feld Typ ein. Siehe Screenshot:

6. Klicken Sie anschließend auf die Schaltfläche OK, und alle Zahlen werden wie im folgenden Screenshot gezeigt ausgeblendet:

Hinweis: Um Zellen mit anderen gewünschten Farben hervorzuheben, markieren Sie den Datenbereich und klicken Sie anschließend auf Start > Bedingte Formatierung verwenden > Regeln verwalten, um das Dialogfeld Bedingte Formatierung verwenden – Regeln verwalten zu öffnen.

Doppelklicken Sie dann auf die vorhandene Regel, um das Dialogfeld Formatierungsregel bearbeiten zu öffnen, und passen Sie die Regel ganz nach Ihren Anforderungen an. Siehe Screenshot:

Erstellen Sie ein dynamisches Heatmap-Diagramm in Excel

Beispiel 1: Erstellen Sie eine dynamische Heatmap mithilfe einer Bildlaufleiste

Wenn sich mehrere Spalten mit Daten in Ihrem Arbeitsblatt befinden, Sie diese aber in einem begrenzten Bereich anzeigen möchten, fügen Sie einfach eine Bildlaufleiste ein – so wird Ihre Heatmap dynamisch, wie in der folgenden Demo gezeigt.

Gehen Sie zur Erstellung dieses Typs eines dynamischen Heatmap-Diagramms wie folgt vor:

1. Fügen Sie ein neues Arbeitsblatt ein und kopieren Sie anschließend die erste Spalte mit den Monaten vom Originalblatt in dieses neue Blatt.

2. Klicken Sie dann auf Entwicklertools > Einfügen > Bildlaufleiste. Siehe Screenshot:

3. Ziehen Sie anschließend mit der Maus, um eine Bildlaufleiste unterhalb der kopierten Daten zu erstellen. Klicken Sie mit der rechten Maustaste auf die Bildlaufleiste und wählen Sie Steuerelement formatieren aus. Siehe Screenshot:

4. Legen Sie im Dialogfeld Objekt formatieren unter dem Register Steuerelement Minimalwert, Maximalwert, inkrementelle Änderung, Seitenänderung und die verknüpfte Zelle entsprechend Ihrem Datenbereich fest – wie im folgenden Screenshot gezeigt:

5. Klicken Sie anschließend auf OK, um dieses Dialogfeld zu schließen.

6. Geben Sie nun in Zelle B1 dieses neuen Arbeitsblatts die folgende Formel ein und drücken Sie die Enter-Taste, um das erste Ergebnis zu erhalten:

Hinweis: In der obigen Formel bezieht sich data1!$B$1:$I$13 auf das Originalblatt mit dem Datenbereich ohne Zeilenüberschrift (Monate), $I$1 ist die Zelle, mit der die Bildlaufleiste verknüpft ist, und $B$1:B1 ist die Zelle, in der Sie die Formel eingeben.

7. Ziehen Sie diese Formelzelle anschließend auf die übrigen Zellen. Wenn Sie nur 3 Jahre im Arbeitsblatt anzeigen möchten, ziehen Sie die Formel von B1 bis D13. Siehe Screenshot:

8. Wenden Sie anschließend die Farbskala der Funktion Bedingte Formatierung verwenden auf den neuen Datenbereich an, um die Heatmap zu erstellen. Sobald Sie die Bildlaufleiste ziehen, bewegt sich die Heatmap dynamisch. Siehe Screenshot:

Beispiel 2: Erstellen Sie eine dynamische Heatmap mithilfe von Optionsfeld

Sie können auch eine dynamische Heatmap mithilfe von Optionsfeldern erstellen: Durch die Auswahl eines Optionsfelds werden die n größten Werte hervorgehoben, durch die Auswahl eines anderen Optionsfelds die n kleinsten Werte – wie in der folgenden Demo gezeigt.

Gehen Sie zur Erstellung dieser Art dynamischer Heatmap wie folgt vor:

1. Klicken Sie auf Entwicklertools > Einfügen > Optionsfeld (Formularsteuerelement), und ziehen Sie anschließend mit der Maus, um zwei Optionsfelder zu zeichnen. Passen Sie den Text entsprechend Ihren Anforderungen an. Siehe Screenshot:

2. Klicken Sie nach dem Einfügen des Optionsfelds mit der rechten Maustaste auf das erste Element und wählen Sie Steuerelement formatieren. Wählen Sie im Dialogfeld Steuerelement formatieren unter der Registerkarte Steuerelement eine Zelle aus, die mit dem Optionsfeld verknüpft ist. Siehe Screenshot:

3. Klicken Sie auf die Schaltfläche OK, um das Dialogfeld zu schließen, und wiederholen Sie anschließend Schritt 2, um das zweite Optionsfeld ebenfalls mit derselben Zelle (Zelle M1) zu verknüpfen.

4. Wenden Sie anschließend die bedingte Formatierung auf den Datenbereich an: Wählen Sie Start > Bedingte Formatierung > Neue Regel aus. Siehe Screenshot:

5. Wählen Sie im Dialogfeld Neue Formatierungsregel in der Liste unter Regeltyp auswählen die Option Formel zur Bestimmung der zu formatierenden Zellen verwenden aus, und geben Sie anschließend folgende Formel ein: = WENN($M$1=1;WENN(B2>=KGRÖSSTE($B$2:$I$13;15);WAHR;FALSCH)) in das Feld „Werte formatieren, für die die Formel zutrifft“. Klicken Sie danach auf die Schaltfläche „Formatieren“, um eine Farbe auszuwählen. Siehe Screenshot:

6. Klicken Sie auf die Schaltfläche OK. Wenn Sie das erste Optionsfeld auswählen, werden dadurch die 15 größten Werte rot hervorgehoben.

7. Um die kleinsten 15 Werte hervorzuheben, behalten Sie die Datenauswahl bei und öffnen Sie das Dialogfeld Neue Formatierungsregel. Geben Sie dann im Feld Werte formatieren, für die die Formel zutrifft folgende Formel ein: =WENN($M$1=2;WENN(B2<=KKLEINSTE($B$2:$I$13;15);WAHR;FALSCH)). Klicken Sie anschließend auf die Schaltfläche Formatieren, um eine weitere gewünschte Farbe auszuwählen. Siehe Screenshot:

Hinweis: In den obigen Formeln steht $M$1 für die Zelle, die mit dem Optionsfeld verknüpft ist, $B$2:$I$13 für den Datenbereich, auf den Sie die bedingte Formatierung anwenden möchten, B2 für die erste Zelle des Datenbereichs und die Zahl 15 für die Anzahl der Werte, die Sie hervorheben möchten.

8. Klicken Sie auf OK, um das Dialogfeld zu schließen. Wenn Sie nun das erste Optionsfeld auswählen, werden die 15 größten Werte hervorgehoben; wählen Sie das zweite Optionsfeld aus, werden die 15 kleinsten Werte wie in der folgenden Demo gezeigt hervorgehoben:

Beispiel 3: Erstellen einer dynamischen Heatmap mithilfe von Kontrollkästchen

In diesem Abschnitt stelle ich ein dynamisches Heatmap-Diagramm mithilfe von Kontrollkästchen vor, mit dem Sie die Heatmap je nach Bedarf ein- oder ausblenden können. Wenn Sie das Kontrollkästchen aktivieren, wird die Heatmap angezeigt; wenn Sie das Kontrollkästchen deaktivieren, wird sie sofort ausgeblendet. Siehe folgende Demo:

1. Konvertieren Sie zunächst Ihren Datenbereich in ein Tabellenformat, damit die bedingte Formatierung beim Einfügen neuer Datenzeilen automatisch angewendet wird. Wählen Sie den Datenbereich aus und drücken Sie anschließend gleichzeitig die Tasten Strg + T, um das Dialogfeld Tabelle erstellen zu öffnen. Siehe Screenshot:

2. Klicken Sie auf OK, um das Dialogfeld zu schließen, und anschließend auf Entwicklertools > Einfügen > Kontrollkästchen (Formularsteuerelement). Ziehen Sie dann mit der Maus, um ein Kontrollkästchen zu erstellen, und passen Sie den Text entsprechend Ihren Anforderungen an – wie in den folgenden Screenshots gezeigt:

3. Klicken Sie mit der rechten Maustaste auf das Kontrollkästchen und wählen Sie Steuerelement formatieren. Wählen Sie im Dialogfeld Objekt formatieren unter der Registerkarte Steuerelement eine Zelle aus, die mit dem Kontrollkästchen verknüpft ist. Siehe Screenshot:

4. Klicken Sie auf OK, um das Dialogfeld zu schließen. Wählen Sie anschließend den Datenbereich aus, für den Sie eine Heatmap erstellen möchten, und klicken Sie auf Start > Bedingte Formatierung verwenden > Neue Regel, um zum Dialogfeld Neue Formatierungsregel zu gelangen.

5. Führen Sie im Dialogfeld Neue Formatierungsregel die folgenden Schritte aus:

• Wählen Sie die Option Alle Zellen basierend auf ihren Werten formatierenaus der Liste Regeltyp auswählenaus;
• Wählen Sie 3-Farben-Skalaaus der Dropdown-Liste Formatvorlageaus;
• Wählen Sie im jeweiligen Feld unter den Dropdown-Listen Formelin der Spalte Typfür Minimum,Mittelpunktund Maximumaus;
• Geben Sie anschließend die folgenden Formeln in die drei Wert-Felder Textfeld ein:
• Minimum:=WENN($M$1=WAHR;MIN($B$2:$I$13);FALSCH)
• Mittelpunkt:=WENN($M$1=WAHR;MITTELWERT($B$2:$I$13);FALSCH)
• Maximum:=WENN($M$1=WAHR;MAX($B$2:$I$13);FALSCH)
• Legen Sie anschließend im Abschnitt Farbe die gewünschten Hervorhebungsfarben fest.

Hinweis: In den obigen Formeln steht $M$1 für die Zelle, die mit dem Kontrollkästchen verknüpft ist, und $B$2:$I$13 für den Datenbereich, auf den Sie die bedingte Formatierung anwenden möchten.

6. Nach Abschluss der Einstellungen klicken Sie auf die Schaltfläche OK, um das Dialogfeld zu schließen. Sobald Sie das Kontrollkästchen aktivieren, wird die Heatmap angezeigt; andernfalls bleibt sie ausgeblendet. Siehe folgende Demo:

Laden Sie die Beispieldatei für das Heatmap-Diagramm herunter

Video: Heatmap-Diagramm in Excel erstellen