Excel Tipps & Tricks

Tipps & Tricks rund um Excel, aber nur die Besten! Haben Sie auch einen Tipp für uns? Schreiben Sie uns über das Kontaktformular. Vielen Dank!

Mappen und Tabellen
Die Startmappe
Excel bietet im Gegensatz zu Word oder PowerPoint keine Druckformatvorlage für neue Mappen an. Wenn Sie eine Mappe so vorbereiten wollen, dass sie automatisch als Vorlage für neue Mappen (auch die leere Mappe nach dem Start) dient, gehen Sie so vor:
Formatieren Sie die Mappe wie gewünscht, weisen Sie Zahlenformate, Kopf/Fußzeilen, Layout zu und tragen Sie Formeln, Texte und Zahlen in die Tabellen ein. Speichern Sie die Mappe als Mustervorlage mit der Bezeichnung MAPPE.XLT bzw. MAPPE.XLTX im Startordner:
C:\Users\B\AppData\Roaming\Microsoft\Excel\XLSTART
(So finden Sie Sie Ihren Roaming-Ordner schnell: Geben Sie im Explorer ein: %appdata%)

Mit Zellen arbeiten
Transponieren
Um einen horizontalen Bereich vertikal oder umgekehrt einen vertikalen Bereich horizontal anzuzeihen, kopieren Sie den Bereich, markieren die erste Zielzelle und wählen im Kontextmenü der Maustaste Inhalte einfügen/Transponieren.
Soll der Bereich verknüpft sein, verwenden Sie die Matrixfunktion MTRANS(): Markieren Sie so viele Zielzellen, wie der Quellbereich enthält, schreiben Sie MTRANS(bereich) und drücken Sie [Strg]+[Umschalt]+[Eingabe]. Das funktioniert auch gut für Bereichsnamen:
=MTRANS(Umsatz)

Nur sichtbare Zellen kopieren
Bei gefilterten Teilergebnissen und gruppierten Daten werden die unsichtbaren Daten mitkopiert. Markieren Sie den Bereich, drücken Sie [F5] und wählen Sie Inhalte. Klicken Sie auf Nur sichtbare Zellen.

Formatieren
Zeilenumbruch im Text
Verwenden Sie [Alt]+[Eingabe], um in längeren Texten einen Zeilenumbruch einzufügen. Damit wird gleichzeitig die Formatierung Zeilenumbruch in die Zelle eingefügt

Tausender-Zahlenformat
Um eine Zahl um 3 Nullen (Tausend) zu reduzieren, weisen Sie ihr einfach dieses Zahlenformat zu:
0.
Mit zwei Punkten werden 6 Nullen ausgeblendet:
0..

Spezielle Zahlenformate
Um eine Zahl mit einem Text zu versehen, weisen Sie der Zelle ein spezifisches Zahlenformat zu. Drücken Sie [Strg]+[1] und tragen Sie das benutzerdefinierte Zahlenformat ein:
0" Mio" -> 12 Mio
0" Tage" -> 23 Tage
oder, mit Bedingung:
[>0]"Gewinn: "#.##0;[Rot][ Gewinn: 100, Verlust: 300

Bedingte Formatierung
Richtig markieren
Markieren Sie alle Zellen, für die das Bedingunsformat vorgesehen ist und wählen Sie Start/Formatvorlagen/Bedingte Formatierung. Wenn Sie das Bedingungsformat ändern wollen, suchen Sie zuerst alle Zellen, die formatiert wurden. Drücken Sie dazu [F5] (Gehezu), klicken Sie auf Inhalte und wählen Sie die Option Bedingte Formate. Wenn Sie die zweite Option Alle verwenden, werden alle Zellen mit Bedingungsformaten markiert. Schalten Sie um auf Gleiche, werden alle Zellen mit dem Format der aktiven Zelle markiert.

Letzten Eintrag hervorheben
Mit dieser Formel markieren Sie den letzten Eintrag in einer Spalte (hier A):
=ANZAHL2($A:$A)=ZEILE(A1)

Tagesdatum kennzeichnen
Um in einem Bereich mit Datumswerten das aktuelle Tagesdatum hervorzuheben, markieren Sie diesen (z. B. A1:c20), wählen Start/Formatvorlagen/Bedingte Formatierung und geben unter Formeln ... ein:
=A1=HEUTE()

Wochenend-Tage hervorheben
Markieren Sie alle Datumswerte, in denen Sie die Samstage und Sonntage kennzeichnen wollen. Wählen Sie Start/Formatvorlagen/Bedingte Formatierung und geben Sie diese Formeln ein (hier mit Zelle A1 als aktive Zelle):
Samstage: =WOCHENTAG(A1)=7
Sonntage: =WOCHENTAG(A1)=1

Doppelte Werte hervorheben
Markieren Sie den Bereich, wählen Sie Start/Formatvorlagen/Bedingte Formatierung und Regeln zum Hervorheben von Zellen/Doppelte Werte.

Datum und Zeit
DATEDIF()
Die Funktion DATEDIF() ist aus gutem Grund nicht dokumentiert, auch nicht in Excel 2007/2010. Wir empfehlen, sie nicht zu verwenden, da sie in einigen Fällen falsch rechnet.

Kalenderwoche
Bis zur Excel-Version 2007 hat die Funktion KALENDERWOCHE() falsch gerechnet, die Kalenderwoche 1 ist nach DIN 1330 die erste Woche, die 4 Tage enthält. In Excel 2010 kann ein Parameter angegeben werden, mit dem die Kalenderwoche richtig berechnet wird:
=KALENDERWOCHE(Zelle;21)
Beispiel:
A1: 01.01.2011
A2: =KALENDERWOCHE($A$1;21)
Ergebnis: 52
Für frühere Versionen hier die KW-Funktion, die richtig rechnet:
=KÜRZEN(($A$1-DATUM(JAHR($A$1+3-REST($A$1-2;7));1;REST($A$1-2;7)-9))/7)

Rechnen mit Zeit
Ein Zeitwert ist eine Dezimalzahl zwischen 0 und 1. Die Zeit 12:00 ist dezimal 0,5. Um mit einem Zeitwert rechnen zu können, multiplizieren Sie den Zellinhalt mit 24. Die Funktion ZEITWERT() rechnet einen Text dezimal um.

Rechnen mit Negativzeiten
Da der Excel-Kalender am 1.1.1900 beginnt, ist die Berechnung einer Negativzeit (z.B. 18:00 - 21:00) nicht möglich. Das Ergebnis wäre ein Zeitwert am Vortag des ersten Tages, Excel zeigt diesen mit einer endlosen #-Kette an. Alternative: Stellen Sie den ersten Kalendertag auf den 1.1.1904 (eine Einstellung, die Excel aus Kompatibilitätsgründen zum OS von Apple mitführt):

Excel 2007: Office-Menü/Excel-Optionen
Excel 2010/2013: Datei/Optionen
Kategorie Erweitert, Beim Berechnen dieser Arbeitsmappe/1904-Datumswerte verwenden.

Unabhängig von dieser Einstellung können Sie mit einer WENN-Funktion dafür sorgen, dass Negativzeiten richtig berechnet werden. Hier am Beispiel einer Arbeitszeitberechnung:
=WENN(C2>B2;C2-B2;1-B2+C2)

Zeitsummen berechnen
Ergibt die Summe aus mehreren Zeitwerten einen Wert größer als 24, zeigt Excel mit dem Zahlenformat (hh:mm) für Zeitwerte nur die ersten 24 Stunden an. Ändern Sie das Zahlenformat in Datum um (TT.MM.JJ hh:mm), sehen Sie das Ergebnis in Tagen und Stunden. Wollen Sie das Ergebnis als Stundensumme sehen, setzen Sie den Platzhalter für Stunden einfach in eckige Klammern:
[hh]:mm

Rechnen & Kalkulieren
Spalten eines Bereiches adressieren
Im Controlling sollten Sie so oft wie möglich mit Bereichsnamen arbeiten. Um eine bestimmte Spalte eines Bereiches zu adresieren, verwenden Sie die Funktion INDEX(). Geben Sie nur die Spaltennummer an, lassen Sie die Zeilennummer weg. Beispiel: Der Bereich "Datenbank" listet in der 3. Spalte alle Absätze. Mit dieser Funktion berechnen Sie die Summe:
=SUMME(INDEX(Absatz;;3))
Das gleiche gilt für Zeilen, lassen Sie die Spaltennummer weg, wird die gesamte Zeile adressiert. Mit dieser Funktion zählen Sie zum Beispiel, wie viele Einträge ein Bereich in de ersten Zeile hat:
=ANZAHL2(INDEX(Bereich;1;))

Runden auf 5 Cent
Mit dieser Funktion runden Sie einen Beitrag in Zelle a1 auf 5 Cent. VRUNDEN rundet auf (weg von Null), wenn der Rest der Division von Zahl durch Vielfaches größer gleich der Hälfte von Vielfaches ist.
=VRUNDEN(A1;0,05)

Zufallszahlen mit ZUFALLSBEREICH()
Die Funktion ZUFALLSBEREICH() erzeugt Zufallszahlen, geben Sie den Bereich in beiden Argumenten an:
=ZUFALLSBEREICH(von;bis)
In Excel bis Version 2003 müssen Sie die analyse-Funktionen dazu einschalten (Extras/Add-Ins).

Mehr als 7 WENN() schachteln
Die WENN()-Funktion kann bis zu 7mal geschachtelt werden. Wenn Sie mehr WENN() brauchen, addieren Sie die Funktionen einfach: =WENN(...)+WENN() ... +WENN().
In Excel 2010 können mehr als 7 WENNs geschachtelt werden.

Tabellennamen im Formeln verwenden
Wenn Sie den Namen einer Tabelle in einer Formel verwenden wollen, verknüpfen Sie ihn in einer INDIREKT()-Funktion. Mit dieser Formel erhalten Sie beispielsweise den Inhalt der Zelle A1 in der Tabelle "Umsatz":
=INDIREKT("Umsatz!A1")
Natürlich können Sie dabi auch Zellen verknüpfen. Steht der Name der Tabelle in C5, schreiben Sie:
=INDIREKT(C5&"!A1")

Einträge zählen
Wie viele verschiedene Einträge enthält ein Bereich? Schreiben Sie eine Matrixformel, hier für den Bereich A1:A100. Drücken Sie zum Abschluss [Strg]+[Umschalt]+[Eingabe]:
=SUMME(1/ZÄHLENWENN(A13:A100;A1:A100))
Wenn der Bereich Leerzeilen enthält, muss die Formel erweitert werden. Schließen Sie wieder mit [Strg]+[Umschalt]+[Eingabe] ab:
=SUMME(1/WENN(ZÄHLENWENN(A1:A100;A1:A100)=0;1;
ZÄHLENWENN(A1:A100;A1:A100)))-ANZAHLLEEREZELLEN(A1:A100)

Zeilenumbruch per Formel
Zeilenumbrüche erzeugen Sie in Zellinhalten mit [Alt]+[Eingabetaste]. Wollen Sie beispielsweise einen mehrzeiligen Text für einen Diagrammtitel oder eine Textverknüpfung konstruieren, verwenden Sie die Funktion =ZEICHEN(10). Die Funktion =ZEICHEN() erzeugt ein beliebiges ASCII-Zeichen, 10 und 13 sind die Code für CR/LF (carriage return/line feed). Beispiel:
="Das ist die erste Zeile"&ZEICHEN(10)&"Das ist die zweite Zeile"

Schutz und Sicherheit
Zellschutz mit Datenüberprüfung
Ein einfacher, aber wirkungsvoller Schutz für einzelne Zellen, wenn der Blattschutz nicht angebracht ist: Markieren Sie den Bereich, weisen Sie ihm eine Datenüberprüfung zu:

Verknüpfungen
Mit der Funktion ZELLE("Dateiname") kann der Name der Tabelle in einer Zelle abgebildet werden:
=TEIL(ZELLE("Dateiname");FINDEN("]";ZELLE("Dateiname"))+1;500)
Wir legen einen Bereichsnamen (z. B. TabName) an und weisen ihm diese Formel als Bezug zu, dann kann dieser in Formeln verwendet werden. Mit dem Parameter A1 wird die Formel automatisch neu berechnet: =ZELLE("Dateiname";A1), gilt dann aber nur lokal.

Diagramme
Farbmarkierungen nutzen
Solange sich nur die Daten ändern, die das Diagramm bereits anzeigt, werden Sie keine Probleme damit haben, denn jede Änderung wird sofort optisch umgesetzt. Schwieriger wird es, wenn Daten wegfallen oder neue Daten hinzukommen: In diesem Fall stimmt der zugewiesene Datenbereich nicht mehr. Bevor Sie jetzt ein neues Diagramm erstellen, testen Sie einfach diese Techniken:

Markieren Sie das Diagrammobjekt und achten Sie auf die Farbmarkierungen, die dabei in der Tabelle angebracht werden:

Eine magenta-gefärbte Linie kennzeichnet den Bereich, der die Beschriftungen (X-Achse im Säulendiagramm, Legende für Torten) enthält.
Grün wird diejenige Zelle markiert, die die Überschrift enthält (Legende im Säulendiagramm, Titel im Kreisdiagramm).
Die blaue Linie kennzeichnet den Datenbereich.
Ändert sich der Datenbestand, können Sie auch über Menüs oder die Multifunktionsleiste die Bereiche neu bestimmen, schneller geht es aber über die entsprechenden Farbmarkierungen. Zeigen Sie auf den Rand der Farbmarkierung und ziehen Sie diesen mit gedrückter Maustaste an eine neue Position. Um den Bereich zu vergrößern oder zu verkleinern, ziehen Sie das Füllkästchen der Farbmarkierung nach unten oder oben.

Verknüpfte Titel
Um im Titel eines Diagramms den Inhalt einer Zelle anzuzeigen, verknüpfen Sie das Titelelement: Markieren Sie es, schreiben Sie die Verknüpfung in die Bearbeitungsleiste und drücken Sie [Eingabe].
=C5
In Excel ab Version 2007 zeichnen Sie ein Textfeld, markieren den Rand des Objektes und schreiben die Verknüpfung in die Bearbeitungsleiste (nicht in die Zelle).

Datenreihen einfügen und löschen
Um eine neue Datenreihe zu erzeugen, kopieren Sie einfach die Daten für die Reihe in die Zwischenablage, markieren das Diagrammobjekt und drücken die Eingabe-Taste. Die Reihenfolge der Datenreihen im Diagramm regelt das letzte Argument der Funktion =DATENREIHE(). Tragen Sie hier die Position (bei horizontalen X-Achsen von links) ein:
=DATENREIHE(Legende;Achsenbereich:DatenBereich;Position)

Nullabfall im Liniendiagramm verhindern
Wenn in einem Liniendiagramm die Rubrikenachse größer ist als die Wertereihe, fallen in der Regel alle Datenpunkte auf den Nullwert zurück, die noch keinen Eintrag haben. Mit einer Optionseinstellung stellen Sie sicher, dass die Diagrammreihe mit dem letzten Datenpunkt endet:
Diagrammtools/Entwurf/Daten auswählen, Ausgeblendete und leere Zellen: Datenpunkte mit einer Linie verbinden.

Dieser Tipp funktioniert leider nicht, wenn die Werte für das Diagramm mit Formeln berechnet werden oder aus Verknüpfungen stammen. In diesem Fall sind die Zellen nicht leer, auch wenn die Formel keinen Wert oder einen Nullwert berechnet. Hier hilft nur die Berechnung der Datenreihen über eine Matrixfunktion:
Drücken Sie [Strg]+[F3] und erstellen Sie einen neuen Bereichsnamen. Dieser Bereichsname überprüft, wie viele Zahlen sich in Spalte C befinden und berechnet daraus eine einspaltige Matrix ab Zelle B6:

Bereichsname: KostenKumuliert
Bezieht sich auf: =BEREICH.VERSCHIEBEN(Tabelle2!$C$6;0;0;ANZAHL(Tabelle2!$C:$C);1)

Markieren Sie im Diagramm die Datenreihe (Linie) und ändern Sie die Formel mit der Funktion DATENREIHE() in der Bearbeitungsleiste ab. Schreiben Sie den Bereichsnamen an Stelle des absoluten Bezuges:

=DATENREIHE(;Tabelle2!$A$6:$A$17;Tabelle2!KostenKumuliert;1)

Damit wird die Diagrammlinie nur bis zum letzten Eintrag gezeichnet, auch die Rubrikenachse passt sich automatisch an. Wenn Sie die Rubrikenachse vollständig zeichnen wollen, kopieren Sie eine Nullreihe in das Diagramm und entfernen die Linienfarbe dieser auf der Nulllinie gezeichneten Reihe.