Auflistung aller Excel-Tipps und -Tricks der ControllerSpielwiese

Der Einfachheit halber haben wir alle Excel-Tipps und -Tricks in dieser Liste vereinigt.
Sie können Sie somit zum Beispiel komplett ausdrucken und nach und nach ausprobieren.
 

Tipp 1: Speicherpfad und Dateiname in einer Zelle ausgeben

Geben Sie in eine beliebige Zelle die Formel =ZELLE("Dateiname") ein. Excel liefert Ihnen in dieser Zelle dann den kompletten Speicherpfad (Speicherort), den Namen der Datei und auch den Namen des aktuellen Tabellenblattes.
Die Funktion liefert nur ein Ergebnis, wenn es sich um eine zuvor bereits gespeicherte und keine neue Datei handelt. Der Name des Tabellenblattes wird nicht ergänzt, wenn es den selben Namen wie die Datei trägt und kein weiteres Tabellenblatt existiert. Nur den Tabellennamen können Sie mit Tipp 39 ausgeben.


 
Tipp 2: Anzeige von Nullwerten unterdrücken

Sie haben in einer Tabelle bereits vorbereitend Formeln für z.B. Folgemonate o.ä. einepflegt, welche nun mit Nullwerten erscheinen. Um die Übersichtlichkeit zu erhöhen können Sie die Anzeige der Nullwerte wie folgt vermeiden:

Wählen Sie den Befehl Optionen aus dem Menü Extras.
Wechseln Sie auf die Registerkarte Ansicht.
Deaktivieren Sie das Kontrollkästchen Nullwerte.
Bestätigen Sie Ihre Wahl mit OK.


 
Tipp 3: Zeilenumbruch in einer Zelle per Tastenkombination

Will man einen längeren Text in einer Zelle eingeben und einen Zeilenumbruch erzeugen kann man sich im Menü Format/Zellen/Ausrichtung bedienen. Cleverer und schneller ist jedoch an der entsprechenden Stelle im Text mit der Tastenkombination ALT + ENTER den Umbruch zu erzwingen, um sofort in der nächsten Zeile weiterschreiben zu können.
Das gleiche ist sehr hilfreich, wenn längere Begriffe für die Legende in Diagrammen verwendet werden, aber in der Darstellung zu viel Platz nehmen. Einfach in der originären Zelle den Begriff mit ALT + ENTER trennen und schon wird in der Legende auch entsprechend getrennt. Die Legende kann somit schmaler angezeigt und Platz gespart werden.


 
Tipp 4: Römische Zahlen anzeigen

Für das Konvertieren von arabischen in römische Zahlen hält Excel die Funktion RÖMISCH bereit. Soll z.B. eine arabische Zahl in der Zelle A2 in eine römische Zahl konvertiert werden, geben Sie in eine neue Zelle die Funktion =RÖMISCH(A2) ein.


 
Tipp 5: Zahlenwerte in mehreren Zellen in andere Formate umwandeln

Sollen z.B. Zahlenwerte in einer Tabelle statt in EUR in TEUR dargestellt werden, gehen Sie wie folgt vor:

Geben Sie in eine beliebige freie Zelle den Wert 1000 ein.
Kopieren Sie diese Zelle (z.B. mit Tastenkombination STRG + C)
Markieren Sie alle Zellen, deren Werte umgewandelt werden sollen.
Wählen Sie im Menü Bearbeiten den Befehl Inhalte einfügen...
Im folgenden Dialog wählen Sie die Option Werte und die Option Dividieren.
Bestätigen Sie Ihre Wahl mit OK.

Alle Werte wurden jetzt durch 1000 geteilt. Auf diese Art und Weise können Sie Werte auch addieren, subtrahieren und multiplizieren. Prozentuale Veränderungen können ebenso vorgenommen werden (* 1,x oder / 0,x).


 
Tipp 6: Benutzerdefinierte Seitennummerierung

Wenn Sie den Ausdruck eines Arbeitsblattes nicht mit der Seite 1 beginnen möchten, z.B. um die Seite(n) in eine bestehende Präsentation einzugliedern, ist es möglich eine benutzerdefinierte Seitennummer in der Fußzeile festzulegen:
 
Wählen Sie im Menü Datei den Befehl Seite einrichten... und dann die Registerkarte Kopfzeile/Fußzeile. Der Button Benutzerdefinierte Fußzeile öffnet die Bearbeitungsfelder der verschiedenen Abschnitte. Geben Sie direkt hinter die Definition der Seitennummer ("Seite &[Seite]") den Zusatz "+5 " an, wenn z.B. der Ausdruck mit Seite 6 beginnen soll. Beenden Sie die Dialoge mit OK, um die Änderung zu bestätigen. Hinweis: Der Zusatz des Leerzeichens nach der Seitenzahl ermöglicht das Verschieben um mehr als 9 Seiten, in dem Beispiel "Seite &[Seite]+15 " um mit Seite 16 zu beginnen.


 
Tipp 7: Datum per Ziffernblock eingeben

Wenn Sie ein Datum mit dem Ziffernblock eingeben wollen, fehlt Ihnen ja bekanntlich hier der Dezimalpunkt. Excel kann das Datum aber auch anders interpretieren:
 
Anstelle des Punktes können Sie bei der Eingabe auch das Divisionszeichen / oder das Subtraktionszeichen - verwenden. Aus 04/04/02 oder 04-04-02 macht Excel nach "Enter" automatisch 04.04.02. Um ein Datum des aktuellen Jahres am Ziffernblock einzugeben, reicht es auch schon aus, z.B. 04-04 einzugeben. Das Jahr wird automatisch ergänzt.


 
Tipp 8: Datum oder Uhrzeit per Tastenkombination eingeben

Oft will man in Excel schnell das aktuelle Datum in eine Zelle einfügen. OK, man könnte es einfach eintippen; manch einer bedient sich auch der HEUTE()-Funktion. Das eine dauert etwas und ist zumindest etwas umständlich, das andere hat den Haken, das Excel das Datum bei jedem Öffnen der Arbeitsmappe automatisch aktualisiert. Die HEUTE()-Funktion ist somit in manchen Situationen unbrauchbar, aber das automatische Einfügen des aktuellen Datums bekommen Sie auch anders hin. Um das aktuelle Datum schnell und konstant einzufügen, so dass auch an den kommenden Tagen noch der gleiche Wert angezeigt wird, können Sie die Tastenkombination Strg + . (Punkt) drücken (Steuerungstaste gedrückt halten und den ganz normalen Punkt eingeben). Excel fügt jetzt das aktuelle Datum als Wert in die betreffende Zelle ein und formatiert im Standardformat für Daten. Die Kombination der Tasten Strg + Shift + . trägt übrigens die aktuelle Uhrzeit ein.


 
Tipp 9: Zellinhalte in Abhängigkeit vom Wert formatieren - Bedingte Formatierung

Mit Hilfe der Bedingten Formatierung lassen sich Zellinhalte je nach Ihrem Wert beliebig optisch herausheben. Die Schrift sowie Zellrahmen und -hintergrund können dabei angepaßt werden.
 
Wählen Sie den zu formatierenden Zellbereich und dann den Befehl Bedingte Formatierung aus dem Menü Format.
Sie können jetzt Bedingungen mit logischen Operanden für die herauszuhebenden Werte definieren oder auch konkrete Werte bestimmen um dann mit dem Button Format in den folgenden drei Registerkarten die Zellformate festzulegen. Beispiele können sein Minuswerte rot, Sonntage kursiv, Werte > 1000 grün, bestimmte Namen blau oder was auch immer ...


 
Tipp 10: Text aus einer Zelle auf zwei (oder mehrere) Zellen aufteilen

Zellinhalte, welche aus mehreren Wörtern bestehen aber in verschiedenen Zellen weiterverarbeitet werden sollen, können in Hilfsspalten aufgeteilt werden. Stehen z.B. in Zelle A1 ein Vor- und ein Nachname, so ist die Aufteilung auf B1 und C1 wie folgt möglich:
 
Steht als Zellinhalt von A1 z.B. Joachim Becker so kann in der Zelle B1 mit der Formel =LINKS(A1;SUCHEN(" ";A1)-1) der Vorname Joachim und in der Zelle C1 mit der Formel =RECHTS(A1;LÄNGE(A1)-(SUCHEN(" ";A1))) der Nachname Becker dargestellt werden.
 
Die Funktion SUCHEN gibt als Formelergebnis die Position des Suchtextes (hier " ") wieder, welche für die linke bzw. rechte Beschneidung des Zellinhaltes von A1 benötigt wird. Tipp: Man kann mit dieser Formel auch prima Email-Adressen in die Texte vor und nach dem @-Symbol separieren.
 
Excel sieht mitlerweile aber auch einen Menüpunkt im Menü DATEN vor: "Text in Spalten". Im folgenden Fenster kann mit einer Vorschau nach verschiedenen Kriterien separiert werden...


 
Tipp 11: Texte aus 2 Zellen in einer Zelle zusammenfügen

Zellinhalte aus meheren Zellen können in einer Zelle zusammengefügt werden. Stehen z.B. in Zelle A1 ein Vorname und in B1 ein Nachname, so ist die Zusammenfassung in C1 wie folgt möglich:
 
Mit der Formel =A1 & " " & B1 können der Zellinhalt von A1 z.B. Joachim mit dem Zellinhalt Becker aus B1 in C1 zu Joachim Becker zusammengefügt werden. Alternative Formel mit dem gleichen Ergebnis: =VERKETTEN(A1;" ";B1) Mit " " wird das Leerzeichen zwischen den einzelnen Wörtern eingefügt. Mit dieser Möglichkeit können sehr gut Ergebnisse aus Tabellen in Überschriften o.ä. verarbeitet werden.


 
Tipp 12: Verketten von Zellen mit Darstellung von Formaten

Aufbauend auf Tipp 11 können auch die Formate von unterschiedlichen Zellinhalten beim Verketten mehrerer Zellen übernommen bzw. neue Formate zugewiesen werden. Steht z.B. in Zelle A1 ein Datum und in B1 ein weiterer beliebiger Text, so kann die Formel wie folgt lauten: =VERKETTEN(TEXT(A1;"TT.MM.JJJJ");B1)
Steht in A1 z.B. eine zweistellige Nachkommazahl, so kann diese mit B1 wie folgt verkettet werden: =VERKETTEN(TEXT(A1;"0,00");B1)


 
Tipp 13: Excel-Diagramm in Powerpoint einfügen und Verknüpfung aufrechterhalten

Um ein in Excel erstelltes Diagramm in eine Powerpoint-Präsentation zu übernehmen und dabei eine spätere automatische Aktualisierung von Änderungen aus Excel nach Powerpoint zu gewährleisten muss man zunächst das Diagramm in Excel markieren, dann kopieren und in Powerpoint danach nicht über Einfügen / Objekt, sondern über Bearbeiten / Inhalte einfügen ... / Link einfügen gehen und Microsoft-Excel-Arbeitsblatt-Objekt auswählen. So ist gewährleistet, dass die Verknüpfung funktioniert und die Daten jeweils auf Basis des zuletzt gespeicherten Standes der Excel-Tabelle in Powerpoint aktuell angezeigt werden.


 
Tipp 14: Zufallszahl zwischen zwei Grenzwerten erzeugen

Die Funktion ZUFALLSBEREICH liefert eine Zufallszahl zwischen einer unteren und einer oberen Grenze:
=ZUFALLSBEREICH(untereGrenze;obereGrenze)
Allerdings ist hierbei zwingend zu beachten, dass das Add-In "Analyse-Funktionen" installiert ist. Menü Extras / Add-Ins-Manager...
Ohne das beschriebene Add-In funktioniert dies auch über die Formel:
=ZUFALLSZAHL()*(obere Grenze-untere Grenze)+untere Grenze


 
Tipp 15: Verschachtelung von WENN-Funktionen erweitern

Da die Möglichkeiten einer verschachtelten WENN-Funktion ja bekanntlich begrenzt sind =WENN(A1=1;1;WENN(A1=2;1;WENN(...))), können mehrere WENN-Funktionen einfach addiert werden, um entsprechend viele Möglichkeiten abzudecken: =WENN(A1=1;1)+WENN(A1=2;2)+WENN(A1=3;3) ... Auch wenn es bessere, jedoch kompliziertere Lösungen gibt, so kommt doch der Laie hiermit schnell zu einem Ergebnis.
Bei Verwendung von Excel 2002 oder älter sollte die Formel folgendermaßen verändert werden:
=WENN(A1=1;1;"")&WENN(A1=2;2;"")&WENN(A=3;3;"")...


 
Tipp 16: Formate mehrfach übertragen

Mit dem Pinsel-Symbol aus der Menüleiste kann man bekanntlich Formate von zuvor markierten Zellen auf andere Zellen oder Zellbereiche übertragen. Wenn man ein bestimmtes Format mehrfach übertragen möchte kann man erneut auf das Pinsel-Symbol klicken oder aber noch einfacher: Ein Doppelklick auf das Pinsel-Symbol aktiviert dieses permanent und nacheinander kann nun das gewählte Format auf verschiedene Zellen übertragen werden ohne jedesmal auf dieses Symbol zu klicken. Ein Klick auf das Symbol deaktiviert den "dauerhaften" Pinsel schließlich wieder. Die Formatübertragung kann aber noch schneller mit der ESC-Taste beendet werden.


 
Tipp 17: Gefilterte Teilergebnisse kopieren

Gefilterte Teilergebnisse können nicht ohne weiteres in ein neues Tabellenblatt kopiert werden. Um wirklich nur die angezeigten Teilergebnisse und nicht alle Datensätze angezeigt zu bekommen, muss man folgendermaßen vorgehen:
Markieren Sie den sichtbaren/gefilterten Bereich und wählen Sie dann im Menü BEARBEITEN den Punkt GEHE ZU und klicken Sie im folgenden Dialogfenster auf die Schaltfläche INHALTE. Markieren Sie die Option „Nur sichtbare Zellen“ und klicken Sie auf OK. Jetzt können Sie die markierten Daten wie gewohnt kopieren und lediglich diese in einen anderen Bereich einfügen. Sollten Sie schon mit Excel2003 unterwegs sein, ist diese Vorgehensweise überflüssig, da es bereits im Programm direkt umgesetzt ist.


 
Tipp 18: Zellen mit Formeln oder bedingten Formatierungen suchen

In einer Tabelle möchten Sie sich alle Zellen anzeigen lassen in denen eine Formel steht oder die bedingt formatiert sind. Wählen Sie dazu im Menü BEARBEITEN den Punkt GEHE ZU und klicken Sie im folgenden Dialogfenster auf die Schaltfläche INHALTE. Markieren Sie die Option "Formeln" und klicken Sie auf OK. Jetzt sind alle Zellen in denen eine Formel steht markiert. Analog gehen Sie vor um sich Zellen mit bedingter Formatierung anzeigen zu lassen. Hier wählen Sie dann die Option "bedingte Formate".


 
Tipp 19: Gitternetz für Präsentationen ausblenden

Das Hintergrundgitter in Excel leistet als Orientierungshilfe einen ausgezeichneten Dienst. Will man die Inhalte einer Tabelle aber z.B. mit einem Beamer vorführen, kann das Gitternetz von Excel stören. Um das Gitter auszublenden, wählen Sie unter EXTRAS/OPTIONEN das Register ANSICHT. Entfernen Sie den Haken vor GITTERNETZLINIEN und bestätigen Sie mit OK. Setzen Sie den Haken wieder, um das Gitter anzuzeigen. Unter dieser Option finden Sie übrigens eine Auswahlliste FARBE. Damit können Sie das Gitternetz anders einfärben, was in Kombination mit farbigen Linien in der Tabelle für weitere Visualisierungsmöglichkeiten sorgen kann.


 
Tipp 20: Alle Formeln mit shortcut ansehen

Wenn Sie sich in Excel alle Formeln, die im aktuellen Datenblatt vorhanden sind ansehen möchten, können Sie dies durch die Tastenkombination STRG + # erledigen. Durch nochmaliges Drücken dieser Kombination ist die normale Ansicht wieder vorhanden.


 
Tipp 21: Ausschnitt einer Tabelle als Grafik weiterverarbeiten

Wollen Sie beispielsweise einen Ausschnitt aus einer Excel-Tabelle als Grafik in eine Präsentation oder an anderer Stelle in eine Excel-Tabelle einfügen gehen Sie folgendermaßen vor: Markieren Sie einfach den gewünschten Bereich, drücken die UMSCHALT-Taste, halten diese gedrückt und wählen aus dem Menü BEARBEITEN den Befehl BILD KOPIEREN. Dieser Befehl wird nur aktiviert, wenn Sie die UMSCHALT-Taste gedrückt halten! Durck diesen Trick wird der Standardbefehl KOPIEREN, der normalerweise an dieser Stelle im Menü steht durch diesen neuen Befehl ersetzt. Die Grafik kann nun bequem in jeder anderen Anwendung weiterbearbeitet werden!


 
Tipp 22: Mehr als 7 Wenn-Verschachtelungen

Wenn Sie eine Wenn-Abfrage starten wollen, welche mehr als 7 Wenn´s beinhaltet, so müssen Sie diese nach den ersten 7 Wenn mit einem + oder einem & verbinden. Folgend ein einfaches Beispiel:
=WENN(B2=1;1;WENN(B2=2;2;WENN(B2=3;3;WENN(B2=4;4;WENN(B2=5;5;WENN(B2=6;6;WENN(B2=7;7)))))))+WENN(B2=8;8;WENN(B2=9;9;WENN(B2=10;10;)))


 
Tipp 23: Eingabe von Brüchen in einer Zelle

Haben Sie schon einmal probiert einen Bruch z.B. 1/3 in eine Zelle einzugeben? Excel interpretiert dies als ein Datum - nämlich den 01. März! Um nun statt des Datums einen Bruch angezeigt zu bekommen gehen sie folgendermaßen vor: Klicken Sie im Menü FORMAT den Befehl ZELLEN. Gehen Sie nun auf das Blattregister ZELLEN und wählen Sie aus dem Listenfeld KATEGORIE den Eintrag BRUCH aus. Nun wählen Sie im angezeigten Listenfeld FORMATE den Eintrag EINSTELLIG aus. Nun bekommen Sie den Eintrag als Bruch angezeigt.


 
Tipp 24: Zahl der Rückgängigschritte auf 100 erhöhen

Beim Aufstellen komplexer Formeln machen Sie nach fehlerhaften Versuchen häufig zahlreiche Arbeitsschritte über das Menü rückgängig. Excel merkt sich die letzten 16 Schritte, die reichen aber leider oft nicht aus. Sie können diesen Zwischenspeicher auf 100 Schritte erhöhen. Dazu ist die Anpassung eines Registry-Eintrags notwendig. Klicken Sie dazu auf Start und danach auf Ausführen. Hier geben Sie die Anweisung "regedit" ein. Navigieren Sie im Registry-Editor zu dem Schlüssel: HKEY-CURRENT_USER\Software\Microsoft\Office\9.0\Excel\Options
Dies gilt für Excel 2000. Für Excel XP steht die Versionsnummer 10.0 und für Excel 2003 11.0. Jetzt suchen Sie im rechten Teil des Fensters den DWORD-Wert "UndoHistory". Sofern er nicht vorhanden ist, erzeugen Sie ihn mit "Bearbeiten", "Neu", "DWORD-Wert". Nun klicken Sie doppelt auf diesen Eintrag, aktivieren im Dialog die Option "Dezimal". Jetzt tragen sie den Wert 100 ein. Abschließend bestätigen Sie den Dialog mit "OK" und schließen die Registry. Ab dem nächsten Start merkt sich Excel 100 Arbeitsschritte.


 
Tipp 25: Zellwert in Textfeld anzeigen

Wenn Sie beispielsweise eine Zahl besonders hervorheben wollen, ist es praktisch wenn Sie nicht an die Begrenzung von Zeilen und Spalten gebunden sind. Dies funktioniert mit einem Textfeld. Ein Textfeld hat den Vorteil, dass es unabhängig von Zeilen und Spalten formatiert werden kann.
Sie können den Inhalt des Textfeldes flexibel gestalten, indem Sie den Bezug auf eine Zelle setzen, in der sich der Inhalt verändert. Erzeugen Sie zuerst ein Textfeld: Sie finden die Schaltfläche in der "Zeichnen" Symbolleiste. Das Textfeld hat zunächst einen schraffierten Rahmen. Klicken Sie nun mit der Maus auf den Rahmen, so dass dieser gerastert angezeigt wird. Jetzt können Sie in die Bearbeitungszeile klicken. Dort geben Sie ein "=" - Zeichen ein und klicken auf die Zelle die den variablen Wert enthält; damit wird der Zellbezug erzeugt. Das Textfeld lässt sich jetzt frei auf der Tabelle verschieben und in der Größe anpassen.


 
Tipp 26: Variable Bezüge mit INDIREKT

Der Befehl INDIREKT erzeugt aus einem (variablen) TEXT Bezüge auf Zellen und Bereiche. Beispiel:
Es soll ein Wert ausgegeben werden, der in Spalte "C" steht, aber dessen Zeilenbezug variabel ist. Die Zeilennummer steht als (variabler) Wert in Zelle A1 (z. B. 14)
anstelle des FIXEN Bezuges
=C14
lautet der VARIABLE Bezug
=INDIREKT(VERKETTEN("C";A1))
Der Blattname (und Dateiname) könnte ebenfalls als Parameter in die Formel integriert werden:
=INDIREKT(VERKETTEN("'";A2;"'!";"C";A1))
(wobei in der Zelle A2 der Blattname stehen müsste) Analog können mit dieser Methode auch variable Bezüge auf ZELLBEREICHE generiert werden.
(eingesandt von Earl.Fred)


 
Tipp 27: Werte in anderen Tabellen (mit SVERWEIS) suchen

Über den Befehl SVERWEIS lassen sich Werte aus anderen Tabellen mit Hilfe eines Suchwertes auffinden:
SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
Suchkriterium: Der Suchwert, z. B. eine Variable oder ein Wert
Matrix: Die Tabelle bzw. der Bereich, aus dem mit Hilfe des Suchkriteriums Werte ausgelesen werden sollen. Die erste Spalte der Matrix muss zwingend die Spalte sein, in der das SUCHKRITERIUM gesucht werden soll.
Spaltenindex: Ist die Spalte, bei 1 beginnend zu zählen, in der der gewünscht Wert steht. Eingabe als Wert oder Variable möglich.
Bereich_Verweis: Gibt an, ob das Suchkriterium GENAU erfüllt sein muss (Bereich_Verweis=falsch) oder UNGEFÄHR (Bereich_Verweis=wahr oder leer).
Am besten zieht man anfangs den Funktions-Assistenten von Excel zu Rate. Voraussetzung ist noch, dass das Suchkriterium in der Matrix EINDEUTIG ist (d. h. wirklich GENAU EINMAL in der zu durchsuchenden Spalte vorkommt), da ansonsten nur der zuerst gefundene Bezugswert zurückgegeben wird. Wird nicht in Zeilen, sondern in Spalten gesucht, funktioniert dies analog mit dem Befehl WVERWEIS.
(eingesandt von Earl.Fred)


 
Tipp 28: Zwei Tabellenbereiche automatisch vergleichen

Wollen Sie vollautomatisch die Werte in zwei Tabellenbereichen vergleichen und die Zellen mit Unterschieden automatisch markieren? Angenommen Sie haben ein Budget erstellt. Die Ist-Werte wurden ebenfalls in eine Tabelle mit gleichem Aufbau wie das Budget eingetragen. Wenn Sie jetzt die Zellen markieren wollen bei denen das Budget überschritten wurde, gehen Sie folgendermaßen vor:
Markieren Sie den Bereich mit den Ist-Zahlen, und öffnen Sie das Menü "Bedingte Formatierung".
In der ersten Liste klicken Sie auf den Eintrag "Zellwert ist", und markieren in der zweiten Liste "größer als".
In das letzte Feld geben Sie eine Formel mit einem Bezug zur linken oberen Zelle des Bereiches mit Budget-Zahlen ein. Für unser Beispiel soll das die Zelle B3 sein. Die Formel lautet dann so: "=Budget!B3". Der Budgetposten müsste also in einer Tabelle "Budget" in der Zelle "B3" liegen.
Klicken Sie nun auf Format und legen Sie das Format fest, mit dem Excel die Zellen mit überschrittenen Budgetbeträgen hervorheben soll (z. B. Schriftfarbe rot).
Nachdem Sie mit OK bestätigt haben, hebt Excel jetzt die Zellen hervor, deren Wert größer ist als in der vergleichbaren Zelle im Budgetbereich.
Diese Technik lässt sich auch für die Prüfung anderer Bedingungen einsetzen. Voraussetzung ist das die zu vergleichenden Bereiche gleich aufgebaut sind. Sie müssen den Vorgang übrigens nicht wiederholen wenn sich die Werte ändern!


 
Tipp 29: Variabler Diagrammtitel

Der Titel eines Diagramms wird ja bekanntlich beim Erstellen eines Diagrammes mit dem Assistenten festgelegt. Um diesen bequem nachträglich in Abhängigkeit einer bestimmten Zelle ändern zu können, bauen Sie folgende Funktion ein: Klicken Sie im Diagramm auf den Diagrammtitel, so dass dieser markiert ist. Nun klicken Sie in die Bearbeitungszeile, tippen ein "="-Zeichen ein und klicken anschließend auf die Zelle, deren Inhalt im Diagrammtitel angezeigt werden soll. Der Bezug erscheint jetzt in der Bearbeitungszeile. Schließen Sie die Eingabe mit der "Enter"-Taste ab. Im Titel wird nun der Eintrag der entsprechenden Zelle angezeigt. Wenn Sie jetzt deren Zellinhalt ändern, ändert sich auch der Titel Ihres Diagrammes.


 
Tipp 30: Änderungen im neuen Excel 2007

Die neue Office Version und das neue Excel 2007 kommen auf den ersten Blick ganz anders daher. Für den Umsteiger beschreiben wir einige wichtige Änderungen, um die Neuerungen schneller zu erschließen - wie gesagt, einige für den Einstieg in die neue Version ...! Sollten Sie weitere Hinweise ergänzen wollen, schreiben Sie uns unter excel@controllerspielwiese.de und wir fügen Ihre Ergänzungen hier an!
zum Dokument


 
Tipp 31: Datumsreihe erstellen - aber ohne Daten der Wochenenden

Angenommen Sie möchten für Wochenübersichten oder eine Projektplanung Datumslisten ohne die Wochenenden erstellen, dann ist auch das mit Excel schnell zu erstellen:
Geben Sie zunächst das Startdatum ein, z.B. "02.07.2007" und lassen Sie die Zelle markiert. Ziehen Sie dann mit gedrückter rechter Maustaste am Ausfüllkästchen nach unten oder nach rechts. Wenn Sie die Maustaste los lassen erscheint nun das Kontextmenü mit 2 interessanten Einträgen. Klicken Sie dort auf den Befehl "Wochentage ausfüllen" und in Ihrem markierten Zellbereich werden nun die Daten der Wochentage eingetragen unter Aussparung der Wochenenden. Weiterhin können Sie jetzt z.B. auch den entsprechenden Wochentag, hier z.B. "Montag" daneben schreiben und die Namen der Wochentage auf dem gleichen Wege ergänzen. Wollen Sie alle 7 Wochentage funktioniert es übrigens auch mit dem automatischen Ausfüllen; hierzu nur den Eintrag "Tage ausfüllen" aus dem gleichen Kontextmenü selektieren.


 
Tipp 32: Formeln durch Namensvergabe übersichtlicher gestalten

Bei der Arbeit mit größeren Tabellen oder komplizierten Formeln werden die eingegebenen Bezüge durch die Koordinaten- oder Bereichsangaben in Formeln schnell unübersichtlich. Häufig liefert eine Formel wie beispielsweise A1*D5*F4+D4/G2*F7, kaum noch einen Rückschluss auf das, was eigentlich berechnet wird. Zur Verbesserung der Übersichtlichkeit und zur Vereinfachung der Formelerstellung können Sie jedoch Zellen und Zellbereiche mit Namen versehen und die vergebenen Namen in den Formeln nutzen.
Um einer Zelle oder einem Bereich einen Namen zu geben gehen Sie wie folgt vor:
1. Markieren Sie die Zelle oder den Bereich, die bzw. der benannt werden soll.
2. Klicken Sie in das Namensfeld an der linken Seite der Bearbeitungsleiste, und geben Sie den gewünschten Namen ein.
3. Bestätigen Sie Ihre Eingabe mit Return.
Nun können Sie diesen Namen in Formeln verwenden. Stehen z.B. in der Spalte B2 bis B5 lauter Einzelkosten, geben Sie um die Summe zu berechnen als Bereich B2:B5 ein. Haben Sie diesen Bereich vorher in Einzelkosten umbenannt, sieht die Formel so aus: Summe(Einzelkosten).


 
Tipp 33: Zellinhalte und Formatierungen für mehrere Tabellen gleichzeitig festlegen

Das man einen Zellinhalt in mehrere Tabellen gleichzeitig eingeben kann, indem man mit STRG und Mausklick (links) die entsprechenden Tabellen auswählt, ist schon weitgehend bekannt. Der dann in eine Zelle eingegebene Wert befindet sich danach in jeder Tabelle in der gleichen Zelle. Sie können auf diese Weise aber auch schnell und einfach Formatierungen an Ihren Tabellen durchführen und sogar die Kopf- und Fußzeile für alle so ausgewählten Tabellen gleichzeitig benutzerdefiniert einstellen.


 
Tipp 34: Mit WAHL-Funktion verschachtelte WENN-Formeln umgehen

Stellen Sie sich folgende Aufgabe vor: Sie wollen die geplante Absatzmenge eines Kalendermonats anhand des jeweils aktuellen Tagesdatums ermitteln. Die 12 Absatzmengen stehen in der Regel in 12 angrenzenden Zellen. Mit der WENN-Funktion ließe sich das Beispiel nur über verschachtelte WENN-Funktionen lösen. Da die Funktion normalerweise maximal 7 Verschachtelungen zulässt, hier aber insgesamt 12 Möglichkeiten (Monate) zu berücksichtigen sind, wird es mehr als nur schwierig. Mit Hilfe der WAHL-Funktion bieten sich dagegen bis zu 29 Wahlmöglichkeiten und hierfür eine Lösung.
Bei der Formel WAHL(MONAT(A5);B2;C2;D2;E2;F2;G2;H2;I2;J2;K2;L2;M2) nehmen wir an, dass in der Tabelle die geplanten Absatzmengen in den Zellen B2 bis M2 stehen und anhand des aktuellen Tagesdatums (15.03.2008) in A5 die Menge des Monates März heraus gelesen werden soll. Die MONAT-Funktion ermittelt die Zahl 3 (für März), daraufhin selektiert die WAHL-Funktion den 3. Wert aus seiner Wertauflistung und gibt den Inhalt der Zelle D2 wieder.


 
Tipp 35: Mit Zeitangaben rechnen

Das Multiplizieren von Zeitangaben stellt in Excel eine besondere Berechnungsart dar. Zum Beispiel beim Berechnen von Telefonkosten kann dies nützlich sein. Gehen Sie wie folgt vor:
Wählen Sie "Zellen formatieren"und dann "Uhrzeit" aus. Addition und Subtraktion funktioniert so problemlos. Schwieriger gestaltet sich die Multiplikation. Im Beispiel soll der Preis für ein 10-minütiges Telefonat vom Festnetz ins Handy-Netz berechnet werden. Hierbei liegt der Minutenpreis bei 12 Cent, also 0,12 Euro. Es entsteht eine Fehlermeldung, wenn der Anwender einfach =00:10*0,12 in ein Tabellenfeld eingibt. Um keine Fehlermeldung zu erhalten, muss in die Zelle eingegeben werden: =('00:10')*60*24*0,12. Manche Programm-Versionen erwarten noch eine zusätzliche Angabe, hier muss die Berechnung wie folgt definiert werden: =ZEITWERT('00:10')*60*24*0,12. Die Anführungsstriche rund um die Minutenzahl in der Stundendarstellung sind besonders wichtig. Indem der Zeitwert mit 60 (steht für die Minuten einer Stunde) und 24 (steht für die Stunden eines Tages) multipliziert wird, wird die interne Datenverarbeitung im Programm berücksichtigt, der daraus entstehende Wert ist im Beispiel 10. Die Multiplikation mit 0,12 gibt den Minutenpreis in Euro an, der Preis für das Beispiel-Telefonat liegt demnach bei 1,20 Euro.


 
Tipp 36: Kommentare direkt in Formeln schreiben

Hatten Sie auch schon das Problem, dass Sie nicht mehr wußten was eine von Ihnen erstellte Formel genau berechnet? Toll wäre es, wenn Sie eine Formel mit einem kurzen Kommentar als Gedächtnisstütze versehen könnten. Das ist in Excel leider generell nicht vorgesehen oder nur durch einen extra zu setzenden Kommentar möglich, aber es gibt noch einen Trick:
Benutzen Sie dafür die Funktion N(). Dabei handelt es sich um eine eigentlich obsolete Funktion, die in der Praxis nicht benötigt wird und nur noch aus Kompatibilitätsgründen vorhanden ist. N() wandelt Zellinhalte in Werte um. Dabei werden Texte, die nicht als numerischer Wert interpretierbar sind, auf den Wert 0 gesetzt. In der Arbeitsmappe selbst erscheint dabei nur der berechnete Wert, der Text wird nur in der Eingabeleiste angezeigt.
Der Trick besteht nun darin, den Kommentar mit +N("Hier tragen Sie den Kommentar ein") anzuhängen. N() liefert hier den Wert 0, eine Addition von 0 hat keine Auswirkung auf das von Excel berechnete Ergebnis und verändert damit auch nicht die Anzeige der jeweiligen Zelle. Wählen Sie die Zelle dagegen aus, sehen Sie in der Eingabezeile sowohl die Formel als auch den Kommentar.
Ein einfaches Beispiel. Statt =5*13 können Sie auch = 5*13 + N("Hier werden zwei Werte multipliziert") benutzen, ohne das Ergebnis der Berechnung durch den Kommentar zu beeinflussen.


 
Tipp 37: Pivot-Tabelle: Datenbereich dynamisch anlegen

Beim Erstellen einer Pivot-Tabelle fragt ein Dialogfeld, welche Daten man analysieren will. Dort muss man dann den Bereich eingeben, z.B. $A$1:$E$50. Damit ist ein fester Bereich festgelegt. Nachteilig ist dies, wenn die der Pivottabelle zugrunde liegende Tabelle in der Größe (mehr/weniger Spalten/Zeilen) schwankt: Es muss immer wieder manuell eingegriffen werden. So schaffen Sie Abhilfe:
Die Lösung besteht darin dem Datenbereich der Tabelle einen Namen zuzuweisen und den Bereich über eine Formel zu dynamsieren.
1. Sie befinden sich im Tabellenblatt, in dem Ihre Daten liegen, die Grundlage Ihrer späteren Pivot-Tabelle
2. Hier gehen Sie über Menü/Einfügen/Namen/Definieren...
3. Geben Sie nun den gewünschten Namen (z.B. Datenbasis) in das Dialogfeld "Namen in der Arbeitsmappe" ein
4. Geben Sie in das Feld "bezieht sich auf" folgende Formel ein:
=BEREICH.VERSCHIEBEN($A$1;;;ANZAHL2($A:$A);ANZAHL2($1:$1))
Sie müssen berücksichtigen, dass die Überschriftenzeile mit in den Bereich fallen muss, in unserem Formelbeispiel steht die erste Datenüberschrift in A1.
Zum Abschluss klicken Sie auf den Button "Hinzufügen" und anschließend schließen Sie das Dialogfenster durch Klick auf OK. Excel fügt den Tabellenblattnamen automatisch der Formel hinzu. Ab nun deckt der vergebene Name immer den passend großen Bereich Ihrer Tabelle ab und zwar in Bezug auf Spalten und Zeilen. (eingesandt von Christian S.)


 
Tipp 38: Mit negativen Uhrzeiten rechnen

Wollen Sie z.B. bei einer Zeiterfassung die Ist mit den Soll Stunden vergleichen und es ergibt sich daraus ein negativer Wert (z.B. 7:00 Uhr - 8:00 Uhr), zeigt Ihnen Excel nur Rauten an. Um das richtige Ergebnis (hier -1:00) angezeigt zu bekommen, gehen Sie wie folgt vor: Gehen Sie auf Extras -> Optionen -> Berechnungen. Hier wählen Sie dann bei den Arbeitsmappenoptionen "1904-Datumswerte" aus. Jetzt können Sie auch mit negativen Uhrzeiten rechnen.


 
Tipp 39: Registername in einer Zelle ausgeben

Wie Sie den Speicherort bzw. den kompletten Speicherpfad mit Dateiname und Registerblatt in einer Zelle ausgeben steht bereits in Tipp 1. Folgendermaßen können Sie hiervon nur den Registernamen anzeigen: Geben Sie in eine beliebige Zelle die Formel  =TEIL(ZELLE("Dateiname");SUCHEN("]";ZELLE("Dateiname"))+1;31) ein. Excel liefert Ihnen in dieser Zelle aufgrund ZELLE("Dateiname") zwar zunächst den ganzen Speicherpfad incl. dem Namen der Datei und auch dem Namen des aktuellen Blattes, nimmt allerdings durch den TEIL-Zusatz wieder den nicht darzustellenden ersten Teil weg. Dabei ist 31 die maximale Anzahl der Stellen, mit der ein Registerblatt (zumindest) bis Excel 2002 tituliert werden kann.


 
Tipp 40: Markierung nach Drücken der Eingabetaste verschieben

Wenn Sie in Excel die Return-Taste drücken, springt die Zellmarkierung in der Grundeinstellung von Excel automatisch nach unten in die nächste Zeile. Manchmal kann es jedoch vorteilhaft sein, dies zu ändern, beispielsweise wenn Sie eine Formel aufbauen und noch weiter testen wollen oder auch wenn mehrere Dateneingaben in einer Zeile erfolgen sollen. Die Richtung der Verschiebung der Zellmarkierung können Sie u.a. über die Excel-Optionen verändern.
In Office 2007 öffnen Sie das Fenster "Excel-Optionen" über die Schaltfläche "Office". Klicken Sie in der linken Seite auf den Befehl "Erweitert" und setzen Sie auf der rechten Seite bei "Markierung nach Drücken der Eingabetaste verschieben" ein Häkchen, in dem Sie das Feld anklicken. Dann können Sie im Listenfeld die gewünschte Richtung wählen.
In vorhergehenden Office Versionen erledigen Sie dies indem Sie über das Menü "Extras/Optionen" das Register "Bearbeiten" auswählen. Dort stehen die gleichen Möglichkeiten zur Verfügung.
Damit die Zellmarkierung nach dem Drücken der Eingabetaste auf der gleichen Stelle bleibt, entfernen Sie das Häkchen vor dem Eintrag "Markierung nach drücken der Eingabetaste verschieben". Das ist dann praktisch, wenn Sie Formeln erstellen und anschließend weiter testen wollen oder die gleiche Zelle noch weiter formatieren möchten.
Eine andere Möglichkeit die Markierung nach der Eingabe in die gewünschte Richtung springen zu lassen sind die Pfeiltasten. Wenn Sie zum Abschließen der Eingabe anstelle der Return-Taste die Pfeiltasten verwenden, springt die Zellmarkierung in die Richtung der jeweils gewählten Pfeiltaste.


 
Tipp 41: Teilergebnisse von Formeln anzeigen

Oftmals nehmen die Formeln in Excel beachtliche Ausmaße an und sind mehrfach verschachtelt bzw. eine lange Reihenfolge von Rechenoperationen - da ist es oft schwierig den Überblick zu behalten, welche Teilergebnisse einzelne Teile der Formel aktuell liefern. Mit einem kleinen Trick ist es jedoch möglich, das Teilergebnis einzelner Operationen direkt in der Bearbeitungsleiste ablesen zu können:
Beispielformel: =(A1+A2)/(B1-B2)
Sie wollen nun wissen, was (A1+A2) ergibt? Wenn Sie in der Bearbeitungsleiste den Formelbereich (A1+A2) markieren, so dass dieser schwarz hinterlegt ist und die Taste F9 drücken erscheint der entsprechende Wert direkt an der Stelle in der Klammer. Drücken Sie jetzt die Enter-Taste ist die Formel durch den Wert ersetzt, mit der ESC-Taste erscheint wieder die ursprüngliche Formel. Dies klappt natürlich mit allen Teil- und auch dem Gesamtergebnis - passen Sie nur auf, dass Vorzeichen, Klammern und ähnliches vollständig in der Markierung enthalten sind um keine falschen Teilergebnisse angezeigt zu bekommen.


 
Tipp 42: Rot-Grün-Invertierung

Wenn die in einem Diagramm darzustellenden Datenpunkte positiv und negativ sind, werden sie in der Regel trotzdem in der von Excel gewählten Farbe angezeigt. Es ist jedoch möglich, die positiven Datenpunkte z.B. grün und die negativen rot darzustellen, ohne die einzelnen Punkte manuell ändern zu müssen. Mit einem Rechtsklick auf eine Datenreihe und der Auswahl "Datenreihen formatieren" kann im angezeigten Dialogfenster das Kontrollkästchen "Invertieren falls negativ" aktiviert werden. Dabei werden die negativen Datenpunkte allerdings immer weiss dargestellt. Um jetzt die negativen Datenpunkte aber andersfarbig darzustellen kann wie folgt vorgegangen werden:
 
In Excel 97 und 2003 ist nach dem aktivieren des Kontrollkästchen "Invertieren falls negativ" die Schaltfläche "Fülleffekte" zu wählen und in der Registerkarte "Muster" sind ein x-beliebiges Muster, dann als Vordergrund "Rot" und als Hintergrund "Grün" zu selektieren. Mit Schaltfläche "OK" jetzt alle Fenster schließen und erneut Rechtsklick auf die Datenreihe, "Datenreihen formatieren" und in der Registerkarte "Muster" "Grün" auswählen. Mit der Schaltfläche "OK" den Vorgang abschließen.


 
Tipp 43: Anzahl Werte gemäß Bedingung zählen

Wenn Sie in einer Datentabelle mit vielen Datensätzen die Anzahl von Zellen zählen wollen, geht das leicht mit der Funktion ANZAHL, welche berechnet, wieviele Zellen in einem Bereich Zahlen enthalten oder mit ANZAHL2, welche die nicht leeren Zellen zählt. Möchten Sie jedoch die Anzahl der Zellen mit bestimmten Werten gemäß einer Bedingung, wie z.B. ungleich Null zählen, steht Ihnen hier die Funktion ZÄHLENWENN zur Verfügung. Sollen also z.B. in einem Bereich A1:A9999 die Anzahl der Zellen mit Werten größer Null gezählt werden sieht die Funktion folgendermaßen aus: =ZÄHLENWENN(A1:A9999;">0"). In den Anführungsstrichen können Sie das Suchkriterium logisch definieren und somit so ziemlich alles zählen...


 
Tipp 44: Datentabellen schnell auf Plausibilitäten prüfen

Eine Aufgabe des Controllers zum Monatsabschluss ist sicherlich das Auswerten von Datenbanken wie z.B. Bestand oder Forderungen zum Stichtag sowie Umsatz oder Materialaufwand im Zeitraum. Oftmals sind dies aufgrund vieler Geschäftsfälle große Datentabellen. Um diese möglichst schnell auf Plausibilität zu prüfen steht mit dem Autofilter ein mächtiges Werkzeug zur Verfügung, welches für ausgewählte Kriterien die entsprechenden Werte anzeigt und Plausibiltätsprüfungen somit vereinfacht. Für eine optimale Verarbeitung markieren Sie die jungfräuliche Tabelle incl. der Kopfzeilen und wählen im Menü "Daten / Autofilter". Als nächstes empfiehlt es sich einen beliebigen Filter per Klick auf ein Kästchen mit dem Pfeil zu setzen und im gefilterten Zustand unter der Tabelle für die notwendigen Spalten/Werte Auto-Summen "=SUMME(...)" zu setzen. Excel wendet aufgrund der Filterung gleich anstatt "=SUMME(..." das "=TEILERGEBNIS(..." an, was für die späteren Auswertungen von Nutzen ist. Die Summenzeile sollte auch immer mit Abstand einer leeren Zeile zu den Daten gewählt werden und die leere mit einbeziehen, was das nachträglich Einfügen von Zeilen erleichtert, da diese automatisch mit einbezogen werden. Wer immer auch das Gesamtergebnis im Blick haben möchte fügt eine weitere Summenzeile unter dem Teilergebnis ein. Dann kann das Filtern auf Plausi beginnen. Häufig werden Daten nicht korrekt eingegeben, oder Bezeichnungen sind fehlerhaft, Werte sind Null, negativ oder leere Zeilen stören in der Tabelle. Ein erster Anhaltspunkt kann oftmals bereits in dem Filterauswahlfeld gewonnen werden, wo auffällige Daten wie z.B. #NV oder auch negative Vorzeichen erkennbar sind. Mit dem gesetzten Filter kann der Ursache nachgegangen werden. Für bestimmte Datenbereiche steht über "Benutzerdefiniert" die Möglichkeit der Eingabe von mehreren Kriterien zur Verfügung wie z.B. zum Filtern von Kundennamen oder Produktbezeichnungen beginnend mit bestimmten Buchstaben (Ku*) oder Werten über/unter bestimmten Grenzen. Leere Zellen können leicht mit dem Eintrag "Leere" gefiltert und dann durch Markierung der Zeilenköpfe im Kontektmenü (rechte Maustaste) über "Zellen löschen" entfernt werden. Es empfielt sich oftmals auch den Tabellenbereich für den Druck zu formatieren: Beim Drucken werden nur die Zeilen der Datenbank ausgedruckt, die beim Filtern sichtbar sind. So lassen sich sehr einfach Ausdrucke nach verschiedenen Kriterien erzeugen. Die Werte eines Lagerortes, Forderungen > 60 Tage, bestimmte Kunden, Produkte als einfacher Bericht oder auch Unstimmigkeiten zur Weitergabe und Datenpflege durch die Kollegen. Die Filterung kann mit dem Eintrag "Alle" wieder aufgehoben.


 
Tipp 45: Unerwünschte Leerzeichen entfernen

Beim Import von Daten aus anderen Anwendungen oder auch während der "Zerlegung" von längeren Feldinhalten kann es vorkommen, dass unerwünschte Leerzeichen hinter einer Information bei der weiteren Verarbeitung Fehler erzeugen. Diese Leerzeichen können mit der Funktion =GLÄTTEN entfernt werden. Somit kann ein eindeutig vergleichbarer Text erzeugt bzw. ein Zellwert für Rechenoperationen generiert werden. Um z.B. in A1 einer Zellinformation die nachstehenden Leerzeichen abzuschneiden verwenden Sie =GLÄTTEN(A1). Soll mit den Informationen/Werten weitergerechnet werden, empfiehlt es sich zur Sicherheit die anschließende Multiplikation mit 1 durchzuführen. Somit wird eine importierte Information von Excel als Zahl erkannt. Die Funktion sieht dann wie folgt aus: =GLÄTTEN(A1)*1 Als unerwünscht werden alle Leerzeichen angesehen, die am Anfang oder am Ende stehen. Die Anzahl der Leerzeichen zwischen zwei Wörtern wird auf eins reduziert, falls sich dort mehrere befinden sollten. Es empfiehlt sich bei einer solchen Operation in den Zellen rechts der Textzellen die Funktion zu erstellen und dann nach erfolgter Glättung die rechten Zellen auf die linken zu übertragen / überschreiben.


 
Tipp 46: Minus hinter einer Zahl in echtes Minus umwandeln

Beim Import von Daten aus anderen Anwendungen kann es vorkommen, dass das Minuszeichen hinter einem Wert angezeigt wird (123-). Da Excel nur mit dem Minus vor dem Wert (-123) rechnen kann, muss die Zellinformation entsprechend bearbeitet werden. Sollen ganze Spalten mit mehreren tausend Werten angepaßt werden, haben diese Werte meist eine unterschiedliche Anzahl von Stellen, was zu berücksichtigen ist. Mit einer einfachen Verbindung von WENN, RECHTS, LINKS und LÄNGE läßt sich eine solche Umsetzung leicht realisieren. Die Formel, für eine Bearbeitung eines Wertes in C2 lautet wie folgt: =WENN(RECHTS(C2;1)="-";-LINKS(C2;LÄNGE(C2)-1)*1;C2)
Hierbei wird:"Wenn rechts ein Minus, dann die Länge des Wertes ohne das Minus, sonst der Wert in C2" ausgegeben. Die obligatorische Multiplikation mit 1 führt eine etwaige Text-Information in einen Wert über, mit dem Excel weiterrechnen kann.


 
Tipp 47: Überwachung von Zellen mit Kamera-Funktion

Neben dem Überwachungsfenster der "Formelüberwachung", welche unter "Extras" zu finden ist, besteht mit der "Kamera"-Funktion eine recht einfache Möglichkeit zur sofortigen Einsicht von Auswirkungen bei sich ändernden Ergebnissen in anderen Datenblättern. Dies kann sehr günstig sein, wenn mehrere Arbeitsmappen und große Tabellenblätter mit vielen Verknüpfungen verarbeitet werden, da es das viele hin- und herklicken erübrigt. Die "Kamera"-Funktion bietet auch noch einige Vorteile gegenüber der Formelüberwachung: Es können mehrere angrenzende Zellen schnell und übersichtlich einbezogen werden, Daten aus anderen Dateien mit integriert und auch sämtliche Formate mit angezeigt werden. Da die Kamera nicht über die Standard-Menüleiste erreichbar ist, empfiehlt es sich, sie in die Symbolleiste aufzunehmen. Bis Excel 2003 über "Ansicht", "Symbolleisten", "Anpassen ..." und im Register "Befehle" in der Kategorie "Extras" die Kamera selektieren. In Excel 2007 die Symbolleiste für den Schnellzugriff anpassen - "weitere Befehle", "alle Befehle" zeigt alphabetisch gelistet die Kamera. Mit dem integrierten Symbol kann sodann die zu überwachende Zelle zunächst markiert werden. Ein Klick auf das Kamera-Symbol selectiert dann diese Zelle und der folgende Klick in das Tabellenblatt, von wo aus überwacht werden soll, fügt das "Foto" ein. Alle Veränderungen aufgrund bestehender Verknüpfungen sind nun leicht ersichtlich.


 
Tipp 48: Unterjährig Durchschnittswerte korrekt berechnen

Durchschnittswerte sind grundsätzlich leicht zu errechnen, da lediglich die Gesamtsumme durch die Anzahl der einzelnen Werte geteilt werden muss. Was jedoch, wenn der Datenbereich ständig eine unterschiedliche Anzahl von Werten aufweist?
Oft kommt es z.B. vor, dass man unterjährig anhand von mehreren Monatswerten den Durchschnitt der bereits vorhandenen Monate in einem vorgegebenen Listenbereich errechnen möchte. Da die Anzahl der Werte hierbei jeden Monat zunimmt muss auch in der Durchschnittsberechnung dieser Wert flexibilisiert werden. Ansicht Beispiel
Als Beispiel sei eine Tabelle definiert, die von B2 bis B13 Monatswerte enthält. Die folgende Formel prüft mit WENN zunächst, ob in B2 überhaupt ein Anfangswert vorliegt (um keinen Fehlerwert zu erzeugen) und berechnet dann die SUMME aller vorliegenden Werte (immer über alle 12 Zellen). Weiterhin wird dann anhand der Funktion ZÄHLENWENN die Anzahl der Werte ermittelt, die größer Null sind (somit die Anzahl der vorhandenen Einträge). Summe durch Anzahl Werte ergibt dann den Durchschnitt. =WENN(B2<>0;SUMME(B2:B13)/ZÄHLENWENN(B2:B13;">0");0)
Beispiel: Excel Datei 97-2003
Da der allgemein bekannte Durchschnitt in der Statistik der arithmetische Mittelwert ist, kann auch die Funktion =MITTELWERT(B2:B13) genutzt werden. Beides führt zum gleichen Ergebnis.


 
Tipp 49: Schnelle Zahlenformatierung über die Tastatur

Für die Festlegung eines Zahlenformats bietet die Dialogbox "Zellen formatieren" eine Vielzahl von Optionen an. Um diese Box aufzurufen, können Sie auch die Tastenkombination STRG 1 wählen. Wenn Sie allerdings genau wissen, welches Format Sie wollen und nicht den etwas umständlichen Weg über die Menüs und die Dialogbox gehen möchten, erreichen Sie verschiedene gebräuchliche Formatierungen auch direkt über eine Tastenkombination.
Mit den folgenden Tasten weisen Sie in Kombination mit den Tasten STRG und UMSCHALT die genannten Formate zu:
1 = Tausendertrennzeichen und zwei Dezimalstellen
2 = Exponentialformat mit zwei Dezimalstellen
3 = Datumsformat mit Tag, Monat und Jahr
4 = Währungsformat, zwei Dezimalstellen
5 = Prozentformat
6 = Standard-Zahlenformat


 
Tipp 50: Abfangen und ausblenden von Fehlermeldungen wie z.B. #DIV/0 oder #NV

Häufig kommt es vor, dass Zellen in einer Arbeitsmappe bereits mit einer Formel gefüllt sind, deren Vorgängerzellen für die Berechnung allerdings noch leer sind oder für die Berechnung ungültige Werte wie z.B. Text enthalten. Somit wird ein Fehlerwert angezeigt und weitere Berechnungen oder Charts funktionieren nicht mehr optimal. Sie können diesen Fehlerwert abfangen, damit die Zelle z.B. leer bleibt oder eine Null ausgibt, solange die Formel noch nicht das gewünschte Ergebnis liefert. Dies ist möglich, indem Sie Ihre Formel mit der Funktion ISTFEHLER verketten, einem sogenannten Fehlerhandler. Die Syntax lautet: =ISTFEHLER(Wert). Wenn Sie bei Formeln also Fehler erwarten, stecken Sie die Formel z.B. in diese Bedingung:
=WENN(ISTFEHLER(Formel);"";Formel)
Beispiel 1: =ISTFEHLER(12/0) gibt den Fehlerwert WAHR zurück, da eine Division durch Null den Fehlerwert #DIV/0! erzeugt. Diesen Wahrheitswert können Sie dann im Wenn-Teil der Wenn-Funktion verwenden.
Beispiel 2: =WENN(ISTFEHLER(12/A1);"";12/A1) gibt dann eine leere Zelle zurück, falls die Zelle A1 eine Null enthält; dagegen eine 6, wenn A1 eine 2 enthält. Die Gänsefüßchen im Dann-Teil der WENN-Funktion erzeugen einen leeren Zelleninhalt, welcher auch für die Aufbereitung in Charts vorteilhaft ist, da kein Nullwert angezeigt wird.
Falls eine Formel eine Null ausgibt, die Sie unterdrücken möchten, genügt es also auch, dieses mit einer einfachen WENN-Funktion zu erledigen: =WENN(Formel=0;"";Formel)
Beliebt ist die Verwendung der Fehlerunterdrückung bei SVERWEISen, die in großen, Datenbeständen abfragen und den Abfragewert ggfs. nicht finden, da nicht vorhanden. Hier lautet der Fehlerwert dann #NV (no value, nicht vorhanden). Ein Beispiel für die Fehlerunterdrückung wäre hierzu:
=WENN(ISTFEHLER(SVERWEIS(B2;D2:E2000;2;0));""; SVERWEIS(B2;D2:E2000;2;0))
Wenn Sie konkret nur den Fehlerwert #NV ausschließen wollen heißt die Lösung ISTNV:
=WENN(ISTNV(SVERWEIS(B2;D2:E2000;2;0));""; SVERWEIS(B2;D2:E2000;2;0))
Seit Excel 2013 geht das mit WENNFEHLER bzw. mit WENNNV auch noch kürzer.
=WENNFEHLER(FORMEL();"Fehlermeldung") und auch für das Beispiel mit #NV:
=WENNNV(SVERWEIS(B2;D2:E2000;2;0);"")


 
Tipp 51: Diagramme schnell und einfach um Daten ergänzen

Um in ein bestehendes Diagramm neue Datenreihen einzufügen können Sie je nach Excel-Version über "Daten hinzufügen" oder "Daten auswählen" in der Menüleiste und/oder im Kontextmenü die Assistenten zum selektieren der neuen Werte öffnen und diese dort hinzufügen. Leichter und schneller geht das jedoch mit einer einfachen Methode: Markieren Sie die einzufügenden Daten incl. der Datenbeschriftung und drücken Sie die Tastenkombination STRG C. Jetzt klicken Sie das Diagramm an, um es zu selektieren/markieren und drücken dann die Tastenkombination STRG V und Excel fügt die Daten als neue Datenreihe in das bestehende Diagramm ein.


 
Tipp 52: Datenreihen in Diagrammen einfach ausblenden

Manchmal kommt es vor, dass man in einem Diagramm viele Datenreihen dargestellt hat und in der Präsentation somit nur schwer ein Verständnis für die Werte bzw. überhaupt ein Überblick geschaffen werden kann. So ist es evtl. güstig, Datenreihen in einem Diagramm einfach auszublenden bzw. nach und nach einzublenden. Wenn Sie Ihr Diagramm fertig erstellt haben können Sie ganze Datenreihen ausblenden, indem Sie die die Daten beinhaltende Zeile in Excel über die Gruppierungsfunktion in dem Menü "Daten/Gliederung" ausblenden. Das Öffnen bzw. Schließen einer Zeile blendet also die Datenreihe jeweils ein bzw. aus. Hierbei werden für die Skalierung der Achsen nur noch die verbleibenden Werte berücksichtigt. Möchten Sie die Skalierung beibehalten können Sie die Linienfarbe der Datenreihe im Kontektmenü Datenreihen formatieren auf "keine Linie" bis Excel 2010 bzw. auf "ohne" in späteren Versionen ändern. Das Diagramm ändert sich bis auf die verschwindende Linie nicht.


 
Tipp 53: Suche nach den n kleinsten(/größten) Werten in einem Bereich

Um nach den kleinsten Werten in einem Bereich zu suchen, z. B. um diese aus einer Datentabelle informativ herauszustellen (Verkäufer mit den niedrigsten Umsätzen; Produkte mit den niedrigsten Deckungsbeiträgen o.ä.), können Sie die Funktion KKLEINSTE verwenden und mit einem entsprechenden Array arbeiten.
Am folgenden Beispiel wird gezeigt, wie nach den fünf kleinsten Werten in einer Datentabelle von E2 bis E1000 (hier stehen die Umsätze, DB's o.ä.) gesucht werden kann und wie diese separat in dem Zellbereich H2 bis H6 aufgelistet werden.
Hierzu ist zunächst der Bereich zu markieren, in dem die Daten aufgelistet werden sollen, im Beispiel: H2 bis H6. Diese Gruppe von Zellen wird dann die von der Arrayformel ermittelten Werte enthalten. Die Formel selber ist in die Bearbeitungsleiste einzugeben und durch Drücken von STRG+UMSCHALT+EINGABETASTE zu aktivieren. Die Formel lautet:
=KKLEINSTE(E2:E1000;{1;2;3;4;5})
Die Formel verwendet eine Arraykonstante, um die Funktion KKLEINSTE fünfmal auszuwerten und das kleinste (1), das zweitkleinste (2), das drittkleinste (3) ... etc. ... Element im Array zurückzugeben, welches in dem Zellbereich E2:E1000 enthalten ist. Für die Suche nach weiteren Werten fügen Sie zusätzliche Argumente zur Konstante und eine entsprechende Anzahl von Ergebniszellen zum Bereich H2:H6 hinzu. Sie können diese Formel auch mit zusätzlichen Funktionen verknüpfen. Um nach den größten Werten in einem Bereich zu suchen, können Sie die Funktion KKLEINSTE durch die Funktion KGRÖSSTE ersetzen.


 
Tipp 54: Datenlücken erkennen und zählen

Vielfach weisen große Datentabellen einzelne Datenfelder ohne Inhalte auf, da diese evtl. schon im Vorsystem nicht korrekt geflegt worden sind. Weniger kritisch ist dies, wenn es sich hierbei nur um mitlaufende Textfelder handelt - wehe nur sie werden in weiterführende Berechnungen einbezogen. Sie können alle Datenfelder in einer Excel-Tabelle dahingehend überprüfen, ob sie auch Zellinhalte besitzen. Dazu verwenden Sie die Funktion ANZAHLLEEREZELLEN.
Sie übergeben der Funktion einen Zellbereich (z.B. A2:H5000). Daraufhin ermittelt die Funktion die Anzahl der Zellen, die in diesem Bereich leer sind.
=ANZAHLLEEREZELLEN(A2:H5000)
Sofern die leeren Zellen auf fehlende Informationen in Vorsystemen schließen lassen können diese somit auch gezielt überarbeitet werden.


 
Tipp 55: Differenzen zwischen Datumswerten berechnen

Um in einer Exceltabelle z.B. anhand der Geburtsdaten der Mitarbeiter das Durchschnittsalter der Belegschaft zu berechnen oder anhand des Eintrittsdatums die Dauer der Betriebszugehörigkeit zu ermitteln, steht seit Excel 5 die undokumentierete Formel DATEDIF zur Verfügung. Mit der Formel können Sie die Anzahl Tage, Monate oder Jahre zwischen zwei Daten berechnen.
Die Syntax lautet: = DATEDIF(Startdatum;Enddatum;"Zeiteinheit")
Die "Zeiteinheit" ist ein Schalter für die zurückzugebende Information in Jahren, Monaten oder Tagen. Sie muss in englischen Kürzeln y, m oder d übergeben werden. Auch Kürzel wie md, ym und yd sind möglich, welche nur Teildifferenzen aus Tagen und/oder Monaten berechnen. md gibt z.B. den Unterschied in Tagen aus, wobei Monate und Jahre ignoriert werden.
Das Startdatum muss <= dem Enddatum sein, sonst gibt die Funktion einen Fehler zurück. Dieser kann jedoch mit =DATEDIF(MIN(A1;A2);MAX(A1;A2);"y") abgefangen werden, wobei in diesem Beispiel in A1 und A2 die verschiedenen Daten stehen.


 
Tipp 56: Tagesdaten ohne Wert-Information in Diagrammen ausblenden

Für die Visualisierung von täglichen Daten kann man schnell z.B. ein Liniendiagramm in Excel erstellen, welches die Werte vom Monatsersten bis zum Monatsletzten darstellt. Standardmäßig stellt Excel dann aber auch alle Tage dar, für die gar keine Werte vorliegen (z.B. die Wochenenden). Dies nennt man auch "gestreckte Darstellung" - die Werte liegen auf der X-Achse in ungleichmäßigen Abständen nebeneinander. Um diese leeren Zeiträume auszublenden und die Anstände anzupassen kann man die Diagrammoptionen entsprechend ändern.
Hierzu markieren Sie das Diagramm und wählen bis Excel 2003 die DIAGRAMMOPTIONEN und welchseln in das Register ACHSEN. Ab Excel 2007 finden Sie ACHSEN unter DIAGRAMMTOOLS / LAYOUT. Hier können Sie nun den Achsentyp verändern, der von Excel standardmäßig "automatisch" dargestellt wird. Bis zu der Version 2003 ist nun der Achsentyp "Kategoie" zu wählen. Ab Excel 2007 klickt man den Typ "Textachse" an, um lediglich die gewünschten Wert-Informationen anzeigen zu lassen.


 
Tipp 57: Ausrichtung von Zahlen am Komma

Zur übersichtlicheren Darstellung von Zahlen mit unterschiedlich vielen Nachkommastellen kann es sinnvoll sein, die Ausrichtung der Zahlen nicht auf den rechten Zellrand sondern auf das Komma zu fokusieren. Mit einem Format der Art 0,00 würden ab der dritten Nachkommastelle keine Werte mehr angezeigt werden und ggfs. lassen sich die Dimensionen der Daten nicht schnell überblicken bzw. es kommt zu Fehlinterpretationen. Über ein benutzerdefiniertes Format lassen sich diese Dezimalzahlen einfach am Komma ausrichten. Hierzu markieren Sie zuerst die auszurichtenden Zellen und geben dann im Kontektmenü "Zellen formatieren" im Register "Zellen" das benutzerdefinierte Format 0,???? an. Die Anzahl der Fragezeichen sollte der maximalen Anzahl von vorkommenden Nachkommastellen bei Ihren Zahlen entsprechen. Die Fragezeichen füllen bei Zahlen ohne oder mit abweichender Anzahl Nachkommastellen die Stellen zum Komma auf, welches somit bei allen Zahlen untereinander steht.


 
Tipp 58: Überschriften automatisch mit aktuellem Datum oder Monat verketten

Oft kommt es vor, dass für einen Bericht in dessen Überschrift ein aktuelles Datum oder der aktuelle Monat aufgeführt sein soll. Z.B. kann das lauten "Umsatzdaten vom 24.11.2011" oder "Monatsbericht November 2011". Um diese Überschriften nicht jedes Mal neu aktualisieren zu müssen (und ggfs. auch mal zu übersehen vor der Berichtsabgabe), kann man die Aktualisierung des Textes mit einer Formel automatisieren. Ziel hierbei ist, dass Excel ein zugrundeliegendes Datum nicht als Zahlenwert anzeigt bzw. den Monat im Klartext aufführt. In den folgenden Fällen werden sowohl Textbausteine wie auch Formeln miteinander verkettet. Zunächst ist es notwendig, dass aktuelle Datum in einer Zelle zu hinterlegen, z.B. durch Eingabe in Zelle H3 mit der Funktion HEUTE().
Jetzt können Sie das erste obige Beispiel in einer beliebigen Zelle wie folgt erzeugen:
="Umsatzdaten vom "&TEXT(H3;"TT.MM.JJJJ")
Mit dem "&"-Zeichen werden der Text und das Datum aneinander gekettet. Das ginge auch mit:
=VERKETTEN("Umsatzdaten vom ";TEXT(H3;"TT.MM.JJJJ"))
Um jetzt nur den Monat aus dem Datum zu selektieren geben Sie folgendes ein:
="Monatsbericht "&TEXT(H3;"MMMM JJJJ")
oder bei dem Bezug des Datums aus einem anderen Tabellenblatt mit entsprechender Ergänzung:
="Monatsbericht "&TEXT(Tabelle2!H3;"MMMM JJJJ")
Die Ausgabe von Tagen, Monaten und Jahren kann man hier also über T,M und J variieren (ggfs. bei englischer Version mit D,M und Y).


 
Tipp 59: Positive und negative Zahlen getrennt summieren

In der Finanzbuchhaltung kommt es vor, dass man die in einer Spalte stehenden Soll- und Habenwerte (z.B. zum Abgleich der Summen) getrennt voneinander aufsummieren möchte. Hierzu kann man eine Matrixformel und die Funktion ABS verwenden, welche den Absolutwert einer Zahl liefert. ABS wandelt negative Zahlen in positive um, positive Zahlen bleiben dagegen positiv. Die Syntax der Funktion lautet ABS(Zahl) wobei Zahl eine reele Zahl sein muss. Für die positiven und negativen Summen lauten die Formeln dann wie folgt:
Für positive Zahlen: {=Summe(<Bereich>+ABS(<Bereich>))/2}
Für negative Zahlen: {=Summe(<Bereich>-ABS(<Bereich>))/2}
Da man die geschweiften Klammern der Matrixformeln nicht selbst eingeben kann, muss man mittels Tastenkombination "Strg-, Shift- und Returntaste" die Eingabe in der Bearbeitungszeile bestätigen.
In der Summenformel werden im ersten Teil alle Zahlen inklusive ihrer Vorzeichen addiert. Im zweiten Teil wird aufgrund der ABS-Funktion die Summe aller Beträge des Bereiches unabhängig von deren Vorzeichen dazugezählt.
Die negativen Zahlen werden im ersten Teil abgezogen, da sie mit Vorzeichen summiert werden. Im zweiten Teil werden sie aber wieder zu der Summe dazugezählt, da dort der Betrag addiert wird. Also werden sie einmal abgezogen und einmal dazugezählt – und treten somit in der Summe nicht auf.
Die positiven Zahlen werden dagegen zweimal addiert, so dass das Ergebnis der Summe-Formel am Ende noch durch zwei geteilt werden muss. Bei der Formel für die Errechnung der negativen Zahlen wird durch das negative Vorzeichen vor der ABS-Funktion die Summierung umgekehrt.


 
Tipp 60: Großbuchstaben in Kleinbuchstaben umwandeln

Häufig werden Daten in Vorsystemen in GROSSBUCHSTABEN eingegeben und verarbeitet. Beim Export in Excel und der dortigen Weiterverarbeitung kann dies stören. Das Umwandeln von Buchstaben kann leicht anhand einer vordefinierten Funktion erfolgen. So gibt es Funktionen, die den Text sowohl komplett in Kleinbuchstaben wie auch vollständig zurück in Großbuchstaben umwandeln, aber auch eine Funktion, die alle Wörter einer ausgewählten Zelle in Kleinbuchstaben mit jeweils führendem Großbuchstaben darstellt, so wie es bei Substantiven notwendig ist.
Während die Funktionen KLEIN und GROSS die Umwandlung aller Buchstaben in das jeweilige Format umsetzen, lautet die Funktion für die Darstellung eines führenden Großbuchstabens und folgender Kleinbuchstaben GROSS2 und deren Syntax =GROSS2(Zelle). Die Funktion wandelt den ersten Buchstaben aller Wörter einer Zeichenfolge in Großbuchstaben um.
Die Funktion sollte in einer neuen Spalte untergebracht werden, deren Ergebnis anschließend ggfs. wieder in die Ursprungsspalte per KOPIEREN und INHALTE EINFÜGEN/WERTE übertragen werden kann.
Teilweise tauchen in der Praxis auch Lösungen in der Form =GROSS2(Klein(Zelle)) zum Umwandeln der Buchstaben auf. In der aktuellen Version von Excel ist es jedoch nicht (mehr) notwendig, die Großbuchstaben zunächst alle in Kleinbuchstaben umzuwandeln.


 
Tipp 61: Bereiche dynamisch berechnen

Um im laufenden Jahr eine monatliche (z.B. Umsatz-) Übersicht mit zugehörigen Planwerten und ggfs. noch einem dynamischen Diagramm zu erstellen, gilt es, am Ende eines jeden Monats den auszuwertenden Bereich um eine Zeile (den aktuellen Monat) zu erweitern. Dies kann man natürlich manuell tun und die entsprechenden Formeln von Hand ändern. Besser ist es jedoch, wenn sich die Auswertungen dynamisch an die nach und nach ausgefüllten Zellen anpassen. Excel kann automatisch erkennen, bis wohin ein Bereich aktuell ausgefüllt ist. Für diese Zwecke (und natürlich auch für andere und weitaus kompliziertere) gibt es die Funktion BEREICH.VERSCHIEBEN, mit der man Bereiche auf eine eigene Weise definieren kann. Die Syntax lautet wie folgt:
=BEREICH.VERSCHIEBEN(AusgangsBereich;ZeilenVerschub;SpaltenVerschub;HöheZeilen;BreiteSpalten)
Beispielhaft soll eine Umsatzstatistik dienen, welche wie folgt aufgebaut ist: In Spalte A stehen einer Überschrift folgend die Monate Jan-Dez.; in Spalte B die Plan-Umsatzwerte (B2 bis B13) und deren Summe in B14; in Spalte C die Ist-Umsätze bis einschließlich Monat März (C2 bis C4) und die Summe C2 bis C13 in Spalte C14. Nun sind die beiden Summen nicht vergleichbar, da es 12 Plan- und nur drei Istwerte sind. Mit folgender Formel können die den Istwerten zugehörigen Planwerte aufsummiert werden:
=SUMME(BEREICH.VERSCHIEBEN(B2;0;0;ANZAHL(C2:C13);1))
Mit ANZAHL wird die Anzahl der vorhandenen Werte ausgerechnet und damit der Bereich von B2 ausgehend definiert. Die Summenformel summiert dessen Inhalte. Diese Formel kann in beliebiger Zelle stehen, also auch die Summenformel in B14 ersetzen. Eine Beispieldatei finden Sie hier: Bereich.Verschieben


 
Tipp 62: Bedingte Summierung über mehrere Suchkriterien

Mit der Funktion SUMMEWENN kann man schnell eine Datenbank nach einem Kriterium durchsuchen und dann die Summe der jeweiligen Einzelwerte ausgeben. Möchte man jedoch nach mehreren Kriterien in einer Datenbank suchen und deren Gesamtsumme ausrechnen, wären hierfür schon mehrere Summewenn-Funktionen notwendig bzw. möglich. Mit der Funktion DBSUMME können jedoch in einer Formel gleich mehrere Bedingungen mitgegeben werden. Die Syntax der Funktion lautet:
=DBSUMME(Datenbank;Feld;Suchkriterien)
"Datenbank" ist der Zellbereich, der die Datentabelle darstellt; die erste Zeile muss die Spaltenüberschriften enthalten.
"Feld" ist die Überschrift der Spalte, welche durchsucht werden soll
"Suchkriterien" ist ein Zellbereich, der die Bedingungen für die Summierung festlegt; die erste Zeile muss die Spaltenüberschrift enthalten.
Ein einfaches Beispiel finden Sie hier: DBSUMME.XLS
Im Beispiel werden in Zelle C14 aus der Datenbank A1:C10 diejenigen Umsätze aus Spalte C addiert, deren Suchkriterien (=Verkaufsgebiet) in dem Zellbereich F1:F3 definiert sind.


 
Tipp 63: Zellen vor ungewollten Eingaben schützen

Um z.B. in Formularen bestimmte Zellen vor ungewollten Eingaben oder dem Überschreiben von Inhalten zu schützen, können diese mit einem Zellschutz belegt werden. Standardmäßig ist für alle Zellen in einem Tabellenblatt der Zellschutz aktiviert. Dies ist im Dialogfenster ZELLEN FORMATIEREN im Register SCHUTZ ersichtlich; hier ist GESPERRT aktiviert. Zum entfernen des Zellschutzes wählen Sie alle betreffenden Zellen aus, die nicht geschützt sein sollen (Mehrfachmarkierung mit Hilfe der gedrückten STRG-Taste) und entfernen den Haken im Kontrollkästchen bei GESPERRT. Klicken Sie nun im Register ÜBERPRÜFEN auf BLATT SCHÜTZEN und vergeben Sie ein Kennwort. Dieses kann bis zu 255 Zeichen lang sein und sich aus einer beliebigen Kombination von Buchstaben, Ziffern, Leer- und Sonderzeichen zusammensetzen. Wenn Sie hier das Kontrollkästchen vor "Gesperrte Zellen auswählen" deaktivieren, können diese im geschützten Blatt nicht mehr angeklickt werden. Nach dem Klick auf OK geben Sie das Kennwort erneut ein. Sie bekommen von nun an eine Fehlermeldung, wenn Sie versuchen geschützte Zellen zu überschreiben oder zu löschen. Von einer zur nächsten ungeschützten Zelle kann direkt mit der Tabulatortaste gesprungen werden.


 
Tipp 64: Eingabezellen in Formularen farbig markieren

Werden Tabellen anderen Anwendern als Formulare zum Ausfüllen von bestimmten Feldern zur Verfügung gestellt, empfiehlt es sich, bestimmte Zellen zu schützen und jene für die Eingaben notwendigen, ungeschützten Zellen farbig zu markieren. Diese Markierung kann einerseits durch Auswahl aller ungeschützten Zellen und dem Ändern der Zellformatierung erfolgen. Komfortabler und schneller geht es mit einer bedingten Formatierung, insbesondere dann, wenn die geschützten und ungeschützten Zellen nachträglich geändert werden müssen. Entfernen Sie hierzu zunächst wie in Tipp Nr.63 beschrieben in allen zu formatierenden Eingabezellen den Zellschutz. Markieren Sie nun den gesamten Formularbereich, zum Beispiel A1:F50 und starten Sie den Dialog BEDINGTE FORMATIERUNG. Als Regeltyp wählen Sie "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus (Excel 2003: "Bedingung Formel ist") und geben als Regelbeschreibung die Formel: =ZELLE("Schutz";A1)=0 ein. Hier ist nur die erste Startzelle A1 des ausgewählten ungeschützten Bereiches einzugeben. Sie können über die Schaltfläche FORMATIEREN in den Registerkarten AUSFÜLLEN z.B. einen gelben Zellhintergrund definieren oder in RAHMEN verschiedene Zellrahmen bestimmen. Nach dem Klick auf OK werden nun alle Zellen, auf denen der Zellschutz deaktiviert ist, farbig und mit der definierten Zellumrahmung hervorgehoben.


 
Tipp 65: PivotTabellen-Assistent ab Excel 2007 / PivotTabelle aus mehreren Datentabellen erstellen

Mit PivotTabellen können Sie umfangreiche Datenbestände auf einfache Weise analysieren, anordnen und zusammenfassen sowie Drilldowns auf die Detaildaten vornehmen. Bis Excel 2007 konnte für die Erstellung der PivotTabellen noch ein PivotTabellen-Assistent über die Menüs direkt angeklickt werden. Dieser erleichterte die Auswahl der Daten und ermöglichte es auch, mehrere Datentabellen zu einer PivotTabelle zusammen zu fassen. Ab Excel 2007 ist dieser Assistent gut versteckt und kann nur noch anhand einer Tastenkombination erweckt oder durchs Einbinden ins Menüband bzw. in die Symbolleiste für den Schnellzugriff aufgerufen werden. Die Tastenkombination für den PivotTabellen-Assistenten lautet Alt + N + P. Für das dauerhafte Einbinden in das Menüband bzw. die Symbolleiste kann über das Register DATEI in den Excel-Optionen der Befehl "PivotTable - & PivotChart-Assistent" (neben weiteren PivotTabellen-Befehlen) hinzugefügt werden. "Befehle auswählen" und "Alle Befehle" zeigt die alphabetische Liste aller möglichen Befehle in Excel - dann Auswahl des Befehls und Schaltfläche "Hinzufügen" klicken. Der PivotTabellen-Assistent fragt im Gegensatz zu dem einfachen Fenster "PivotTabelle erstellen" bereits vorab, welche Daten man analysieren möchte und bietet zum Beispiel die Möglichkeit "Mehrere Konsolidierungsbereiche" für das tabellenübergreifende Erstellen an.


 
Tipp 66: Spaltenüberschriften auf allen Druckseiten anzeigen

Wenn eine aus mehreren Seiten bestehende Excel-Tabelle fertig berechnet und gestaltet ist, kommt es beim Ausdruck oft zum Entsetzen: Der Zahlenfriedhof ist ab der zweiten Seite unübersichtlich, da die Spaltenüberschriften fehlen. Dabei können diese doch ganz leicht auf den Folgeseiten mit einer Funktion wiederholt werden. Hierzu ist es wichtig, in das Menü SEITE EINRICHTEN zu gelangen. In dessen Registerkarte TABELLE läßt sich das dann einstellen. Doch gelangt man hier schon lange nicht mehr einfach so hin. Ging es früher noch über das Menü DATEI, wird es ab Excel 2007 schon schwieriger und unübersichtlicher, so dass man sich am besten gleich in die Symbolleiste für den Schnellzugriff unter "weitere Befehle ..." das passende Icon zieht. Dies dann angeklickt und das Register geöffnet kann im Bereich DRUCKTITEL zunächst festgelegt werden, ob die Spalten- und/oder Zeilenbeschriftungen wiederholt werden sollen. Um z.B. die Spaltenüberschriften auf allen Folgeseiten zu wiederholen, klicken Sie in das Feld bei WIEDERHOLUNGSZEILEN OBEN. Jetzt ist es möglich, mit der Maus direkt in die Zeilenköpfe (linker Rand) der Tabelle zu klicken. Excel übernimmt daraufhin die Zellkoordinaten in das Dialogfenster und Sie bestätigen mit OK. Wenn Ihre Überschrift aus mehreren Zeilen besteht, klicken Sie übrigens auf die erste Zeilennummer, halten die Maustaste gedrückt und ziehen über weitere Zeilennummern. Excel druckt jetzt auf jeder Seite die ausgewählten Zeilen als Überschriften.


 
Tipp 67: Veränderungen in Excel-Tabellen protokollieren und nachvollziehen

Sollen mit einer Excel-Datei mehrere Anwender arbeiten, so ist es oft sinnvoll, alle Änderungen genau zu erfassen und zu protokollieren. Fehlerhafte oder ungewollte Änderungen können somit nachvollzogen und korrigiert werden. Wer hat also zu welchem Zeitpunkt welche Zellinhalte verändert? Excel bietet hierfür eine Protokoll-Funktion an, welche folgendermaßen genutzt werden kann:
Öffnen Sie zunächst die Arbeitsmappe, in der Sie Veränderungen nachvollziehen möchten. In Excel ab Version 2007 klicken Sie das Register ÜBERPRÜFEN im Menüband oder der Multifunktionsleiste an und klicken dann in der Gruppe ÄNDERUNGEN auf ÄNDERUNGEN NACHVERFOLGEN - ÄNDERUNGEN HERVORHEBEN. In Excel bis einschließlich Version 2003 rufen Sie über das Menü EXTRAS das Komando ÄNDERUNGEN NACHVERFOLGEN - ÄNDERUNGEN HERVORHEBEN auf. In allen Versionen zeigt Ihnen Excel dann ein Dialogfenster an, in dem Sie das Kontrollkästchen ÄNDERUNGEN WÄHREND DER EINGABE PROTOKOLLIEREN. ARBEITSMAPPE WIRD FREIGEGEBEN aktivieren müssen. Die wählbaren Optionen hinsichtlich WANN und WER werden zumeist mit IMMER und JEDER sinnvoll gewählt. Möchten Sie die Änderungen direkt in dem Arbeitsblatt sehen, sollten Sie ÄNDEURNGEN AM BILDSCHIRM HERVORHEBEN wählen. Die Protokollierung kann aber auch auf einem neuen Blatt stattfinden (welches man dann ggfs. auch noch verstecken kann...) - hierzu entsprechend klicken. Nach Bestätigung mit OK muss die Arbeitsmappe gespeichert werden.
Excel ergänzt nun nach jeder Änderung an Zellen einen Kommentar und führt den angemeldeten User namentlich mit den durchgeführten Änderungen auf. Die Protokollierung schalten Sie auf dem gleichen Wege wieder aus, indem Sie das Kontrollkästchen ÄNDERUNGEN WÄHREND DER EINGABE PROTOKOLLIEREN. ARBEITSMAPPE WIRD FREIGEGEBEN deaktivieren.


 
Tipp 68: Begriffe mit identischer Schreibweise in Tabellen finden

Oft kommt es vor, dass in großen Datenbanken Begriffe oder Zahlen, wie z.B. ein Artikel, ein Ort oder auch ein Kunden mit gleicher Schreibweise und somit mehrfach vorliegen. Wenn das nicht gewollt ist, bietet sich eine Plausibilitätsprüfung an, ob der Begriff vorkommt und wenn ja, in welcher Zeile. Und: Dabei soll natürlich bitte auch nach GROSS-und kleinbuchstaben unterschieden werden. Dies geht in strukturierten Datenbanken mit einem Datenfilter, kann aber auch leicht mit einer Funktion und einer Matrixformel gelöst werden.
Hierzu bietet sich die Formel IDENTISCH an, welche zunächst prüft, ob zwei Zeichenfolgen identisch sind. In diesem Fall wird WAHR ausgegeben. Andernfalls gibt die Funktion den Wert FALSCH zurück. IDENTISCH beachtet die Groß- und Kleinschreibung, ignoriert aber Formatierungsunterschiede. Die Syntax lautet: IDENTISCH(Text1;Text2)
Um jetzt z.B. zu überprüfen, ob der Begriff "Müller" in der Spalte B in dem Bereich B2 bis B2500 vorkommt, lautet die Matrix-Formel {=ODER(IDENTISCH("Müller";B2:B2500))}. Da nicht lediglich ein Wert verglichen wird, sondern ein Zellbereich durchsucht werden soll, muss mit ODER und der Matrix gearbeitet werden.
Die Zeilennummer des ersten gefundenen Wertes kann mit der Formel
{=MIN(WENN(IDENTISCH("Müller";B2:B2500);ZEILE(2:2500)))} ermittelt werden.
Anmerkung: Die Formel muss als Matrixformel eingegeben werden. Nachdem Sie das Beispiel in ein leeres Arbeitsblatt kopiert haben, markieren Sie hierzu jede Formelzelle mit einer Matrixformel einzeln. Drücken Sie F2, und dann zusätzlich STRG+UMSCHALT+EINGABE. Nach jeder Änderung an der Formel ist dieser "Kunstgriff" (leider) wieder von Nöten ...


 
Tipp 69: Prüfen, ob Zellbereiche identisch sind

Neben dem Suchen von identisch geschriebenen Begriffen (s. Excel-Tipp 68) kann man auch recht einfach ganze Spalten und Zellbereiche auf ihre Inhalte prüfen und feststellen, ob sie identisch sind. Weiterhin ist es dann z.B. auch möglich, die Anzahl der unterschiedlichen Zellen auszugeben und somit evtl. Fehler oder Falscheingaben ermitteln und bearbeiten zu können. Hierzu bietet sich ebenfalls die Funktion IDENTISCH an.
Angenommen in den zu vergleichenden Spalten A und B sind jeweils Werte in den Zeilen 2 bis 20000. In einer beliebigen anderen Zelle kann man nun folgende Array-Formel eingeben, die die Zellbereiche vergleicht und nur bei exakter Identität (auch Groß-/Kleinschreibung) den Text "Zellen identisch" ausgibt:
{="Zellen "&WENN(UND(IDENTISCH(A2:A20000;B2:B20000));"";"nicht ")&"identisch"}
Wichtig ist das Eingeben der Array-Formel {Matrixformel} ohne die geschweiften Klammern. Diese {Klammern} werden nicht eingegeben sondern zum Abschluss der Formel durch gleichzeitiges Drücken von Strg+Shift+Enter erzeugt (statt Enter allein). Sonst erscheint ggfs. die Fehlermeldung #WERT!
Sind die Zellbereiche nicht identisch, liefert die Wenn-Funktion für den Sonst-Wert den Text "nicht", welcher durch die vor- und nachgesetzten Texte "Zellen" & "identisch" zu dem Ergebnis "Zellen nicht identisch" verkettet wird.
Die Anzahl der abweichenden Inhalte kann in einer separaten Zelle mit dieser Array-Formel angezeigt werden, welche sich zum Zählen der unterschiedlichen Zellen der N-Funktion bedient:
{=SUMME(N(A2:A20000<>B2:B20000))}


 
Tipp 70: Fehlerwerte auf dem Bildschirm anzeigen aber nicht drucken

Ab Excel 2002 können Sie beim Drucken bestimmen, ob bzw. in welcher Form Fehlerwerte dargestellt werden. Unter Menü / Datei / Seite einrichten / Tabelle / Fehlerwerte als, gibt es diesbezüglich vier Varianten zur Auswahl: Dargestellt, leer, -- und #NV. Die Bildschirmanzeige wird von dieser Einstellung nicht beeinflusst.


 
Tipp 71: Benutzerdefinierte Datenreihen schnell erstellen

Wie in Tipp 31 bereits erklärt, lassen sich bestimmte vordefinierte Datenreihen in Excel schnell erstellen. So können z.B. Listen mit Monatsnamen und Wochentagen (mit und ohne Wochenenden) in verschiedenen Formaten schnell durch ziehen der markierten Zelle erzeugt werden. Aber auch benutzerdefinierte häufig wiederkehrende Datenreihen können leicht angelegt und über diese Funktion Autoausfüllen dann schnell erstellt werden. Dies können z.B. die firmeneigenen Produktgruppen, Vertreterlisten, Materialgruppen, Liste der Firmenwagen o.ä. sein.
Angenommen Sie haben immer wieder verschiedene Auswertungen mit bestimmten Materialgruppen zu erstellen und wollen beim Aufbau einer neuen Auswertung diese Materialgruppen schnell in einer Liste untereinander stehen haben. Vor dem ersten Mal müssen Sie Excel mit der Liste einmalig "bekannt" machen und diese als sogenannte Benutzerdefinierte Liste importieren. Dazu sind die einzelnen Daten der Liste wie z.B. Alu, Stahl, Holz, Gummi, etc. untereinander in ein Tabellenblatt zu schreiben und zu markieren. Bis Excel 2007 kann das Fenster Benutzerdefinierte Liste über Extras / Optionen aufgerufen werden, ab 2010 über Datei / Optionen / Erweitert im Bereich Allgemein. Jetzt kann die ausgewählte Liste mit der Schaltfläche Importieren angelegt und mit OK gespeichert werden - fertig. Schreiben Sie nun in ein Feld Ihrer Wahl die erste Materialgruppe Alu, markieren es und ziehen den Anfasser (kleines Quadrat unten rechts in der Markierung) nach unten, sehen Sie beim Ziehen bereits die Vorschau der Werte Stahl, Holz, … und erhalten beim Loslassen die fertige Liste.


 
Tipp 72: Kalenderwoche berechnen

Natürlich bietet Excel auch eine Funktion, um die Kalenderwoche zu berechnen. Die Funktion KALENDERWOCHE hat folgende Syntax:
=KALENDERWOCHE(Datum;Rückgabe)
Das Argument Datum sollte mit einem Verweis auf eine Zelle mit Datum versehen werden, für das Sie die Woche wissen möchten. Excel erwartet das Datum als sogenannte Fortlaufende Zahl (seit dem 01.01.1900). Besondere Beachtung verdient dann auch das Argument Rückgabe, das bis Excel 2007 aus dem Wert 1 oder 2 bestehen konnte. Es bestimmt, an welchem Tag eine Woche beginnt. Wenn Sie eine 1 eingeben, beginnt die Woche an einem Sonntag. Nach diesem Muster wird zum Beispiel in den USA verfahren. Wie in den meisten europäischen Ländern startet die Woche auch in Deutschland am Montag. Sie müssen also eine 2 eintippen, um hier zu einem korrekten Resultat zu kommen. Mitlerweile gibt es weitere Parameter: 11-17 für den Wochenbeginn von Montag bis Sonntag sowie 21 für den Montag gem. ISO (s. unten). Ab Excel 2013 kann auf Kalenderwoche verzichtet werden. Es gibt nun die Funktion ISOWEEKNUM(date), welche aus dem angegebenen Datum die erste Kalenderwoche gemäß ISO-Norm ermittelt. In Europa wird die ISO 8601 verwendet. Als erste Kalenderwoche im Jahr wird die Woche mit dem ersten Januar-Donnerstag definiert. Dadurch fällt der 4. Januar immer in die erste Kalenderwoche.


 
Tipp 73: Annuität berechnen mit der Funktion RMZ

Um zum Beispiel bei Annuitätendarlehen oder Leasingverträgen die monatlichen Zahlungen auszurechnen, kann die Funktion RMZ (RMZ = Regelmäßige Zahlung / die zu zahlende Annuität pro Periode) in Excel genutzt werden. Eine Annuität ist eine regelmäßige, gleich bleibende Zahlung für z.B. einen Kredit. Sie setzt sich aus den Zinsen und der Tilgung zusammen. Während sich Zins- und Tilgungsanteil ständig verändern, bleibt die Annuität jedoch als Summe von Zins- und Tilgungsanteil konstant. Die Voraussetzung für die Anwendung die Funktion RMZ sind konstante Zahlungen und ein konstanter Zinssatz.
Die Syntax lautet: =RMZ(Zins;Zzr;Bw;Zw;F)
Die einzelnen Argrumente haben dabei folgende Bedeutung:
Zins :   Zinssatz pro Periode (Zahlungszeitraum)
Zzr :   Anzahl der Zahlungszeiträume
Bw :   Barwert: Der Gesamtbetrag, den eine Reihe zukünftiger Zahlungen zum gegenwärtigen Zeitpunkt wert ist. Dieser Wert wird auch Kreditbetrag genannt
Zw :   Zukünftiger Wert (Endwert), den Sie nach der letzten Zahlung erreicht haben möchten. Fehlt das Argument Zw, wird der Wert 0 (Null) angenommen, d. h. der Endwert eines Kredits ist gleich 0
F :   Fälligkeit: Gibt an, wann Zahlungen fällig sind; kann den Wert 0 (am Ende einer Periode) oder 1 (am Anfang) annehmen
Beispiel: Es soll ein Darlehen über 50.000 (Bw) aufgenommen werden. Der Zinssatz beträgt 6,0 % p.a. (Zins = 6,0%/12 = 0,5/Monat) und die Laufzeit 5 Jahre (Zzr = 5*12 = 60 Monate). Der Endwert (Zw) bei vollständiger Rückzahlung ist 0; die Fälligkeit (F) da Zahlung am Periodenanfang ist 1. Wie hoch ist die monatliche Rate?
Die Formel lautet:  =RMZ(6,0%/12;5*12;50000;0;1)
Die monatlich zu zahlende Rate inklusive der Zinsen beträgt: -961,83
Die Zinsen betragen übrigens in diesem Beispiel 7.709,86 und der Gesamtaufwand 57.709,86 bei einem effektiven Jahreszinssatz von 6,17% p.a. (interner Zinssatz, IRR).
Da der Wert negativ (als Zahlungsrate) dargestellt wird, können Sie mit der Absolut-Funktion das Ergebnis auch lesbarer gestalten: =ABS(RMZ(6,0%/12;5*12;50000;0;1))
Sie sollten unbedingt darauf achten, dass Sie für Zins und Zzr zueinander passende Zeiteinheiten verwenden (im Beispiel Monate). Wenn Sie für einen Kredit mit fünfjähriger Laufzeit und einem jährlichen Zinssatz von 6% monatliche Zahlungen leisten, müssen Sie für Zins 6%/12 und für Zzr 5*12 angeben.
Wenn Sie den von der Funktion RMZ zurückgegebenen Wert mit Zzr (5*12) multiplizieren, können Sie den Gesamtbetrag berechnen, den Sie bis zum Ende zu zahlen haben.
Aber Achtung: Ein von RMZ zurückgegebener Betrag umfasst immer nur die Tilgung und die Zinsen. Nicht eingerechnet werden Steuern, Ausgleichszahlungen oder Gebühren, die gelegentlich für Darlehen und Kredite zu berücksichtigen sind.


 
Tipp 74: Druckbereiche dynamisch gestalten

In Excel gibt es die Möglichkeit von großen Tabellen nur bestimmte Bereiche auszudrucken. Hierzu markieren Sie die entsprechenden Zellbereiche der Tabelle und wählen unter "Seitenlayout" die Option "Druckbereich" und den Menüpunkt "Druckbereich festlegen". Der so definierte Druckbereich ist jetzt unabhängig von Änderungen und Ergänzungen in der Tabelle festgelegt. Wenn sich die Tabelle um weitere Zeilen oder Spalten vergrößert, werden diese nicht automatisch mit ausgedruckt sondern der Druckbereich muss neu festgelegt werden. Mit einer Formel kann man den Druckbereich auch in Abhängigkeit von der Anzahl der gefüllten Zeilen gestalten.
Hierzu wählen Sie im Register "Formeln" den Punkt "Namen definieren" und es erscheint ein Fenster, in dem Sie einen Namen für den Druckbereich vergeben können. Geben Sie hier das Wort ‚Druckbereich' ein. In das Feld "Bezieht sich auf" geben Sie noch folgende Funktion ein:
=INDIREKT(TABELLE1!$A$1)
Beenden Sie die Eingaben mit "OK".
$A$1 bezieht sich dabei auf eine Hilfszelle, die auch anderswo stehen könnte und die den nachfolgenden Inhalt haben muss. Angenommen ist im Folgenden auch, dass Ihre Datentabelle in den Spalten A bis H Daten enthält und diese in der Zeile 2 beginnen: Zunächst markieren Sie in diesem Beispiel die Zelle A1 Ihrer Tabelle und geben in A1 folgende Formel ein:
="A2:H"&ANZAHL2(H:H)
Diese bewirkt, dass das Ergebnis der Prüfung, wie viele nicht leere Zellen in der Spalte H sind, an die Definition des Druckbereiches A1:H angefügt wird. Wenn gemäß diesem Beispiel in der Spalte H weitere Zellen mit Inhalten versehen werden, verändert sich künftig automatisch auch der Druckbereich.


 
Tipp 75: Pivot-Tabellen - Detaildaten schnell und einfach anzeigen

Mit Hilfe von Pivot-Tabellen können Sie große Datenmengen schnell und einfach in übersichtliche Tabellen zusammenfassen und analysieren. Ist die Pivot-Tabelle auf der Grundlage von einer großen Anzahl von Datensätzen erst einmal erstellt, kommt es nicht selten vor, dass die zusammengefassten Informationen wieder daraufhin untersucht werden müssen, wie sie sich zusammensetzen. Hierzu kann man natürlich in den Ursprungsdaten mit dem Excel-Filter arbeiten und über ein oder mehrere Kriterien filtern, mit Teilergebnissen die Summen kontrollieren und die ausgewählten Datensätze prüfen. Einfacher und schneller kommt man jedoch mit einem Sprung direkt aus der Pivot-Tabelle zu diesem sogenannten Datenextrakt.
Haben Sie zum Beispiel in einer Pivot-Tabelle mehrere Tausend Umsätze nach Ländern und/oder Produktgruppen sowie für bestimmte Verkäufer aus den Jahres- oder Monatsdaten zusammengefasst, können Sie mit einem einfachen Doppelklick auf die Zelle mit dem zu analysierenden Umsatz einen Datenextrakt (Detaildaten) in einer neuen Tabelle generieren, der die Original-Datensätze enthält, welche in ihrer Summe den angeklickten Umsatzwert ergeben. Excel erstellt die Tabelle immer in einem neuen Tabellenblatt, die Ursprungsdaten bleiben erhalten. Ein Filtern in der Ursprungstabelle ist gar nicht mehr nötig und Sie können in dem obigen Beispiel sofort ersehen, in welchen Ländern mit welchen Produktgruppen der Verkäufer X seine einzelnen Umsätze generiert hat.


 
Tipp 76: Zellkommentare mit Grafiken und Bildern optimieren

Kommentare in Excel sind eine feine Sache, um einzelne Einträge in der Tabelle zu erläutern, Formeln zu erklären oder auch um mehr Hintergrundinformationen zu den Werten zu geben, ohne dass diese die ganze Zeit sichtbar sind und Platz für andere Berechnungen rauben. Dabei lassen sich nicht nur Texte in das Kommentarfeld eingeben sondern vielmehr können auch ganze Grafiken, Bilder, Diagramme u.ä. aufgenommen werden, welche dann erst sichtbar werden, wenn die Zelle mit dem Maus-Cursor angesteuert wird.
Liegt die anzuzeigende Grafik noch nicht als Datei vor, sollte diese zunächst erstellt werden. Das kann u.a. mit Hilfe eines beliebigen Zeichenprogrammes erfolgen. Durch das Kopieren (Str. + c) von bestimmten Zellbereichen oder Diagrammen, dem Einfügen (Strg + v) dieser Daten in das Zeichenprogramm und mit dem Abspeichern dieser so erstellten neuen Datei, z.B. im Bitmap-Format, ist dies schnell erledigt.
Ist die Datei erstellt kann Ihr Inhalt in den Kommentar an der gewünschten Stelle eingefügt werden. Ein Kommentarfeld selber fügen Sie in der markierten Excel-Zelle ein, indem Sie mit der rechten Maustaste auf diese klicken und im erscheinenden Kontextmenü den Menüpunkt "Kommentar einfügen" wählen. Das umrahmte Textfeld kann dann wiederum durch Klick mit der rechten Maustaste auf den Textrahmen weiterbearbeitet werden. Hierzu den Menüpunkt "Kommentar formatieren" wählen. In dem sich öffnenden Fenster können Sie nun diverse Formatierungen für das Kommentarfeld definieren. In dem Register "Farben und Linien" können Bilder und Grafiken eingefügt werden. Hierzu wählen Sie in dem ersten Auswahlfeld "Farbe" den Punkt "Fülleffekte" und in dem nächsten Fenster das Register "Grafik". Über die Schaltfläche "Grafik auswählen" gelangen Sie in ein Dialog-Fenster, in dem Sie zu Ihrer Grafik oder einem Bild für den Kommentarhintergrund navigieren können. Markieren Sie das gewünschte Bild und drücken Sie auf "Einfügen" sowie auf "OK" in den bereits geöffneten Dialogen. Im Register "Farben und Linien" können Sie auch noch die Transparenz des Bildes einstellen, was oft sehr hilfreich ist. Über das Kontextmenü der rechten Maustaste können Sie Kommentare jederzeit wieder bearbeiten, löschen oder auch dauerhaft ein- und ausblenden. Achtung: Zusätzlich eingegebener Text wird über der Grafik angezeigt.


 
Tipp 77: Springen in letzte, erste, linke oder rechte Zellen

Wer viel mit großen und umfangreichen Excel-Tabellen arbeiten muss, der kennt das: Der Cursor steht irgendwo mitten in der Tabelle und man möchte nun direkt in die letzte Zelle einer Spalte springen (um vielleicht das Spaltenergebnis zu kontrollieren). Bei nur wenigen Zeilen ist das ja kaum ein Problem. Aber wenn die Tabelle über hunderte oder tausende von Zeilen hat - auf alle Fälle größer ist als der sichtbare Bereich -, dann ist das Ganze nicht mehr so einfach überschau- und durchführbar. Entweder muss nun gescrollt werden, man nutzt die Sprungtasten und/oder -tastenkombinationen oder man klickt sich einfach per Doppelklick hin: Und letzteres geht so:
Wenn Sie z.B. an das untere Ende einer Tabelle springen möchten (die letzte Zelle einer Spalte), gehen Sie so vor: Markieren Sie eine beliebige Zelle in der Spalte. Es wird jetzt die Randmarkierung dieser Zelle sichtbar und Sie können mit dem Mauscursor über die Markierung gehen. Hierbei verändert der Cursor sein Aussehen. Mit einem Doppelklick auf die untere Randmarkierung der gewählten Zelle springen Sie in die unterste Zelle der Spalte. Genaugenommen springen Sie bis zu der letzten Zelle in der Spalte, bevor eine leere Zelle auftritt. Per Doppelklick können Sie auch in andere Richtungen springen: obere Randmarkierung führt nach oben, linke und rechte in die entsprechenden Richtungen der Zeile.
Das Springen funktioniert wie oben erwähnt auch mit Hilfe von Tastenkombinationen: Mit Strg + Ende springen Sie ans Ende der Tabelle. Mit Strg + Pos1 in die erste Zelle (A1) der Tabelle. Mit Shift + Strg + Pfeiltaste in die entsprechende Pfeilrichtung bis zur letzten gefüllten Zelle des gewählten Zellbereiches.


 
Tipp 78: Summen trotz Fehlerwerten richtig berechnen

Fehlerwerte wie z.B. #NV lassen sich beim Arbeiten mit großen Datenmengen und mehreren Tabellenverknüpfungen oft gar nicht vermeiden und sind auch oft gar nicht im Blickfeld des Betrachters bzw. nicht im gerade angezeigten Tabellenbereich. Die Folge ist jedoch, das Excel mit diesen fehlerhaften Informationen nicht weiterrechnen kann und nachfolgende Berechnungen wie z.B. Summenformeln ebenfalls diese Fehlerwerte liefern. Wenn dies nicht gewollt ist und mit den restlichen Werten weitergerechnet werden muss, können Sie mit einer Funktion trotz der Fehlerwerte z.B. eine Summe bilden.
Mit Hilfe der Tabellenfunktion SUMMEWENN kann der Fehlerwert aus der Summenbildung ausgeschlossen werden. Die Formel lautet für eine Summe in Zelle B100 über die Spalte B2 bis B99 dann z.B. =SUMMEWENN(B2:B99;"<>#NV")
Hierbei wird der Funktion - von dem Semikolon getrennt - zunächst der zu summierende Bereich übergeben und dann als zweites Argument in Anführungszeichen das Kriterium für das Summieren. In diesem Beispiel alles was ungleich dem Fehlerwert #NV ist. Auch andere Fehlerwerte lassen sich so abfangen.


 
Tipp 79: Mit Eingabemeldungen andere Benutzer auf Anforderungen hinweisen

Neben den Kommentaren in einzelnen Zellen gibt es noch weitere Möglichkeiten, andere Nutzer einer Datei auf bestimmte Anforderungen z.B. bei der Eingabe von Daten hinzuweisen. Dies sind Eingabemeldungen, welche bei dem Anklicken der jeweiligen Zelle erscheinen. Ähnlich wie ein Kommentar, nur das die Zellen mit Eingabemeldungen nicht wie diese markiert werden (rote Ecke) und das Eingabemeldungen nicht über das Kontextmenü von dem Anwender bearbeitet werden können.
Diese Meldungen können über die Datenüberprüfung (bis Excel 2003: Gültigkeitsprüfung) erstellt werden. Eigentlich ist die Datenüberprüfung dafür vorgesehen, Eingabemöglichkeiten durch festgelegte Regeln zu beschränken, um Fehler zu vermeiden. Wenn Sie auf die Regeln verzichten, können Sie trotzdem die sehr praktischen Eingabemeldungen nutzen und andere Nutzer informieren und warnen. Dazu markieren Sie die Zellen, die Sie mit Eingabemeldungen versehen möchten. Auch mehrere Zellen können gleichzeitig markiert werden, wenn sie identische Meldungen bekommen sollen. Klicken Sie im Register "Daten" im Bereich "Datentools" auf "Datenüberprüfung" (bis Excel 2003: "Daten"/"Gültigkeit").
In der erscheinenden Dialogbox aktivieren Sie das in der folgenden Dialogbox dargestellte Register "Eingabemeldung" und können dann in der Dialogbox in das Feld "Titel" eine Überschrift für Ihren Hinweis und in das Feld "Eingabemeldung" die notwendigen Informationen eingeben. Diese Eingaben bestätigen Sie mit einem Klick auf "OK". Sobald der Anwender nun eine mit einer Eingabemeldung versehene Zelle aktiviert, wird die Meldung angezeigt.


 
Tipp 80: Prüfen, ob in einem Bereich nur Zahlen auftreten

Wenn Sie einmal schnell prüfen möchten, ob in einem großen und unübersichtlichen Bereich nur Zahlen auftreten, können Sie dazu die Statusleiste einsetzen. Rechts in der Statusleiste zeigt Excel automatisch (und im Standard) die Summe der Zahlen an, die aktuell markiert sind. Also: Ganzen Bereich markieren und wenn in mindestens einer der Zellen ein Fehlerwert auftritt, erscheint diese Summe nicht.


 
Tipp 81: Brüche problemlos in Excel-Zellen eingeben

Wenn Sie einen Bruch mit einem Querstrich in eine Zelle eingeben, wandelt Excel die Eingabe automatisch in ein Datum um. Aus 1/4 wird dann automatisch der 1. April. Um die Darstellung des Bruches mit einem Querstrich zu erzeugen und mit diesem Wert auch weiterrechnen zu können, geben Sie davor einfach die Zahl 0 und anschließend ein Leerzeichen ein. Dann wandelt Excel die Eingabe nicht in ein Datum um. für das erwähnte Viertel geben Sie also 0 1/4 ein. Wenn Sie mit diesem Wert in anderen Zellen weiterrechnen, wird das Format beim Erstellen der Formel auch in diesen Zellen angewandt, kann aber z.B. in Dezimale abgeändert werden (Zellen formatieren).


 
Tipp 82: Prüfen, ob eine bestimmte Datei bereits geöffnet ist

Bei der Arbeit mit komplexeren Daten kann es notwendig sein, dass neben der aktuellen Excel-Datei noch weitere Dateien geöffnet sein müssen, um z.B. dort Daten hineinzuschreiben. Arbeitet man zeitgleich mit mehreren geöffneten Dateien, verliert man ggfs. auch schon mal den Überblick, was alles auf ist und was nicht. Dies kann man zum Beispiel mit einem Makro abfragen; auch kann man die Datei dann gleich mit einem Makro öffnen, wenn sie es noch nicht ist. Allerdings kann man sich auch ohne Makro direkt in einer Zelle anzeigen lassen, ob die Datei XYZ schon geöffnet ist. Hierfür hat Excel nicht direkt eine Lösung parat, kann aber mit Hilfe einer vorhandenen Funktion und einem kleinen Umweg diese Information bereit stellen.
Nehmen wir an, Sie benötigen die Datei "Daten.xls" mit dem Tabellenblatt "Januar". Excel schreibt diese Namen bekanntlich als [Daten.xls]Januar!
Mit Hilfe der Funktion INDIREKT können Sie die Frage beantworten, ob diese Datei geöffnet ist. Die Funktion kann nämlich nicht auf geschlossene Dateien zugreifen. Wenn Sie den Dateinamen direkt in die Formel eingeben möchten sieht es wie folgt aus:
="Die Datei ist "&WENN(ISTFEHLER(INDIREKT("'[Daten.xls]Januar'!A"&1));"nicht";"")&"geöffnet."
Hierbei werden die drei Teile in den Anführungszeichen durch das "&" miteinander verknüpft. Betrachten wir nur den Mittelteil der im geschlossenen Zustand das "nicht" liefern soll:
WENN(ISTFEHLER(INDIREKT("'[Daten.xls]Januar'!A"&1));"nicht";"")
Hier will INDIREKT auf die Datei und deren Zelle A1 zugreifen. Schafft INDIREKT das jedoch nicht, weil die Datei nicht geöffnet ist, wird ein Fehler erzeugt, den ISTFEHLER auffängt und gemäß den WENN-Bedingungen das "nicht" den Verknüpfungen zufügt. Natürlich können Sie die Dateinamen und Blattnamen auch in Zellen hinterlegen. Z.B. in A1: Daten und in B1: Januar, dann lautet der Formelteil: … INDIREKT("'["&A1&".xls]"&B1&"'!A"&1)


 
Tipp 83: Nur positive oder nur negative Werte einer Liste addieren

Oftmals sind in Listen positive und negative Werte bunt gemischt vorhanden (z.B. Rechnungs- und Gutschriftsbeträge). Soll in einer Spalte nun nicht die Gesamtsumme aller Werte aufaddiert, sondern nur die Teilsumme der positiven oder negativen Werte, dann geht das z.B. wie in den folgenden Formeln dargestellt mit Hilfe der Funktion SUMMEWENN:
=SUMMEWENN(A1:A100;">0")
=SUMMEWENN(A1:A100;"<0")



 
Tipp 84: Summierung bei mehreren Suchkriterien durchführen

Gibt es für die Summierung von bestimmten Werten mehrere Suchkriterien in verschiedenen Spalten, so kommt man mit der einfachen Funktion SUMMEWENN nicht weiter, denn SUMMEWENN durchsucht nur eine Spalte nach einem Suchkriterium.
Wenn z.B. alle Zellen summiert werden sollen, bei denen in der 1. Spalte ein bestimmtes Suchkriterium (z.B. Kunde) steht und zusätzlich in der 2. Spalte ein weiteres (z.B. Produkt), schafft SUMMEWENN dies nicht, genauso wie es nicht mehrere Spalten aufsummieren kann.
Abhilfe schafft eine Arrayformel oder ab Excel 2007 auch schon mal SUMMEWENNS.
Fangen wir doch mal einfach mit der Funktion SUMMEWENNS an: Angenommen wir haben in einer Tabelle in Spalte A die Kunden und in B die Produkte. Aufzusummieren sind die Umsätze in Spalte C. Wollen wir nun für einen Kunden A die Summe aller gekauften Produkte B haben, so lautet die Formel:
=SUMMEWENNS(C2:C7;A2:A7;"A";B2:B7;"B")
Im Beispiel ist der Umsatzwertebereich in C2:C7 (Anders als bei SUMMEWENN steht dies Kriterium am Anfang der Formel). Aus den Kunden in A2:A7 soll Kunde A gesucht und dann zusätzlich in B2:B7 nach Produkt B und die Summe der Umsätze bei dieser Kombination ausgegeben werden. Dies lässt sich auch um weitere Kriterien ergänzen, es sind bis zu 127 Paare zulässig ... wer's braucht ...
Mit einer Arrayformel sieht das Ganze dann so aus:
{=SUMME(WENN((A:A="A")*(B:B="B");C:C))}
Hier bedienen wir uns "nur" SUMME und WENN, allerdings innerhalb der Matrix ... Ein Beispiel und Infos zur Erstellung der Arrayformel/Matrixformel finden Sie in unserer Beispieldatei.


 
Tipp 85: Mit der Anzahl von veränderlichen Werten einer gefilterten Liste weiterrechnen

Mit der Funktion ANZAHL kann man kontrollieren, wieviel Zellen bereits mit Werten gefüllt sind und mit diesem Ergebnis z.B. in anderen Zellen weiter rechnen. Nutz man die Funktion jedoch unterhalb einer Liste, deren Zeilen man filtert, liefert die Formel weiterhin das gleiche Ergebnis. Sie bezieht also die gefilterten und ungefilterten Werte mit ein. Möchte man allerdings mit der veränderlichen Anzahl von gefilterten Werten oder Datensätzen weiterrechnen, so empfiehlt sich eine andere Funktion.
Mit der Funktion TEILERGEBNIS, welche vielen vom Berechnen von Summen in gefilterten Listen bekannt ist, geht dies auch. Allerdings kombiniert mit einer anderen Funktionskonstanten als der bekannten SUMME, welche Werte summiert. Die Funktion TEILERGEBNIS(Funktion;Bezug1;[Bezug2];...) hat an der ersten Stelle der Klammer eine Funktionskonstante, deren Wert bestimmt, was Excel mit den Daten in "Bezug" anstellt. Weitbekannt ist hier der Wert 9 für das Bilden von Summen: =TEILERGEBNIS(9;A2:A100)
Für das Berechnen der Anzahl der Werte verwendet man die 2: =TEILERGEBNIS(2;A2:A100)
Weitere Möglichkeiten für Teilergebnisse liefern die Werte 1 für Mittelwert, 3 für Anzahl2, 4 für Max, 5 für Min, 6 für Produkt, 7 für Stabw, 8 für Stabwn, 10 für Varianz und 11 für Varianzen.
Mit Bezug2 können Sie übrigens bis zu 254 Bereiche oder Bezüge benennen, für die Sie das Teilergebnis berechnen möchten… nur falls Sie das mal dringend brauchen ;-)


 
Tipp 86: Aufzählungszeichen ganz leicht in Excel anzeigen

Mal eben eine Aufzählung aus Word in Excel kopiert - und schon sind die Aufzählungszeichen weg. Kennen wir alle, nicht wahr. Leider kann in Excel ein bereits bestehender Text nicht ohne weiteres mit Aufzählungszeichen formatiert werden. Als kleine Hilfe kann da eine Tastenkombination dienen. Aber auch eine Formatierung der Zellen ist mit einem kleinen Trick möglich. Doch zunächst zur Tastenkombi: Drücken Sie in einer leeren Zelle die Kombination Alt + 7 am Ziffernblock (nicht die 7 in der Zahlenreihe auf der Tastatur, denn die ist vorbelegt!). Jetzt fügt Excel einen Aufzählungspunkt ein. Evtl. bereits vorhandener Text wird überschrieben, da die Zelle nicht lediglich formatiert wird. Folgend sollte zur besseren Übersicht ein Leerzeichen und dann der eigentliche Aufzählungstext eingegeben werden. Der Punkt wird als Symbol eingefügt und ist in der Bearbeitungszeile sichtbar. Tipp: je nach Modell kann mit der Tastenkombi Alt + FN + 7 auch am Laptop der Ziffernblock angesprochen werden (ansonsten wird der 7. Befehl in der Quick Access Toolbar ausgeführt).
Um das Überschreiben zu verhindern und auch nachträglich Werte einfügen zu können, sind Zellen auch mit dem Aufzählungspunkt formatierbar. Wichtig auch hierfür ist die Aktivierung des Ziffernblocks auf Ihrer Tastatur. Markieren Sie zunächst die Zellen, in denen Sie die Aufzählungszeichen einfügen wollen. Öffnen Sie das Menü "Zellen formatieren", wählen "Zahlen" und die Kategorie "Benutzerdefiniert" (ganz unten). Klicken Sie in das Textfeld unter "Typ" und löschen Sie den vorhandenen Eintrag. Halten Sie die Alt-Taste gedrückt, geben Sie auf dem Ziffernblock die Zahlen 0149 ein und lassen dann los. Nun erscheint der Aufzählungs-Punkt in diesem Feld. Drücken Sie die Leertaste und danach das @-Symbol. Excel sollte jetzt o @ anzeigen und Sie können Sie mit OK bestätigen und Ihre Aufzählungstexte eingeben.
Haben Sie als Zeichen lieber einen Strich, können Sie alternativ auch einen Bindestrich/Minuszeichen oder etwas anderes für Sie sinnvolles (- ,+ ,= ,$ ,§ , #, …) statt des Punktes (0149) eingeben. Wichtig für die Zuweisung des Formates ist lediglich das @-Symbol.


 
Tipp 87: Durchmesser-Zeichen (Ø) mit Tastenkombination erzeugen

Um das Durchmesser-Zeichen in einer Zelle anzuzeigen kann man sich der Windows-Zeichentabele bedienen und dies dort suchen, kopieren und einfügen. Schneller geht es, wenn man angeleht an Tipp 86 mit einer Tastenkombination arbeitet. Vorausgesetzt, man kann sich Alt-0216 merken ... also Alt-Taste gedrückt halten und auf der Zifferntastatur (wichtig, geht nur dort!) die Ziffernreihenfolge 0216 eintippen. Sobald man die Alt-Taste wieder losläßt, erscheint das Ø-Zeichen. Die Tastenkombinationen aller Sonderzeichen, welche über die deutsche Tastatur erreichbar sind, finden Sie auf der folgenden WebSite in sinnvollen Gruppen gegliedert: http://typefacts.com/tastaturkuerzel


 
Tipp 88: Dropdown-Liste in Excel erstellen

Insbesondere wenn mehrere Personen eine Excel-Datei bearbeiten und diese um Datensätze ergänzen, kann es zu unterschiedlichen Benennungen von Werten oder auch Schreibfehlern u.ä. bei den Einträgen kommen. Diese erschweren nachträgliche Auswertungen wiederum sehr und müssen ggfs. nachträglich korrigiert werden. Hier lohnt es sich vielleicht eine Dropdown-Liste zu erstellen, so dass Sie in ein bestimmtes Feld nur fest vorgegebene Werte eintragen (lassen) können. Auch muss man die Werte nicht immer erneut schreiben, sondern wählt sie einfach aus einer Liste aus.
Bevor Sie eine Dropdown-Liste erstellen können, müssen Sie die möglichen Eingabewerte definieren und in einem (ggfs. nicht sicht-/druckbaren Bereich) als Liste untereinander eingeben. Die Dropdown-Liste erzeugen Sie nun, indem Sie die Zelle anklicken, in der die Auswahl später verfügbar sein soll und dann im Register "Daten" den Bereich "Datentools" und dort "Datenüberprüfung" klicken. In dem sich öffnenden Fester wählen Sie im Register "Einstellungen" bei "Gültigkeitskriterien" im Feld "Zulassen" den Wert "Liste" und geben bei "Quelle" den Bezug zu dem zuvor angelegten Wertebereich ein oder markieren einfach den gewünschten Zellbereich mit der Maus. Stellen Sie jetzt noch sicher, dass das Kontrollkästchen "Zellendropdown" aktiviert ist und drücken Sie "OK". Somit ist die Liste schon fertig und kann in der definierten Zelle genutzt werden.
Optional können Sie in den beiden anderen Registern noch definieren, ob Eingabemeldungen beim Klicken auf die Zelle angezeigt werden sollen, oder wie Excel bei der Eingabe von ungültigen Daten reagieren soll ... einfach ausprobieren!


 
Tipp 89: Auswahlliste mit bereits eingegebenen Daten anzeigen lassen

Manchmal ist es sinnvoll, dass Excel beim Eingeben von neuen Daten bereits Textvorschläge macht. Sie geben die ersten Zeichen eines Wortes ein und erhalten daraufhin einen Vorschlag zur automatischen Vervollständigung des Zelleintrags, also für den gesamten Inhalt der Zelle. Der Vorschlag ist erst noch markiert, kann aber mit der Eingabetaste komplett übernommen werden. Das Vorgehen funktioniert immer, wenn in den darüberliegenden Zellen bereits Inhalte mit gleichem Wortanfang stehen und es keine Verwechslung/Dopplung der Einträge gibt. In manchen Fällen sind aber relativ viele Zeichen einzugeben, bis Excel endlich einen eindeutigen Eintrag erkennt und vorschlägt. Schwierig bzw. viel Aufwand ist es also, wenn viele gleich beginnende oder auch komplexe Inhalte in den Zellen verarbeitet werden sollen. Sind die einzugebenden Texte immer wiederkehrend, gibt es auch die Möglichkeit, sich alle bereits über der Zelle eingetragenen Inhalte in einer Auswahlliste anzeigen zu lassen und den gewünschten Wert einfach zu selektieren.
Um diese Auswahliste zu erhalten, müssen Sie nur die Tastenkombination "ALT" + "Pfeil nach unten" drücken. Excel bietet dann in der markierten Zelle eine Auswahlliste mit allen darüber liegenden Informationen an. Die Auswahl können Sie nun mit der Maus oder mit den Pfeiltasten treffen und mit der Eingabetaste bestätigen - fertig. Sollte bei Ihnen schon das automatische Vervollständigen der Zellwerte nicht funktionieren, ist es evtl. in den "Excel-Optionen/Erweitert" in den "Bearbeitungsoptionen" zu aktivieren.


 
Tipp 90: Datum eines Wochentages in einer bestimmten Kalenderwoche errechnen

Im Projektcontrolling kann es sinnvoll sein, in Berichten einzelne Fälligkeitsdaten für bestimmte Projektschritte anzugeben. Oftmals wird hierzu der letzte Tag der Woche gewählt und dieses Datum per Hand eingetragen. Dabei gibt es Möglichkeiten das Datum z.B. eines Freitages automatisch mithilfe der Jahreszahl und der Kalenderwoche zu berechnen - und diese werden ja üblicherweise in Projekten mitgeführt. Zur Lösung dieses Problems kombinieren Sie die Funktionen DATUM und WOCHENTAG miteinander, welche die folgende Syntax haben:
DATUM(Jahr;Monat;Tag)     und     WOCHENTAG(Zahl;Typ)
Die Formel für die Berechnung des Freitags der Kalenderwoche 16 in 2016 lautet beispielsweise:
=DATUM(2016;1;7*16+1-WOCHENTAG(DATUM(2016;;);3))
Als Ergebnis wird das Datum 15.04.2016 ausgegeben, der heutige Freitag!
Excel kommt mit den beiden verschachtelten Funktionen somit allein in die Nähe des Tages, konkret aber immer nur bis zum Donnerstag und braucht somit von Ihnen noch die klare Info, welchen Tag es ausgeben soll. Für Freitag ist somit +1 im Mittelteil der Formel einzugeben. Für die anderen Wochentage verwenden Sie die folgenden Werte: Montag -3, Dienstag -2, Mittwoch -1, Donnerstag -0, Samstag +2 und Sonntag +3.


 
Tipp 91: Spalten einer Tabelle nach Zeileninhalt sortieren

Die Standardeinstellung für das Sortieren in Excel ist das Sortieren nach Spalteninhalt. Dies ist sicherlich auch die gebräuchlichste Variante, jedoch nicht die einzige Möglichkeit, um Listen zu sortieren. Falls Sie eine Liste mit Zeilenstruktur sortieren möchten oder z.B. in einer Tabelle die Spalten nach den Überschriften in alphabetische Reihenfolge sortieren möchten, können Sie dies problemlos erledigen. Lediglich die Option hierfür hat man in Excel etwas versteckt:
Markieren Sie die komplette Liste, welche Sie sortieren wollen, ggfs. incl. der Spaltenüberschriften. Klicken Sie im Register DATEN auf SORTIEREN. Stören Sie sich nicht daran, dass die erste Zeile nicht mitmarkiert ist, zu diesem Zeitpunkt glaubt Excel noch, dass Sie "normal" sortieren wollen und erkennt Überschriften. Soweit also alles noch ganz bekannt von dem üblichen Sortieren nach Spalteninhalt. Jetzt wählen Sie jedoch die Schaltfläche OPTIONEN. In dem sich öffnenden Fenster ist "Zeilen sortieren" ausgewählt. Wenn Sie die Option "Spalten sortieren" wählen und mit OK bestätigen, ändern sich die Eingabemöglichkeiten in dem vorherigen Fenster. Sie können jetzt nach Zeilen sortieren und z.B. die Zeile, nach der sortiert werden soll, auswählen. Dies könnte z.B. die Zeile mit der Spaltenüberschrift sein. Klicken Sie auf OK und die Spalten werden sortiert.
Wenn Sie im selben Arbeitsblatt eine andere Liste wieder zeilenweise sortieren möchten, dann müssen Sie die Option wieder entsprechend umstellen. Schalten Sie dazu die Option "Spalten sortieren" im Dialogfenster zurück auf "Zeilen sortieren". Auf Sortiervorgänge in anderen Mappen oder anderen Arbeitsblättern hat das Umstellen auf das Sortieren nach Spalten keinen Einfluss.
Übrigens: Wenn Sie Zeilen sortieren, die Teil einer Arbeitsblattgliederung sind, werden die Gruppen der höchsten Ebene (Ebene 1) von Excel sortiert, sodass die Detailzeilen oder -spalten zusammen bleiben, selbst wenn die Detailzeilen oder -spalten ausgeblendet sind.


 
Tipp 92: Unterschiedliche Tabellenbereiche zur Präsentation auf einer Seite ausdrucken

Oftmals hat man in größeren Tabellen und Dateien mehrere Teilergebnisse, Diagramme und Darstellungen, welche nicht direkt beieinander stehen und somit selbst mit einer Druckbereich-Optimierung nicht auf einer Seite ausgedruckt oder auf ein PDF gebracht werden können bzw. nicht auf eine Präsentationsseite für PPT passen. Wenn Sie die Bereiche markieren und drucken, werden leider alle auf einzelne Seiten gedruckt/gespeichert. In wiederkehrenden Berichten die Ergebnisse immer wieder kopieren und zusammenzuflicken, ist eine zu aufwendige Lösung. Mit der bereits für die Überwachung von Formeln in Tipp 47 vorgestellten Kamerafunktion können Sie die Teilbereiche jedoch einmal auf einem extra Tabellenblatt in einem späteren Druckbereich zusammenstellen und auch künftig bei aktualisierten Daten immer noch leicht auf einer Seite verfügbar haben.
Da die Kamera nicht über die Standard-Menüleiste erreichbar ist, empfiehlt es sich, sie in die Symbolleiste aufzunehmen. Bis Excel 2003 über "Ansicht", "Symbolleisten", "Anpassen ..." und im Register "Befehle" in der Kategorie "Extras" die Kamera selektieren. Ab Excel 2007 die Symbolleiste für den Schnellzugriff anpassen - "weitere Befehle", "alle Befehle" zeigt alphabetisch gelistet die Kamera. Markieren Sie dann einen gewünschten Tabellenbereich, ein Diagramm o.ä. und klicken Sie auf das Kamera-Symbol. Excel selectiert dann diese Zelle und ein folgender Klick in einen neuen Tabellenbereich/Druckbereich fügt das "Foto" als dynamisches Bild ein. Stellen Sie mit Hilfe solcher verknüpfter Bilder nun in dem neuen Tabellenbereich eine Druckansicht aller Tabellenbereiche und Darstellungen zusammen, die Sie für den Ausdruck brauchen. Alle künftigen Veränderungen in den Originalzellen werden aufgrund der bestehenden Verknüpfung in Ihrem neuen Druckbereich aktuell und komprimiert dargestellt. Wichtige Voraussetzung für den Druck: Sorgen Sie dafür, dass im Kontextmenü unter "Grafik formatieren" auf der Registerkarte "Eigenschaften" die Option "Objekt drucken" aktiviert ist.


 
Tipp 93: Schnelles Kopieren von Tabellenblättern

Kopien von Tabellenblättern können in einer Exceldatei mit 5 Klicks erstellt werden. Gewöhnlich ist das ein Rechtsklick auf den Reiter des Tabellenblattes für das Öffnen des Kontextmenüs, dann in der Auswahl auf "Verschieben/Kopieren" sowie noch die Wahl der Stelle zum Einfügen der Kopie und den Haken für Kopie, damit das Blatt nicht einfach nur Verschoben wird. Die Bestätigung mit "OK" erstellt dann die Kopie. Das ganze geht aber auch mit nur einem Klick!
Wenn Sie den Klick auf das zu kopierende Tabellenblatt halten und zusätzlich die Taste "Strg" drücken und gedrückt halten, kann eine Kopie des Tabellenblattes nun mit dem Mauszeiger an eine beliebige Position neben das bereits bestehende Tabellenblatt gezogen und dort fallengelassen werden (Loslassen der Maustaste). Excel zeigt beim Ziehen bereits, analog zum Kopieren von Dateien im Windows Explorer, durch ein kleines "+" an, dass das Objekt/Tabellenblatt nun nicht nur verschoben, sondern kopiert wird.


 
Tipp 94: Differenz zweier Datumsangaben unter Berücksichtigung von Schaltjahren

Ab Excel 2013 kann man die Differenz zweier Datumsangaben (Anzahl Tage) zusätzlich zu der in Tipp 55 beschriebenen Funktion DATEDIF() auch mit der Funktion DAYS() berechnen. Hierbei werden auch die Schaltjahre korrekt berücksichtigt. Die Syntax lautet wie folgt: DAYS(Endedatum; Anfangsdatum). Selbstverständlich können Sie auch mithilfe einer einfachen Subtraktion zweier Datumswerte eine Differenz erhalten, achten Sie dabei aber auf die Schaltjahre ... ;-)


 
Tipp 95: Text in gültigen Zahlenwert umwandeln

Gerade bei aus Vorsystemen importierten Daten kommt es immer wieder vor, dass Zahlen von Excel nicht als solche erkannt, sondern als Text interpretiert werden. Bis Excel 2007 blieb dann nur der Weg über den Textimport-Assistenten oder die Funktion WERT, welche allerdings mit Dezimalzahlen nicht funktioniert. Seit Excel 2013 gibt es hierfür die neue Funktion NUMBERVALUE. Diese Funktion wandelt eine als Text erkannte Zahl in einen gültigen Zahlenwert um. Als Argumente übergeben Sie der Funktion NUMBERVALUE(Text, Decimal_separator, Group_separator) den Text und das in der Datenquelle verwendete Dezimaltrennzeichen sowie optional noch ein Tausendertrennzeichen. Als Ergebnis erhalten Sie dann eine Zahl mit dem Standard-Dezimaltrennzeichen und ggfs. dem Tausendertrennzeichen.


 
Tipp 96: Inhalte schnell in alle angrenzenden Zellen kopieren

Sie haben neben einer bestehenden Tabelle in einer neuen Spalte Werte für eine Berechnung in der obersten Zelle eingetragen und die Formel passt. Jetzt möchten Sie diese natürlich gerne bis an das Ende der Tabelle runter kopieren. Das geht mit kopieren und einfügen sicher auf unterschiedliche Art und Weise mit Maus und/oder Tastatur, ist bei sehr langen Tabellen manchmal umständlich, wenn das Tabellenende nicht im sichtbaren Bereich ist. Den in der Zelle stehenden Inhalt können Sie aber auch schnell mit der Maus in alle angrenzenden Zellen unterhalb dieser Zelle kopieren. Hierzu genügt es, die Zelle mit dem zu kopierenden Inhalt zu markieren. Es wird jetzt die Randmarkierung dieser Zelle sichtbar und Sie können mit dem Mauscursor über die Markierung an der rechten unteren Ecke gehen, wo sich der sogenannte "Anfasser" (auch: Ausfüllkästchen) befindet. Dabei verändert der Cursor sein Aussehen zu einem kleinen, schwarzen Kreuz. Hier mit der Maus ein Doppelklick und der Inhalt wird bis zu der letzten ausgefüllten, angrenzenden Zelle kopiert. Vorsicht ist geboten, wenn zwischendurch angrenzende Zellen leer sind, dann wird auch der Einfügevorgang hier abgebrochen.


 
Tipp 97: Formel schnell ohne Anpassung der relativen Zellbezüge kopieren

Wenn Sie eine Formel von einer Zelle in die darunter liegende kopieren, passt Excel die standardmäßig verwendeten relativen Zellbezüge automatisch an. Oft ist es jedoch erwünscht, eine Formel mit genau denselben Bezügen in eine andere Zelle zu kopieren. Eine Möglichkeit ist hierbei, die Formel in der Bearbeitungsleiste zu markieren, dort zu kopieren und dann in die gewünschte Zelle einzufügen. Alternativ kann man natürlich auch nach dem Kopieren die Bezüge ändern oder kurzfristig zu absoluten Bezügen wechseln. Diese Möglichkeiten sind, insbesondere bei aufwendigen Formeln, recht umständlich und es lässt sich einfacher lösen. Excel bietet hierzu eine spezielle Tastenkombination an. Sie markieren zunächst die Zelle unterhalb der Zelle mit der zu kopierenden Formel. Mit der Tastenkombination Strg + , können Sie jetzt die Formel über der aktiven Zelle übernehmen. Die Tastenkombination Strg + Shift + , macht übrigens das Gleiche bei Werten. Soll die Kopie der Formel nicht in der Zelle direkt unter dem Original landen, sondern in einer beliebigen anderen Zelle, kann die Tastenkombi evtl. auch weiterhelfen. Wenn keine Formate zu Schaden kommen, lässt sich die neu erzeugte Formel durch "Anfassen" und "Verschieben" an den gewünschten Ort rücken, da ja beim Verschieben die relativen Zellbezüge erhalten bleiben. Gleiches gilt übrigens auch für das Ausschneiden: Sie können die Formel per Maus mit Ausschneiden und Einfügen oder per Tastenkombi STRG + x und STRG + v an den neuen Ort bringen.


 
Tipp 98: Zeiten abrunden und Zeitformate ändern

Für das Runden von Zahlen bietet Excel bekanntlich einige Standardfunktionen wie RUNDEN, ABUNDEN, AUFRUNDEN, VRUNDEN, OBER- und UNTERGRENZE und vielleicht noch mehr an. Doch wenn es an das Rechnen mit Zeiten und unterschiedliche Schreibweisen von Zeiten (hh:mm und hh,mm) geht, wird es oftmals schwierig. Angenommen also, Sie möchten Werte in den Stundenaufschreibungen im Format hh:mm für den Bericht auf die volle Viertelstunde abrunden und damit weiterrechnen. Soll heißen aus der erfassten Zeit 8:14 sollen 8,00 Stunden werden, aus 8:21 nur noch 8,25 oder aus 8:59 nur 8,75 Stunden.
Da diese Zeitwerte nicht im Zehnersystem ausgedrückt werden, müssen Sie hierfür einen kleinen Trick nutzen: Multiplizieren Sie die Zeit zunächst mit der Anzahl der Stunden eines Tages, also 24. Da wir auf Viertelstunden runden möchten, müssen wir die Stunde noch weiter in 4 Viertelstunden teilen - also multiplizieren mit 4. Sie können auch direkt mit 96 multiplizieren. Für Rundungen auf zehn Minuten übrigens mit 144, auf fünf Minuten mit 288. Dieses Ergebnis können Sie jetzt runden und dann durch die gewählten Multiplikatoren teilen. Das Ergebnis sind jetzt die gerundeten Stunden (noch in dem selben Zeitformat), also z.B. 8:00 Stunden. Die Formel für einen Zeitwert in der Zelle A1 lautet bis hier: =ABRUNDEN((A1*24*4);0)/24/4 oder alternativ: =ABRUNDEN((A1*96);0)/96
Fehlt aber noch die Umrechnung des Formates. Hierfür ist es nützlich zu wissen, dass Excel zum Rechnen im Zehnersystem den Wert von 1/24-tel Tag für eine Stunde ansetzt. Also ist für die Umrechnung vom Format hh:mm in das Zehnersystem mit 24 zu multiplizieren und umgekehrt durch 24 zu dividieren. Wenn wir uns in der obigen Formel das multiplizieren und dividieren mit 24 kürzen, lautet die fertige Formel: =ABRUNDEN((A1*24*4);0)/4


 
Tipp 99: Zellinhalte mit Bezügen, Formaten und Spaltenbreite komfortabel in andere Bereiche kopieren

Nachdem Sie einmal strukturierte Daten, wie z.B. Produkt-, Projekt- oder Arbeitsschrittbezeichnungen in einem Arbeitsblatt angelegt haben, werden diese häufig auch in anderen Arbeitsblättern wieder für andere/folgende Auswertungen benötigt. Ärgerlich ist es dann, wenn man diese "nur" einfach kopiert hat und die Struktur oder Reihenfolge der Daten sich nachträglich ändert oder erweitert. Die elegantere Methode ist der Einsatz von relativen Bezügen aus dem zweiten Arbeitsblatt auf den Quellbereich im ersten. Anstatt einen relativen Bezug jedoch von Hand einzugeben und durch Kopieren, Einfügen und Autoausfüllen oder auch Ziehen mit der Maus auf entsprechend viele Zelle zu übertragen, lässt sich auch eine recht unbekannte Excel-Möglichkeit nutzen, welche schneller und komfortabler ist.
Markieren Sie zunächst den gesamten Bereich, auf den Sie sich beziehen möchten und kopieren Sie diesen. Klicken Sie jetzt in die linke obere Zelle in Ihrem Zielbereich. In den älteren Excel-Versionen rufen Sie den Befehl "Inhalte einfügen" auf. Hier finden Sie die Schaltfläche "Verknüpfen". In den neueren Versionen finden Sie "Verknüpfungen einfügen" in den "Einfügeoptionen" unter "Weitere Einfügeoptionen" als Schaltfläche mit dem "Kettensymbol".
Der kopierte Bereich wird jetzt verknüpft eingefügt. Dies bedeutet, das bei Änderungen der Ursprungszellen automatisch auch die Inhalte in den verknüpften Zellen geändert werden. Beim verknüpften Einfügen überträgt Excel nur die reinen Daten, nicht die Formate der Ursprungszellen. Die Formate und sogar die Zellenbreite können Sie in weiteren Arbeitsschritten zusätzlich übernehmen, wenn Sie jeweils wiederholt "Inhalte einfügen"/"Einfügeoptionen" wählen und nacheinander einzeln auf "Formate" bzw. "Spaltenbreite" klicken. Dies funktioniert solange, wie sich die kopierten Informationen noch im Zwischenspeicher befinden.


 
Tipp 100: Listen nach Farbkriterien filtern

Um in sehr langen Listen nur bestimmte Werte zu selektieren nutzt man den Filter. Mit ihm kann man dann bestimmte Werte ausblenden oder nur bestimmte anzeigen lassen, was auch nach logischen Kriterien (<, >, =, etc.) definierbar ist. Man kann die Top 10 Werte filtern oder die Werte über oder unter dem Durchschnitt anzeigen. Wenig bekannt ist die Möglichkeit nach Farben zu filtern, was besonders interessant wird, wenn Sie in der zu filternden Spalte bedingte Formatierungen verwenden. Und dies geht so:
Wenn Sie in Ihrer Liste noch keinen Filtern eingestellt haben, klicken Sie in Ihre zu filternde Liste und wählen in dem Register Daten in der Gruppe Sortieren und Filtern den Befehl Filtern. Die Liste bekommt so in der Überschriftszeile die Filtersymbole mit den dahinter wählbaren Dropdownmenüs. Klicken Sie in der Spalte in der Ihre Farbwerte stehen auf dieses Pfeilsymbol, öffnet sich das Dropdownmenü. Hier können Sie nun neben vielen anderen, teilweise oben erwähnten Möglichkeiten, den Befehl Nach Farbe filtern wählen und in dem nach rechts verzweigenden Menü die Auswahl der in Ihrer Spalte verwendeten Farben treffen.


 
Tipp 101: Listen nach Farbkriterien sortieren

Wenn sie zusätzlich zu den in Tipp 100 beschriebenen Filtermöglichkeiten nach Farbkriterien Ihre Werte auch noch nach Farben sortieren möchten, bietet Excel auch hierzu eine Möglichkeit: Wenn Sie den Filter eingestellt haben, klicken Sie in der Spalte in der Ihre Farbwerte stehen auf das Pfeilsymbol. Es öffnet sich das Dropdownmenü und Sie können den Befehl Nach Farbe sortieren wählen und in dem nach rechts verzweigenden Menü die Auswahl der in Ihrer Spalte verwendeten Farben treffen.


 
Tipp 102: Laufzeit eines Darlehens mit der Funktion ZZR berechnen

Excel bietet bereits einige vordefinierte Funktionen, mit denen Darlehen berechnet werden können. Nicht nur der Zinssatz, die Tilgungsrate oder die hieraus folgende Annuität (s. unseren Tipp 73 - Annuität berechnen mit der Funktion RMZ) sind wichtige Faktoren für einen Darlehensvergleich. Auch die Zeit, welche für die vollständige Rückzahlung des gewährten Darlehens benötigt wird, ist entscheidend. Um diese Dauer der Zahlungsverpflichtung bei konstantem Zinssatz zu berechnen stellt Excel von Haus aus bereits die vordefinierte Funktion ZZR zur Verfügung.
Die Funktion lautet: =ZZR(Zins;Rmz;Bw;[Zw];[F])
Die ersten drei Argumente der Syntax von ZZR sind erforderlich, zwei weitere sind optional. Zins ist der Zinssatz pro Periode in % inklusive der Tilgung. Rmz ist die regelmäßige Zahlung, also der Betrag der Annuität (Achtung: mit neg. Vorzeichen), welche pro Periode gezahlt werden muss. Die Annuität umfasst Zins und Tilgung. Bw ist der Barwert bzw. der Gesamtdarlehensbetrag. Zw ist optional und wird bei Fehlen als 0 angenommen; gibt den zukünftigen Wert bzw. Endwert nach der letzten Zahlung an. Dieser ist bei Krediten meist Null. F ist optional und wird bei Fehlen als 0 angenommen; gibt an, ob die Zahlungen am Anfang (1) oder Ende (0) einer Periode fällig sind. Die Fälligkeit ist bei Krediten meist am Ende einer Periode.


 
Tipp 103: Die letzte Zelle im Arbeitsblatt anspringen, die noch Daten oder Formatierungen enthält

Excel merkt sich gerne Zellen, die Sie vormals für Daten oder Berechnungen genutzt haben, auch wenn dort ggfs. nur noch Formate definiert sind und diese weit von dem aktuell von Ihnen genutzten Bereich liegen. Da alle tatsächlich genutzten Zellen in die Berechnung der Dateigröße einfließen kann das zu erheblichem Speicherbedarf und längeren Laufzeiten führen. Wenn Sie Zellbereiche zum Beispiel ab Zeile 5.000 für temporäre Berechnungen einmal genutzt haben, werden sie weiterhin als genutzt angesehen, auch dann, wenn Sie die Inhalte gelöscht haben und heute nur noch die Zellen 1 bis 30 Nutzen.
Um zunächst zu überprüfen, welchen Bereich Excel als genutzt ansieht, verwenden Sie die Tastenkombination Strg + Ende. Damit springt Excel in die unterste rechte Zelle des genutzten Bereichs, welcher jemals bearbeitet wurde. Sollte die unterste rechte Zelle weit unter oder/und neben den tatsächlich noch genutzten Zellen liegen, lohnt sich ein Eingriff und es können die überflüssigen Zeilen und/oder Spalten bis zu der letzten Zelle gelöscht werden: Zeilen- und/oder Spaltenköpfe markieren und mit dem Kontextmenü der rechten Maustaste löschen, oder alternativ die Tastenkombination Strg + - nutzen. Nach dem Löschen schließen Sie die Arbeitsmappe, speichern dabei die Änderungen und öffnen sie wieder. Excel hat jetzt die Zellen "vergessen", was Sie mit Strg + Ende leicht überprüfen können.


 
Tipp 104: Aus dem aktuellen Datum das Quartal berechnen

Um z.B. für den nächsten Quartalsbericht ein bereits vorhandenes Datumsfeld für die automatische Ausweisung des Quartals in einer Überschrift zu nutzen, können Sie das Quartal mit "WennWennWenn..." ermitteln. Leichter für Sie und weniger Excelrechenleistung beanspruchend geht es mit einer kleinen Formel.
Schreiben Sie das gewünschte Datum z.B. in A1 (für das Systemdatum auch mit Tastenkombi Strg + .), dann können Sie das zugehörige Quartal des Jahres einfach mit folgender Formel berechnen:
=AUFRUNDEN(MONAT(A1)/3;0)
Aus dem eingegebenen Datum wird mit Hilfe der Funktion MONAT der Monatswert (1-12) ermittelt und durch 3 geteilt. Da das z.B. im März mit 3 noch gut funktioniert, aber im Februar schon 0,66.. ergeben würde, runden wir das Ergebnis auf, mit 0 Nachkommastellen. Sie können das ganze dann auch noch hübsch verketten und direkt in eine Berichtsüberschrift o.ä. übernehmen:
="Quartalsbericht für das "&AUFRUNDEN(MONAT(A1)/3;0)&". Quartal"


 
Tipp 105: Spaltenindex für SVERWEIS auch in großen Tabellen einfach ermitteln

Der SVERWEIS ist mit eine der beliebtesten Funktionen für die Arbeit mit Excel. Wie er funktioniert, haben wir bereits in Tipp 27 erklärt. Wer den SVERWEIS oft benutzt, kennt gewiss das folgende Dilemma: Das Suchkriterium und die Datenmatrix sind im Funktionsassistenten eingegeben, es fehlt jetzt nur noch der Spaltenindex bzw. die Spaltennummer aus der ein übereinstimmender Wert ermittelt werden soll. Bei kleinen Tabellen mit nur ein paar Spalten ist das ja noch übersichtlich oder lässt sich gut im Kopf rechnen ... zack, fertig! Bei richtig breiten, also so richtig breiten Tabellen, führt das schnell zu Fehlern. Sie können die Anzahl der Spalten bzw. den Spaltenindex jedoch recht einfach und schnell auf verschiedene Art und Weise ermitteln:
Die einfachste Methode lautet wohl: Augen auf! Denn Excel zeigt Ihnen beim Markieren der Matrix bereits rechts unten neben der Bereichsmarkierung an, wieviel Zeilen und Spalten Sie aktuell auswählen. Das funktioniert beim Markieren mit der Maus, solange Sie die Maustaste gedrückt halten, wie auch mit der Tastatur, solange Sie mit der Shift- und den Pfeiltasten markieren. Aufpassen muss man lediglich beim Verwenden des Formelassistenten, da dieser im Vollbildmodus die Darstellung unterdrückt. Dann also rechts neben das Eintragfeld für die Matrix klicken und den Assistenten minimieren. Jetzt wird die Anzahl beim Markieren angezeigt, nur noch Spaltenzahl merken und in das entsprechende Feld eintragen.
Auch wenn Sie Ihre Formel schon eingegeben haben und Sie evtl. einen Fehler aufweist, können Sie den Spaltenindex noch schnell ohne den Formelassistenten ermitteln. Hierzu einfach den Bereich markieren und wieder: Augen auf! Excel zeigt Ihnen (nur) solange Sie mit gedrückter Maustaste (oder gedrückter Shift-Taste) markieren in dem Namenfeld oben links neben der Bearbeitungsleiste die Anzahl der Zeilen und Spalten Ihrer Markierung. Sobald Sie die Maustaste loslassen hält Excel zwar die Markierung, zeigt aber die Anzahl nicht mehr an.
Wie Sie den Spaltenindex mit der Funktion VERGLEICH dynamisch ermitteln und somit auch bei Veränderungen in Ihrer Tabelle wie z.B. dem Löschen oder Ergänzen von Spalten noch die richtigen Ergebnisse erhalten, zeigen wir im nächsten Tipp 106 ...


 
Tipp 106: Spaltenindex für SVERWEIS mit der Funktion VERGLEICH dynamisch ermitteln

In Tipp 27 und Tipp 105 haben wir den SVERWEIS erklärt bzw. gezeigt, wie Sie in großen Tabellen den Überblick über den Spaltenindex behalten können. Wer oft mit dem SVERWEIS arbeitet kennt sicherlich auch das Problem, dass Excel beim Löschen oder Einfügen von Spalten innerhalb der Datenmatrix den Spaltenindex nicht verändert und somit für die dann geänderte/falsche Anzahl an Spalten falsche Ergebnisse angezeigt werden. Dies kann man jedoch dynamisieren. Eine Möglichkeit, den SVERWEIS dynamisch zu gestalten, besteht darin, den Spaltenindex durch die Funktion VERGLEICH zu ersetzen.
VERGLEICH sucht in einer Matrix nach einem angegebenen Element und gibt die relative Position dieses Elementes in der Matrix zurück; also nicht den Wert selbst. Die Syntax lautet: VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])
Suchkriterium ist der Wert, mit dem Sie Elemente in der Suchmatrix abgleichen möchten. Die Suchmatrix ist der Zellbereich, der durchsucht werden soll und der Vergleichstyp gibt an, auf welche Weise Excel die Werte in der Suchmatrix mit dem Wert für Suchkriterium abgleicht. Der [optionale] Vergleichstyp kann weggelassen werden, was dem Wert 1 entspricht (Suche nach dem größten Wert, der kleiner oder gleich dem Wert für Suchkriterium ist). Da in unserem Fall die Suche nach dem ersten Wert erfolgen soll, der mit dem Wert für Suchkriterium genau übereinstimmt, entspricht der Wert für Vergleichstyp hier 0.
Für die Dynamisierung des SVERWEISes können wir nun die relative Position der gewünschten Spalte ermitteln und verwenden dazu die Spaltenüberschriften.
Beispiel: Sie wollen in einer Tabelle (A1:Z100) mit 26 Spalten (Spaltenindex = 26) und einer Spalte "Umsatz" in der 26. Spalte mit dem SVERWEIS den Umsatzwert für das Land "Frankreich" aus Spalte A ermitteln. Der SVERWEIS sieht dann ohne Dynamisierung wie folgt aus: =SVERWEIS("Frankreich";A1:Z100;26;0)
Um jetzt auch beim Löschen/Einfügen von Spalten noch das richtige Ergebnis aus der Spalte "Umsatz", die dann nicht mehr in Spalte Z steht, zu erhalten, wird der Spaltenindex durch VERGLEICH ersetzt. Die Formel zur Ermittlung der Position von "Umsatz" lautet: =VERGLEICH("Umsatz";A1:Z100;0)
Zusammengesetzt lautet die Funktion dann:
=SVERWEIS("Frankreich";A1:Z100;VERGLEICH("Umsatz";A1:Z100;0);0)
Gut zu wissen: VERGLEICH unterscheidet beim Abgleichen von Zeichenfolgen (Text) nicht zwischen Groß- und Kleinbuchstaben und gibt die Funktion den Fehlerwert #NV zurück, wenn kein übereinstimmender Wert gefunden wird. Ist der Vergleichstyp gleich 0 und ist als Suchkriterium eine Zeichenfolge angegeben, können Sie im Argument Suchkriterium auch die Platzhalterzeichen Fragezeichen (?) und Sternchen (*) verwenden. Ein Fragezeichen ersetzt ein Zeichen; ein Sternchen ersetzt eine beliebige Zeichenfolge. Wenn Sie nach einem Fragezeichen oder Sternchen suchen möchten, müssen Sie eine Tilde (~) vor das zu suchende Zeichen setzen.


 
Tipp 107: Projektfortschritte mit Harvey Balls visualisieren

Harvey Balls sind kreisförmige Ideogramme mit unterschiedlicher Füllung, die dazu dienen, qualitative Daten anschaulich zu machen. Sie werden z.B. verwendet, um anzuzeigen, welche Erfüllungsgrade einzelne Werte gegenüber einem festgelegten Vergleichswert aufweisen. Grafik Harvey Balls
Wollen Sie z.B. in Excel den aktuellen Projektfortschritt, Plan-Ist-Umsatzauswertungen oder eine Budgetauslastung visualisieren, sind Harvey Balls eine gute Wahl. Sie gibt es seit Excel 2007 mit Verbesserungen ab Excel 2010. Somit variieren die Möglichkeiten je nach Version auch etwas. Einfügen können Sie Harvey Balls mit Hilfe der Bedingten Formatierung.
Markieren Sie also zunächst den Bereich, in dem z.B. Ihre prozentualen Projektfortschritte stehen und wählen Sie im Startmenü den Menüpunkt Bedingte Formatierung. Wählen Sie jetzt "Neue Regel …" und dann den Regeltyp "Alle Zellen basierend auf Ihren Werten formatieren" aus. Im Feld Formatstil ändern Sie auf "Symbolsätze" und in der Symbolart stellen Sie Harvey Balls ein (früher: 5 Viertel). Die aufgeführte, mehrstufige Regel für die einzelnen Symbole sollten Sie ändern, denn u.a. ist der Status des gefüllten Kreises mit 80% voreingestellt, was nicht wirklich dem Status "erfüllt" entspricht. Je nachdem, wie Ihre Ausgangswerte vorliegen bietet es sich auch an, den Typ jeweils auf Zahl zu ändern und dann z.B. mit den Werten 100, 75, 50 und 25 zu arbeiten. Bestätigen Sie mit OK haben Sie Ihr Ergebnis und können die Ausgangswerte und Kreise in Ihrer Tabelle/Zelle direkt vergleichen. Rufen Sie die Bedingte Formatierung erneut auf, und wählen "Regeln verwalten" und "Regel bearbeiten", können Sie die mehrstufige Regel anpassen. Setzen Sie ggfs. jetzt noch den Haken bei "Nur Symbol anzeigen" um damit die Werte auszublenden.
Nachteile dieser Möglichkeit sind, dass die Harvey Balls in frühen Excel-Versionen nur in schwarz zur Verfügung stehen und in Ihrer Größe nicht veränderbar sind. Auch stehen mit den 5 Kreisformen nur 5 Projektzustände zur Wahl. Doch auch hierfür gibt es Lösungen ... s. Tipp 108


 
Tipp 108: Projektfortschritte mit Harvey Balls farblich visualisieren (statisch)

In Tipp 107 haben wir gezeigt, wie Sie Harvey Balls, die kreisförmigen Ideogramme mit unterschiedlicher Füllung für Ihre Berichte nutzen können.
Wenn Sie es etwas farbiger und umfangreicher mögen, können Sie die Harvey Balls für statische Berichte auch in Farbe und weiteren Darstellungsformen nutzen, ganz ohne Bedingte Formatierung. z.B. in der Form: Grafik Harvey Balls farbig
Die folgende Methode basiert auf den Windows-Schriftcodes, lässt sich auch auf beliebige andere Zeichen anwenden und erlaubt eine Formatierung der Harvey Balls in Farbe und Größe.
Markieren Sie zunächst die Zelle, in der Sie eines der Zeichen als Statusanzeige einfügen möchten und und wählen Sie auf der Registerkarte "Einfügen" ganz rechts den Befehl "Symbol". In dem sich öffnenden Fenster wählen Sie dann im Register "Symbol" die Schriftart "Segoe UI Symbol", welche u.a. die gesuchten Zeichen enthält. Zur besseren Sicht vergrößern Sie das Fenster durch Ziehen an der rechten unteren Ecke und scrollen Sie zu den gesuchten Zeichen, welche dann angeklickt und mit dem Button "Einfügen" in das Tabellenblatt übernommen werden können. Sie können auch gleich mehrere Zeichen nacheinander in eine Zelle einfügen, indem Sie das Symbol-Fenster nicht schließen und nacheinander Symbole anklicken und den Einfügen-Button klicken. Nach dem Schließen des Fensters können die einzelnen Symbole normal wie andere Schriftarten und Buchstaben auch in der Bearbeitungszeile markiert, kopiert und in andere Zellen eingefügt werden. Auch das Färben und die Veränderung der Größe funktionieren analog durch Markieren und Farbselektion bzw. Schriftgradänderung.
Nachteile bei dieser Möglichkeit sind, dass die Zeichen nur statisch sind, d.h. sie ändern sich anders als bei der Bedingten Formatierung nicht mit den zugrundeliegenden Werten. Doch auch hierfür gibt es Lösungen ...


 
Tipp 109: Projektfortschritte mit Harvey Balls dynamisch farblich visualisieren

In Tipp 108 haben wir gezeigt, wie Sie Harvey Balls, die kreisförmigen Ideogramme mit unterschiedlicher Füllung, auch farblich darstellen können (Grafik Harvey Balls farbig). Dies war eine einfache statische Variante für schnelles, kurzes und einmaliges Reporting, bei der sich die Balls nicht dynamisch mit den zugrundeliegenden Werten ändern. Sie können Ihre Auswertungen natürlich auch mit farbigen Balls dynamisieren. Anhand eines Beispiels mit sechs Projekten wollen wir Ihnen zeigen, wie das z.B. möglich ist:
Grafik Harvey Balls Beispiel
Wir benötigen hierzu die Funktion SVERWEIS, um auf die Balls über eine Hilfsspalte zuzugreifen und die Bedingte Formatierung, um die Balls gemäß einer definierten Abstufung über eine zweite Hilfsspalte farblich zu gestalten.
Zunächst müssen die benötigten Symbole aus einem Zeichensatz, wie z.B. der Schriftart Arial Unicode MS, in Excel eingefügt werden (s. hierzu ggfs. auch Tipp 108). In unserem Beispiel haben wir diese in I7:I12 abgelegt und dazu in H7:H12 prozentuale Grenzwerte definiert.
Dann müssen die Zellen F7:F12 markiert und die folgende Funktion in alle Zellen eingegeben werden:
=SVERWEIS(E7;$H$7:$I$12;2)
Formatieren Sie die Zellen F7:F12 mit dem gleichen Zeichensatz (Arial Unicode MS) und erhöhen die Schriftgröße auf einen für Ihren Report gut lesbaren Wert.
Der SVERWEIS sucht den Prozentsatz aus E7 in der Matrix und gibt den Wert aus der zweiten Spalte aus. Jetzt werden bereits Harvey Balls angezeigt, welche noch gemäß der Definition in K7:L12 gefärbt werden müssen. Dies wird mit der Bedingten Formatierung für die Zellen F7:F12 erzeugt. Im Beispiel wird mit nur drei Regeln gearbeitet. Der Zellbereich F7:F12 wird zuvor komplett mit Schriftfarbe Rot formatiert, wodurch bereits zwei Fälle definiert sind. Weiter mit: Markieren, Bedingte Formatierung aufrufen, Neue Regel erstellen.
Mit der Regel =$E7=$K$7, dem Format "Schwarze Schrift" und der Anwendung auf =$F$7:$F$12 wird begonnen, gefolgt von =$E7>=$K$11, dem Format "Grüne Schrift" und der Anwendung auf =$F$7:$F$12 sowie der Regel =$E7=$K$10, dem Format "Orange Schrift" und der Anwendung auf =$F$7:$F$12. Die Reihenfolge der zweiten und dritten Regel ist wichtig für die Abarbeitung in Excel, um korrekte Ergebnisse zu erzielen. Das Beispiel können Sie hier downloaden.


 
Tipp 110: Nettoarbeitstage ohne Wochenenden und Feiertage berechnen

Wenn Sie für ein anstehendes Projekt die für das Projekt zur Verfügung stehenden Arbeitstage der Mitarbeiter einplanen möchten, können Sie das natürlich am Kalender abzählen. Excel kann das allerdings auch automatisch für Sie übernehmen und berechnet Ihnen je nach Formel und zur Verfügung gestellten Daten die Nettoarbeitstage unter Berücksichtigung von Wochenenden und Feiertagen.
Die Gesamt(brutto)tage zwischen z.B. Projektstart und -ende können Sie ja bekanntlich durch eine einfache Subtraktion der Datumszellen oder mit DATEDIFF erreichen.
Mit der Funktion NETTOARBEITSTAGE können Sie zusätzlich aus zwei Datumsangaben die Anzahl der dazwischenliegenden Arbeitstage errechnen. Die Syntax lautet wie folgt:
NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;[Freie_Tage])
Bei Start- und Endedatum in B2 bzw. B3 lautet die Formel zunächst:
=NETTOARBEITSTAGE(B2;B3)
So werden alle Tage außer den Wochenenden berücksichtigt, ggfs. auch Feiertage. Diese können Sie jedoch wie folgt abziehen: Wenn Sie Feiertage z.B. in dem Zellbereich E2:E4 auflisten, können Sie diese der Formel für die Berechnung zur Verfügung stellen:
=NETTOARBEITSTAGE(B2;B3;E2:E4)
Bei der Liste der freien Tage kann es sich entweder um einen Zellbereich, der die Datumsangaben enthält, oder eine Matrixkonstante der fortlaufenden Zahlen handeln, die die Datumsangaben darstellen. Excel zieht diese Tage von den zuvor berechneten ab.


 
Tipp 111: Erzeugen von einfachen Grafikelementen für Planabweichungen

Für einen kleinen Plan-Ist-Vergleich schnell noch eine Balkengrafik, welche die positiven und negativen Abweichungen darstellt ... das geht mit einer einfachen Funktion. Das Ergebnis könnte z.B. so aussehen:
 
Grafikelemente in Excel erzeugen
 
So können Sie sich Ihre Sparklines quasi selber machen. Z.B. mit der Funktion WIEDERHOLEN. Die Syntax lautet wie folgt:
=WIEDERHOLEN(Text;Multiplikator)
Das Argument „Text“ ist erforderlich; der Inhalt bzw. die Zeichenfolge, den Sie wiederholen möchten. Multiplikator ist ebenso erforderlich; eine positive Zahl, die angibt, wie oft "Text" wiederholt werden soll.
Für das Beispiel kann als Wiederholungs-Text der Großbuchstabe „I“ oder das „Pipe-Symbol“ genutzt werden. Es werden zwei Spalten, eine für negative und eine für positive Werte benötigt. Zusätzlich wird das Ergebnis mit einer WENN-Abfrage dynamisiert.
Die Formeln lauten wie folgt:
In H8: =WENN(F8<0;WIEDERHOLEN("I";-F8);WENN(F8=0;"";""))
In I8: =WENN(F8>0;WIEDERHOLEN("I";F8);WENN(F8=0;"";""))
Die Werte in Zeile H werden rechts-, die in Zeile I linksbündig angeordnet.


 
Tipp 112: Nettoarbeitstage bei unterschiedlichen Wochenarbeitszeiten

Wie Sie Nettoarbeitstage ohne Wochenenden und Feiertage berechnen, hatten wir schon in unserem Tipp 110 gezeigt. Oft kommt es aber vor, dass man nicht alle Arbeitstage der Arbeitswoche für z.B. einen Projektplan benötigt, sondern nur die Tage an denen ein Teilzeitarbeiter auch wirklich im Hause ist oder an denen ein Projektmitarbeiter überhaupt nur fürs Projekt zur Verfügung steht.
Dies ist möglich mit der Funktion NETTOARBEITSTAGE.INTL
Sie gibt die Anzahl der vollen Arbeitstage zwischen zwei Datumsangaben zurück. Dabei werden Parameter verwendet, um anzugeben, welche und wie viele Tage auf Wochenenden fallen. Wochenenden und Tage, die als freie Tage angegeben sind, werden nicht zu den verfügbaren Arbeitstagen gezählt. Die Syntax lautet wie folgt:
NETTOARBEITSTAGE.INTL(Ausgangsdatum;Enddatum;[Wochenende];[Freie_Tage])
Ausgangs- und Enddatum sind dabei zwingend erforderlich, um die dazwischenliegenden Arbeitstage zu berechnen. Das Ausgangsdatum kann vor oder nach dem Enddatum liegen und auch mit diesem identisch sein. Wochenende ist optional und gibt die Tage der Woche an, die als Wochenendtage behandelt werden und damit nicht zur Anzahl der vollen Arbeitstage zwischen Ausgangsdatum und Enddatum zählen sollen. Es gibt eine Vielezahl von vordefinierten Wochenendnummer für die verschiedenen Wochentage und Kombinationen. Z.B. definiert "1" oder nicht festgelegt (leer) Samstag und Sonntag als Wochenende, "11" nur den Sonntag und "17" nur den Samstag.
Für das Wochenende kann aber auch ein Zeichenfolgewert aus sieben Zeichen angegeben werden, wobei jedes Zeichen für einen Wochentag, beginnend mit Montag, steht. Die "1" bezeichnet einen arbeitsfreien Tag, "0" einen Arbeitstag. Es sind nur die Zeichen "1" und "0" zulässig. Die Zeichenfolge "0000011" gibt beispielsweise ein Wochenende an, das heißt, Samstag und Sonntag.
Steht ein Projektmitarbeiter also nicht am Wochenende und nicht am Dienstag und Mittwoch zur Verfügung, kann mit der Zeichenfolge "0110011" gearbeitet werden.
Weiterhin können optional mit Freie_Tage auch einzelne oder mehrere Datumsangaben, welche arbeitsfreie Tage bezeichnen, definiert werden. Freie_Tage kann z.B. als Bereich von Zellen mit den entsprechenden Datumsangaben angegeben werden. Die Reihenfolge der Datumsangaben ist beliebig.


 
Tipp 113: Tabellenfilter schnell an und ausschalten

Wer viel mit Listen arbeitet, wird regelmäßig Filter anwenden (müssen). Schneller, als diese über das Menü DATEN / FILTERN aufzurufen, geht es – wie immer – mit einer Tastenkombination. Um schnell einen Filter hinzuzufügen bzw. auszuschalten, können Sie einfach in die Liste klicken und STRG + SHIFT+ L eingeben.


 
Tipp 114: Ende eines Abschreibungszeitraumes tagesgenau berechnen

Die Abschreibungswerte für z.B. eine Maschine in Excel zu berechnen, ist u.a. mit den Funktionen LIA, DIA und GDA leicht möglich. Wenn man allerdings Anlagenverzeichnis und -spiegel in Excel führt oder plant, gilt es auch, für einzelne Anlagen das Abschreibungsende (Abgangsdatum) tagesgenau zu berechnen. Man kann hierzu mit den Funktionen TAG, MONAT und JAHR basteln, oder aber auch, da Excel mit Datumsangaben rechnen kann, dem Anschaffungsdatum entsprechend viele Tage für Abschreibungsdauer * 365 Tage hinzurechnen. Dabei errechnet Excel jedoch zumeist fehlerhafte Daten, da Schaltjahre nicht berücksichtigt werden.
Genau wird es mit der Funktion MONATSENDE. Mit ihr können Sie Rückzahlungs- oder Fälligkeitstermine berechnen, die auf den letzten Tag eines Monats fallen. Die Syntax der Funktion lautet =MONATSENDE(Ausgangsdatum;Monate)
Das Argument Ausgangsdatum ist erforderlich und entspricht dem Anfangsdatum der Abschreibung. Monate ist als ganze Zahl (ohne Nachkommastellen) erforderlich und gibt an, wie viele Monate nach dem Ausgangsdatum liegen sollen. Monate könnte für andere Beispiele auch negativ sein und ein in der Vergangenheit liegendes Datum ermitteln.
Die Berechnung eines Enddatums für eine Anschaffung vom 31.03.2089 bei 10-jähriger AfA entspricht: =MONATSENDE("31.03.2089"; 10*12) und liefert das Datum 31.03.2099.



 
Tipp 115: Benutzerdefiniertes Format zur Kombination von Datum und Wochentag

Die Excelfunktionen zur Darstellung eines Datums sind äußerst vielfältig und in den neueren Excelversionen gibt es mittlerweile auch die Möglichkeit, ein Datum mit dem entsprechenden Wochentag zu kombinieren. Dieses sogenannte "Datum, lang" entspricht z.B. "Dienstag, 24. Februar 2099". Abwandlungen hiervon sind dann allerdings nur (wie gehabt) individuell mit einem benutzerdefiniertem Format zu erreichen. Wenn also z.B. etwas kürzer "Di 24.02.2099" oder "24.02.2099 (Di)" dargestellt werden soll, geht das nicht mehr über ein Standard-Datumsformat. Das ist jedoch kein Problem, denn diese Art der Datumsdarstellung können Sie ganz leicht selbst erzeugen:
Markieren Sie die Zellen mit dem Datum, die Sie formatieren möchten und wählen Sie (z.B. im Kontextmenü/rechte Maustaste) das Dialogfenster "Zellen formatieren". Im Register "Zahlen" wählen Sie die Kategorie "Benutzerdefiniert". Mit einem benutzerdefinierten Format hat man hier die Möglichkeit, die Ausgabe des Datums sehr umfangreich zu gestalten, und zwar mit Hilfe der Platzhalter T, M und J (in Grossbuchstaben!) sowie einiger Erweiterungen, welche man in dem Feld "TYP" eingeben kann. Alle Standard-Vorschläge enthält das Listfeld darunter.
Im Feld "TYP" können Sie jetzt beispielsweise mit der Zeichenfolge "TT.MM.JJJJ (TTT)" das obige Datum aus unserem Beispiel als "24.02.2099 (Di)" anzeigen lassen. Wenn Sie anstelle der Kurzform des Wochentages die ausgeschrieben Variante bevorzugen, erweitern Sie den Ausdruck (TTT) um ein weiteres "T" zu (TTTT).
Die Darstellungsformen sind vielfach, aber begrenzt. Sie können die Klammern weglassen, mit Komma arbeiten, mit Leerstellen und auch mit Abständen. Letzteres ist z.B. sinnvoll bei vorangestellten Wochentagen (unterschiedlicher Länge) in einer Liste und wird in unserem nächsten Newsletter behandelt.
Achtung: Sollte jetzt in der Zelle "#######" angezeigt werden, ist die Zelle wahrscheinlich nur zu schmal, sodass nicht der vollständige Wert angezeigt werden kann. Doppelklicken Sie auf den rechten Rand der Spalte, die die Zellen mit den "#" enthält. Dadurch wird die Spalte genau soweit verbreitert, dass der Inhalt vollständig zu sehen ist. Sie können natürlich auch den rechten Rand der Spalte mit der Maus ziehen, sodass Ihre Spalte die gewünschte Breite hat.
Und generell: Wenn Sie nur schnell das aktuelle Datum in ein Arbeitsblatt eingeben möchten, markieren Sie die gewünschte, leere Zelle, drücken "STRG+." (Punkt) und abschließend die EINGABETASTE.



 
Tipp 116: Optische Trennung von Datum und Wochentag im benutzerdefinierten Format

In unserem letzten Tipp haben wir erklärt, wie Sie mit einem benutzerdefinierten Format Datum und Wochentag in einer Zelle ausgeben können. Dies sieht dann z.B. mit vorangestellten Wochentagen in etwa so aus:
 
Wochentag und Datum im benutzerdefinierten Format
 
Unschön bei diesem Beispiel sind die unterschiedlichen Längen der Wochentage und somit die gefranzten linken Ränder. Abhilfe schafft hier ein Format mit Leerstellen und Platzhaltern. Das könnte dann in dem Beispiel so aussehen:
 
Wochentag und Datum im benutzerdefinierten Format
 
Um den Wochentag optisch vom Datum zu trennen, können Sie das Sternchen (*) nutzen, gefolgt von einem Leerzeichen. Ein mögliches und hier verwendetes benutzerdefiniertes Format wäre somit: „TTT* TT.MM.JJJ“.
Das Sternchen "drängt" den Wochentag an den linken Rand und sorgt dafür, dass der freibleibende Platz in der Zelle von dem nachfolgenden Zeichen aufgefüllt wird. In unserem Beispiel also Leerzeichen und zwar so viele, bis die Zelle voll ist. Somit stehen sowohl die Wochentage wie auch das Datum bündig untereinander.
Anstelle eines Leerzeichens können auch beliebige andere Zeichen für das Auffüllen zwischen Wochentag und Datum genutzt werden.
Zusatztipp: Um den Inhalt jetzt auch noch etwas vom jeweiligen Zellenrand abzusetzen, so wie auf der zweiten Grafik geschehen, können Sie das Format um je ein führendes und ein abschließendes Leerzeichen ergänzen: „ TTT* TT.MM.JJJ “.



 
Tipp 117: Formeln für Analysezwecke als Text anzeigen

Wenn Sie in eine Zelle klicken, wird die in der Zelle hinterlegte Formel in der Bearbeitungsleiste angezeigt – aber nur dann und nur dort. Manchmal kann es aber auch sehr nützlich sein, den Anwendern einer Datei oder auch sich selbst schon beim Entwickeln der Datei, die in bestimmten Zellen verwendeten Formeln als Text anzuzeigen. Dies kann z.B. die Dokumentation von Kalkulationen erleichtern oder auch zu Schulungszwecken für Excel-Lernende sehr nützlich sein.
Seit Excel 2013 steht hierfür die Funktion FORMELTEXT() zur Verfügung.
Die Syntax der Funktion lautet: =FORMELTEXT(Bezug)
Als Argument benötigt diese Funktion einen Bezug auf die Zelle, deren Formel als Textzeichenfolge angezeigt werden soll. Sie können auch auf andere geöffnete Arbeitsmappen und –blätter verweisen und von dort Formeln als Text extrahieren. Wird FORMELTEXT in einer Zelle verwendest, welche keine Formel enthält, wird ein #NV-Fehler ausgegeben. Ebenso, wenn eine Arbeitsmappe, auf die verwiesen wird, gar nicht geöffnet ist.
Wollen Sie z.B. in Zelle D25 eine Formel aus D24 anzeigen lassen lautet die Funktion in der Zelle D25: =FORMELTEXT(D24)



 
Tipp 118: Verborgene Daten aus Dateien entfernen

Mit der Dokumentprüfung können Sie ihre Excel-Dateien auf einfache Weise untersuchen und sich damit persönliche oder vertrauliche Informationen wie Kommentare, Dokumenteigenschaften, unsichtbaren Inhalt, ausgeblendete Zeilen wie auch Arbeitsblätter und einiges mehr anzeigen lassen. Die Dokumentprüfung ermöglicht somit z.B., unerwünschte Informationen zu entfernen, bevor Sie eine Datei an einen Kollegen weitergeben. Dies geht mit dem Dokumentinspektor, welcher übrigens ebenso in Word und PowerPoint zur Verfügung steht.
In der Version 2007 erreichen Sie die Prüfung über die Office-Schaltfläche, klicken dann auf "Vorbereiten" und anschließend auf "Dokument prüfen". In Excel 2010/2013 klicken Sie im Menüband "Datei" auf den Befehl "Informationen" und danach auf die Schaltfläche "Auf Probleme prüfen | Dokument prüfen".
In dieser Box wählen Sie aus, in welchen Elementen Ihrer Datei nach verborgenen Daten gesucht werden soll. Ihre Auswahl bestätigen Sie mit der Schaltfläche "Prüfen". Daraufhin erscheint das Dialogfenster "Dokumentprüfung", in dem Sie sehen, welche verborgenen Daten bei der Überprüfung in den ausgewählten Elementen Ihrer Datei gefunden wurden.
Hinter allen Elementen, in denen verborgene Daten und vertrauliche Informationen gefunden wurden, sehen Sie die Schaltfläche "Alle entfernen". Mit einem Mausklick darauf löschen Sie die Inhalte aus der Datei. Wenn Sie alle gewünschten Löschungen vorgenommen haben, können Sie zum Check nochmal auf "Erneut prüfen" klicken. Die erneute Prüfung sollte nun in den relevanten Elementen keine Ergebnisse mehr liefern. Es empfiehlt sich, die Datei vorab als Kopie zu speichern.



 
Tipp 119: Gleiche Formen mehrfach einfügen und Formen nachträglich ändern

In Excel gibt es eine Reihe von vorgefertigten Formen, wie z.B. Rechtecke, Kreise, Linien, Pfeile, Flussdiagramme, Sterne und viele mehr. Diese können Sie einfach in der Registerkarte „Einfügen“ unter „Formen“ anschauen und per Klick einfügen. Wenn Sie mehrere gleiche Formen in eine Tabelle einfügen wollen, können Sie das auch ohne jedes Mal über „Einfügen“ und „Formen“ die Form neu zu selektieren.
Um mehrere Formen einzugeben klicken Sie hierzu zunächst auf der Registerkarte „Einfügen“ auf „Formen“. Klicken Sie jetzt mit der rechten Maustaste auf die Form, die Sie hinzufügen wollen, und klicken Sie in dem sich öffnenden Kontextmenü auf „Zeichenmodus sperren“. Klicken Sie dann auf die gewünschte Stelle im Arbeitsbereich, und ziehen Sie die Form, um sie zu platzieren. Tipp: Zum Erstellen eines perfekten Quadrats oder eines perfekten Kreises drücken Sie UMSCHALT, und halten die Taste während des Ziehens gedrückt. Nachdem Sie alle gewünschten Formen hinzugefügt haben, drücken Sie die Taste ESC, um die Sperre des Zeichnungsmodus zu deaktivieren. Diese Funktion ähnelt der sogenannten Einrastfunktion von „Format übertragen“. Bis Excel 2003 war sie auch noch durch einen Doppelklick auf das Formsymbol zu aktivieren.
Wenn Sie jetzt z.B. sechs Rechtecke gewählt haben können Sie diese auch alle gleichzeitig nachträglich ändern, z.B. in solche mit abgerundeten Ecken, ohne das Excel evtl. von Ihnen bereits geänderte Farbinhalte, Linien o.ä. verändert. Hierzu markieren Sie die sechs Formen die Sie ändern möchten. Mehrere Formen markieren Sie, indem Sie die STRG-Taste gedrückt halten, während Sie auf die zu ändernden Formen klicken. In der Registerkarte „Einfügen“ können Sie nun unter „Formen“ das Rechteck mit den abgerundeten Ecken auswählen/klicken und Excel ändert alle Ihre zuvor markierten Formen ohne deren Formate wie Farben oder Linien zu verändern bzw. zurückzusetzen.



 
Tipp 120: Summierung bei mehreren Suchkriterien in verschiedenen Spalten

Für die Summierung bei mehreren Suchkriterien in verschiedenen Spalten gibt es mehrere Möglichkeiten. In Tipp 84 hatten wir Ihnen schon ein Beispiel mit SUMMEWENNS und alternativ auch mit einer Array-Formel aufgezeigt. In diesem Tipp stellen wir Ihnen eine weitere Möglichkeit mit der Funktion SUMMENPRODUKT vor. Konkret an einem Beispiel, wie Sie außerhalb einer Datentabelle z.B. den Teilabsatz und Teilumsatz für ein bestimmtes Produkt in einer definierten Variante ermitteln können.
Die Funktion SUMMENPRODUKT gibt die Summe der Produkte entsprechender Bereiche oder Arrays zurück. Im Standard ist das die Multiplikation, aber Addition, Subtraktion und Division sind ebenso möglich (s.u.).
Die Syntax lautet: = SUMMENPRODUKT(Matrix1; [Matrix2]; [array3];...)
In unserem Beispiel möchten wir aus einer Umsatzdatenbank die Teilumsätze von Produkt "A" in der Variante "Groß" ermitteln:
 
Tabelle SUMMENPRODUKT
 
Absatz, Einzelpreis und Umsatz sind in den obigen Spalten aufgeführt. In einem separaten Bereich kann die Berechnung erfolgen (Werte in H10 (A) und I10 (Groß)):
Berechnungsfeld SUMMENPRODUKT
Die Formel für den Absatz in J10 lautet:
=SUMMENPRODUKT((B7:B16=H10)*(C7:C16=I10);(D7:D16))
Dabei werden aus den Bereichen B7:B16 UND C7:C16 nur die Werte selektiert, für welche die Bedingung =H10/Produkt "A" bzw. =I10/Variante "Groß" erfüllt ist. Für diese wird dann aus dem Bereich D7:D16 der Wert ermittelt und die Summe gebildet.
Die Formel für den Umsatz in K10 lautet:
=SUMMENPRODUKT((B7:B16=H10)*(C7:C16=I10);(D7:D16);(E7:E16))
Statt dem Semikolon (s. Syntax) zwischen den beiden letzten Matrix-Bereichen kann auch mit dem gewünschten Operator (* für Multiplikation) gearbeitet werden.
Andere arithmetische Operationen führen Sie aus, indem Sie die Semikolons, welche die Arrayargumente voneinander trennen, durch die gewünschten arithmetischen Operatoren (*,/, +,-) trennen. Nachdem alle arithmetischen Vorgänge ausgeführt wurden, werden die Ergebnisse auch hierbei wie gewohnt summiert.
Die als Argumente angegebenen Arrays müssen bezüglich der Zeilen- und Spaltenanzahl immer identisch sein. Ist dies nicht der Fall, gibt SUMMENPRODUKT den Fehlerwert #WERT! zurück.
Gleiches ist übrigens auch mit SUMMEWENNS möglich:
=SUMMEWENNS(F7:F16;B7:B16;H10;C7:C16;I10) führt zu dem gleichen Ergebnis.
In unserer Beispieldatei können Sie weiterhin nachvollziehen, wie die Funktion SUMMENPRODUKT in Ihrer Grundfunktion zu dem gleichen Ergebnis wie die Funktion SUMME führt.
Das Beispiel gibt's hier im Download.
 
In unserem nächsten Tipp (November-Newsletter) lesen Sie dann, wie Sie SUMMENPRODUKT verwenden, um z.B. zu berechnen, wie viele Kunden wie oft in einem bestimmten Zeitraum gekauft haben. Ganz nützlich, zu wissen, ob Sie in erster Linie Kunden haben, die nur einmal kaufen, oder ob Sie viele Stammkunden mit Mehrfachkäufen haben.



 
Tipp 121: Anzahl Käufe je Kunde mit SUMMENPRODUKT und ZÄHLENWENN ermitteln

In einem Monat kaufen einige Kunden mehrfach oder haben mehrere Umsatzpositionen bei deren Käufen in Ihrer Umsatzdatenbank erzeugt. Mit den Funktionen SUMMENPRODUKT und ZÄHLENWENN können Sie die Anzahl von Käufen je Kunde auswerten und somit den Anteil der Wiederkäufer und Stammkunden an den Gesamtkunden ermitteln.
 
Zur Veranschaulichung folgendes Beispiel mit 5 verschiedenen Kunden und insgesamt 10 Käufen:
 
Tabelle Käufe je Kunde
 
Die Anzahl der Käufe läßt sich mit ZÄHLENWENN leicht ermitteln. Funktionsweise der Funktion ZÄHLENWENN lesen Sie in Tipp 43 und 48. Im Beispiel kann man bei numerischen Kundennummern den Bereich C7:C16 nach Werten >1 durchsuchen und somit die Anzahl ausgeben:
=ZÄHLENWENN(C7:C16;">1") Weiter geht es mit der Berechnung der Anzahl verschiedener Kunden mit einer Kombi aus SUMMENPRODUKT und ZÄHLENWENN:
=SUMMENPRODUKT(1/ZÄHLENWENN(C7:C16;C7:C16))
Zuerst ermittelt die Funktion ZÄHLENWENN für jeden Eintrag in Spalte C wie oft der Kunde vorkommt. Um festzustellen, wie viel unterschiedliche Kunden vorkommen, wird pro Zeile ein Gewichtungsfaktor angegeben. Die Summe aller Gewichtungsfaktoren pro Kunde ergibt immer 1, weil der Kunde ja auch nur einmal gezählt werden darf. Es werden also für alle Zeilen aus Spalte C die anteiligen Werte ermittelt. Das bedeutet, wenn ein Kunde z.B. dreimal in der Tabelle gefunden wird, wird jedes Mal 1/3 gezählt und 3 x 1/3 sind wieder 1. Die Funktion SUMMENPRODUKT addiert die zuvor ermittelten 10 Werte schließlich auf.
 
In einer kleinen Auswertung kann man nun wie folgt darstellen:
 
Auswertung Käufe je Kunde
 
Es hat also 1 Kunde 1x gekauft, 3 kauften 2x und 1 Kunde 3x.
Die Formel (hier in J19) zur Berechnung der Kunden für 3 Käufe greift auf die "3" in I19 zu und lautet wie folgt:
=SUMMENPRODUKT(N(ZÄHLENWENN(C7:C16;C7:C16)=I19))/I19
ohne den Zellbezug auf I19 zur Veranschaulichung mit direkter Werteingabe auch:
=SUMMENPRODUKT(N(ZÄHLENWENN(C7:C16;C7:C16)=3))/3
ZÄHLENWENN ermittelt für jeden Eintrag in der Spalte C, wie oft der Kunde vorkommt. Dann wird für jede Zeile geprüft, ob die Anzahl die Zahl 3 ist. Die Funktion N() wandelt den Wahrheitswert "Ja" in die Zahl 1 und "Nein" in die Zahl 0 um. SUMMENPRODUKT addiert die Werte der Spalte auf. Das Ergebnis bis hier ist 3. Zuletzt das Ergebnis noch durch die angegebene Anzahl der Käufe dividiert (im Beispiel 3 Käufe) und das Gesamtergebnis lautet: 1 Kunde hat 3x gekauft.
 
Sind in dem Datenbestand auch leere Zeilen, wird der Fehler #DIV/0! ausgegeben. Hier eine Möglichkeit für das obere Beispiel zur Ermittlung der Anzahl Kunden, um den Fehler abzufangen:
=SUMMENPRODUKT((C7:C17<>"")/ZÄHLENWENN(C7:C17;C7:C17&""))
 
Das Beispiel gibt's hier im Download.
 
Ähnliche Fragestellungen, die so bearbeitet werden können, sind z.B. auch die Anzahl verschiedener abgesetzter Produkte je Kunde oder die Anzahl bei Lieferanten bezogener Artikel.



 
Tipp 122: Konsolidieren von Daten aus mehreren Arbeitsblättern oder Dateien

Excel bietet die Möglichkeit, Daten aus einer Vielzahl von Arbeitsblättern in einer Übersicht zusammenzufassen. Hiermit lassen sich zum Beispiel aus Monatstabellen eine Jahrestabelle erstellen, Daten aus verschiedenen Regionen zusammenfassen oder aus sämtlichen Kostenstellenbudgets ein Gesamtbudget konsolidieren. Wichtig ist, dass es auch etwas zu konsolidieren gibt, Äpfel und Birnen gehen nicht, aber gleiche Kostenarten, gleiche Produkte und ähnliche, übereinstimmende Zeilenüberschriften aus einzelnen Tabellen kann man ganz leicht in einer Gesamtübersicht zusammenfügen.
 
Angenommen Sie haben aus fünf Kostenstellenbereichen jeweils die Planzahlen in einem ausgefüllten Formblatt zurück erhalten. Ergänzen Sie eine neue, leere Tabelle. In dieser positionieren sie den Cursor an der Stelle, wo die Gesamtdaten erscheinen sollen und wählen im Register DATEN bei den DATENTOOLS den Punkt KONSOLIDIEREN.
 
Es öffnet sich das Fenster "Konsolidieren". Bei "Funktion" ist SUMME vorgewählt, lässt sich aber für andere, statistische Konsolidierungen auch verändern. Im Feld "Verweis" setzen Sie den Cursor und markieren dann in dem ersten Arbeitsblatt mit den Planzahlen die vorhandenen Daten inklusive der Zeilen- und Spaltenüberschriften. Sie wählen "Hinzufügen" und erstellen vier weitere Verweise, bis Sie alle fünf Arbeitsblätter selektiert haben. Setzen Sie dann noch die Haken für die "Beschriftung aus" bei "Oberster Zeile" und "Linker Spalte" und wählen "OK". Excel konsolidiert jetzt die Daten aus allen fünf Arbeitsblättern in dem neuen Arbeitsblatt und Sie müssen nur noch mit dem Formatpinsel oder per copy&paste das einheitliche Format ergänzen.
 
Sie können die einzelnen Arbeitsblätter in einer Datei zusammenfassen, aber auch aus einzelnen Dateien die Konsolidierung vornehmen. Haben die Daten die gleiche Reihenfolge und dieselben Beschriftungen konsolidieren Sie nach Position. Bei gleichen Beschriftungen aber unterschiedlicher Position kann die Konsolidierung nach Kategorie auch Daten aus unterschiedlichen Layouts zusammenfügen, ähnlich einer Pivot-Tabelle.



 
Tipp 123: Mit dynamischen Tabellen arbeiten - Dynamische Tabellen erstellen

Aus einer statischen Tabelle eine dynamische Tabelle zu erzeugen, ist recht einfach und die Arbeit mit dynamischen Tabellen spart Zeit und bietet zusätzliche Möglichkeiten. So können Sie z.B. schon beim Aufbau der Tabelle Zeit sparen, da Excel in neuen Spalten Formeln automatisch ergänzt, Summen berechnet und Filter setzt. Sie können mit einem Klick aus Schnell-Formatvorlagen wählen, leicht Duplikate aus der Tabelle entfernen oder auch die Tabelle als Ganzes exportieren. Bei Erweiterungen aktualisieren sich auf die dynamische Tabelle beziehende Verknüpfungen automatisch. Für besondere Anforderungen stehen zusätzlich weitere Möglichkeiten zur Verfügung, wie z.B. der automatisierte Zugriff mittels (mehrfacher) Dropdownlisten. Mit diesen Möglichkeiten befassen wir uns in den kommenden Tipps.
 
In unserem heutigen, ersten Tipp zu den dynamischen Tabellen behandeln wir das Erstellen der Tabellen, das Erweitern von Daten, das Formatieren der Tabelle sowie die Rückwandlung in eine statische Tabelle.
 
Als Beispiel nehmen wir folgende, noch unvollständige Tabelle mit Artikeln und Preisen zur Hand:
 
Dynamische Tabelle Ausgang
 
Um aus dieser Tabelle eine dynamische Tabelle zu erzeugen und die beschriebenen Erleichterungen zu erfahren, klicken wir in ein beliebiges Feld in der Tabelle und wählen im Register „Start“ in dem Bereich „Formatvorlagen“ die Option „Als Tabelle formatieren“ (Alternativ auch mit Tastenkombination Strg+T erreichbar). Excel selektiert nun Ihren Tabellenbereich und fragt Sie, ob der Bereich Überschriften enthält. Wenn der Bereich vollständig selektiert ist bestätigen Sie mit OK. Das Ergebnis sieht wie folgt aus:
 
Dynamische Tabelle erstellt
 
Excel hat nun aus dem statischen Bereich eine dynamische Tabelle erzeugt, einen (Standard-)Formatvorschlag erstellt und Filter gesetzt. Weiterhin finden Sie jetzt das neue Register „Entwurf“ für die Auswahl der verschiedenen Tabellentools, welches auch künftig bei Selektion der dynamischen Tabelle zur Verfügung steht: Klicken Sie neben die Tabelle, ist das Register nicht mehr vorhanden, klicken Sie wieder in die Tabelle, wird es eingeblendet. Dort können Sie nun auch eine andere Formatvorlage auswählen. Die Formate können aber auch wie gewohnt in dem Bereich „Schriftart“ verändert werden. Die erste Formatvorlage in der Liste erhält Ihre bisherigen Formatierungen.
 
Zum Erweitern der noch fehlenden Steuerbeträge und dem Bruttopreis geben Sie nun jeweils in die oberste Zeile die entsprechenden Formeln ein und Excel fügt dies automatisch für alle Zeilen der dynamischen Tabelle ein. Allerdings zeigt Excel die Formeln in dynamischen Tabellen anders an: Aus Ihrer Eingabe =D2*0,19 macht Excel =[Preis netto]*0,19 und aus =D2+E2 wird =[Preis netto]+[MwSt 19%].
 
Dynamische Tabelle erweitert
 
Wollen Sie aus der dynamischen Tabelle wieder eine statische Tabelle machen, wählen Sie in dem Register „Entwurf“ den Bereich „Tools“ und dort „In Bereich konvertieren“. Bestätigen Sie die Sicherheitsabfrage mit „Ja“ und alle dynamischen Funktionen sind verschwunden. Lediglich das Format der gewählten Formatvorlage bleibt erhalten.
 
Dynamische Tabelle konvertiert
 
Das Beispiel gibt's hier im Download.



 
Tipp 124: Dynamische Tabellen um neue Einträge erweitern

Ein Vorteil von dynamischen Tabellen ist die Möglichkeit der schnellen, einfachen und (relativ) sicheren Aktualisierung. Und das nicht nur bei neuen Einträgen in die Tabelle selbst, sondern insbesondere auch innerhalb der auf die Tabellen zugreifenden Auswertungen. Die vorhandenen Verknüpfungen werden automatisch angepasst und für Zugriffe wie z.B. mit (mehrfachen) Dropdownlisten stehen sofort auch die neuen Daten zur Verfügung.
 
Um in unserem obigen Beispiel aus Tipp 123 eine neue Produktgruppe anzulegen bzw. der Tabelle hinzuzufügen, bestehen mehrere Möglichkeiten:
Die Tastaturfreunde setzen den Cursor hierzu in die letzte, rechte Zelle der dynamischen Tabelle und drücken die TAB-Taste. Für Mausfreunde haben die dynamischen Tabellen einen Anfasser unten rechts in der letzten Zelle – Anfassen mit linker Maustaste, gedrückt halten, ziehen (auch mehrere Zeilen). Ganz banal und vermutlich am schnellsten ist es jedoch, einfach in einer leeren Zelle direkt unterhalb der dynamischen Tabelle einen neuen Eintrag zu tätigen. Excel erweitert dann sofort die Liste inkl. der Formate, bietet aber auch Autokorrektur-Optionen direkt neben dem Eintrag zur Auswahl an. Hiermit kann z.B. die Tabellenerweiterung angehalten werden.
 
Zum Hinzufügen einer Zeile fügen Sie die Daten in die Zelle ganz links unterhalb der letzten Tabellenzeile ein. Zum Hinzufügen einer Spalte fügen Sie die Daten auf der rechten Seite der Spalte ganz oben in der Tabelle ein. Beim Erweitern der dynamischen Tabelle fügt Excel immer ganze Zeilen ein. Das heißt sofern Sie z.B. durch Ziehen der letzten rechten Zelle über mehrere neue Zeilen die Tabelle erweitern, fügt Excel entsprechend viele Zeilen ein und verschiebt sich in dem Bereich befindliche Daten um diese Anzahl Zeilen nach unten und überschreibt nichts - gut so ;-)



 
Tipp 125: Dokumentenspeicherort in Symbolleiste anzeigen

Mit der Funktion =Zelle(“Dateiname“) können Sie in jeder beliebigen Zelle den Speicherpfad, Speicherort und den Dateinamen inklusive des aktiven Tabellenblattes anzeigen lassen, bzw. mit etwas Formelkram auch nur Teile davon darstellen… Dies haben wir bereits in unseren vorherigen Tipps gezeigt. Den kompletten Speicherpfad der aktiven Arbeitsmappe können Sie sich aber auch in der Symbolleiste permanent einstellen und haben diesen somit jederzeit im Blick.
Möglich macht das eine Erweiterung der Symbolleiste für den Schnellzugriff, welche sich je nach Excel-Version oberhalb oder unterhalb der Menüs befindet. Diese Symbolleiste drängt sich übrigens geradezu auf, vom User benutzerdefiniert gestaltet zu werden und lässt sich wie folgt verändern:
Wählen Sie in der Symbolleiste für den Schnellzugriff rechts neben dem letzten Symbol den kleinen weißen Pfeil und wählen Sie in der sich öffnenden Befehlsliste den Eintrag Weitere Befehle …. Alternativ klicken Sie mit der rechten Maustaste auf die Symbolleiste und wählen im Kontextmenü von Excel 2016/2013 den Befehl Passen Sie die Symbolleiste für den Schnellzugriff an aus. In Excel 2010/2007 heißt der Befehl Symbolleiste für den Schnellzugriff anpassen.
Es öffnet sich das Dialogfeld Excel-Optionen.
In der linken Leiste ist die Option Symbolleiste für den Schnellzugriff (Excel 2016/2013/2010) bzw. Anpassen (Excel 2007) bereits aktiviert.
Oberhalb der langen Liste der Befehle können Sie die Anzahl der angezeigten Auswahl begrenzen oder erweitern: Befehle auswählen. Da der Befehl Dokumentenspeicherort nicht bei den häufig verwendeten erscheint, wählen Sie den Eintrag Alle Befehle.
Scrollen Sie in der eingeblendeten Befehlsliste zu dem Eintrag Dokumentspeicherort und klicken Sie rechts auf die Schaltfläche Hinzufügen, um den Befehl in den rechten Bereich der von Ihnen in Ihrer Symbolleiste angezeigten Befehle aufzunehmen. Excel packt die neuen Einträge ans Ende, wo der Befehl auch gut aufgehoben ist. Schließen Sie das Dialogfeld durch einen Klick auf OK.
Der Speicherort der aktuellen Arbeitsmappe wird nun immer in der Symbolleiste für den Schnellzugriff angezeigt.
Für Nutzer älterer Versionen: In den Excel-Versionen vor Excel 2007 blenden Sie die Symbolleiste Web ein. Hier wird Ihnen der komplette Speicherpfad der Datei angezeigt.



 
Tipp 126: Alle gesetzten Filter mit einem Klick entfernen

Für die Arbeit mit großen Tabellen mit vielen Spalten ist es sinnvoll, diese zu filtern. Hierfür setzt man in die Zeile über der Datentabelle einen Filter und kann nun in den verschiedenen Spalten auch mehrfach nach Datensätzen filtern/suchen. Da Dateien auch mit gesetzten Filtern gespeichert werden können ist es beim nächsten Öffnen manchmal mühevoll die verschiedenen gesetzten Filter wiederzuentdecken und einzeln zurückzusetzten. Schneller geht das mit einer Möglichkeit, die Excel bereits im Menüband vorgibt, die allerdings nur wenigen bekannt ist.
Hierzu können Sie einfach im Menüband im Register „Daten“ die Schaltfläche „Filter löschen“ anklicken und schon sind sämtliche gesetzten Filter entfernt. Keine Angst, der über die Tabelle gelegte Filter an sich bzw. die Filterschaltflächen über der Tabelle bleiben Ihnen dabei weiterhin erhalten.



 
Tipp 127: Den Wochentag aus einem Datum berechnen

Da Excel das Datum als fortlaufende Zahl führt, kann man mit dem aktuellen Datum eine ganze Menge berechnen, es mit anderen Werten verknüpfen oder auch nur Teile davon separat darstellen. So kann man z.B. aus einem Datum den jeweiligen Wochentag berechnen. Dieser lässt sich dann prima separat in einer Zelle darstellen oder auch mit weiterem Text oder anderen Formeln verknüpfen, um zusätzliche Informationen zu generieren.
Um aus einem Datum den Wochentag zu berechnen, gehen Sie wie folgt vor: Geben Sie zunächst ein Datum in eine beliebige Zelle ein, z.B. in die Zelle D1 den 31.12.2099. Geben Sie nun in eine andere Zelle, z.B. D2 die Formel =TEXT(D1;"TTTT") ein. Wenn Sie mit ENTER bestätigen, erscheint der Wochentag. Für das Beispiel ist es ein Donnerstag. Die Formel können Sie sehr gut mit anderen Formeln wie z.B. =HEUTE() oder auch für Verknüpfungen und Verkettungen mit Text verwenden.



 
Tipp 128: Ursprüngliche Formate nach Kopieren und fälschlichem Einfügen wieder herstellen

Für schnelles Kopieren und Einfügen von Daten gibt es mehrere unterschiedliche Möglichkeiten. Genauso wie die Möglichkeiten, was Sie von dem Kopierten einfügen … Formeln, Werte, Formate, Kommentare, … oder vielleicht doch alles? Wenn dann eigentlich nur die Werte gewollt waren und doch etwas voreilig alles eingefügt wurde, ist der Schrecken groß. Schnell das Einfügen rückgängig machen und dann nur die Formeln einfügen. Z.B. ein paar Klicks per Kontextmenü der rechten Maustaste, Inhalte einfügen … Werte – passt. Das geht aber schneller, denn es gibt eine Tastenfolge, mit der Sie die ursprünglichen Formate direkt nach dem Einfügen wieder herstellen können.
Probieren Sie es aus, markieren Sie sich eine nett formatierte Zelle und kopieren Sie mit Strg+c. Wählen Sie eine anders formatierte Zielzelle und fügen Sie mit Strg+v alle Zellinformationen ein.
Wenn Sie jetzt im Anschluss direkt die Tastenfolge Strg und dann w drücken, bleiben die eingefügten Werte vorhanden, es erscheinen aber wieder die vorherigen Formate.
Wichtig zu wissen: Excel macht im Grunde genommen selber rückgängig und holt den Inhalt aus der Zwischenablage. Solange also die Zwischenablage noch aktiv ist, funktioniert die Tastenfolge. Beachten Sie den Unterschied zwischen Tastenfolge und Tastenkombination. Hier sind die Strg- und die w-Taste nacheinander einzeln zu drücken.
Auch wichtig zu wissen ist, dass etwaige Formeln mit der Tastenfolge Strg und w nicht wieder hergestellt werden, Excel trägt nur die Werte (w wie Werte) aus der Zwischenablage ein. Doch es gibt noch weitere Tastenfolgen die mehr erledigen. Im folgenden sind nur einige davon genannt:
Strg und f – fügt die Formeln aus der Zwischenablage ein
Strg und o – Formeln und Zahlenformat
Strg und a – Werte und Zahlenformat
Strg und e – Werte und Quellformatierung
Strg und r – nur die Formatierung
Strg und k – keine Rahmenlinien
Strg und u – fügt so ziemlich alles ein, erhält aber die ursprüngliche Formatierung



 
Tipp 129: Plan-Ist-Abweichungen visualisieren mit Balkengrafiken


 
Tipp 130: Rahmenlinien schnell mit der Maus zeichnen bzw. entfernen

Um Tabellen den passenden Rahmen zu verpassen, ist es ein gebräuchlicher Weg, diese zu markieren und dann mittels "Zellen formatieren" im Register "Rahmen" die gewünschten Linien zu selektieren. Bei Änderungen und unterschiedlichen Rahmenarten kann das manchmal etwas langwierig sein. Ein nützliches Werkzeug ist das weniger bekannte "Rahmenlinie zeichnen" und "Rahmenlinie entfernen", was direkt über das Menüband aufgerufen werden kann. Mit der Maus können dann eingestellte Linienarten direkt "gezeichnet" bzw. "radiert" werden. Auch die diagonalen Linien zur Entwertung einer Zelle sind hiermit schnell eingefügt.
 
Grafik Excel-Tipp 130
 
Die Lösung für alle Ihre Rahmenprobleme finden Sie im Register "Start" in der Gruppe "Schriftart". Dort finden Sie das Symbol für Rahmenlinien mit einem Dropdown-Pfeil daneben. Ein Klick auf den Pfeil öffnet das Menü, wo Sie bestimmte Rahmenlinien und -stärken auswählen können. Die Selektion wird dann in dem Symbol angezeigt und für folgende Aktionen aktiviert. Weiter unten in dem Dropdown-Menü ist der Bereich "Rahmenlinien zeichnen".
 
Excel-Tipp 130 Rahmenlinien
 
Wenn Sie hier "Rahmenlinie zeichnen" klicken, ändert sich der Mauszeiger in einen Stift und Sie befinden sich jetzt in dem Editiermodus. Jeder Klick auf einen Zellrahmen erzeugt nun eine Rahmenlinie in der vorselektierten Stärke und Farbe, egal ob oben, unten oder an den Seiten. Den Editiermodus verlassen Sie durch Drücken der ESC-Taste oder einen Doppelklick mit der Maus innerhalb Ihrer Tabelle.
"Lienienfarbe" und "Linienart" funktionieren gleich, führen in den Editiermodus und tun was sie sollen. "Rahmenlinie entfernen" zeigt im Editiermodus ein Radiergummi an und lässt Sie Rahmenlinien einzeln "wegklicken" und radiert auch großflächig Bereiche aus. "Rahmenraster zeichnen" steht auch noch zur Verfügung, bringt allerdings keine wesentlichen Vorteile ggü. der einfachen Formatierung mit "Alle Rahmenlinien".
Das Erzeugen der diagonalen Linien in den Zellen erfolgt übrigens nicht durch einfachen Klick, sondern durch das Klicken und Ziehen von Ecke zu Ecke - probieren Sie es aus...



 
Tipp 131: Erweitertes Markieren – mehrere Zellen mit Tastatur und/oder Maus auswählen

In Excel können Zellinhalte aus einer oder mehreren Zellen wie auch ganze Zeilen oder Spalten durch selektives Markieren auswählt werden.
 
Mit Hilfe der Tastatur und ohne Maus haben Sie u.a. folgende Möglichkeiten:
Markieren Sie eine Zelle, drücken Sie die UMSCHALT-Taste und halten Sie diese gedrückt. Drücken Sie nun zusätzlich z.B. auf die Pfeiltaste „nach unten“. Excel erweitert nun die Markierung um die angrenzende, untere Zelle. Dies geht mit den verschiedenen Pfeiltasten natürlich in alle gewünschten Richtungen und so lange, wie Sie die UMSCHALT-Taste gedrückt halten. Nach oben und unten geht das auch mit den Bildlauftasten. Damit erweitert sich die Markierung über jeweils einen Seitenbereich.
Wenn Sie zusätzlich zur UMSCHALT- noch die STRG-Taste drücken, können Sie die Markierung auch automatisch bis zum letzten Wert in der Spalte (alternativ: Zeile) erweitern. Hierzu also UMSCHALT+STRG+Pfeiltaste drücken. Weiter mit der Markierung „wandern“ können Sie, indem Sie die STRG-Taste loslassen, UMSCHALT gedrückt behalten und mit den Pfeiltasten die Markierung in die unterschiedlichen Richtungen erweitern.
Eine ganze Spalte markieren Sie übrigens, indem Sie eine Zelle in der gewünschten Spalte auswählen und STRG+LEERTASTE drücken. Eine ganze Zeile, indem Sie eine Zelle in der Zeile auswählen und UMSCHALT+LEERTASTE drücken. STRG+A markiert ganze Tabellen oder auch Listen.
 
Mit der Maus geht dann noch mehr:
Ganze Spalten oder Zeilen sind mit der Maus schnell über die Spalten- bzw. Zeilenköpfe zu markieren. Das ganze Tabellenblatt mit einem Klick auf die Schaltfläche „Alles auswählen“ in der linken oberen Ecke:
Schaltfläche: Alles auswählen
 
In Kombination von Tastatur und Maus sind die Möglichkeiten aber noch umfangreicher und bestehende Markierungen lassen sich z.B. leicht um nicht angrenzende Zellen erweitern:
Einen ganzen Zellblock wie z.B. A1:D10 markieren Sie, indem Sie die Zelle A1 (Startzelle) wählen, die UMSCHALT-Taste gedrückt halten und D10 (Zielzelle) klicken.
Probieren Sie das gleiche mit der STRG-Taste. Das Ergebnis sind lediglich zwei markierte Zellen: A1 und D10. D.h. also, mit Hilfe der STRG-Taste können Sie einzelne Zellen oder auch Zellbereiche markieren, die sich nicht direkt nebeneinander befinden! Solange Sie die STRG-Taste gedrückt halten, können Sie beliebig viele Zellen markieren, was z.B. hilfreich sein kann, wenn man schnell und nur ganz selektiv bestimmte Werte in Tabellen anders farbig formatieren möchte. Diese Art des Markierens mit der STRG-Taste nennt man erweitertes Markieren.



 
Tipp 132: Zellwerten Einheiten wie kg, ml, Stück, qm, °C und weitere hinzufügen

Einzelnen Werten in Excel-Zellen Währungseinheiten hinzuzufügen ist nicht schwer. Das gelingt schnell über das Dialogfenster „Zellen formatieren ...“. Mit der Kategorie „Währung“ lassen sich so recht einfach € oder $ oder etwa auch EUR und USD auswählen. Etwas komplizierter, aber auch schnell hinzuzufügen, sind die nicht in der Kategorie vorgesehenen Einheiten wie kg, ml, Stück, qm, °C oder auch andere, frei definierbare Werte.
 
Das direkte Eintippen einer Einheit wie z.B. kg zusammen mit dem Wert in eine Zelle führt nicht zum Ziel: Wenn Sie z.B. “50 kg” eingeben, zeigt Excel das zwar genauso an, behandelt den Zellinhalt aber nicht mehr als Zahl. Die Folge davon: Sie können nicht mehr mit dem Wert 50 rechnen. Einige lösen dieses Problem, indem sie im der Spalte neben dem Wert eine Zelle mit der Maßeinheit anlegen, was recht unpraktisch bei nachträglichen Veränderungen an der Struktur der Tabelle sein kann und manchmal auch nicht schön aussieht.
 
Besser ist es, die gewünschte Einheit im Dialogfenster „Zellen formatieren ...“ in der Kategorie „Benutzerdefiniert“ für die Zelle, welche den Wert enthält, zu definieren. Als vorgewählter „Typ“ ist hier zunächst immer „Standard“ ausgewählt. Um einem Wert von z.B. 50 die Einheit kg zuzuweisen, können Sie „Standard“ überschreiben mit:
 
0 „kg“
 
Geben Sie nach der Null ein Leerzeichen ein, sodass in der Darstellung auch ein Abstand zwischen der 50 und kg erscheint. Die gewünschte Einheit muss immer in „“ stehen, damit Excel diese "nur" als Zusatz zum eigentlichen Wert interpretieren kann. Sie können auch andere Zahlenformate mit der in Anführungszeichen dargestellten Einheit kombinieren, z.B.:
 
0,00 „kg“    - falls Sie Nachkommastellen wünschen oder
#.##0 „kg“ - falls es etwas mehr sein darf (Tausendertrennzeichen ohne Nachkommastellen)
 
Dies funktioniert identisch für alle andere Einheiten. Das Gradsymbol bei °C finden Sie übrigens meistens direkt auf Ihrer Tastatur links neben dem Ausrufezeichen (Shift-Taste drücken). Sie können es aber auch über den Symbolzeichensatz kopieren und einfügen ... und der ermöglicht dann noch ganz andere „Einheiten“.



 
Tipp 133: Aktuelles Datum mit Text verbinden und weiterhin damit rechnen

Das aktuelle Datum kann man in einer Zelle mit Text verbinden. Z.B. um „Fällig bis: 29.09.2021“ darzustellen. Das Datum wird dabei über die Funktion HEUTE() geliefert. Die Kombination von einem Wert und einem Text ist jedoch etwas knifflig, wenn man damit weiterrechnen will. Mit der Funktion VERKETTEN oder dem Berechnungsoperator & ergibt sich aber leider folgendes Ergebnis: Fällig bis: 44467. Über entsprechende Funktionen oder Formatierungen kann der Wert 44467 jedoch als Datum dargestellt werden.
 
Die erste, etwas umständlichere Möglichkeit zur Lösung bietet die Funktion TEXT. Unterschiedliche Zeichenfolgen können mit ihrer Hilfe kombiniert werden. So wird mit Hilfe der Funktion Text aus dem Wert 44467 das Datum 29.09.2021 angezeigt, ohne dass Sie das Zellformat ändern:
 
=TEXT(44467;"TT.MM.JJJJ")
 
Mit VERKETTEN oder dem Operator & ergeben sich somit die gewünschten Zellinhalte „Fällig bis: 29.09.2021“.
 
=VERKETTEN("Fällig bis: ";TEXT(HEUTE();"TT.MM.JJJJ"))
="Fällig bis: " & TEXT(HEUTE();"TT.MM.JJJJ")
 
Allerdings kann man in beiden Fällen nicht mehr direkt mit dem Datum rechnen, da es für die Ansicht in Text umgewandelt wurde. Um dieses heutige Datum (angenommen es steht in Zelle A4) nun z.B. mit den Daten sämtlicher fälliger und nicht fälliger Rechnungen zu vergleichen, besteht die Möglichkeit, nur die letzten 10 Zeichen des Zellwertes/das Datum, zu vergleichen. Z.B. mit RECHTS(A4;10):
 
=SUMMEWENN(F:F;"<="&RECHTS(A4;10);G:G)
 
Eine weitere, etwas schnellere Möglichkeit bietet eine entsprechende Formatierung der Zelle mit Text und Datum über ein Benutzerdefiniertes Format. Analog zu den benutzerdefinierten Einheiten in Tipp 132 können auch ganze Texte den Zellwerten vorangestellt werden. Schreiben Sie in eine neue Zelle zunächst die Funktion =HEUTE(). Angezeigt wird dann das aktuelle Datum, die Zelle ist standardmäßig mit TT.MM.JJJJ formatiert. Wählen Sie die Zelle erneut aus, klicken mit der rechten Maustaste zum Dialogfenster „Zellen formatieren ...“ und geben in der Kategorie „Benutzerdefiniert“ folgendes ein:
 
"Fällig bis:" TT.MM.JJJJ
 
Wenn Sie jetzt Enter drücken, zeigt Ihnen Excel den gewünschten Text und behält die Datumsformatierung für weitere Vergleiche mit den oben genannten Rechnungsdaten bei. Die Formel für die oben genannte SUMMEWENN-Funktion sehe dann wie folgt aus:
 
=SUMMEWENN(F:F;"<="&A4;G:G)



 
Tipp 134: Hyperlinks in Tabellen einfügen, bearbeiten und entfernen

In Excel kann man mit Hyperlinks zu einer anderen Zelle in der aktuellen Arbeitsmappe springen, in andere Dateien wechseln (nicht nur .xls), Webseiten aufrufen oder auch per E-Mail-Adresse eine neue E-Mail öffnen. Damit kann man den Nutzer in den Dokumenten führen oder auch gezielt zu Aktionen aufrufen bzw. ihm diese mit einem Link ermöglichen (navigieren, Webseite öffnen, E-Mail schreiben).
 
Mit der Funktion HYPERLINK erstellt man eine solche Verknüpfung. Die Syntax der Funktion lautet: HYPERLINK(Hyperlink_Adresse, [Anzeigename])
Allerdings bietet Excel für das Einfügen eines Hyperlinks einen einfachen Assistenten. Gehen Sie zunächst in die Zelle, aus der Sie verlinken möchten und öffnen Sie im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Der Assistent "Link einfügen" öffnet sich und man erkennt schon die verschiedenen Möglichkeiten, welche sich hier bieten. Standard mäßig ausgewählt ist die Möglichkeit einer Verlinkung im aktuellen Dokument:
 
Excel-Tipp 134 Assistent
 
Steht der Cursor beispielsweise in A1 können Sie, um nun z.B. einen einfachen Sprung aus Zelle A1 in beispielsweise C10 vorzunehmen, in dem Feld "Geben Sie den Zellbezug ein" den Bezug "C10" eingeben. Mit der Bestätigung auf OK ist der Link fertig. Der Linktext in der Zelle wird jetzt blau angezeigt und ist unterstrichen; beim Klicken darauf springt der Cursor in Zelle C10.
 
Sie können den Hyperlink nachträglich bearbeiten und z.B. den anzuzeigenden Text ändern oder das Linkziel nochmals anpassen. Hierzu ist jedoch folgendes zu beachten:
 
Wenn Sie auf eine Zelle klicken, die eine HYPERLINK-Funktion enthält, springt Excel zu dem Linkziel oder öffnet das angegebene Dokument oder die Webseite. Um jedoch eine Zelle auszuwählen, die einen Link enthält, ohne zum Linkziel zu springen, klicken Sie auf die Zelle, und halten Sie die Maustaste kurz gedrückt, bis der Auswahl-Mauszeiger wieder zu dem Kreuz-Zeiger wird, und lassen Sie dann die Maustaste los. Die Zelle ist jetzt aktiv und Sie können über "Link" den Assistenten wieder aufrufen. Alternativ können Sie die Zelle mit der Tastatur ansteuern oder auch mit einem Rechtsklick das Kontextmenü aufrufen, in dem dann z.B. auch der Punkt "Hyperlink bearbeiten ..." verfügbar ist und Sie auch zum Assistenten führt.
 
Die mit einem Link versehenen Zellen können Sie ganz normal formatieren, also auch den Unterstrich wieder entfernen oder die Farbe ändern oder oder oder ... Möglich ist auch das Verlinken beim Klick auf eine Grafik oder ein sonstiges Objekt. Hierzu ist dieses zu selektieren und gleichwegs mit dem Assistenten der Link zu erstellen.
 
Einen Hyperlink entfernen Sie entweder, indem Sie den gesamten Zellinhalt einfach löschen oder über das Kontextmenü (rechte Maustaste) den Befehl "Link entfernen" wählen. Bei lezterer Alternative bleibt Ihr Zellinhalt bestehen, nur die Verlinkung wird aufgehoben.



 
Tipp 135: Mit Hyperlink aus Excel eine Webseite aufrufen

In Excel kann man mit Hyperlinks zu einer anderen Zelle in der aktuellen Arbeitsmappe springen, in andere Dateien wechseln (nicht nur .xls), Webseiten aufrufen oder auch per E-Mail-Adresse eine neue E-Mail öffnen. Damit kann man den Nutzer in den Dokumenten führen oder auch gezielt zu Aktionen aufrufen bzw. ihm diese mit einem Link ermöglichen (navigieren, Webseite öffnen, E-Mail schreiben).
 
Um aus einer Exceltabelle nun eine Webseite aufzurufen, steht die Funktion HYPERLINK bereit, mit der man eine solche Verknüpfung erstellen kann. Die Syntax der Funktion lautet: HYPERLINK(Hyperlink_Adresse, [Anzeigename])
Allerdings bietet Excel für das Einfügen einen einfachen Assistenten, mithilfe dessen Sie die Verknüpfung erstellen können. Gehen Sie zunächst in die Zelle, aus der Sie verlinken möchten, schreiben Sie ggfs. schon den Linktext in die Zelle und öffnen Sie dann im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Der Assistent "Link einfügen" öffnet sich und man erkennt schon die verschiedenen Möglichkeiten, welche sich hier bieten. Standard mäßig ausgewählt ist die Möglichkeit einer Verlinkung im aktuellen Dokument. Wechseln Sie auf "Datei oder Webseite".
 
Excel-Tipp 135 Assistent
 
Nun können Sie die gewünschte Webseitenadresse direkt in das Feld "Adresse" eingeben oder Sie wählen in der Liste der "Besuchte Webseiten" die passende aus. Den anzuzeigenden Linktext können Sie hier ebenfalls nochmals ändern. Ist die Adresse nicht bekannt bzw. nicht vollständig erinnerbar, können Sie diese über das Suchsymbol googlen; es öffnet sich der voreingestellte Browser.
 
Schnell und einfach eine Verlinkung auf eine Webseite erstellen können Sie auch per Copy&Paste: Googlen Sie zunächst die betreffende Webseite, kopieren Sie im Browser deren vollständige Adresse, wechseln Sie dann zu Excel und fügen Sie den Inhalt (aus der Zwischenablage) und somit die Adresse in die gewünschte Zelle ein. Je nach Browser und Browsereinstellung kann es sein, dass Sie nicht den Linktext, sondern den Webseitentitel angezeigt bekommen und somit den Hyperlink ggfs. noch anpassen müssen. Die Möglichkeiten hierzu lesen Sie oben in unserem Tipp 134 - Hyperlinks in Tabellen einfügen, bearbeiten und entfernen.



 
Tipp 136: Mit Hyperlink aus Excel eine E-Mail versenden

In Excel kann man mit Hyperlinks zu einer anderen Zelle in der aktuellen Arbeitsmappe springen, in andere Dateien wechseln (nicht nur .xls), Webseiten aufrufen oder auch per E-Mail-Adresse eine neue E-Mail öffnen. Damit kann man den Nutzer in den Dokumenten führen oder auch gezielt zu Aktionen aufrufen bzw. ihm diese mit einem Link ermöglichen (navigieren, Webseite öffnen, E-Mail schreiben).
 
Um aus einer Exceltabelle nun eine E-Mail zu starten, steht die Funktion HYPERLINK bereit, mit der man eine solche Verknüpfung erstellen kann. Excel stellt für das Einfügen einen einfachen Assistenten zur Verfügung, mithilfe dessen Sie die Adresse und auch einen Betreff eingeben können. Klicken Sie zunächst in die Zelle, aus der Sie verlinken möchten, schreiben Sie ggfs. schon die E-Mail-Adresse in die Zelle und öffnen Sie dann im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Der Assistent "Link einfügen" öffnet sich und man erkennt schon die verschiedenen Möglichkeiten, welche sich hier bieten. Standard mäßig ausgewählt ist die Möglichkeit einer Verlinkung im aktuellen Dokument. Wechseln Sie auf "E-Mail-Adresse".
 
Excel-Tipp 136 Assistent
 
Sofern Sie schon eine Adresse in Ihre Zelle eingegeben hatten, erscheint diese bereits als "Anzuzeigender Text", ansonsten sind die Felder zunächst leer. Geben Sie dann den anzuzeigenden Text ein, die E-Mail-Adresse und wenn gewünscht auch noch einen Betreff. Unterstützend zeigt Excel eine Liste der zuletzt verwendeten Adressen an, aus der man auch die E-Mail-Adresse selektieren kann. Excel fügt bei der Eingabe der E-Mail-Adresse automatisch „mailto:“ hinzu, um einen E-Mail-Link zu erzeugen. Da in dem Betreff für einen Link technisch bedingt keine Leerzeichen enthalten sein dürfen, füllt Excel diese automatisch mit der Zeichenfolge "%20" auf.
 
Schnell und einfach können Sie eine E-Mail-Verlinkung in Excel per Copy&Paste erstellen: Hierzu benötigen Sie jedoch zunächst die ausgeschriebene E-Mail-Adresse, kopieren Sie diese und wechseln dann zu Excel. Fügen Sie den Inhalt (aus der Zwischenablage) direkt in die gewünschte Excelzelle ein. Je nach Browser und Browsereinstellung kann es sein, dass Sie noch nicht den E-Mail-Link, sondern "nur" den Text angezeigt bekommen. Markieren Sie dann die Zelle nochmals und drücken Sie "Enter", damit Excel den Link erstellt. Dies ist an dem Unterstrich und der blauen Textfarbe zu erkennen. Den Linktext, die Adresse und den Betreff können Sie auch nachträglich noch bearbeiten und formatieren. Die Möglichkeiten hierzu lesen Sie oben in unserem Tipp 134 - Hyperlinks in Tabellen einfügen, bearbeiten und entfernen.



 
Tipp 137: Mit Hyperlink eine andere Datei öffnen

In Excel kann man mit Hyperlinks zu einer anderen Zelle in der aktuellen Arbeitsmappe springen, Webseiten aufrufen, per E-Mail-Adresse eine neue E-Mail öffnen und auch in andere Dateien wechseln - und das nicht nur in Excel-Dateien. Damit kann man den Nutzer in den Dokumenten führen oder auch gezielt zu Aktionen aufrufen bzw. ihm diese mit einem Link ermöglichen (navigieren, Webseite öffnen, E-Mail schreiben, Datei öffnen). So können z.B. weitere Infomationen zu den Excelinhalten mit verknüpften PDF- oder Bilddateien angeboten werden. Diese müssen dann nicht in die Datei integriert werden und vergrößern diese nicht unnötig. Wichtig bleibt allerdings die Aufrechterhaltung der Verknüpfung bzw. das Sicherstellen, dass die zusätzlichen Dateien auch immer an dem Ort verbleiben, auf den die Verknüpfung weist.
 
Um aus einer Exceltabelle eine andere Datei zu öffnen, steht die Funktion HYPERLINK bereit, mit der man eine solche Verknüpfung erstellen kann. Die Syntax der Funktion lautet: HYPERLINK(Hyperlink_Adresse, [Anzeigename]). Sie kann über den Funktionsassistenten aufgerufen werden.
 
Excel stellt aber für das Einfügen der Links einen zusätzlichen Assistenten zur Verfügung, mithilfe dessen Sie die Adresse und den Anzeigenamen (den in der Zelle anzuzeigenden Text) eingeben oder auch nach der Datei in Ihrer Ordnerstruktur suchen können. Klicken Sie zunächst in die Zelle, aus der Sie verlinken möchten und öffnen Sie dann im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Der Assistent "Link einfügen" öffnet sich und man erkennt schon die verschiedenen Möglichkeiten, welche sich hier bieten. Standard mäßig ausgewählt ist die Möglichkeit einer Verlinkung im aktuellen Dokument. Wechseln Sie auf "Datei oder Webseite".
 
Excel-Tipp 137 Assistent
 
Sofern Sie schon einen Inhalt in Ihre Zelle eingegeben hatten, erscheint dieser bereits als "Anzuzeigender Text", ansonsten sind die Felder zunächst leer. Geben Sie dann den anzuzeigenden Text ein und wählen Sie die aufzurufende Datei aus. Hierzu können Sie bei Auswahl von "Aktueller Ordner" in Ihrer Ordnerstruktur mit den bekannten Explorerfunktionen suchen oder auch bei Auswahl von "Zuletzt verwendet" entsprechende Excel-Dateien direkt selektieren. Ein Klick auf die Datei und der Dateiname wird in das Feld "Adresse" übernommen. Ein Klick auf "OK" und der Link wird erstellt. Dies ist an dem Unterstrich und der blauen Textfarbe zu erkennen. Sie können auch statt der Datei einen Ordner auswählen, dann wird beim Verwenden des Hyperlinks der Ordner im Explorer geöffnet. Den Linktext, die Adresse und die gesamte Zelle können Sie auch nachträglich noch bearbeiten und formatieren. Die Möglichkeiten hierzu lesen Sie oben in unserem Tipp 134 - Hyperlinks in Tabellen einfügen, bearbeiten und entfernen.



 
Tipp 138: Mit Hyperlinks in Exceldateien navigieren

In Excel kann man mit Hyperlinks zu einer anderen Zelle im gleichen Dokument oder einer anderen Tabelle der Arbeitsmappe springen, in andere Dateien wechseln wie auch Webseiten aufrufen oder mit E-Mail-Adressen eine neue E-Mail öffnen. Damit kann man den Nutzer in den Dokumenten führen oder auch gezielt zu Aktionen aufrufen bzw. ihm diese mit einem Link ermöglichen (navigieren in einem bzw. zwischen mehreren Tabellenblättern; Dateien öffnen).
 
Um von einer aktiven Excelzelle eine andere Zelle zu springen, steht die Funktion HYPERLINK bereit, mit der man eine solche Verknüpfung zwischen Tabellen und Dateien erstellen kann. Die Syntax der Funktion lautet: HYPERLINK(Hyperlink_Adresse, [Anzeigename]). Sie kann über den Funktionsassistenten aufgerufen werden.
 
Excel stellt aber für das Einfügen der Links einen zusätzlichen Assistenten zur Verfügung, mithilfe dessen Sie die Zell-Adresse und den Anzeigenamen (den in der Zelle anzuzeigenden Text) eingeben können. Klicken Sie zunächst in die Zelle, aus der Sie verlinken möchten und öffnen Sie dann im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Der Assistent "Link einfügen" öffnet sich und man erkennt schon die verschiedenen Möglichkeiten, welche sich hier bieten. Standard mäßig ausgewählt ist die Möglichkeit einer Verlinkung im aktuellen Dokument. Ist dies bei Ihnen aufgrund anderer Einstellungen nicht der Fall, wechseln Sie zunächst auf "Aktuelles Dokument".
 
Excel-Tipp 138 Assistent
 
Sofern Sie schon einen Inhalt in Ihre Zelle eingegeben hatten, erscheint dieser bereits als "Anzuzeigender Text", ansonsten sind die Felder zunächst leer. Geben Sie dann den anzuzeigenden Text ein und den Zellbezug wie im obigen Beispiel (für einen Sprung aus Zelle A1 nach C10) ein. Ein Klick auf "OK" und der Link wird erstellt. Dies ist an dem Unterstrich und der blauen Textfarbe zu erkennen. Alternativ können Sie für den Zellbezug auch eine andere Stelle (Tabelle, Festgelegte Namen) in der Datei wählen. Der Assistent zeigt Ihnen hierzu die in Ihrer Arbeitsmappe bereits vorhandenen Tabellenblätter und ggfs. auch bereits definierte "Festgelegte Namen" an. Geben Sie nur eine Tabelle an, ergänzt Excel immer automatisch den Zellbezug A1.
 
Alternativ können Sie auch in eine andere Datei verlinken. Hierzu können Sie statt "Aktuelles Dokument" "Datei oder Webseite" auswählen und in Ihrer Ordnerstruktur mit den bekannten Explorerfunktionen suchen oder auch bei Auswahl von "Zuletzt verwendet" entsprechende Excel-Dateien direkt selektieren. Ein Klick auf die Datei und der Dateiname wird in das Feld "Adresse" übernommen. Über den Button "Textmarke" definieren Sie im folgenden Pop-up-Fenster noch zusätzlich in welche Tabelle (Stelle) und in welche Zelle (Zellbezug) gesprungen werden soll.
 
Excel-Tipp 138 Textmarke
 
Den Linktext, die Adresse und die gesamte Zelle können Sie auch nachträglich noch bearbeiten und formatieren. Die Möglichkeiten hierzu lesen Sie oben in unserem Tipp 134 - Hyperlinks in Tabellen einfügen, bearbeiten und entfernen.



 
Tipp 139: Hyperlinks auf Textmarken beim Einfügen von Zeilen oder Spalten mitwandern lassen

Mit Hyperlinks kann man in Excel zu einer anderen Zelle im gleichen Dokument, einer anderen Tabelle der Arbeitsmappe aber auch zu Zellen in anderen Dateien springen. Damit kann man den Nutzer in den Dokumenten führen und navigieren. Wie diese Hyperlinks mit Zellbezügen und Textmarken erstellt werden, lesen Sie in den vorherigen Tipps (Nr. 134 – 138). Die Schwierigkeit bei den Hyperlinks mit Zellbezügen ist die, dass die Bezüge zwar relativ (z.B. A100) eingegeben werden können, Excel das aber absolut ansieht. Dies hat zur Folge, das beim Einfügen oder Löschen von Zeilen das eigentliche Linkziel gar nicht mehr in der Beispielzelle A100 ist, der Link mit der Textmarke aber immer noch da hinspringt.
 
Es gibt eine Möglichkeit, dass die Verlinkung "mitwandert", wenn Zeilen oder Spalten eingefügt oder gelöscht werden. Geben wir der Sache einen Namen:
 
In Excel können Sie für eine Zelle oder einen Zellbereich einen Namen vergeben. Mit diesem festgelegten Namen benennen Sie den Inhalt der Zelle und definieren nicht Ihren Ort (z.B. A100). Wandert der Inhalt durch das Einfügen oder Entfernen von Zellbereichen, wandert der Name quasi mit. Wenn Sie statt einem Zellbezug auf eine bestimmte Zelle also einen Festgelegten Namen verwenden, ist das Problem gelöst.
 
Einen Namen verpassen Sie Ihrer Zelle auf dem schnellsten Wege, indem Sie nach Auswahl der Zelle oder des Zellbereiches im Namenfeld der Bearbeitungsleiste den gewünschten Namen direkt eintragen und mit der Eingabetaste bestätigen. Wählen Sie also z.B. Zelle A100, überschreiben Sie diesen angezeigten Wert einfach im Namenfeld (mit z.B. "Auswertung2" – Achtung, Leerstellen mag Excel auch hier gar nicht).
 
Excel-Tipp 139 Namenfeld1
 
Nach der Bestätigung der Eingabetaste wird der neue Name bei jeder Auswahl der Zelle im Namenfeld angezeigt.
 
Excel-Tipp 139 Namenfeld2
 
Fügen Sie jetzt z.B. drei neue Zellen vor Zeile 100 ein, werden Sie sehen, dass in Zelle A100 kein Name mehr angezeigt wird, dafür steht er aber in Zelle A103!
 
Excel-Tipp 139 Namenfeld3
 
Somit können Sie Ihr Sprungziel "Auswertung2" jetzt mit einem Hyperlink belegen. Klicken Sie zunächst in die Zelle, aus der heraus Sie verlinken möchten und öffnen Sie dann im Menü "Einfügen" mit Klick auf das Symbol in der Gruppe "Link" den Link-Assistenten. Eine schnelle Tastenkombination für den Aufruf lautet STRG + K:
 
Excel-Tipp 134 Links
 
Wenn Sie in der aktuellen Datei verlinken möchten, wechseln Sie zunächst auf "Aktuelles Dokument", ansonsten auf "Datei oder Webseite". Geben Sie einen Text ein, der als Linktext angezeigt werden soll und wählen Sie anstatt des Zellbezuges Ihren Festgelegten Namen. Alle in der Datei festgelegten Namen werden hier angezeigt. Nach dem Klick auf einen Namen setzt Excel das Feld mit dem Zellbezug inaktiv.
 
Excel-Tipp 139 Festgelegte Namen
 
Ein Klick auf "OK" und der Link wird erstellt. Dies ist an dem Unterstrich und der blauen Textfarbe zu erkennen. Jetzt können Zeilen oder Spalten eingefügt oder gelöscht werden - der Bezug bleibt immer auf diese Stelle im Dokument.
 
Hinweis:
Auch über das Kontextmenü der Maus können Sie für Zellen und Zellbereiche Namen vergeben: Wählen Sie die Zelle beziehungsweise den Zellbereich, den Sie benennen möchten, klicken Sie mit der rechten Maustaste darauf und wählen Sie in dem Kontextmenü "Namen definieren …" aus. In dem neuen Fenster können Sie neben dem Namen auch noch dessen örtliche Gültigkeit und Größe definieren bzw. einen Kommentar ergänzen. Im Menü "Formeln" in der Gruppe "Definierte Namen" finden Sie den Namensmanager, mit dem Sie nachträglich die Daten auch noch ändern können.
 
Ein Name darf maximal 255 Zeichen lang sein, Buchstaben, Zahlen, Unterstriche und Punkte enthalten und muss mit Buchstaben oder einem Unterstrich beginnen. Nicht erlaubt sind Leerzeichen, Bindestriche und Namen, die wie Zellbezüge aussehen (z.B. A100, C10).
 
Den Linktext, die Adresse und die gesamte Zelle können Sie auch nachträglich noch bearbeiten und formatieren. Die Möglichkeiten hierzu lesen Sie oben in unserem Tipp 134 - Hyperlinks in Tabellen einfügen, bearbeiten und entfernen.



 
Tipp 140: Umwandlung von Text in Hyperlinks bei der Eingabe verhindern

Wie die obigen Tipps zeigen können Hyperlinks viel erleichtern. Manchmal allerdings möchte man gar nicht, dass der Link-Text in einen Hyperlink umgewandelt wird. Vielmehr soll der reine Text als Information stehen bleiben. Das Excel die Eingabe mit der Umwandlung in einen Link abschließt, können Sie verhindern, indem Sie z.B. vor den Linktext einen Apostroph (') setzen.
 
Beispiel: 'https://www.controllerspielwiese.de
 
Excel erkennt alles nach einem Apostroph als reinen Text. Gleiches gilt auch für die Darstellung von Formeln, wenn diese nicht berechnet, sondern als Informationstext angezeigt werden sollen, z.B.: '=A1+C4
 
Sie können die Umwandlung in einen anklickbaren Link auch verhindern, wenn Sie den Link gar nicht eingeben, sondern von anderswo kopieren und lediglich aus der Zwischenablage einfügen (Strg + V).
 
Sollte Links doch ungewollt erstellt worden sein, können Sie diese auch nachträglich noch recht schnell und einfach entfernen. Markieren Sie alle relevanten Zellen und im Kontextmenü der rechten Maustaste den Menüpunkt "Links entfernen".



 
Tipp 141: Umwandlung von Text in Hyperlinks dauerhaft abschalten

Wenn Ihnen die Möglichkeit der Unterdrückung der Linksetzung aus Tipp 140 zu müselig ist oder Sie mehrere Links eingeben möchten, können Sie die Verwandlung in einen Hyperlink auch dauerhaft bzw. für eine bestimmte Zeit abschalten.
 
Wählen Sie hierzu im Menü "Datei" die Excel-Optionen und dort den Menüpunkt "Dokumentprüfung". Mit den AutoKorrektur-Optionen können Sie einstellen, wie in Excel Text bei der Eingabe korrigiert und formatiert wird. Wählen Sie das Register "AutoFormat während der Eingabe" und entfernen Sie den Haken für das Ersetzen von Pfaden durch Links:
 
Excel-Tipp 141 Hyperlinks
 
Nach den Eingaben einfach wieder den Haken setzen und künftig werden auch gewollte Links wieder automatisch umgewandelt. Die Deaktivierung funktioniert ähnlich auch in Word und PowerPoint.



 
Tipp 142: Ganze Zeilen in Abhängigkeit von Werten formatieren und farbig hervorheben

Wenn Sie z.B. eine Liste regelmäßig mit Daten pflegen, ob der Kunde die Forderung gezahlt hat, oder ob der Projektstatus erreicht ist, dann kann es visuell vorteilhaft sein, die ganze Zeile farbig hervorzuheben. Somit lassen sich die noch zu bearbeitenden Datensätze schnell erblicken. Um die ganze Zeile automatisch in Abhängigkeit von Ihren Eingaben formatieren zu können, nutzen Sie die Bedingte Formatierung. Hiermit können Sie definieren, wie bestimmte Zellen basierend auf Ihren Eingaben gestaltet werden. Das geht nicht nur für die Zelle selbst, in die Sie einen Wert eingeben, sondern auch für ganze Zeilen oder Spalten.
 
Um es an einem Beispiel erklären zu können, nehmen wir einen Ausschnitt aus unserem Liquiditätsplanungs-Tool:
 
Excel-Tipp 142 Ausgang
 
Die hier eingetragenen Forderungen wurden noch nicht bezahlt. Erfolgt der Geldeingang, wird als Status „bez“ eingetragen und alle Werte in der Zeile sollen entsprechend kenntlich gemacht werden. In unserem Beispiel werden alle Einträge der gleichen Zeile anhand einer bedingten Formatierung bereits durchgestrichen. Jetzt sollen sie zusätzlich mithilfe einer weiteren bedingten Formatierung farbig hervorgehoben werden. Setzen Sie den Cursor in D6 und los geht es:
 
Excel-Tipp 142 Bedingte Formatierung
 
Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf den kleinen Pfeil können Sie die Menüstruktur öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt sowie die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und weiter von „Textinhalt...“.
 
Excel-Tipp 142 Bedingte Formatierung
 
In dem sich öffnenden Fenster kann nun definiert werden, bei welchem Textinhalt und mit welchen Formaten die Zellen formatiert werden sollen. Wir geben links „bez“ ein, wählen rechts mit „benutzerdefiniertem Format...“ aus und zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen im Register „Ausfüllen“ ein helles Grün aus. Mit der Bestätigung auf OK wird unsere neue Regel angelegt.
 
Diese können wir jetzt weiter anpassen, indem wir wiederholt auf „Bedingte Formatierung“ klicken und in dem Menü den Punkt „Regeln verwalten...“ wählen. Es öffnet sich der Manager für die Regeln zur bedingten Formatierung. Es besteht hier die Möglichkeit, sich alle Regeln einer Datei, eines Arbeitsblattes oder auch nur der aktuellen Auswahl (aktive Zellen) anzeigen zu lassen.
 
Excel-Tipp 142 Bedingte Formatierung
 
Sie sehen hier unsere neu erstellte Regel, wählen „Regel bearbeiten...“ und können im nächsten Fenster die „Formatierungsregel bearbeiten“.
 
Excel-Tipp 142 Bedingte Formatierung
 
Wählen Sie die unterste Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden" aus. Nun kann die Formel eingegeben werden, aufgrund derer die bedingte Formatierung aktiv werden soll. Geben Sie ein:
 
=$D6="bez“
 
Sie können hier relative und absolute Bezüge definieren. Da wir das Kriterium „bez“ immer in der Spalte D haben, es aber auch in anderen Zeilen abgefragt werden soll, muss die Spalte absolut mit dem $-Zeichen, die Zeile relativ ohne eingegeben werden.
 
Wählen Sie noch über den Schalter „Formatieren“ eine hellgrüne Füllfarbe und bestätigen Sie die Eingaben mit OK.
 
Excel-Tipp 142 Manager
 
In dem Manager sehen Sie jetzt schon das definierte Format. Jetzt muss nur noch der Anwendungsbereich von $D$6 in unserem Fall auf $A$6:$F$17 erweitert werden. Nutzen Sie hier nicht die Pfeiltasten während Ihrer Eingaben, das mag Excel gar nicht, sondern klicken Sie bei Bedarf mit der Maus in die Formeln. Bestätigen Sie die Eingaben mit OK. Das Ergebnis sieht jetzt wie folgt aus:
 
Excel-Tipp 142 Zielzustand
 
Die verwendete Beispieldatei in Excel gibt's hier zum Download.
 
Die Originaldatei Excel-Liquiditätsplanung 2.0 finden Sie in unseren Berichtsvorlagen.



 
Tipp 143: Berichtsname als Text zusammen mit Datumswert aus einer Zelle in der Überschrift darstellen

Wie schön ist es, wenn man monatlich möglichst wenig Aufwand mit der Aktualisierung von Berichten hat. Ein weiterer kleiner Vorteil auf diesem Weg kann sein, dass Sie bereits in der Überschrift den Berichtsnamen mit dem aktuellen Tagesdatum oder einer Monats- und Jahresangabe aus einer Zelle verknüpfen. Da man in Excel in einer Zelle jedoch nur ein Zellformat definieren kann, wird das Datum bei Verknüpfungen nicht in dem gewünschten Format angezeigt. Mit einem kleinen Trick lassen sich Datumsangaben und Texte aber in einer Formel darstellen. Und dann ist vieles möglich ...
 
Zellinhalte und Texte verknüpfen Sie in Excel mit dem &-Symbol (Kaufmännisches Und-Zeichen). Verknüpfen Sie in einer Zelle jedoch einen Text mit einer Datumsangabe stellt Excel das Datum nicht in einem Datumsformat dar, sondern als die fortlaufende Zahl, mit der es intern rechnet. Das würde dann aussehen, wie z.B. "Berichtsname 44621". Wandelt man die Datumsangabe jedoch in einen Text um, lassen sich die zwei Texte dann wunderbar kombinieren. Hierzu dient die Funktion TEXT, mit deren Hilfe Sie durch das Anwenden einer Formatierung mithilfe von Formatcodes die Anzeige des Zahlwertes ändern. Die Syntax lautet:
 
=TEXT(Wert, den Sie formatieren möchten;"Formatcode, der angewendet werden soll")
 
Diese Text-Funktion wird dann für unsere Zwecke mit dem Berichtsnamen verknüpft. Eine Lösung könnte wie folgt aussehen:
 
="Berichtsname "&TEXT(C6;"MMMM JJJJ")
 
Hierbei wird unterstellt, das in der Zelle C6 ein Datum steht, dessen Format dort beliebig ist. Das Datum ist der Wert, den Sie formatieren möchten. Der Formatcode, der angewendet werden soll ist "MMMM JJJJ" - also Monat und Jahr ausgeschrieben; dazwischen eine Leerstelle! Steht in Zelle C6 z.B. der 01.03.2099 ergibt die Formel: Berichtsname März 2099
 
Alternativ können Sie natürlich den Berichtsnamen auch indirekt aus einer Zelle oder dem Blattregister (s. Tipp 39) auslesen. Mit den möglichen Formatcodes können Sie z.B. auch das Tagesdatum verknüpfen: =TEXT(HEUTE();"TT.MM.JJ"), oder nur den aktuellen Wochentag: =TEXT(HEUTE();"TTTT"). Auch Zeitwerte sind möglich, falls es mal um Sekunden geht ... alle verfügbaren Formatcodes finden Sie unter "Zellen formatieren" im Register "Zahlen".



 
Tipp 144: Schnell alle Kommentare in allen Arbeitsblättern löschen

Kommentare einfügen, bearbeiten und auch wieder löschen erledigt man recht schnell mit dem Kontextmenü der rechten Maustaste. Mehrere Kommentare in einem Arbeitsblatt kann man selektiv löschen, indem man die Zellen markiert und das Kontextmenü und "Kommentare löschen" aufruft. Sollen alle Kommentare in einer Excel-Datei mit einem Mal und somit in allen Arbeitsblättern gelöscht werden, geht das auch recht einfach in nur zwei Schritten mit einer Tastenkombi und einer Menüfunktion. Das kann ganz nützlich sein, wenn man z.B. eine Datei an Kollegen weitergibt und die eigenen Kommentare vom Aufbau der Datei nicht mehr lesenswert oder überholt sind.
 
Sie müssen hierzu nicht alle Zellen in allen Arbeitsblättern einzeln markieren und über das Kontextmenü löschen. Sie markieren zunächst alle Arbeitsblätter! Hierzu klicken Sie auf die Blattregister - es gibt zwei Möglichkeiten: Und zwar mit gedrückter und gehaltener STRG-Taste zum Aktivieren einzelner, zusätzlicher Register oder, und viel schneller: Sie klicken zuerst auf das äußerste z.B. linke Register und bei gehaltener SHIFT-Taste auf das äußerste rechte Register. Alle Arbeitsblätter sind jetzt selektiert.
 
Drücken Sie nun zweimal die Tastenkombination STRG + A. Generell gilt: Wenn das Tabellenblatt Daten enthält, wird mit STRG + A der aktuelle Bereich markiert. Wenn STRG + A ein zweites Mal gedrückt wird, wird das gesamte Tabellenblatt markiert. Und weil Sie ja alle Register aktiviert hatten, sind jetzt in allen Tabellenblättern sämtliche Zellen markiert.
 
Das Kontextmenü der rechten Maustaste ist bei dieser Mehrfachselektion nicht erreichbar. Sie können jetzt jedoch in der Registerkarte Start in der Befehlgruppe Bearbeiten die Dropdown-Liste für den Befehl Löschen öffnen. Hier wählen Sie Kommentare löschen. In den alten Excel-Versionen bis einschließlich 2003 verwenden Sie die Funktion BEARBEITEN - LÖSCHEN - KOMMENTARE. Klicken Sie anschließend eine beliebige Zelle an, um die Markierung aller Arbeitsblätter und Zellen wieder aufzuheben.
 
Excel-Tipp 144 Menü Löschen



 
Tipp 145: Blitzvorschau: Spalten automatisch von Excel füllen lassen

Ab Excel 2013 steht Ihnen bereits die Blitzvorschau (auch: Flash Fill) zur Verfügung. Mit Ihrer Hilfe können Sie ohne viel Formelaufwand verschiedene Bearbeitungen in Datentabellen vornehmen. Sie können sich z.B. automatisch Daten ausfüllen lassen, Daten zerlegen, Daten kombinieren oder auch Daten korrigieren. Praktisch, wenn aus den Vorsystemen nur irgendwas ohne Formate und Struktur geladen wird. Konkrete Möglichkeiten gibt es sicher viele; einige Beispiele können sein: Vor- und Nachnamen aus einer Zelle in zwei aufteilen oder aus zwei in eine zusammenfügen. Vor- und Nachnamen tauschen und mit Komma trennen. Daten aus E-Mail-Adressen zerlegen. Fehlerhafte Groß- und Kleinschreibung korrigieren oder alles in Kleinbuchstaben umwandeln. Werte wie z.B. Telefonnummern mit Klammern, Leerstellen oder Slash formatieren ...
 
Wichtig hierbei ist, dass Excel anhand Ihrer Vorgabe ein Muster erkennt, anhand dessen eine Blitzvorschau für die folgenden Zellen erstellt werden kann. Passt Ihnen diese, können Sie diese einfach übernehmen, ansonsten auch wieder verwerfen.
 
Für ein erstes Beispiel nehmen wir an, dass aus einem Vorsystem Nach- und Vornamen in der folgenden Form in Excel übernommen worden sind (nachname; vorname):
 
Blitzvorschau allgemein
 
Um z.B. nun den Nachnamen aus der Spalte A zu extrahieren, schreiben Sie in der benachbarten Spalte B einfach den ersten Nachnamen in die erste Zeile (Becker):
 
Blitzvorschau Nachname
 
Der Befehl Blitzvorschau befindet sich in Excel 2013 bis 2019 auf der Registerkarte Daten in der Gruppe Datentools (s. Markierung):
 
Blitzvorschau Register
 
Aktivieren Sie die Zelle mit dem Nachnamen, falls dies nicht mehr der Fall ist und sobald Sie den Befehl Blitzvorschau durch Klicken ausführen, schlägt Excel bereits für alle weiteren Zellen die entsprechenden Werte vor. Sind diese korrekt, brauchen Sie den Vorschlag nur noch mit der ENTER- Taste zu bestätigen.
 
Blitzvorschau eingeben
 
Die Blitzvorschau ist daran zu erkennen, dass Excel neben den Werten ein Symbol für die Blitzvorschauoptionen einfügt. Sind die Werte nicht korrekt, können Sie über die Blitzvorschauoptionen die Blitzvorschau rückgängig machen.
 
Blitzvorschau Optionen
 
Mit der Blitzvorschau füllt Excel also Daten automatisch aus, wenn ein Muster erkannt wird. Damit dies funktioniert, muss sich die auszufüllende Spalte immer direkt neben den vorliegenden Datenspalten befinden. Es dürfen keine leeren Spalten dazwischen vorhanden sein. Die zu verarbeitende Spalte (A) muss aber nicht zwingend neben der neuen Spalte (B) liegen, wie folgende Beispiele belegen. Hierzu geben wir in Spalte C nun den ersten Vornamen ein und wählen den Befehl Blitzvorschau. Dies geht neben dem bereits oben genannten Icon auch mit der Tastenkombination STRG + E. Beide Möglichkeiten sind die manuelle Auswahl. Die Blitzvorschau kann auch automatisch voreingestellt werden (s.u.).
 
Blitzvorschau Vorname
 
Beiden Beispielen ist gemein, dass wir Vor- und Nachname jeweils mit einem Großbuchstaben geschrieben haben. Excel nimmt dies als Vorlage und schreibt alle folgenden Werte ebenso groß, unabhängig davon, ob die Grunddaten in Spalte A mit einem Klein- oder Großbuchstaben beginnen. Dies kann zu Korrekturen von fehlerhaften Schreibweisen genutzt werden.
 
Haben Sie in Ihren Excel-Optionen unter „Erweitert“ bei den „Bearbeitungsoptionen“ bereits „AutoVervollständigen für Zellwerte aktivieren“ ausgewählt und auch den Haken bei „Automatische Blitzvorschau“ gesetzt, erhalten Sie die Vorschau immer automatisch beim Eingeben eines zweiten Wertes:
 
Blitzvorschau Flash Fill
 
Um die Blitzvorschau zu aktivieren, füllen Sie dann die erste Zelle der auszufüllenden Spalte mit dem gewünschten Wert und erhalten während Sie den zweiten Wert schreiben Ihren Vorschlag für die weiteren Werte in der Vorschau.
 
Auf diese Weise können Sie die vorhandenen Daten auf vielfältige Weise für neue Darstellungen nutzen. Zum Beispiel die Reihenfolge von Vor- und Nachname drehen, oder nur die Initialen schreiben:
 
Blitzvorschau Vor- und Nachname
 

 
Blitzvorschau Initials
 

 
Blitzvorschau Initialien
 
Weitere Möglichkeiten über die Namen hinaus die Blitzvorschau zu nutzen, gibt es z.B. generell bei fehlerhafter Groß- und Kleinschreibung, wenn man alles in Kleinbuchstaben umwandeln möchte, beim Zerlegen oder Zusammenstellen von E-Mail-Adressen, bei der Strukturierung von Telefonnummer mit Klammern und/oder Leerstellen und und und …
 
Die Blitzvorschau kann sehr viel, alles kann sie jedoch auch nicht. In dem Beispiel mit den Namen erkennt es weitere, zusätzliche Vornamen z.B. nicht korrekt und hat teilweise auch Probleme mit der Kleinschreibung, wenn einzelne Ausgangswerte in Großbuchstaben vorliegen. Das Ergebnis der Blitzvorschau ist auch immer statisch. Dies bedeutet, dass es sich nicht aktualisiert, wenn sich die ursprünglichen Datensätze ändern.



 
Tipp 146: Doppelte Werte in Tabellen finden und farbig markieren

Oft möchte man in Excel-Listen bestimmte mehrfach vorkommende Daten finden und bearbeiten. Weis man welche das sind, könnte man danach filtern. Oft sind die Datenbestände aber auch zu groß oder unklar, sodass sich ein schneller Überblick über alle mehrfach vorkommenden Werte anbietet. Excel bietet eine ganz einfache Möglichkeit, doppelte Werte in einer Liste automatisch anzuzeigen und zu formatieren.
 
Mit Hilfe der Bedingten Formatierung können Sie alle doppelten Werte finden. In unserem Beispiel nehmen wir einen Ausschnitt von dem Forderungsbestand aus unserem Liquiditätsplanungs-Tool:
 
Excel-Tipp 146 Ausgangssituation
 
Markieren Sie zunächst Ihre Liste bzw. die Zellen, in denen Sie nach doppelten Werten suchen möchten. In unserm Beispiel wären das die Kunden in Spalte E. Tipp: Es ist nicht notwendig, die Zellen einzeln oder als Block von … bis … zu markieren. Sie können die ganze Spalte bzw. wenn gewünscht auch gleich mehrere Spalten über die Spaltenköpfe selektieren.
 
Excel-Tipp 146 Doppelte Werte
 
Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf das Symbol können Sie die Menüstruktur der Bedingten Formatierung öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt sowie die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und klicken weiter auf „Doppelte Werte...“.
 
Excel-Tipp 146 Auswahl Format
 
In dem sich öffnenden Fenster kann nun definiert werden, mit welcher Füllung die doppelten Werte/Zellen formatiert werden sollen. Wir können rechts statt der vordefinierten hellroten Füllung auch unterschiedliche weitere vordefinierte Farben wählen, oder mit „benutzerdefiniertem Format...“ zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen in den verschiedenen Registern diese Zellen andersartig formatieren. Mit der Bestätigung auf OK wird unsere neue Regel angelegt. Das Ergebnis sieht jetzt wie folgt aus:
 
Excel-Tipp 146 formatierte doppelte Werte
 
Die verwendete Beispieldatei in Excel gibt's hier zum Download.
 
Die Originaldatei Excel-Liquiditätsplanung 2.0 finden Sie in unseren Berichtsvorlagen.



 
Tipp 147: Nach allen doppelten Werten in Tabellen filtern

In der wie in Tipp 146 beschriebenen, anhand der bedingten Formatierung formatierten Liste können Sie sich nur die farblich gekennzeichneten, doppelten Werte anzeigen lassen, indem Sie Nach der Farbe der ausgewählten Zellen filtern.
 
Klicken Sie hierzu mit der rechten Maustaste auf eine der farbigen Zellen und wählen Sie im Kontextmenü der rechten Maustaste „Filter“ und „Nach der Farbe der ausgewählten Zelle filtern“.
 
Excel-Tipp 147 Nach Farbe filtern
 
Excel setzt nun in der Liste einen Filter und filtert auch gleich die in diesem Beispiel einzigen beiden farbig formatierten Zeilen. Somit können Sie sich nur die doppelten Werte in Ihren Listen anzeigen lassen.
 
Excel-Tipp 147 Nach Farbe filtern
 
Mit einem erneuten Rechtsklick auf eine der farbigen Zellen können Sie den Filter wieder aufheben (Kontextmenü Filter und Filter löschen ...), jedoch die in der Tabelle gesetzte Filterfunktion nicht gänzlich löschen.



 
Tipp 148: Vermögensgegenstände selektieren, die seit einem bestimmten Datum auf Lager liegen

Für die Bewertung von Beständen, Forderungen und ähnlichem ist es notwendig, den Datenbestand nach dem Alter des Vermögens zu separieren. So können rechtlich notwendige Abwertungen aufgrund von Überalterung schnell - auch unterjährlich - berechnet und verfolgt werden. Bei großen Datenbeständen können Sie die Datensätze in Excel mit nur wenigen Klicks zunächst markieren und dann durch Filtern separieren und summieren.
 
Um Daten hervorzuheben, die vor oder nach einem bestimmten Datum liegen, hilft die Bedingte Formatierung. Für unser Beispiel nehmen wir einen Ausschnitt von dem Lagerbestand aus unserem ABC-Analyse-Tool, wo wir in der Spalte E das Datum des letzten Wareneingangs interpretieren und Wareneingänge vor 2020 anzeigen wollen:
 
Excel-Tipp 148 Ausgangssituation
 
Markieren Sie zunächst die Zellen mit dem Datum, in denen Sie nach den veralteten Werten suchen möchten. In unserm Beispiel ist das die Spalte E.
Tipp: Um alle Werte der Spalte schnell zu markieren, selektieren Sie die oberste Zelle, halten dann die SHIFT- und die Strg-Taste gedrückt und springen mit der „Pfeil-Taste nach unten“ zu dem letzten Wert der Spalte.
 
Excel-Tipp 148 Kleiner als
 
Im Register „Start“ befindet sich in der Gruppe „Formatvorlagen“ die „Bedingte Formatierung“. Mit Klick auf das Symbol können Sie die Menüstruktur der Bedingten Formatierung öffnen, innerhalb der es bereits einige vordefinierte Möglichkeiten für bedingte Formatierungen gibt. Auch finden Sie hier die Möglichkeiten neue Regeln zu erstellen oder bestehende zu verwalten/zu ändern. Für unser Beispiel nutzen wir die Möglichkeit „Regeln zum Hervorheben von Zellen“ und klicken weiter auf „Kleiner als ...“.
 
Excel-Tipp 148 Auswahl Format
 
In dem sich öffnenden Fenster kann nun definiert werden, ab welchem Datum die älteren Werte eingefärbt und mit welcher Füllung diese Zellen formatiert werden sollen. Um zum Beispiel zum Jahresende 2022 die Bestände mit Wareneingang älter als 2 Jahren anzuzeigen, geben wir das Datum 01.01.2020 ein. Wir können rechts statt der vordefinierten hellroten Füllung auch unterschiedliche weitere vordefinierte Farben wählen, oder mit „benutzerdefiniertem Format...“ zusätzlich in dem sich dann öffnenden, üblichen Fenster für Zellformatierungen in den verschiedenen Registern diese Zellen andersartig formatieren. Mit der Bestätigung auf OK wird unsere neue Regel angelegt. Das Ergebnis sieht jetzt wie folgt aus:
 
Excel-Tipp 148 Ergebnis
 
Um sich jetzt nur die älteren, farblich gekennzeichneten Werte anzeigen zu lassen, können Sie, wie im obigen Tipp 147 beschrieben nach der Farbe der gekennzeichneten Zellen filtern und eine Summe bzw. ein Teilergebnis aus diesen Werten berechnen.
 
Die Originaldatei Excel-ABC-Analyse-Tool finden Sie in unseren Berichtsvorlagen.



 
Tipp 149: Mehrere Zahlenwerte aus einer Zelle addieren

Normalerweise liegen zu summierende Zahlenwerte in verschiedenen Zellen vor, die man mit z.B. einer einfachen Summenformel =Summe(Zahl1;Zahl2;…) in einer separaten Zelle aufaddieren kann. Kommen Daten aus Vorsystemen und werden diese beim Importieren nicht korrekt in einzelne Zellen aufgegliedert, sondern zusammen in einer einzelnen Zelle mit Zeilenumbruch dargestellt, hilft eine einfache Summenformel nicht weiter. Das kann z.B. vorkommen, wenn einzelne Auftragsnummern mit mehreren Rechnungsnummern in Excel übergeben werden und somit der eine Auftrag und sämtliche Rechnungswerte in einer Zeile angezeigt werden. Die Summenformel benötigt Zahlen und ignoriert die als Text dargestellten Umbrüche mit denen keine weiteren Berechnungen möglich sind. Umgehen kann man dies mit einer komplexeren Formel.
 
Nehmen wir hierzu ein einfaches Beispiel:
 
Excel-Tipp 149 Ausgangssituation
 
In den Zellen B2 und B3 sind jeweils mehrere Werte mit Zeilenumbruch dargestellt. Diese können aus Fremdprogrammen exportiert oder auch direkt mit Zeilenumbruch (Alt + Enter) erfasst worden sein (s. hierzu auch Tipp Nr. 3). In den Zellen C2 und C3 sollen die zwei bzw. drei Zahlenwerte aus B2 bzw. B3 addiert werden. Dies geht mit folgender Formel in C2 für die Werte aus B2 (C3 analog für B3):
 
=SUMMENPRODUKT(GLÄTTEN(TEIL(WECHSELN(B2;ZEICHEN(10);WIEDERHOLEN(" ";999));ZEILE(INDIREKT("1:"&LÄNGE(B2)-LÄNGE(WECHSELN(B2;ZEICHEN(10);))+1))*999-998;999))*1)
 
Die verschiedenen Funktionen basteln aus den zwei bzw. drei Werten in den Beispielzellen die jeweilige Summe zusammen:
 
Excel-Tipp 149 Mehrere Zahlenwerte aus einer Zelle addieren



 
Tipp 150: Alle Formeln in einer Tabelle finden und farbig hervorheben

In komplexen Tabellen mit vielen Formeln ist es oftmals hilfreich, sich Zellen mit Formeln zu visualisieren und somit einen besseren und schnelleren Zugriff auf Berechnungen zu haben. Dafür gibt es einerseits die Möglichkeit, in einer Tabelle zwischen der Ergebnisansicht und der Formelansicht zu wechseln oder anderseits auch mal kurzfristig alle Zellen in denen sich Formeln befinden farblich hervorzuheben.
 
Um zwischen der Ergebnis- und der Formelansicht zu wechseln, klicken Sie auf der Registerkarte „Formeln“ in der Gruppe „Formelüberwachung“ auf die Schaltfläche „Formeln anzeigen“.
 
Excel-Tipp 150 Formeln anzeigen
 
Anstatt der Formelergebnisse sehen Sie nun die hinterlegten Formeln und können diese wie gewohnt bearbeiten. Ein erneuter Klick auf die Schaltfläche „Formeln anzeigen“ lässt die Formeln wieder verschwinden und es werden wieder die Ergebnisse angezeigt.
Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Stundensatzberechnung.
 
Eine weitere Möglichkeit, alle Zellen mit Formeln hervorzuheben, besteht darin, nach Formeln zu suchen. Hierzu klicken Sie in der Registerkarte „Start“ innerhalb der Gruppe „Bearbeiten“ auf die Schaltfläche „Suchen und Auswählen“ und in dem Menü auf den Befehl „Formeln“.
 
Excel-Tipp 150 Formeln suchen
 
Excel markiert Ihnen jetzt alle Zellen, in denen sich Formeln befinden. In dem folgenden Beispiel sind dies die Spalten mit den berechneten Abweichungen:
 
Excel-Tipp 150 Formeln anzeigen
 
Möchten Sie diese lediglich markierten Zellen mit den Formeln für die weitere Bearbeitung farbig hervorheben, können Sie diese z.B. mit einer Füllfarbe oder einer anderen Formatierung versehen. Also z.B. in der Gruppe „Schriftart“ über die Schaltfläche „Füllfarbe“.
 
Excel-Tipp 150 Formeln formatieren
 
Sie sehen nun alle Formeln, können diese bearbeiten und die Formatierung danach wieder über die Suche aus allen Zellen entfernen. In dem obigen Beispiel also über die Auswahl „Keine Füllung“. Beachten Sie bei diesem Weg nur, dass Sie mit dem gewählten Format ggfs. bereits vorhandene Formate in den Formelzellen überschreiben bzw. entfernen.
Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Plan-Ist-Vergleich Visualisierung.



 
Tipp 151: Gesperrte und nicht gesperrte Zellen direkt anhand eines Symbols erkennen

Arbeitsmappen werden oft von mehreren Anwendern benutzt und es ist sodann ratsam, bestimmte Zellinhalte vor versehentlichem Überschreiben zu sperren und einen Blattschutz zu definieren. Doch es steckt viel Arbeit im Aufbau solcher geschützter Tabellen und auch bei späteren Anpassungen der Daten durch das jeweilige Sperren und Entsperren von einzelnen Zellen. Dabei fehlt oft die Übersicht, welche Zellen aktuell überhaupt gesperrt sind. In Excel hat jede Zelle standardmäßig das Attribut „Gesperrt“ und somit müssen spätere Eingabezellen vorab entsperrt werden, damit bei aktiviertem Blattschutz später eine Eingabe möglich ist. Der bekannteste Weg, um dieses Attribut zu verändern, führt über einen Rechtsklick in der gewünschten Zelle in das Kontextmenü „Zellen formatieren...“ und über die Registerkarte „Schutz“ zu dem Selektionsfeld, wo das Häkchen dann vor „Gesperrt“ entfernt werden kann.
 
Dies geht aber auch viel schneller mit nur einem Klick und gleichzeitig kann Ihnen Excel dazu laufend zu jeder selektierten Zelle den jeweiligen Status anzeigen.
 
Dieser Weg, den Sperrstatus einer Zelle zu verändern führt über das Startmenü und die dortige Gruppe „Zellen“. Ein Klick auf das Auswahlfeld „Format“ öffnet das folgende Menü mit den Informationen zum Schutz von Zellen:
 
Excel-Tipp 151 Zellen per Mausklick sperren und entsperren
Bild 1: Zellen per Mausklick sperren und entsperren
 
Ist eine Zelle gesperrt, ist das Schloßsymbol in dem Menüeintrag mit einem Rahmen umlegt, bei nicht gesperrten Zellen erscheint hier nur das Schloß. Somit kann man an dieser Stelle schon erkennen, wie der Sperrstatus der jeweils markierten Zellen ist und diese mit einem Klick sperren bzw. entsperren. Um dies jetzt aber auch noch laufend im Blick zu haben, empfiehlt es sich, diese Option in die Schnellzugriffsleiste zu übernehmen. Dies geht mit einem Rechtsklick auf den Eintrag „Zelle sperren“ und der Auswahl „Zu Symbolleiste für Schnellzugriff hinzufügen“:
 
Excel-Tipp 151 Zelle sperren zur Symbolleiste für Schnellzugriff hinzufügen
Bild 2: „Zelle sperren“ zur Symbolleiste für Schnellzugriff hinzufügen
 
Die selektierten Zellen können dann mit nur einem Klick auf das neue Symbol oben in der Symbolleiste entsperrt und auch wieder gesperrt werden. An dem Symbol selbst erkennen Sie auf einen Blick anhand der Hervorhebung den jeweiligen Sperrstatus der Zellen (in diesem Beispiel eine gesperrte Zelle, das Symbol ist hervorgehoben).
 
Excel-Tipp 151 Zelle entsperren und sperren mit einem Klick und Anzeige vom Sperrstatus
Bild 3: Zelle entsperren und sperren mit einem Klick und Anzeige vom Sperrstatus in der Symbolleiste links oben
 
Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Liquiditätsplanung.



 
Tipp 152: Rückwärts wandern, springen, suchen etc. u.a. mit der Umschalttaste

Sie haben in Excel eine Vielzahl von Möglichkeiten mit Tasten zu navigieren, viele bringen Sie vorwärts. Aber, manchmal will man ja auch rückwärts bzw. nach oben navigieren. Hier einige Tipps, damit Sie im Bedarfsfall auch mal eine andere Richtung einschlagen können:
 
Es ist bekannt, dass Sie nach der Eingabe von Werten mit der Entertaste den Cursor auf die darunterliegende Zelle (Grundeinstellung) bewegen. Drücken Sie für die Eingabe jedoch die Tabulatortaste, wandert der Cursor nach rechts. Dies können Sie öfter wiederholen, drücken Sie aber die Entertaste nach einer nächsten Eingabe, springt der Cursor nicht in die direkt darunter sich befindende Zelle, sondern unter die erste mit der Tab-Taste begonnene Eingabe zurück.
 
Zurück ist auch das Stichwort für die Shift-Taste (auch: Umschalttaste genannt). Diese kann in Kombination mit der Entertaste auch eine gute Hilfe sein: So können Sie z.B. nach einer Eingabe bei gedrückter Shift-Taste mit der Entertaste den Cursor auf die Zelle über der aktuellen Eingabezelle bewegen.
 
Aber auch ohne Eingaben in die Zellen können Sie mit der Tab-Taste vorwärts, also nach rechts wandern. Drücken Sie jedoch die Shift- und dann die Tab-Taste, wandern Sie zurück bzw. nach links.
 
Praktischer Anwendungsfall: Wenn Sie in einem Tabellenblatt mehrere vereinzelte Zellen oder Zellbereiche markiert haben, können Sie diese mit der Tab-Taste anspringen – vorwärts. Drücken Sie die Shift-Taste und dann die Tab-Taste, springen Sie rückwärts. Das kann auch sehr nützlich sein, wenn Sie aktuell alle Formelzellen markiert haben (s. hierzu auch Tipp 150) und diese anwählen wollen ohne die Markierung zu verlieren. Weiterhin ist es ebenso interessant, wenn Sie zwischen nicht geschützten Zellen (und) in Formularen rückwärts wandern möchten.
 
Mit der Shift-Taste können Sie auch im Suchfenster (Strg+F) rückwärts suchen. Die Suche oder das Ersetzen läuft normalerweise nur von oben nach unten und von links nach rechts. Aber: Halten Sie einfach die Shift-Taste gedrückt und klicken Sie auf „Weitersuchen“. Schon geht die wilde Fahrt rückwärts ...



 
Tipp 153: Mit Datenschnitten Tabellen filtern und auf das Gewünschte reduzieren

Um große Datenbestände nach bestimmten Kriterien zu analysieren bietet Excel die Funktion "Filter". Über die damit gesetzten Pfeile in den Spaltenüberschriften haben Sie viele Möglichkeiten zum Sortieren und Filtern der Datentabelle. Auch besteht die Möglichkeit mit Pivot-Tabellen zu arbeiten. Mit einem einfachen Datenschnitt können Sie sich aber auch ohne Filter und Pivot-Tabellenkenntnisse ganz einfach einzelne Datensätze bequem anzeigen lassen und schnell zwischen diesen wechseln. Auch werden die möglichen Datenwerte visuell besser dargestellt als mit der Filteroption. Datenschnitte erleichtern somit ungeübten die Arbeit mit großen Datenbeständen.
 
Excel-Tipp 153 Datenschnitt
Bild 1: Formatierte Tabelle mit Datenschnittfiltern
 
Voraussetzung ist jedoch zunächst, dass Sie Ihre Tabelle in eine Formatierte Tabelle, auch intelligente Tabelle genannt, umwandeln. Wie (leicht) das geht, haben wir bereits in Tipp 123: "Mit dynamischen Tabellen arbeiten - Dynamische Tabellen erstellen" beschrieben. Es sind nur 3 Klicks: Menü Start / Formatvorlagen / Als Tabelle formatieren.
 
Um jetzt einen Datenschnitt einzufügen, klicken Sie zunächst in eine Zelle Ihrer intelligenten Tabelle, um eine Verbindung zu den Daten herzustellen. Im Menü Einfügen finden Sie dann in der Gruppe Filter den Befehl Datenschnitt. In dem sich öffnenden Fenster können Sie die Kriterien (Spaltenüberschriften) auswählen, für die Sie Datenschnitte selektieren möchten. Für jedes hier gewählte Kriterium setzt Excel nach Bestätigung mit OK dann einen sogenannten Datenschnittfilter, welchen Sie bequem mit der Maus positionieren und in der Größe ändern können. Den Datenschnitt können Sie alternativ auch über das Menü Entwurf einfügen, welches bei Auswahl einer Formatierten Tabelle im Menüband erscheint. Hier lässt sich in der Gruppe Tools der Datenschnitt einfügen.
 
Excel-Tipp 153 Auswahl der Datenschnittfilter
Bild 2: Auswahl der Datenschnittfiltern
 
Excel-Tipp 153 Beispiel Datenschnittfilter
Bild 3: Beispiel Datenschnittfilter
 
Mit jedem Klick auf die angezeigten Werte (Schaltflächen) können Sie die Tabelle entsprechend nach diesen Werten filtern. Die zwei Optionsfelder neben der Überschrift des Datenschnittfilters erlauben eine Mehrfachauswahl sowie das Löschen der bereits gesetzten Filter.
 
Solange Sie den Datenschnittfilter selektiert haben, steht ihnen das Register Optionen zur Verfügung, in dem Sie weitere Eigenschaften, wie z.B. die Größe und die Farbe ändern können.
 
Die verwendete Beispieldatei in Excel gibt's hier zum Download.



 
Tipp 154: Zur letzten Zelle einer Tabelle per Mausklick springen

Bei der Arbeit mit sehr langen Tabellen ist die Navigation durch das Scrollen mit der Maus sehr zeitaufwändig. Schneller geht es per Tastenkombination oder auch der Hyperlink-Funktion. Wer aber gar nicht erst die Maus loslassen möchte, kann auch ganz einfach per Mausklick in die jeweils äußersten Zellen einer Tabelle springen.
 
Und das geht so: Wenn Sie an z.B. das untere Ende einer Tabelle springen möchten (die letzte beschriebene Zelle in einer Spalte), gehen Sie folgendermaßen vor. Markieren Sie eine beliebige Zelle in der Spalte. Es wird jetzt die Randmarkierung dieser Zelle sichtbar und Sie können mit dem Mauscursor über diese Markierung gehen. Hierbei verändert der Cursor sein Aussehen und es wird ein Pfeil-Kreuz angezeigt. Mit einem Doppelklick auf z.B. die untere Randmarkierung der gewählten Zelle springen Sie (mit der aktiven Zelle) in die unterste Zeile der Tabelle. Genaugenommen springen Sie bis zu der letzten Zelle in der Spalte, bevor eine leere Zelle auftritt. Per Doppelklick auf die anderen Ränder können Sie auch in andere Richtungen springen: Obere Randmarkierung führt nach oben, linke und rechte in die entsprechenden Richtungen der Tabelle.
 
Das Ganze funktioniert auch, wenn Sie bereits mehrere Zellen markiert haben und auf die Randmarkierung klicken. Es ist nicht notwendig, erst eine einzelne Zelle zu aktivieren. Die aktive Zelle springt im Fall einer Mehrfachmarkierung in die Zeile bzw. Spalte, über der Sie den Doppelklick platziert haben.



 
Tipp 155: Werte aus mehreren Tabellenblättern schnell und flexibel addieren

Es gibt eine Reihe von möglichen Anwendungen, bei denen in verschiedenen Tabellenblättern Daten gesammelt werden, welche dann in einem Summenblatt addiert darzustellen sind. So z.B. bei monatlichen Plan-Ist-Vergleichen, welche auch kumuliert dargestellt werden sollen. Oder bei der Stundenerfassung einzelner Mitarbeiter, deren Stunden dann in ihrer Gesamtheit berichtet werden müssen. Um dafür in einem Summenblatt mit einer Formel alle Werte aus den einzelnen Tabellenblättern addieren zu können liegt eine einfache Aufsummierung nahe:
 
=Jan!C4+Feb!C4+Mar!C4+Apr!C4+Mai!C4+ … +Dez!C4
 
bzw. im Fall der Mitarbeiter:
 
=HansMaier!C4+KarlMueller!C4+TobiasSchulz!C4+ArminSchmidt!C4+SteffanBecker!C4
 
Eine solche Formel enthält für den Plan-Ist-Vergleich schon mal 12 Tabellenbezeichnungen und in dem zweiten Beispiel mit vielen Mitarbeitern ggfs. noch viele weitere Tabs mehr. Um die Summierung einfacher und übersichtlicher zu berechnen, gibt es eine elegante Möglichkeit, die noch weitere Vorteile bei z.B. Änderungen der zu erfassenden Mitarbeiter oder deren Namen bietet.
 
Diese einfachere Lösung bietet die Summenformel. So können für das obige Beispiel die Werte in den Zellen der 12 Monatstabellenblätter mit folgender Formel addiert werden:
 
=SUMME(Jan:Dez!C4)
 
bzw. im Fall der Mitarbeiter:
 
=SUMME(HansMaier:SteffanBecker!C4)
 
Wichtig hierbei ist zu verstehen, dass Excel dabei die Tabellenblätter als einen Bereich sieht. Dieser wird von links nach rechts mit dem ersten Tabellenblatt (Jan/HansMaier) bis zu dem letzten Tabellenblatt (Dez/SteffanBecker) definiert. Die Summenformel addiert die Werte aus der bezeichneten Zelle (hier C4) aller Tabellenblätter in diesem Bereich.
 
Ein weiterer Vorteil neben der Übersichtlichkeit ist die Möglichkeit der flexibleren Veränderung der zu summierenden Tabellenblätter: Werden zwischen dem ersten und letzten in der Formel bezeichneten Tabellenblatt weitere Blätter eingefügt, gelöscht oder verschoben, werden deren Werte automatisch mit in die Summe einbezogen oder nicht mehr beachtet. So können z.B. Mitarbeiter ohne Veränderungen der Summenformeln hinzugefügt oder gelöscht werden. Deren Schreibweisen sind auch nicht relevant, da Excel ja nur auf die Namen des ersten bzw. letzten Blattes in dem Bereich fokussiert. Excel ändert beim Einfügen, Löschen oder Verschieben die Formel nicht, solange die Bezeichnungen der begrenzenden Tabellenblätter nicht geändert werden.



 
Tipp 156: Datenreihen ohne Formatierungen ausfüllen

Excel nimmt uns ja schon eine Menge Arbeit ab, z.B. wenn wir neue Tabellen erstellen und das Automatische Ausfüllen von Datenreihen in Anspruch nehmen. Auf diese Weise können wir u.a. Zahlen- und Datumsreihen schnell erzeugen. Ist die Tabelle allerdings schon formatiert und sollen nur die Werte der formatierten Datenreihen aktualisiert werden, ist es schon etwas komplizierter. Beim AutoAusfüllen füllt Excel standardmäßig ja nicht nur die Werte aus, sondern kopiert auch die Formate der Ausgangszelle gleich mit. Das stört oft, kann aber leicht umgangen werden.
 
In einem Beispiel sollen Datumswerte aktualisiert, hier konkret in der ersten Spalte der Jan 23 durch Jan 24 ersetzt und dann die folgenden Monats- und Jahreswerte aufsteigend angepasst werden. Dabei sollen die Formate der Ausgangszelle nicht mitgenommen und die zwischendurch vorhandenen Abgrenzungen zwischen den Jahren auch nicht überschrieben werden.
 
Excel-Tipp 156 Datenreihen ohne Formatierungen ausfüllen
Bild 1: Beispieldatei Mitarbeiter-Darlehen mit zu aktualisierender Datenreihe
 
Ändern Sie zunächst in der Ausgangszelle das Datum auf Jan 24 und markieren Sie diese Zelle anschließend wieder. Mit der Funktion AutoAusfüllen können Sie jetzt die gesamte Datenspalte aktualisieren, indem Sie mit dem Mauszeiger über die rechte, untere Ecke der Zelle gehen und mit der rechten Maustaste den Anfasser (jetzt ein schwarzes Kreuz) anklicken und bis zum Ende Ihrer Datenliste nach unten ziehen.
 
Excel-Tipp 156 Zellen Anfasser
Bild 2: Wert aktualisieren und Anfasser mit rechter Maustaste anklicken
 
Excel-Tipp 156 Datenreihe
Bild 3: Maustaste halten und bis zum Ende der Datenreihe ziehen ... In dem Beispiel wurde zur Veranschaulichung „nur“ bis zum Mrz24 gezogen.
 
Beim Loslassen der rechten Maustaste erscheint ein Kontextmenü und darin wählen Sie den Befehl „Ohne Formatierung ausfüllen“.
Kurzer Einschub: In der obigen Grafik sehen wir gut, dass Excel unter dem Kontext auch den vorgeschlagenen Wert für die letzte Zelle der Markierung, hier Mrz 25, anzeigt. Diese Ansicht der Vorschauwerte erflogt auch schon während des Ziehens über den jeweiligen Zellen.
Zurück zu der Datenreihe: Diese ist jetzt automatisch aufsteigend in den markierten Zellen ausgefüllt und die Formatierungen sind nicht überschrieben worden.
 
Excel-Tipp 156 Nur mit Werten ausgefüllte Datenreihe
Bild 3: Nur mit Werten - hier Datumswerte - ausgefüllte Datenreihe
 
Die obigen Grafiken zeigen Ausschnitte aus unserem Tool zur Berechnung eines Mitarbeiter-Darlehens. Sie finden dies in unserer Toolbox bei den Berichtsvorlagen.



 
Tipp 157: Wochenenden in einer Datumsreihe automatisch farblich hervorheben

Sie haben in Excel viele Daten als Kalender oder als Datumsreihe dargestellt und verlieren den Überblick, wann die freien Tage sind? Dann lassen Sie Excel doch z.B. die Wochenenden automatisch ganz einfach farblich hervorheben. Wir nutzen das z.B. in unserer Liquiditätsplanung oder in unseren Kalender-Tools, wo die Vorlagen auch in allen künftigen Monaten bzw. Jahren Gültigkeit haben sollen und somit die Wochenenden ständig in anderen Zellen dargestellt werden müssen – Excel passt das künftig automatisch an.
 
Excel-Tipp 157 Beipiel
Beispiel: Der 3. und 4. Tag im Februar sind als Samstag und Sonntag farblich abgesetzt
 
Alles was Excel braucht, ist die Information, welcher Wochentag in der jeweiligen Zelle betroffen ist und wie die Formatierung lauten soll. Das Mittel zum Zweck lautet Bedingte Formatierung. Da wir ja in jeder Zelle bereits das Datum vorliegen haben, kann Excel daraus einfach den Wochentag errechnen.
 
Das geht mit der Funktion: WOCHENTAG(Zahl;[Typ]).
 
Die Funktion WOCHENTAG wandelt eine fortlaufende Zahl (unser Datum) in einen Wochentag um. Der Tag wird standardmäßig als ganze Zahl ausgegeben, die einen Wert von 1 (Sonntag) bis 7 (Samstag) annehmen kann. Die Option Typ können wir für unser Beispiel zunächst vernachlässigen.
 
Wollen wir also zunächst alle Sonntage gestalten, lautet die Syntax für die Regelbeschreibung:
=WOCHENTAG(Zelle)=1
 
Um die Datumszeile entsprechend zu formatieren, markieren Sie zunächst alle Zellen mit den Daten, in unserm Beispiel 1. - 31. und gehen dann im Register Start auf Bedingte Formatierung und wählen Neue Regel …
 
Excel-Tipp 157 Bedingte Formatierung
Bild 2: Bedingte Formatierung wählen
 
Für die Neue Formatierungsregel wählen Sie den Regeltyp „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ aus und geben in das Feld für die Regelbeschreibung unsere Funktion ein: =WOCHENTAG(C4)=1
 
Excel-Tipp 157 Regel definieren
Bild 3: Regelbeschreibung
 
Jetzt können Sie noch ein Format festlegen: Button „Formatieren“ drücken und in dem sich dann öffnenden, bekannten Fenster „Zellen formatieren“ die gewünschte Farbe bzw. Hintergrund wählen.
 
Excel-Tipp 157 Formate wählen
Bild 4: Formate wählen
 
Nach der zweimaligen Bestätigung mit OK haben Sie das Ergebnis:
 
Excel-Tipp 157 Ergebnis
Bild 5: Sonntage sind rot formatiert
 
Um das Beispiel zu vervollständigen ist für die Formatierung der Samstage in Orange eine weitere Regel notwendig. Die Syntax lautet analog:
 
=WOCHENTAG(C4)=7
 
Sollen beide Tage gleich formatiert werden, kann man das auch mit einer einzigen Regel erreichen. Entweder man verbindet die oben genannten Funktionen mit ODER, oder man beschäftigt sich mit der oben auch schon erwähnten Option Typ. Mit Typ 16 haben Samstag und Sonntag die Werte 1 bzw. 2 und in der Formel reicht ein einfaches =<3
 
Die obige Grafik zeigt einen Ausschnitt aus unserem Tool zur Liquiditätsplanung, welches Sie kostenlos zum Üben downloaden können.



 
Tipp 158: Schaltjahre in Excel berechnen

Excel weiß genau, bei welchem Jahr es sich um ein Schaltjahr handelt. Schließlich hat Excel ein eigenes Datumssystem, basierend auf dem Gregorianischen Kalender. So bietet es auch bei allen Formeln den Monatsletzten betreffend für Februar 2024 einen 29. Tag an.
 
Leicht zu überprüfen, wenn Sie eine Zeitreihe der Monatsletzten erstellen bzw. fortführen. Hierfür einfach den 31.12.2023 und den 31.01.2024 in zwei Zellen untereinander schreiben, beide markieren und mit der Maus runterziehen. Excel füllt automatisch die nächste Zelle mit dem 29.02.2024 aus.
 
Aber wie kann man jetzt berechnen, welches Jahr ein Schaltjahr ist? Denn nicht jedes durch vier teilbare Jahr ist ein Schaltjahr, es gibt Ausnahmen. Wir stellen gleich drei Möglichkeiten vor, wie Sie das Schaltjahr berechnen können. Bestimmt gibt es noch viele weitere Ansätze und genau hier wird es spannend, denn solche Möglichkeiten zeigen unterschiedliche Wege auf, wie man in Excel generell Lösungen erarbeiten kann.
 
Wichtig zu verstehen: Schaltjahre gibt es, um die Abweichungen zwischen dem "normalen" Kalender mit 365 Tagen und dem Lauf der Erde um die Sonne zu korrigieren. Für Schaltjahre gibt es eine Regel mit einer Ausnahme und einer Ausnahme von der Ausnahme:
Regel: Jedes Jahr, dessen Jahreszahl ohne Rest durch 4 teilbar ist, ist ein Schaltjahr. Dies korrigiert die Abweichung noch nicht exakt, weswegen die folgende Ausnahme besteht:
Ausnahme: Jahre, die ein Jahrhundert abschließen (z. B. 2100 und 2200), sind keine Schaltjahre. Und weil es immer noch nicht ganz passt:
Ausnahme von der Ausnahme: Jahre, die durch 400 ganzzahlig teilbar sind (z.B. 2000), sind doch Schaltjahre.
 
Wie kann man nun in Excel mit einer Formel eine Jahreszahl überprüfen, ob ein Schaltjahr betroffen ist und wie werden die Regel und Ihre Ausnahmen dabei berücksichtig? Wir stellen drei Möglichkeiten vor. Nehmen wir hierzu an, die Jahreszahl, die es zu überprüfen gilt steht in Zelle A1 und lautet 2028:
 
=WENN(ODER(REST(A1;400)=0;UND(REST(A1;4)=0;REST(A1;100)<>0));"Schaltjahr";"Kein Schaltjahr")
 
Mit Hilfe der WENN-Funktion wird hier überprüft, wie der Wert in A1 durch 400 bzw. 4 und 100 teilbar ist und ob ein Rest dabei besteht. Daraufhin werden dann als Dann- und Sonst-Werte die Texte "Schaltjahr" oder "Kein Schaltjahr" ausgegeben.
 
=WENN(TAG(DATUM(A1;3;1)-1)<>29;"Kein Schaltjahr";"Schaltjahr")
 
Bei dieser Wenn-Funktion wird zunächst das Datum aus der Jahreszahl in A1 und dem Monat März (3) und dem ersten Tag (1) gebildet. Hiervon wird in der TAG-Funktion ein Tag abgezogen (-1) und somit der Tag vor dem 1. März des Jahres ermittelt. Ist dieser Wert für den Tag ungleich 29 ergibt sich aus dem Dann-Wert der Text "Kein Schaltjahr".
 
=WENN(ISTFEHLER(("29.02."&A1)*1);"Kein Schaltjahr";"Schaltjahr")
 
Bei dieser dritten Möglichkeit wird mit der ISTFEHLER-Funktion geprüft, ob es einen 29.02. zu der Jahresangabe in A1 gibt. Wichtig hierbei ist die Multiplikation der Textfolge "29.02."&A1" mit 1, um diese in eine Zahl (Datumswert) umzuwandeln. Nur so erkennt Excel ein Datum. Dann- und Sonst-Werte liefern analog zu obigem Beispiel die entsprechenden Texte.




 

Schauen Sie auch in unserer Excel-Makroliste nach arbeitsvereinfachenden Tipps und Möglichkeiten für Ihr tägliches Controlling mit Excel!


 

  zurück zur Übersicht der Excel-Tipps

nach oben