Excel: Tipps und Tricks zur Office-Anwendung

Die Tabellenkalkulation mit Excel ist zuweilen recht tückisch. So manche Formel brachte uns zur Verzweiflung, ein anderes Mal hatten wir Probleme mit Arbeitsmappen, der Formatierung von einzelnen Zellen oder größeren Tabellen-Bereichen.

Wisst ihr, wie man Zellen miteinander verbindet oder mit Wurzeln oder Brüchen rechnen kann? In dieser Dokumentation geben wir Tipps und Tricks weiter, die uns das Arbeiten mit Excel erleichtert haben. Hier gilt: Nicht alle Tipps sind für alle Excel-Versionen gültig. Getestet wurden die Tipps zuletzt mit den 2013er- und 2019er-Versionen von Excel.

Abstand zwischen Datumsfeldern

Sie möchten den Abstand zwischen zwei Datumsfeldern, etwa 27.11.2020 bis 30.07.2021, berechnen. Dazu soll in einem dritten Feld die Anzahl der Tage erscheinen. Wie muss man vorgehen, um Tage zwischen zwei Datumsfeldern zu berechnen?

Formate für die Eingabefelder und Ausgabe muss man nicht festlegen. Die Rechnung wird korrekt ausgeführt. Excel greift automatisch auf eine Plausibilitätskontrolle zurück. Selbst Schaltjahre werden berücksichtigt.

In das Ergebnisfeld tragen Sie diese Formel ein:
=Summe(Feld2-Feld1)

Soll der erste oder letzte Tag mitgerechnet werden, müssen Sie noch 1 dazu addieren:
=Summe(Feld2-Feld1)+1

Soll die Berechnung unabhängig von der Reihenfolge der Felder erfolgen, so gilt die Formel:
=Summe(ABS(Feld1-Feld2))
Hinweis: ABS sorgt dafür, dass negative Zahlen in positive Zahlen umgewandelt werden.

AutoFilter anwenden

Excel bietet Ihnen die Möglichkeit, mittels AutoFilter häufig eingegebene Worte oder Werte als Liste pro Zelle zur Verfügung zu stellen. Markieren Sie dazu eine oder mehrere Zellen und klicken Sie bei "Daten" auf "Filtern".

Die markierten Zellen erhalten nun jeweils einen kleinen Button, der die Liste enthält. Hierin finden Sie die zuletzt verwendeten Eingaben und einige Standards für die Arbeit in Excel.

Mit den vorher gefilterten Datensätzen können Sie sogar rechnen. Klicken Sie auf die Zelle, in der die Summe der gefilterten Datensätze erscheinen soll, und klicken Sie auf die Schaltfläche Summe. Excel erkennt, dass die Daten gefiltert sind, und bietet automatisch die Formel =TEILERGEBNIS(9;Zellbereich) anstelle der sonst üblichen Summenformel an.

Bedingtes Verknüpfen

Sie suchen unter Excel nach einer Möglichkeit, den Inhalt von zwei Zellen nur dann zu addieren, zu multiplizieren oder zu verknüpfen, wenn die Inhalte der Zellen identisch sind. Wie geht das?

Zunächst müssen Sie die richtigen Operatoren kennen. Für die Addition zweier Zahlen ist das +, für die Verknüpfung zweier Zeichenketten das & zuständig. Um die Zellen bedingt zu verknüpfen, eignet sich die Funktion WENN. Um zwei Zellen auf Gleichheit zu überprüfen, verwenden Sie den Operator = .

Die Formel in der Zielzelle könnte also lauten: =WENN(A1=B1;A1+B1;)

In diesem Fall addiert Excel die beiden Zahlen aus A1 und B1, wenn sie denselben Wert haben. Andernfalls geschieht nichts. Um wenigstens eine Rückmeldung für den Fall der Ungleichheit zu erhalten, können Sie die Anweisung um eine Textausgabe erweitern:

=WENN(A1=B1;A1+A2;"Inhalte ungleich")

Blätter verschieben oder kopieren

Blatt verschieben: Maustaste links: Blattname anklicken und nach links oder rechts verschieben.

Blatt kopieren: Maustaste rechts: Blattname anklicken - Kopieren.

Brüche in eine Tabelle eingeben

Excel weist manchen Eingaben automatisch ein bestimmtes Zahlenformat zu. So wandelt es z.B. die Eingaben 2.5, 2-5 und 2/5 automatisch in das Datum 2. Mai um. Möchte man aber mit 2/5 den Bruch "zwei geteilt durch fünf" darstellen, ist dieser vorauseilende Gehorsam von Excel nur störend. Sie können Excel jedoch mit einem einfach Trick dazu bewegen, den Bruch so auszugeben, wie Sie es wünschen, ohne umständlich das Zahlenformat verändern zu müssen.

Geben Sie einfach eine Null, gefolgt von einem Leerzeichen, und dahinter den eigentlichen Bruch ein, also z.B. 0 2/5. Excel wandelt diese Eingabe automatisch so um, als hätten Sie die Zelle mit "Start | Zahl | Bruch" formatiert. Die Darstellung als Bruch erscheint übrigens nur in der Zelle. In der Eingabezeile von Excel sehen Sie das Ergebnis des Bruchs.

Daten in vorgegebene Bereiche eintragen

Angenommen, Sie möchten in Excel in alle Zellen von A1 bis C4 Daten eintragen. Sie werden wahrscheinlich, in A1 beginnend, mit der [Return]-Taste zum jeweils darunterliegenden Feld springen, müssten aber spätestens beim Spaltenwechsel die Cursortasten bemühen, um zur Zelle B1 zu gelangen.

Doch es geht auch einfacher. Wenn Sie schon vorher wissen, in welche Zellen Sie Daten eintragen wollen, so markieren Sie einfach diese Zellen. Excel wechselt dann bei jedem Drücken der [Return]-Taste zur nächsten Zelle in der Markierung, ohne dass Sie einmal eine Cursortaste benutzen müssen.

Gaming Notebooks

Die Zellbereiche müssen nicht einmal zusammenhängen. Wenn Sie die Taste [Strg] gedrückt halten, können Sie auch verstreute Zellen in die Markierung mit aufnehmen. Die Reihenfolge, in der Sie die Zellen markieren, bestimmt auch die Reihenfolge, in der Excel die Zellen anspringt.

Fehler wegen Division durch Null

Problem: In einer Excel-Tabelle wird eine Formel verwendet, die eine Division durchführt (=E5/D12). Die Zellen, auf die sich die Formel bezieht, sind noch leer, da die Werte erst zu einem späteren Zeitpunkt eingetragen werden sollen.

Die Fehlermeldung #DIV/0! kann man mit folgender Formel unterdrücken:
=WENN(ISTFEHLER(E5/D12);"";E5/D12)

Bei dieser Formel spielt es keine Rolle, welche Berechnung man durchführt. Die Formel unterdrückt alle Fehlermeldungen, die eine Formel verursachen könnte.

Eine weitere Möglichkeit, diese Fehlermeldung zu unterdrücken, bietet folgende Formel:
=WENN(D12=0;"";E5/D12)

Sie prüft vorher, ob die Division erlaubt ist. Wenn nicht, gibt sie einfach einen Leerstring aus. Anstelle des Leerstrings kann man auch jede beliebige Meldung ausgeben lassen:
=WENN(D12=0;"Dein Fehler";E5/D12)

Fehlermeldungen beim Starten von Excel

Beim Start von Excel können folgende Fehlermeldungen auftreten:

– Objekt verknüpfen und einbetten nicht möglich...
– Beim Initialisieren der VBA-Bibliotheken ist ein Fehler aufgetreten...
– Die Anwendung in 0x300050ceb verweist auf Speicher tralala...
– Der Vorgang read konnte nicht durchgeführt werden...

Abhilfe: Excel aus der Kommandozeile (Start | Ausführen) mit folgendem Befehl starten:
excel.exe /regserver

Formate werden nicht korrekt übernommen (F2-Problem)

Format-Probleme (z.B. bei SVERWEIS "Wert wird nicht gefunden"). Erst wenn man in diese Zelle klickt und Enter drückt (oder mit F2 und Enter), funktioniert der SVERWEIS. Wie kann man das auf alle anderen Zellen übertragen, ohne jede Zelle einzeln anfassen zu müssen?

Spalte nach WORD kopieren, und von WORD wieder zurück nach Excel, dann sollte es klappen.

Formeln automatisch prüfen lassen

Wenn Sie in Excel Formeln eingeben, schleicht sich schon mal der eine oder andere Tippfehler ein. Je komplexer die Formeln sind, um so schwerer lässt sich die Fehlerquelle ausfindig machen. Die Fehlermeldung hilft dabei auch nicht.

Sie können für die Fehlersuche eine Eigenheit von Excel ausnutzen: Es wandelt alle Funktionsnamen und Zellbezüge bei korrekter Syntax immer in Großbuchstaben um. Wenn Sie also Ihre Formeln konsequent in Kleinbuchstaben schreiben, wandelt Excel alle Zeichen mit Ausnahme der Syntax-Fehlerquelle in Großbuchstaben um. Jetzt brauchen Sie nur noch nach Kleingeschriebenem zu suchen, und schon ist der Vertipper lokalisiert.

Große Tabellenbereiche komfortabel markieren

Wenn man einen größeren Tabellenausschnitt mit der Maus auswählen möchte, wird die Markierung mit hoher Geschwindigkeit erweitert, sobald der rechte oder untere Fensterrand erreicht ist. Das ganze lässt sich schwer kontrollieren, denn auch der Versuch, die Markierung zu verkleinern, läuft wieder mit hoher Geschwindigkeit ab. Welche Möglichkeit gibt es, große Tabellenbereiche zuverlässig zu markieren?

Für die Markierung von umfangreichen Bereichen gibt es drei empfehlenswerte Techniken:

Wenn man die Markierung im sichtbaren Tabellenausschnitt nur langsam spalten- oder zeilenweise erweitert, erhöht sich die Geschwindigkeit nur geringfügig, sobald man an den Fensterrand gelangt. So lässt sich sehr genau kontrollieren, welche Zellen ausgewählt werden.

PC Drucker, Scanner, Kopierer

Eine wenig bekannte Technik sieht folgendermaßen aus: Klicken Sie auf die Zelle in der linken oberen Ecke der gewünschten Markierung. Dann verwenden Sie die Bildlaufleisten, um in die rechte untere Ecke der gewünschten Markierung zu scrollen. Schließlich drücken Sie die Umschalt-Taste und halten sie gedrückt, während Sie in die rechte untere Ecke der Markierung klicken.

Sollten Sie den genauen Bezug der Zelle in der rechten unteren Ecke der Markierung kennen, geht es auch so: klicken Sie auf die Zelle in der linken oberen Ecke der Markierung. Dann drücken Sie F5, geben den Bezug der Zelle in der rechten unteren Ecke der gewünschten Markierung ein, drücken die Umschalt-Taste und halten sie gedrückt, während Sie auf "OK" klicken.

Kommentare dauerhaft anzeigen

Unter Excel können Sie Zellen mit einem Kommentar versehen. Dieser Kommentar erscheint, wenn Sie den Mauszeiger über die entsprechende Zelle bewegen. Sie können die Tabellenkalkulation aber auch dazu veranlassen, alle Kommentare jederzeit anzuzeigen.

Gehen Sie zu "Datei | Optionen | Erweitert | Anzeige": dort aktiviert man "Kommentare und Indikatoren". Dann mit einem Klick auf die Schaltfläche "OK" bestätigen.

Kopf- und Fußzeilen einrichten

Das Einrichten von allgemeinen Kopf- und Fußzeilen ist unter Excel etwas versteckt. Sie finden die entsprechenden Einstellungen unter "Seitenlayout | Seite einrichten".

Dort haben Sie im Register Kopfzeile/Fußzeile die Möglichkeit, unter mehreren vordefinierten Formaten zu wählen oder eine benutzerdefinierte Kopf- bzw. Fußzeile anzulegen. Mit dem Schalter "Seitenansicht" können Sie Ihre Einstellungen dann noch überprüfen.

Kosten auf nächste Seite übernehmen

Sie möchten Ihre gesamten Kosten in einer Excel-Datei verwalten. Die erste Tabelle nimmt die fortlaufenden Kosten auf. Die zweite und die weiteren Tabellen rechnen jeweils ein gesamtes Jahr ab, wobei die Gesamtkosten von der ersten Tabelle zu übernehmen sind. Mit welcher Formel kann man die Kosten aus Tabelle 1 mit der jeweiligen Jahrestabelle verknüpfen?

Seit Excel seine Arbeitsblätter in mehrere Tabellen unterteilt, existiert auch die Möglichkeit, dreidimensional auf die Zellen zuzugreifen. So berechnet =SUMME(Tabelle1:Tabelle2!A1:D6) die Summe über die dreidimensionale Matrix, die sich auf die Tabellen 1 und 2 sowie den Bereich A1 bis D6 bezieht.

Aber auch einfache Bezüge auf Zellen sind so möglich. Die Formel =Tabelle1!A6 überträgt den Wert der Zelle A6 in Tabelle 1 in eine beliebige andere Zelle; auch in die einer anderen Tabelle.

Scrollen des Bildschirms

Wenn man die Pfeiltasten betätigt, so springt der Cursor normalerweise von Zelle zu Zelle (links, rechts, hoch oder runter).

Es kann aber vorkommen, dass stattdessen der gesamte Bildschirm verschoben wird, wenn man die Pfeiltasten drückt; dabei wird die Zellenmarkierung nicht verschoben!

Abhilfe: die "Rollen-Taste" (oder Scroll-Lock) auf der Tastatur ist eingeschaltet: Diese Taste einmal drücken, dann gehts wieder (in Word ist das anders gelöst: mit AutoBildlauf).

Tabelle drehen (Transponieren)

Oft stellt man erst hinterher fest, dass man in einer Excel-Tabelle die Spalten besser als Zeilen und die Zeilen besser als Spalten dargestellt hätte.

Excel kann das automatisch umstellen: Tabelle markieren und in die Zwischenablage kopieren; in eine andere Zelle klicken, um den Startpunkt der Tabelle festzulegen; dann Bearbeiten - Inhalte einfügen wählen.

In der Dialogbox den Schalter "Transponieren" wählen und dann mit "OK" bestätigen.

VBA: Fehler bei Programmlauf mit Left(), Right() oder anderen VBA-Befehlen

Beim Debuggen erkennt VBA manchmal seinen eigenen Befehlscode nicht. Vermutlich ein Registrierungsproblem: in der Entwicklungsumgebung (ALT + F11) über Menü "Extras | Verweise" nachsehen, ob ein Verweis der aktuell installierten Excel-Anwendung zu VBA geschaltet ist.

Solche Probleme treten auch auf, wenn ein AddIn installiert ist, das VBA-Funktionen über benutzerdefinierte Funktionen belegt.

Abhilfe: folgenden Syntax verwenden: z.B. VBA.Left(), VBA.Right(), VBA.MsgBox, VBA.DoEvents, VBA.Form1, usw.

Wildcard * oder ? (Platzhalter) im Text suchen

Um in einer Tabelle nach dem Zeichen * zu suchen (oder dieses Zeichen durch ein anderes zu ersetzen), muss man eine Tilde voranstellen (also: ~*), damit es nicht als Wildcard interpretiert wird.

Dasselbe gilt auch für den Fragezeichen-Platzhalter ? (also: ~?).

Wurzel berechnen

Prüfungsaufgabe: Ein Durchmesser soll mit der dritten Wurzel aus dem Volumen einer Billardkugel berechnet werden.

Lösung: Die Funktion Wurzel verwendet Excel ausschließlich für die Berechnung der Quadratwurzel. Das ist aber nicht weiter schlimm, denn der mathematische Hintergrund einer Wurzelberechnung ist nicht kompliziert: Der Ausgangswert für die Wurzelberechnung wird mit dem Kehrwert der gewünschten Wurzel potenziert. Das ist für Kopfrechner eine harte Nuss, für Excel aber eine leichte Aufgabe.

Um also die dritte Wurzel aus einem Wert in der Zelle A1 zu berechnen, geben Sie folgende Formel ein:

=A1^(1/3)

Das Dachzeichen dient dabei als Operator für das Potenzieren. Auf diese Weise können Sie auch jede beliebige Wurzel ziehen. Sie brauchen bloß den Wert 3 gegen einen anderen auszutauschen. Mit dem Wert 2 erhalten Sie so beispielsweise die Quadratwurzel.

Wenn Sie die Wurzelberechnung innerhalb einer großen Formel verwenden, setzen Sie Klammern um den Ausdruck. Das beugt möglichen Fehlern vor.

Zeilenumbruch in Zelle erzeugen bzw. per VBA entfernen

Ein Zeilenumbruch besteht aus zwei Zeichen: Chr(13) und Chr(10). Diese Zeichen kann man innerhalb der Excelzelle mit der Tastenkombination ALT und ENTER manuell erzeugen, wenn die Zellen-Eigenschaft auf "Zeilenumbruch = ja" bestätigt wurde (Menü: Start | Ausrichtung | Zeilenumbruch).

In VBA lässt sich der Zeilenumbruch am besten mit vbCrLf einfügen. Beispiel:

ActiveCell.Value = "Heute " & vbCrLf & "ist " & vbCrLf & "ein " & vbCrLf & "guter " & vbCrLf & "Tag"

Ergebnis:

   Heute 
   ist 
   ein 
   guter 
   Tag 

Andere Möglichkeit per VBA mit Chr(13) und Chr(10):

ActiveCell.Value = "Heute " & Chr(13) & Chr(10) & "ist " & Chr(13) & Chr(10) & "Sonntag"

Wie man im kleinen Bild sieht, können nach jedem Zeilenumbruch beim Einfügen von Werten aus anderen Anwendungen hässliche Vierecke entstehen. Und beim Exportieren von Excel-Werten nach Access kann man auf Fehlermeldungen stoßen, denn Access hat den Import verweigert.

Will man nun diese Vierecke entfernen, stößt man auf ein Problem, besonders wenn man Hunderte von Zellen manuell zu korrigieren hätte.

Abhilfe mit der Replace-Funktion (für die aktive Zelle):

ActiveCell.Value = VBA.Replace(alterText, Chr(10), " ")

Oder mit "Suchen und Ersetzen" im gesamten Arbeitsblatt per VBA-Makro:

Cells.Replace What:="" & Chr(10) & "", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Folgenden Tipp fanden wir auf www.office-loesung.de, der aber versagt, wenn der Benutzer mit einem Notebook OHNE Zifferntastatur arbeitet:

– Nutze die "Ersetzen"-Funktion (im Menü "Bearbeiten") von Excel.
– Zuerst die Spalte oder die Zellen kennzeichnen, in denen sich die zu entfernenden Zeilenumbrüche befinden.
– Dann das "Ersetzen"-Fenster aufrufen Strg + H.
– Den Cursor in die "Suchen nach"-Zeile stellen und dann die ALT-Taste drücken und festhalten.
– Über die Zifferntastatur 0010 eingeben – danach die ALT-Taste loslassen.
– Im Eingabefeld ist nichts zu sehen ist, das ist korrekt.
– Das "Ersetzen durch"-Feld kann leer gelassen oder ein Leerzeichen eingegeben werden.
– Die Zeilenumbrüche werden dann durch das eingegebene Zeichen getrennt.
– Nach Klick auf den Button "Alle ersetzen" werden die Zeilenumbrüche in den gekennzeichneten Zellen entfernt.

Zelleninhalt unsichtbar machen

Markieren Sie die betreffenden Zellen. Dann wählen Sie mit der Maus die Menüs "Start | Zahl | Kategorie: Benutzerdefiniert" an.

In das Feld "Format" geben Sie ein: ;;; (3 mal Semikolon).

Zentrierte Überschrift über mehrere Zellen hinweg

Text der Überschrift eingeben, dann Bereich markieren (z.B. Zellen A1 bis F1).

Im Menü "Start | Ausrichtung" befindet sich das Symbol "Verbinden und Zentrieren".

© Excel: Tipps und Tricks zur Office-Anwendung wurde dokumentiert von Winfried Brumma (Pressenet), 2020.