CLS: Tipps & Tricks: Excel allgemein



Tipps & Tricks: Excel allgemein

Auf dieser Seite finden Sie 17 Tipps & Tricks für Excel allgemein. Soweit keine Einschränkungen dazu genannt werden, gelten diese Tipps & Tricks für alle Versionen von Excel.

Wenn in den folgenden Erläuterungen Texte wie MsgBox formatiert sind, handelt es sich um konkret einzugebende Inhalte wie VBA-Code oder die Eingabe von Werten. Menüs wie Datei Speichern sind wie hier zu sehen formatiert. Schaltflächen oder Registerkarten auf Dialogen werden wie Menüs behandelt.

Alle Tipps sind nach bestem Wissen geprüft, aber selbstverständlich ohne Gewähr. Sollten Sie doch einen Fehler darin entdecken, würden wir uns freuen, wenn Sie uns per E-Mail Bescheid sagen.

Alle Formeln anzeigen ID 336 Aktuell

Mit der Tastenkombination Strg+# lässt sich die Ansicht aller Zellen umschalten, so dass statt der Ergebnisse überall deren Formeln sichtbar werden. Die Spaltenbreiten vergrössert Excel dabei vorübergehend ebenfalls. Mit der gleichen Tastenkombination schalten Sie auch wieder zurück.

Sie haben eine lange Liste und möchten wissen, wie oft ein bestimmter Eintrag enthalten ist? Solange Sie die Liste sortieren dürfen (eine Zelle der Spalte markieren und das Symbol A-Z anklicken), ist das schnell gemacht: nach der Sortierung markieren Sie die nun aufeinanderfolgenden Einträge mit der Maus.

Während Sie die Maus noch gedrückt halten, finden Sie links oben (wo sonst die Zelladresse steht) eine Angabe wie 99Z * 1S. Das ist die Anzahl der markierten Zellen in Zeilen und Spalten, hier ist also der gesuchte Wert 99mal enthalten.

Bei sehr langen Listen ist das im Tipp "Anzahl bestimmter Werte ermitteln (1): Sortieren" empfohlene Auffinden der zu markierenden Einträge schon mühsam. Eventuell dürfen Sie die Daten ja auch gar nicht sortieren.

Alternativ dazu können Sie die Liste filtern, indem Sie mit Daten Filter AutoFilter den AutoFilter aktivieren. In der passenden Spalte wählen Sie aus der Filter-DropDown-Liste den gesuchten Eintrag. Bis zum nächsten Klick nennt Excel in der Statusleiste dann mit 99 von 1500 Datensätzen gefunden die Anzahl der nun noch übriggebliebenen Zeilen.

Haben Sie in einer Zelle beispielsweise das Wort "März" eingegeben und ziehen an dem kleinen Markierungsrechteck in der rechten unteren Ecke der Zelle, so ruft Excel eine Funktion namens AutoAusfüllen auf, die anhand dieses Schlüsselwortes die folgenden Monate einträgt.

Dies funktioniert ebenso für Wochentage, Quartale (Achtung, das Leerzeichen nach dem Punkt ist wichtig: "1. Quartal"!) oder Datumsangaben. Natürlich ist Excel der Kalender bekannt, so dass auf den 27. Februar der 28. Februar und dann je nach Schaltjahr der 1. März folgt.

Scheinbar funktioniert es jedoch nicht für Jahreszahlen. Das ist jedoch Absicht, denn "1996" ist eine beliebige Zahl und könnte ja auch ein einzugebender Wert sein. Um nun die Werte trotzdem weiterzuzählen, die Excel "freiwillig" nicht mit AutoAusfüllen behandelt, halten Sie beim Ziehen die Strg-Taste gedrückt. Der Mauszeiger wird zusätzlich durch ein kleines Kreuz gekennzeichnet. Jetzt ist es beispielsweise auch möglich, bei "1" anfangend weiterzuzählen.

Diese Methode schaltet das Excel-AutoAusfüllen-Verhalten einfach um, so dass Sie ebenso das AutoAusfüllen verhindern können, wo es versehentlich auftritt.

(In einer Spalte, deren AutoFilter aktiv ist, funktioniert es allerdings nicht, Sie müssen vorher den Filter ausschalten.)

Um das Datum nicht mit einer (immer wieder aktualisierten) Funktion wie =HEUTE(), sondern fest einzugeben, können Sie das Tastenkürzel Strg+. (Punkt) nutzen. Für die Uhrzeit heißt es entsprechend Strg+: (Doppelpunkt), was sich durch das Datums- bzw. Zeit-Trennzeichen gut merken lässt.

Wenn beide nacheinander in einer einzigen Zelle eingegeben werden sollen, müssen sie durch ein Leerzeichen getrennt werden.

Sowohl bei der Angabe eines Datenbereichs für Datenbanken als auch der SVerweis-Funktion müssen Sie oft einen zusammenhängenden Bereich von Zellen mit den gewünschten Daten markieren. Dafür gibt es eine, wenn auch etwas versteckte, Funktion bereits fertig vorbereitet. Sie finden diese im GeheZu-Dialog, den Sie entweder mit Bearbeiten Gehe zu... oder mit F5 aufrufen. Dort erscheint nach Klick auf die Inhalte...-Schaltfläche der InhalteAuswählen-Dialog. Hier markieren Sie Aer Bereich, damit alle Zellen um die aktuelle herum mit einem Inhalt als Bereich markiert werden.

Alternativ können Sie einen solchen Datenbereich auch mit Strg+Umschalt+* (Ctrl+Shift+*) markieren.

Bei großen Tabellen ist es mehr als lästig, wenn dauernd die Überschriften aus dem Bildbereich verschoben werden. Dann sollten Sie diese fixieren. Es gibt zwei Methoden, das durchzuführen:

  • Entweder klicken Sie in eine beliebige Zelle, dann wird die Fixierung später links und oberhalb der Markierung eingerichtet, oder
  • Sie schieben oberhalb des senkrechten Rollbalkens und rechts(!) des waagerechten Rollbalkens die schmalen Anfasser an die gewünschte Position (mit jetzt vier Rollbalken).
In beiden Fällen wählen Sie danach Fenster Fenster fixieren, um die Zellen oben/links zu fixieren. Ab jetzt bewegen die Rollbalken nur noch den rechten unteren Teil der Tabelle. Sie heben das auf durch Fenster Fixierung aufheben, eine noch vorhandene Teilung lässt sich
  • per Doppelklick auf die eben benutzten Anfasser oder
  • durch Fenster Teilung aufheben
wieder entfernen. Während die Fixierung aktiv ist, springt der Cursor mit Strg+Pos1 nicht mehr in die Zelle $A$1, sondern "nur" noch in die linke obere Ecke des nicht-fixierten Bereichs.

Wenn Sie ein eingebettetes Objekt (Grafik, Diagramm, Zeichenobjekt, etc.) auf einer Tabelle verschieben, laßt es sich problemlos frei in alle Richtungen bewegen. Auch die Größe ist beliebig veränderlich.

Um dabei stattdessen exakt das Tabellengitter zu treffen, müssen Sie die Alt-Taste gedrückt halten. Dann springt das Objekt immer genau an die Zellen-Ecken, entweder mit seiner Ecke oder seiner Seitenmitte. Sobald damit die linke obere Ecke eingerastet ist, nehmen Sie mit wiederum gedrückter Alt-Taste eine Größenanpassung an der rechten unteren Ecke vor, so dass die Grafik nun exakt im Tabellengitter eingerastet ist. Ab jetzt paßt sich die Größe dynamisch an der zugrundeliegenden Zelle an.

Manchmal ist es praktisch, einen Wert oder vor allem eine Formel in mehrere Zellen gleichzeitig einzutragen. Selbst wenn Sie diese jedoch vorher markieren, erfolgt die Eingabe immer nur in die aktive (weiße) Zelle innerhalb der (schwarzen, neuerdings hellblauen) Markierung.

Wenn Sie jedoch die Eingabe nicht mit der Return-(Eingabe-)Taste alleine bestätigen, sondern stattdessen mit Strg+Return, werden die Formeln in alle markierten Zellen gleichzeitig übernommen. Relative Bezüge in Formeln werden dabei automatisch korrigiert.

Im Tipp "Dreidimensionales Rechnen" finden Sie eine Methode, um gleich strukturierte Tabellen einfach summieren zu können. Aber auch für völlig unterschiedliche Daten, sogar aus mehreren Dateien, bietet Excel eine bequeme Lösung an: Konsolidieren. Zur Vereinfachung soll hier nur von mehreren Blättern in einer einzigen Datei ausgegangen werden. Diese allerdings sehen so aus:

  A B
1 Tische 5
2 Stühle 17
3 Lampen 3

In der nächsten Tabelle stehen zwar auch diese Daten, aber in anderer Position und mit anderer Reihenfolge:

  E F
6 Stühle 99
7 Lampen 12
8 Tische 8

In Tabelle3 gibt es zudem noch abweichende Bezeichnungen und neue Begriffe:

  B C
2 Tische 10
3 Lampe 1
4 Hocker 19
5 Stühle 3

Damit haben Sie alle Daten vorbereitet. In einer vierten Tabelle öffnen Sie mit Daten Konsolidieren den Dialog zur Zusammenfassung dieser Daten. Klicken Sie dort in die Eingabezeile von Verweis und markieren den Datenbereich in der Tabelle1 und übernehmen den Verweis mit Hinzufügen in die Liste Vorhandene Verweise. Verfahren Sie ebenso mit den übrigen Bereichen, so dass dann folgende Angaben im Listenfeld zu sehen sind:

Tabelle1!$A$1:$B$3
Tabelle2!$E$6:$F$8
Tabelle1!$B$2:$C$5

Klicken Sie nun noch Linker Spalte für Beschriftung aus an und bestätigen den Dialog mit OK. Sie finden anschließend an der aktuellen Zelle eine Liste aller vorkommenden Objekten mit ihren Gesammtsummen (weil diese Funktion im Dialog ausgewählt war):

  B C
6 Tische 23
7 Lampe 1
8 Hocker 19
9 Stühle 119
10 Lampen 15

Da sich "Lampe" und "Lampen" aus Excel-Sicht unterscheiden, können diese nicht automatisch gemeinsam addiert werden. Nach einer alphabetischen Sortierung sollten solche Probleme aber leicht zu finden sein. Das Ergebnis lässt sich sogar noch verbessern, wenn Sie im Dialog Verknüpfung mit Quelldaten ankreuzen. Das können Sie auch jetzt noch nachträglich machen, denn die Liste der zu konsolidierenden Daten bleibt gefüllt.

Auf den ersten Blick sieht das neue Ergebnis gleich aus, aber diese Daten sind nun gegliedert und zeigen nach dem Klick auf das +-Zeichen der zugeklappten Zeilen alle Einzelwerte inklusive deren Quelle. Der Dateiname ist hier zwar wenig informativ, weil ja alle aus dieser Mappe stammen, aber das jeweilige Blatt steckt in der Formel drin, die hinter den Detailzahlen steckt.

Auf einem Laptop wird es schnell eng in der Symbolleiste, so dass manche Symbole automatisch ausgeblendet oder von Ihnen vielleicht manuell entfernt wurden. Das ist nicht schlimm, wenn es die richtigen sind, denn einige davon sind sowieso schon doppelt belegt. Dazu müssen Sie beim Anklicken die Umschalt-Taste gedrückt halten.

  • Die Sortierung sortiert jeweils gegenläufig.
  • Die Öffnen- und Speichern-Symbole ersetzen sich gegenseitig.
  • Aus Drucken wird die Seitenansicht und umgekehrt.
  • Einzug verkleinern ist mit Einzug vergrößern austauschbar.
  • Die Dezimalstellen werden hinzugefügt statt gelöscht.
Leider ändert sich das Symbol erst nach dem Klick, so dass Sie eventuell noch ein wenig probieren müssen, um weitere Wechsel-Symbole zu finden.

Wenn Sie viele Tabellen oder solche mit langen Namen in Ihrer Excel-Datei haben, ist es ein wenig mühsam, diese in den Registerlaschen auszuwählen. Meistens ist gerade das gesuchte Register von dem waagerechten Rollbalken verdeckt und Sie müßten die Navigationsleiste unten links einsetzen, um es wieder in den sichtbaren Bereich zu holen.

Nutzen Sie die Navigations-Elemente statt dessen direkt zur Auswahl, indem Sie auf diese (die Buttons ganz links, nicht die Registerlaschen) einen Rechtsklick machen. Dann erscheint direkt ein PopUp-Menü mit den Namen aller Blätter zur Auswahl.

Oft erleichtert es die Eingabe wesentlich, wenn in einer Zelle eine Auswahl von zulässigen Werten direkt angeboten werden kann. Diese Möglichkeit versteckt sich in Excel ein bißchen im Menü Daten Gültigkeit.

Zuerst brauchen Sie dafür jedoch eine Liste der anzuzeigenden Werte in der Tabelle. Anschließend markieren Sie eine andere Zelle in dieser Tabelle, wählen Daten Gültigkeit und stellen für Zulassen Liste ein. Nach Klick in die Eingabezeile für Quelle markieren Sie die anzuzeigende Wertliste in der Tabelle und bestätigen den Dialog.

Jetzt finden Sie neben der Zeile einen Pfeil, anhand dessen sich ein DropDown-Listenfeld mit den gewünschten Werten öffnen. Im Tipp "Zugehörige Werte in anderer Liste finden" sehen Sie einen typischen Einsatz dieser Fähigkeit, damit direkt ein Ort aus der mit SVerweis benutzten Liste angeboten wird.

Sie werden jedoch anhand der dann erscheinenden Fehlermeldung feststellen, dass sich die Datenquelle für dieses Listenfeld angeblich immer in der gleichen Tabelle befinden muss. Das ist aber nicht richtig, denn Sie müssen statt einer konkreten Zelladresse lediglich Bereichsnamen einsetzen. Schließen Sie also den Dialog und benennen die Zellen in der Tabelle (nur einspaltig möglich!) beispielsweise als ListeOrte. Im Dialog geben Sie diesen Namen für Quelle an, wobei Sie die F3-Taste dazu nutzen können.

Diese Liste funktioniert auch dann, wenn sich die Zellen des Bereichsnamens auf einem anderen Tabellenblatt befinden.

Um ganze Zeilen (für Spalten gilt es immer entsprechend) zu kopieren, müssen diese zuerst auf ihrem Zeilenkopf, also der grau hinterlegten Zeilenzahl ganz links, markiert werden. Dann können Sie den Markierungsrand (nicht das kleine schwarze Viereck!) mit der Maus anpacken und gedrückt verschieben, dabei sehen Sie einen Vierfachpfeil als Mauszeiger. So allerdings wird diese Zeile an ihrem Ziel eine andere Zeile überschreiben.

Um diese zwischen zwei Zielzeilen einzufügen, müssen Sie beim Loslassen der Maus noch die Umschalt-Taste gedrückt halten. Sie sehen dabei, dass sich der Umriß von einem Rechteck zu einem schmalen Strich verändert, der nur noch zwischen zwei Zeilen das zukünftige Ziel angibt.

Wie in Windows-Programmen üblich, können Sie beide Aktionen mit zusätzlich gedrückter Strg-Taste durchführen, damit die Zeile bzw. Spalte dabei kopiert wird. Alle Zellbezüge in Formeln werden dabei immer sinnvoll angepasst.

Der im Tipp "Einen Datenbereich markieren" bereits erwähnte InhalteAuswählen-Dialog kann noch viel mehr, nämlich bestimmte Fehler in Daten finden. Wenn Sie eine Tabelle haben, in der korrekt ganz viele Zellen mit Zahlen und einem Zahlenformat wie "0,00 kg" enthalten sind, einige aber fälschlich mit Textdaten wie "1,23 kg" (also der kg-Einheit als Teil des Inhalts und nicht der Formatierung!) ausgefüllt wurden, dann fehlen diese Texte in allen Summen. Excel zeigt auch keinen Fehler an, weil die Summe-Funktion Texte einfach mit dem Wert 0 versieht.

Für diese Analyse können Sie den GeheZu-Dialog ebenfalls nutzen, den Sie entweder mit Bearbeiten Gehe zu... aufrufen oder mit F5 anzeigen. Auch hier geht es mit der Inhalte...-Schaltfläche zum InhalteAuswählen-Dialog. Wenn Sie nun Konstanten (im Gegensatz zu Formeln) anklicken, gilt hier etwas überraschend die Liste der Kontrollkästchen, obwohl das nicht besonders intuitiv gestaltet ist! Markieren Sie von diesen nur Text, dann finden Sie innerhalb des Bereichs alle diese problematischen Textdaten markiert.

Gerade nach dem Import von Daten sind diese oft in einer einzigen Zelle zusammengefaßt, weil sie vorher beispielsweise in einem Textformat nur durch Leerzeichen und nicht Tabulatorzeichen getrennt waren. Excel ist aber sehr gut in der Lage, Daten einer Zelle wieder aufzuteilen.

Angenommen, Sie haben eine ganze Spalte voller Namen, also Vorname Leerzeichen Nachname. Damit Sie diese nach den Nachnamen sortieren können, müssen sie in einer getrennten Spalte stehen. Wichtig ist vor dem folgenden Schritt, dass die Spalte rechts daneben (besser noch mehrere Spalten!) leer ist, denn deren Werte werden gnadenlos überschrieben. Markieren Sie die Daten und wählen Daten Text in Spalten. Im ersten Schritt des Assistenten stellen Sie Getrennt als ursprünglichen Datentyp ein und im zweiten Schritt das Leerzeichen. Dann sehen Sie in der Vorschau des Dialogs schon die zukünftige Trennung, die Sie mit Fertig stellen bestätigen.

Zur Auftrennung von eMail-Adressen zwischen Namen und Domain geben Sie im zweiten Schritt das Assistenten hinter Andere das @-Zeichen (sog. "Klammeraffe", mit Alt Gr+Q einzugeben) ein.

Um die Tabellen-Ansicht zu vergrößern oder zu verkleinern, gibt es in der Symbolleiste eine Ausklappliste mit Zoom-Faktoren. Das ist jedoch viel zu mühsam, um mal eben eine Zahl zu vergrößern oder für den Gesamtüberblick die Tabelle zu verkleinern. Drehen Sie bei gedrückter Strg-Taste am Maus-Scrollrad, so ändert sich ebenfalls der Zoomfaktor, aber viel schneller in der Bedienung als über die Symbolleiste.