Excel-Tipps von Daniel Unrein
Die Excel-Tipps 51 bis 100 ff. finden Sie hier.
2. Die Schnellzugriffsleiste nutzen
Wichtige und häufig verwendete Befehle (oder auch Makros) sind am besten in den Schnellzugriff zu hinterlegen, um sie in Reichweite der Maus zu haben und sie rasch ansteuern zu können.
Hierzu ist im Menüband die rechte Maustaste zu klicken und PASSEN SIE DIE SYMBOLLEISTE FÜR DEN SCHNELLZUGRIFF AN und unter BEFEHLE AUSWÄHLEN „Nicht im Menüband enthaltene Befehle“ zu wählen.
3. Mit dem Pinsel Formate übertragen
Eine sehr nützliche und schnelle Möglichkeit zum Kopieren von Zellformatierungen (neben KOPIEREN → INHALTE EINFÜGEN → FORMAT) ist FORMAT ÜBERTRAGEN (das „Pinsel-Icon“) in der Symbolleiste START. Hierzu ist zuerst die Ausgangszelle anzuklicken, dann FORMAT ÜBERTRAGEN/der Pinsel und anschließend die Zelle/den Zellbereich auf den das Format übertragen werden soll.
Wenn ein Format auf verschiedene Zellen übertragen werden soll, dann kann mit Doppelklick auf FORMAT ÜBERTRAGEN das Format solange auf verschiedene Zellen übertragen werden, bis wieder auf das Pinsel-Symbol geklickt wird. Dies ist ein nützlicher kleiner „Trick“, der Ihnen in der Praxis viel Zeit beim – doch eher lästigen – Formatieren ersparen kann.
4. „AutoVervollständigung“ bei Funktionseingabe
Beim Arbeiten mit Formeln ist die AUTOVERVOLLSTÄNDIGUNG ein nützliches Werkzeug. Dieses wird bei der Eingabe einer Funktionsbezeichnung in eine Zelle aktiviert und kann von Ihnen benutzt werden, um Formeln schneller einzugeben. Nach Eingabe des ersten Zeichens einer Funktionsbezeichnung werden die infrage kommenden Funktionen automatisch angezeigt und können dann mit der Tab-Taste (siehe Abb.) schnell ausgewählt werden. Dies ist deutlich schneller, als die Formel komplett zu tippen. Zudem werden (sobald eine öffnende Klammer oder innerhalb des Klammerausdrucks ein Semikolon eingegeben wird) alle geeigneten Optionen für die Argumente der Funktion aufgelistet.
Gerade für Anfänger im Formelbereich kann dies eine gute Hilfe – insbesondere bei der Eingabe komplexerer Formeln – darstellen.
Abb.: Tab-Taste
5. Tastenkürzel F4
Durch (mehrmaliges) Drücken von F4 in der Bearbeitungsleiste können bei Eingabe einer Formel absolute in gemischte und relative Bezüge umgewandelt werden.
Im Arbeitsblatt wiederholt F4 hingegen den letzten Befehl oder die letzte Aktion, sofern möglich.
6. Optimale Spaltenbreite mit Doppelklick einstellen
Ist ein Text länger als die Spalte, dann wird der Text scheinbar in die benachbarte Zelle geschrieben. Sofern die benachbarte Zelle einen Eintrag enthält, wird der Text hingegen abgeschnitten.
Um die optimale Spaltenbreite für die aktuell benötigte Textgröße schnell einstellen zu können, empfiehlt es sich, zwischen die beiden Spalten mit einem Doppelklick zu klicken. Die Maus muss zuvor wie ein Kreuz (mit Pfeilen links und rechts) aussehen.
7. Formeln kopieren/AUTOAUSFÜLLEN
Die Funktion zum AUTOAUSFÜLLEN ist eines der effizientesten Werkzeuge bei der Eingabe von Werten und Daten. Durch Ziehen am Ausfüllkästchen rechts unten in der Zellmarkierung können Werte wahlweise fortgeschrieben oder aber kopiert werden. Das bringt Zeitersparnis und ist sicherlich die bekannteste sowie eine der geeignetsten Methoden, Formeln rasch in angrenzende Zellen zu übertragen. Zur Nutzung von AUTOAUSFÜLLEN gelten dabei die folgenden Regeln:
- Einfaches Ziehen mit linker Maustaste am Datenkreuz: Zahlen werden auf diese Weise kopiert; Datumswerte oder zeitliche Abfolgen, wie 01.01.2015, Mai oder Donnerstag, werden hingegen als Datenreihe fortgeschrieben. Dasselbe gilt für Text-Zahlen-Kombinationen wie „Quartal 1“ (nach dem 4. Quartal 2014 folgt das 1. Quartal 2015 und nicht das 5. Quartal 2014). Begriffe von benutzerdefinierten Listen werden dabei ebenfalls fortgeschrieben.
- Mit linker Maustaste und gedrückter STRG: Diese Kombination dient sozusagen als Umkehrung von Füllen und Kopieren. Wird beispielsweise an einer Zelle mit dem Wert 5 gezogen, erstellt Excel eine fortlaufende Nummerierung und keine Kopie des Werts. Hingegen wird ein Datumswert dann kopiert und nicht wie beim normalen Ziehen fortgeschrieben (vgl. Arbeitsblatt „AUTOAUSFÜLLEN“).
- Mit rechter Maustaste ziehen: Beim Loslassen der Maus öffnet sich das Kontextmenü, welches in Abhängigkeit des Datentyps verschiedene Ausfüll-Optionen anbietet. Bei Zahlen können hiermit die beiden obigen Varianten abgebildet, aber auch lineare oder geometrische Reihen erstellt und somit eine lineare bzw. exponentielle Trendentwicklung dargestellt werden. Bei Datumswerten bestehen weitere hilfreiche Optionen, wie etwa Tage oder auch nur Wochentage ausfüllen (vgl. Arbeitsblatt „AUTOAUSFÜLLEN“).
8. Formeln kopieren/AUTOAUSFÜLLEN
Befinden sich rechts oder links von der zu kopierenden Zelle bereits Daten in der Spalte, können Sie durch Doppelklick auf das AUSFÜLLKÄSTCHEN Formeln sehr schnell kopieren. Excel füllt dann in einem Arbeitsschritt den gesamten Zellbereich bis zur letzten Zeile der Tabelle. Dabei können auch mehrere nebeneinander liegende Zellen markiert und per Doppelklick nach unten kopiert werden. In diesem Fall kehrt die STRG-Taste die beiden Optionen des Kopierens bzw. Ausfüllens – im Gegensatz zum Ziehen – nicht um (siehe Tipp 7). Datumswerte werden also immer fortlaufend gefüllt, und Werte werden stets kopiert.
9. Nach Farbe(n) filtern/sortieren
In den neueren Excel-Versionen können Sie auch nach Farbe(n) filtern bzw. sortieren. Diese Funktionalität ist gerade auch in Kombination mit der bedingten Formatierung besonders nützlich.
- Der Menüpunkt DATEN → FILTERN → NACH FARBE FILTERN wird dabei nur aktiviert, wenn in der zu filternden Spalte unterschiedliche Farbformatierungen vorhanden sind. Im Menü auf NACH FARBE FILTERN stehen die verwendeten Farben zur Verfügung und können ausgewählt werden.
- Ebenfalls können Sie auch nach Farben sortieren – und zwar ist unter DATEN → SORTIEREN → SORTIEREN NACH „Zellfarbe“ zu wählen (alternativ über „Daten/Filtern/Nach Farbe sortieren“); werden mehrere Farben verwendet, kann an dieser Stelle die Reihenfolge für die Sortierung der einzelnen Farben festgelegt werden.
10. Formeln debuggen/analysieren
Verschachtelte Formeln (gerade Array- und Matrix-Formeln) können sehr komplex sein. Es gibt daher eine Möglichkeit, das Ergebnis des Teils einer Formel anzuzeigen. Dies nennt man auch Debuggen. Hierfür wird das Tastenkürzel F9 verwendet.
Vorgehensweise: Eine Formel (innerhalb einer verschachtelten Formel) markieren und F9 drücken. Das Ergebnis der Formel wird dann in der Bearbeitungsleiste angezeigt.
11. Benutzerdefinierte Listen
Benötigen Sie eine bestimmte Abfolge von Begriffen (z.B. wie im Folgenden Produkte oder auch Gesellschafts-, Standortnamen etc.) nicht nur einmal, sondern immer wieder, können Sie eine sog. BENUTZERDEFINIERTE LISTE erstellen. Am besten sind dazu alle Begriffe, die in der Liste enthalten sein sollen, in die Zellen eines Tabellenblatts zu schreiben. Unter DATEI → OPTIONEN → ERWEITERT → ALLGEMEIN → BENUTZERDEFINIERTE LISTEN BEARBEITEN → NEUE LISTE sind die im Tabellenblatt geschriebenen Begriffe zu markieren; dann ist auf IMPORTIEREN und abschließend auf OK zu klicken.
Nun kann immer wieder auf diese Liste zurückgegriffen werden, ohne diese jedes Mal neu eingeben zu müssen. Hierbei können Sie benutzerdefinierte Listen auf zwei unterschiedliche Arten in Excel verwenden:
- zum Füllen von Zellbereichen,
- zum benutzerdefinierten Sortieren von Tabellen.
Um einen Zellbereich mit den Werten der Liste zu füllen, schreiben Sie einen Begriff aus der Liste in eine Zelle des Tabellenblatts und ziehen den Inhalt dann per AUTOAUSFÜLLEN nach unten oder nach rechts. Zum automatischen Ausfüllen: Platzierung des Mauszeigers in das kleine Quadrat in der rechten unteren Ecke der Zellenmarkierung; es erscheint ein kleineres Kreuz, dann linke Maustaste klicken und „ziehen“.
Um eine Liste nicht in der Standardsortierreihenfolge, sondern entsprechend der individuellen Prioritätensetzung zu sortieren, ist nach Klick auf die rechte Maustaste auf SORTIEREN UND FILTERN (oder nur SORTIEREN) → BENUTZERDEFINIERTES SORTIEREN zu klicken (vgl. auch den nächst7en Tipp zum Sortieren von bereits voreingestellten Listen/von Monaten).
12. Listen nach Monaten von Januar bis Dezember sortieren
Wollen Sie eine Liste nach Monatsnamen sortieren, wird diese korrekt alphabetisch angezeigt – genau das ist im Regelfall aber selbstverständlich nicht erwünscht. Mit einem einfachen Trick können Sie die Monate nach ihrer zeitlichen Abfolge von Januar bis Dezember ordnen.
Hierzu sind die Monate zu markieren und unter DATEN → SORTIEREN → REIHENFOLGE ist „Benutzerdefinierte Listen“ zu wählen. Hier finden Sie dann die Monatsnamen bzw. auch deren Abkürzungen. Nach Bestätigung mit OK liegen die Monate in der gewünschten Reihenfolge vor.
13. Tastenkürzel: F2
Das Tastenkürzel „F2“ bearbeitet die aktive Zelle und positioniert die Einfügemarke am Ende des Zelleninhalts (egal ob Text oder Formel). Dadurch lässt sich der Zellinhalt schnell bearbeiten.
14. Mit der Statusleiste arbeiten
Wenn Daten in einem Arbeitsblatt markiert sind, können Sie relevante Berechnungen wie Summe, Minimum, Maximum, Mittelwerte oder Anzahl der Zahlenargumente auch direkt in der Statusleiste ablesen. Die Statusleiste ist ein sehr praktisches Hilfsmittel gerade für eine schnelle Basis-Analyse von Daten.
Ist die gewünschte Berechnung nicht enthalten, müssen Sie die Statusleiste mit der rechten Maustaste anklicken und im Kontextmenü den entsprechenden Eintrag wählen.
15. Tastenkürzel STRG+a
Bei Eingabe einer Formel, d.h. nach Erkennung der Formel durch Excel, kann mit STRG+a der Funktionsassistent aufgerufen werden.
Bei Drücken von STRG+a im Arbeitsblatt wird hingegen die gesamte Tabelle markiert (und kann dann beispielsweise formatiert werden).
16. Zellenformatvorlagen
Das Formatieren von Zellen geht am einfachsten mittels der bestehenden ZELLENFORMATVORLAGEN im Menü START → FORMATVORLAGEN.
Neben der Übernahme vorgefertigter Vorlagen besteht auch die Möglichkeit, diese selbst zu definieren. Insbesondere in Kalkulationsmodellen kann es beispielsweise zweckmäßig sein, Input- von Berechnungszellen optisch unterscheidbar zu machen. Die Verwendung von speziellen individuellen Formatvorlagen bietet dabei verschiedene Vorteile, wie Zeitersparnis bei der Modell-Entwicklung, unternehmensweit einheitliches Aussehen etc. Zudem ist es nutzerfreundlich, da beispielsweise die Eingabezellen schnell(er) identifizierbar sind.
17. Formeln kopieren mit der Tastatur
Befindet sich in einer Zeile eine Formel in der äußert linken Ecke, so kann diese mit STRG+R in alle rechts anliegenden Zellen kopiert werden.
Alternativ kann mit STRG+U die erste Zelle einer Markierung nach unten in der Spalte kopiert werden.
STRG+ kopiert eine Formel aus einer Zelle oberhalb der Markierung ohne jegliche Anpassung, verdoppelt diese also quasi.
18. Inhalte von Zellen unsichtbar machen
Der Inhalt einer Zelle kann selbstverständlich unsichtbar gemacht werden. Entweder indem Sie die Zellfarbe auf Weiß einstellen – gegebenenfalls auch abhängig von einem Wert mit der bedingten Formatierung – oder durch Einsetzen der benutzerdefinierten Formatierung. Mit „;;;;“ werden alle Inhalte einer Zelle ausgeblendet (siehe Abbildung).
19. Duplikate entfernen/markieren
Duplikate in Listen stellen meist entweder einen Fehler dar, oder die mehrfach vorkommenden Werte sollen aggregiert/summiert werden. Will man aus mehrfach anzutreffenden Werten eine Liste mit Unikaten (nur jeweils einmal vorhandene Werte) schaffen, auf die sich beispielsweise Formeln beziehen können – und kann oder will nicht Pivot einzusetzen –, dann lässt sich hierzu auch auf die Funktion DATEN → DUPLIKATE ENTFERNEN zurückgreifen. Die doppelt bzw. mehrfach vorkommenden Werte werden dann einfach von Excel entfernt; es bleiben nur Unikate/eindeutige Werte übrig.
Mithilfe der bedingten Formatierungen können diese markiert werden – und zwar unter START → BEDINGTE-FORMATIERUNG → REGELN ZUM HERVORHEBEN VON ZELLEN → DOPPELTE WERTE.
20. Zellen mit bedingter Formatierung finden/markieren
In der normalen Excel-Ansicht können Sie bedingt formatierte Zellen nicht einfach von „normalen“ Zellen unterscheiden. Sollten Sie nicht mehr wissen, wo bedingte Formatierungen eingesetzt wurden, lassen sich diese wie folgt auffinden: START → SUCHEN UND AUSWÄHLEN → BEDINGTE FORMATIERUNG. In der Folge werden alle bedingt formatierten Zellen (hier die doppelten Werte, siehe auch den vorherigen Tipp) markiert.
21. Hyperlink mit Tastenkürzel einfügen
Mit STRG+k rufen Sie das Dialogfeld LINK EINFÜGEN zum Einfügen eines neuen Hyperlinks auf. Ist ein vorhandener Hyperlink markiert, wird hingegen das Dialogfeld LINK BEARBEITEN angezeigt.
Hyperlinks bieten hilfreiche Navigations- und Interaktionsmöglichkeiten; beispielsweise kann durch Einsatz von Hyperlinks eine Benutzerführung in Excel-Reports ermöglicht werden.
22. Die Blitzvorschau (ab Excel 2013)
Ein kleines Highlight in Sachen neuer Funktionalität in Excel 2013 ist sicherlich die Blitzvorschau – eine Erweiterung des automatischen Ausfüllens. Die Anwendung sucht bei Dateneingabe nach möglichen Erfassungsmustern. Sobald die Blitzvorschau erkannt hat, welches Muster vorliegt, gibt es die restlichen Daten sofort komplett ein bzw. schlägt die Vervollständigung nach dem erkannten Muster vor.
Wollen Sie beispielsweise zu einer Liste von Namen noch rechts davon die sich aus den Anfangsbuchstaben von Vor- und Nachnamen zusammensetzenden Kürzel ausgeben, kann die Blitzvorschau (unter DATEN → DATENTOOLS → BLITZVORSCHAU oder STRG+E) eingesetzt werden. Es ist in die Zelle rechts vom ersten Namen (Patrick Müller) zu gehen und „PM“ einzutippen. Sobald Sie in der nächsten Zelle das „D“ (für Daniel) eingeben, erkennt Excel das Muster und ergänzt das „U“ (für Unrein) – sowie auch für alle anderen Kürzel automatisch. Passt das von der Blitzvorschau vorgeschlagene Muster, können Sie die vorgeschlagenen Werte durch Drücken von ENTER übernehmen.
Extra-Tipp: Die Blitzvorschau funktioniert auch mit einer Vielzahl an weiteren Mustern (bspw. Nummern aus Texten herauslesen). Wird ein Muster nicht direkt automatisch erkannt, können Sie mit der manuellen Blitzvorschau arbeiten. Dann sind – je nach Datenmaterial – ein paar (mehr) Vorgaben zu machen und der zu vervollständigende Bereich ist samt der Vorgaben zu markieren. Unter DATEN ist dann BLITZVORSCHAU anzuklicken.
.png)
23. Zahlen und Text mit ANZAHL/ANZAHL2 zählen
Die Funktion ANZAHL ermittelt, wie viele Zahlen eine bestimmte Liste von Argumenten enthält, also wie viele Zellen in einem Bereich Zahlen enthalten.
Im Unterschied zu ANZAHL berücksichtigt ANZAHL2 auch alle Text-, Wahrheitswerte und Fehlerwerte, ermittelt also wie viele Zellen in einem Bereich nicht leer sind.
Es gibt demnach keine Standard-Funktion, um auch die Anzahl der Textzellen zu zählen. Dies ergibt sich aber aus Kombination der beiden oben genannten Formeln, indem Sie ANZAHL2-ANZAHL rechnen. Demnach wird durch ANZAHL2(A1:100)-ANZAHL(A1:100) die Anzahl aller Textzellen in diesem Bereich berechnet.
24. Den letzten Wert einer Spalte ermitteln
Mit der Funktion INDEX können Sie in Verbindung mit ANZAHL2 den letzten Wert bzw. Text, der in einer Zeile oder Spalte steht, ermitteln. Voraussetzung ist, dass keine Leerzellen dazwischen vorkommen. Die Formel für die Ermittlung der letzten Zelle mit Inhalt aus Spalte A lautet: =INDEX(A:A;ANZAHL2(A:A))
25. Leere Zellen in einer Spalte/in einem Bereich zählen
Um die leeren Zellen (bspw. als Indikator für noch fehlende Einträge in einer Liste) in einem Bereich zu zählen, steht Ihnen die Funktion ANZAHLLEEREZELLEN zur Verfügung. Dabei werden neben echten leeren Zellen auch Zellen mitgezählt, in denen leere Zeichenfolgen nach einem Gleichheitszeichen (Formeln) und bspw. bei WENN-Funktionen stehen. Zellen, die Nullwerte enthalten, werden hingegen nicht mitgezählt.
Um den Bereich D1:D20 nach leeren Zellen abzusuchen und die noch offenen, auszufüllenden Punkte zu ermitteln, können Sie die folgende Formel eingeben:
=ANZAHLLEEREZELLEN(D1:D20)
26. Leere Zellen optisch hervorheben
Um nicht ausgefüllte Zellen/Felder in einer Liste optisch hervorzuheben, kann die bedingte Formatierung eingesetzt werden. Unter START → BEDINGTE FORMATIERUNG → NEUE REGEL → NUR ZELLEN FORMATIEREN, DIE ENTHALTEN ist im Drop-Down-Menü „Nur Zellen formatieren mit“ LEERZEICHEN zu wählen.
Alternativ kann zudem auch eine eigene Regel/Bedingung über die Formel ISTLEER definiert werden. Dann ist einzutragen unter START → BEDINGTE FORMATIERUNG → NEUE REGEL-FORMEL ZUR ERMITTLUNG DER ZU FORMATIERENDEN ZELLEN VERWENDEN:
=ISTLEER(A1:G217).
27. Berechnung der CAGR (Compound Annual Growth Rate) in Excel
Als Wachstumsrate bezeichnet man die relative Zunahme einer Größe in einem Zeitraum. Eine spezielle und für Fragestellungen im Finanzbereich häufig genutzte Wachstumsrate ist die jährliche Wachstumsrate (engl. Compound Annual Growth Rate, abgekürzt CAGR). Sie stellt das durchschnittliche jährliche Wachstum einer zu betrachtenden Größe dar. Zur Berechnung in Excel wird der aktuelle Wert (hier im ersten Fall 25) durch den Ausgangswert (hier im ersten Fall 21) geteilt. Von dem Ergebnis wird die n-te Wurzel gezogen, wobei n die Anzahl der Jahre (hier 3 = 2014 ./. 2011) ist, die betrachtet werden. Die CAGR kann dabei nicht nur über eine/diese manuelle Formel [(25/21)^(1/3)-1] berechnet werden, sondern auch über die Funktion ZINS.
Diese Funktion berechnet den Zinssatz einer konstanten Zahlung je Periode und kann sehr gut genutzt werden, um den Zinssatz von Guthabenverzinsungen oder Annuitätendarlehen zu berechnen. Diese Logik kann für die Berechnung der CAGR etwas angepasst werden: Die Wachstumsrate des Umsatzes von 2011 bis 2014 (im Beispiel, siehe Abb.) entspricht letztlich dem Zins für ein Darlehen in Höhe des Startwerts (BW, muss negativ eingegeben werden), für das während der Laufzeit (Zzr; hier: 3 Jahre) keine Zinszahlungen (RMZ) getätigt werden und das am Ende mit dem Endwert (ZW) zurückbezahlt wird.
28. Die Schnellanalyse (ab Excel 2013)
Auch wenn viele Analysen im Controlling meist gerade nicht schnell oder „mal eben so einfach“ gehen, gibt es auch Fälle, in denen eine erste Basisanalyse schon ausreichend sein kann, um sich einen Eindruck zu verschaffen.
Excel 2013 bietet eine Funktion, die es erlaubt, Daten per SCHNELLANALYSE auswerten zu können. Alle hierin enthaltenen Funktionalitäten sind selbstverständlich auch im normalen Menü verfügbar, aber über die SCHNELLANALYSE schnell und komfortabel durch ein direkt bei den Daten erscheinendes Menü ansteuerbar. Sobald Sie Daten mit der Maus markiert haben, erscheint am unteren rechten Rand das Icon – und bei Klick darauf – das Menü zur SCHNELLANALYSE. Hierin können Sie in wenigen Schritten Ergebnisse über Formeln wie Summen oder Mittelwerte berechnen, intelligente Tabellen und Pivots erstellen oder auch Visualisierungen in Form von Diagrammen, der bedingten Formatierung und Sparklines vornehmen.
29. Runden
Mit der Formel RUNDEN kann sowohl auf Nachkommastellen als auch auf ganze Zahlen gerundet werden. Hierbei gilt die einfache kaufmännische Regel: Werte über fünf werden aufgerundet, Werte unter fünf abgerundet. Bei 0 Nachkommastellen wird auf ganze Zahlen gerundet. Sollen Werte definitiv immer auf- oder abgerundet werden, bedient man sich zwei anderer Excel-Funktionen:
Die Formel ABRUNDEN rundet eine Zahl auf eine vorgegebene Anzahl von Stellen ab.
AUFRUNDEN funktioniert ähnlich wie ABRUNDEN, nur eben für das Aufrunden von Zahlen.
Anbei ein paar Beispiele:
30. Fenster fixieren
Bei größeren Tabellenblättern empfiehlt es sich, immer unter ANSICHT → FENSTER FIXIEREN die oberste Zeile/die Überschriften-Zeile zu fixieren, damit diese beim Scrollen nach unten noch sichtbar bleibt und so eine bessere Zuordnung der Daten ermöglicht.
31. Datenüberprüfung beim Dateninput einsetzen – nur Zahlen > 0 erlauben
In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG → DATENÜBERPRÜFUNG.
Beispielweise dürfen für eine Verkaufspreisliste keine Werte kleiner gleich Null vorkommen. Um dies einzustellen, muss im Datenüberprüfungs-Menü unter ZULASSEN die Kategorie „Dezimal“ gewählt werden, da die Werte Dezimalwerte enthalten (können), die bei der Wahl der Kategorie „Ganze Zahl“ fälschlicherweise als Fehler ausgegeben werden würden. Anschließend müssen die Gültigkeitsparameter eingegeben werden – für das hier gewählte Beispiel stellen alle Werte größer 0 gültige Daten dar (vgl. Abb.).
32. Datenüberprüfung beim Dateninput einsetzen II – Listen einsetzen
In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG → DATENÜBERPRÜFUNG. Es kann beispielsweise zweckmäßig sein, den Kollegen eine LISTE mit auszuwählenden Inhalten zur Verfügung zu stellen („Zulassen: Liste“). Dann können nur aus diesen ausgewählten Vorgaben-Werten/-Begriffen gewählt – und keine falschen/sonstigen Eingaben getätigt – werden.
Extra-Tipp: Zudem kann eine solche Liste als ein komfortables und interaktives Auswahlfeld fungieren und so (beispielsweise indem sich Formeln wie der SVERWEIS auf die Auswahlzelle beziehen) für Dynamik/Flexibilität in Excel-Reports sorgen.
33. Datenüberprüfung beim Dateninput einsetzen III – Nur Zahlen/Text zulassen
In Reports oder Tabellen-Blättern, in denen Nutzer/Kollegen Eingaben machen müssen, empfiehlt es sich, mögliche Fehler durch ungewünschte Nutzereingaben beim Dateninput möglichst zu vermeiden/proaktiv abzufangen. Hierzu eignet sich die Excel-Funktionalität DATEN → DATENÜBERPRÜFUNG→ DATENÜBERPRÜFUNG.
Mit benutzerdefinierten Gültigkeitsregeln („Zulassen: Benutzerdefiniert“) lassen sich im Vergleich zu den Vorgaben für Zahlen-, Datums-, Textlängen- oder Listeneingaben (siehe die vorherigen Tipps) weitere interessante Vorgaben gestalten. Hierbei ist eine Formel einzugeben, die den Wahrheitswert WAHR oder FALSCH ergeben muss. Die Eingabe im Prüfungsbereich wird nur akzeptiert, wenn die Formel WAHR ist. Es kann beispielsweise vorab festgelegt werden, ob in einer Spalte nur Zahlen – oder alternativ nur Text – eingetragen werden sollen.
Sollen beispielsweise (wie im Fall bei den Standardkosten) in Ihrer Liste nur Zahlen eingetragen werden können, ist die jeweilige Zelle zu selektieren und dann unter DATENÜBERPRÜFUNG → ZULASSEN die Option „Benutzerdefiniert“ zu aktivieren und die Formel aus der Abb. unten einzugeben. Soll hingegen nur Text eingegeben werden können, wäre hingegen =ISTTEXT einzugeben.
34. Datenüberprüfung beim Dateninput einsetzen IV – Duplikate vermeiden
Wie in den vorherigen Tipps erläutert, können vorab über die DATENÜBERPRÜFUNG definierte Gültigkeitsregeln dabei helfen, dass bestimmte Fehler erst gar nicht entstehen, sondern direkt bei der Eingabe abgefangen werden. Mit benutzerdefinierten Gültigkeitsregeln lassen sich im Vergleich zu den Vorgaben für Zahlen-, Datums-, Textlängen- oder Listeneingaben weitere interessante Vorgaben gestalten.
So kommt es z.B. bei der Pflege von (Stammdaten-)Listen in Excel zu dem häufig auftretenden Problem, das doppelte Werte (wie Produktcodes) vorhanden sind, obwohl diese eigentlich (ein-)eindeutig sein sollten. Eine sinnvolle Einschränkung, die sich bei einer in Excel geführten Stammdatenliste – insbesondere bei Elementen wie dem Produkt- bzw. sonstigen eindeutigen Identifikations-Codes oder -Schlüsseln – anbietet, ist daher also die Vermeidung von doppelten Einträgen (sog. Duplikate). Auch dies lässt sich mit benutzerdefinierten Gültigkeitsregeln (analog zur Vorgehensweise mit ISTZAHL, siehe den vorherigen Tipp) mit der Funktion ZÄHLENWENN umsetzen: DATEN → DATENÜBERPRÜFUNG→ DATENÜBERPRÜFUNG → ZULASSEN, die Option „Benutzerdefiniert“ aktivieren und die Formel aus der Abb. unten eingeben.
35. Datenüberprüfung beim Dateninput einsetzen V – Sprechende Fehlermeldung einsetzen
Beim Einsatz von Datenüberprüfungen – und der damit beschränkten Möglichkeit der Dateneingabe (siehe auch die vorherigen Tipps) – ist es im Regelfall immer empfehlenswert den Kollegen zu erläutern, was sie denn eigentlich genau tun bzw. eingeben sollen. Mit jeder Regel können daher Eingabe- und Fehlermeldungen formuliert werden.
Die Eingabemeldung erscheint, sobald Sie mit der Maus auf eine mit einer Gültigkeitsregel versehene Zelle gehen; die Fehlermeldung, wenn falsche – d.h. vom Gestalter der Tabelle/Ihnen nicht gewünschte – Werte in eine Tabelle eingeben werden.
Die Standard-Fehlermeldung trägt dabei nicht sonderlich zum Verständnis bei. Diese Meldungen können aber auch im Menü (siehe Abb.) unter EINGABEMELDUNG bzw. FEHLERMELDUNG frei eingegeben werden (hier exemplarisch für Excel-Tipp 30 – nur Werte größer Null sind erlaubt). Unter FEHLERMELDUNG ist unter TYP auch die weitere Vorgehensweise festzulegen: Dabei wird mit „Stopp“ die Eingabe geblockt, und der Vorgang muss abgebrochen werden, während bei „Warnung“ und „Information“ die Eingabe – trotz der erscheinenden Warnhinweise – getätigt werden kann.
36. Inhalte gruppieren (und nicht ausblenden)
Auszublendende Inhalte (Spalten oder Zeilen) sind immer nur über GRUPPIEREN (DATEN → GRUPPIEREN) auszublenden, niemals tatsächlich über AUSBLENDEN.
Gruppiert werden kann zudem auch mit der Tastatur (sog. Shortcuts/Tastenkürzel): ALT + SHIFT + → (Pfeiltaste nach rechts)
Ungruppiert mit ALT + SHIFT + ← (Pfeiltaste nach links)
37. Einfach nach Texten filtern
Filtern gehört zu den Routinetätigkeiten in Excel. Seit Excel 2010 ist die Funktionalität dabei um eine hilfreiche und sehr flexible Suchfunktion erweitert worden. Bei einer Kunden-Liste (oder wie hier im Beispiel Produkt-Liste), die durchaus sehr viele Daten enthalten kann, entfällt nun das mühsame Scrollen an das Listenende, um etwa einen bestimmten Zulieferer- oder Produktnamen zu finden. Stattdessen können Sie nun die zu filternden Textteile direkt eingeben.
38. Schnelle Zeilennummerierung
Um schnell eine fortlaufende Zeilennummerierung zu erstellen, kann mit ZEILE gearbeitet werden. Einfach =Zeile() in die erste Zeile schreiben und die Formel mit AUTOAUSFÜLLEN (siehe Excel-Tipp Nr. 7.) herunterziehen. Eine Spaltennummerierung können Sie hingegen mit der Funktion SPALTE, also =Spalte(), erstellen.
39. Ganze Spalte/Zeile markieren mit Tastenkürzeln/Shortcuts
Zeilen oder Spalten können Sie mit der Maus markieren, indem Sie auf die Zeilennummer oder auf den Spaltenbuchstaben mit der linken Maustaste klicken. Schneller wird es oft über die Tastatur mit den folgenden Tastenkürzeln/Shortcuts gehen:
40. In Arbeitsmappen zwischen Blättern navigieren
Einzelne Arbeitsblätter in einer Datei können Sie nicht nur mit der Maus, sondern auch schnell mit einer Tastenkombination ansteuern. Drücken Sie hierzu auch STRG+Bild↓, wenn Sie in der Arbeitsmappe vorwärtsschreiten wollen, und STRG+Bild↑, wenn Sie zurücklaufen möchten.
41. Intelligente Tabellen einsetzen I – automatische Erweiterung
Ab Excel 2007 können Sie die sog. intelligenten Tabellen (unter: START → ALS TABELLE FORMATIEREN oder über Shortcut STRG+t) einsetzen. Im erscheinenden Menü ist der zu markierende Bereich zu prüfen und festzulegen, ob die Tabelle Überschriften enthält, und abschließend mit OK zu bestätigen.
Nach der Umwandlung sind das automatische Setzen der Filter sowie das neue Layout (kann selbstverständlich unter TABELLENFORMATVORLAGEN geändert werden) die äußerlich markantesten Änderungen. Zudem erscheint das Menü TABELLENTOOLS, in dem alle relevanten Einstellungen für die intelligente Tabelle getätigt werden können. Ähnlich der Formatvorlagen aus früheren Versionen können Tabellen durch die zur Verfügung gestellten Layout-Optionen schnell und einfach formatiert werden – eine durchaus nützliche Funktionalität! Dies ist aber noch lange nicht alles (siehe die nächsten Tipps).
42. Intelligente Tabellen einsetzen II – automatische Erweiterung
Im letzten Tipp (Nr. 41) wurden die Formatierungsmöglichkeiten von intelligenten Tabellen vorgestellt. Dabei ist der Menü-Name ALS TABELLE FORMATIEREN – vorsichtig formuliert – etwas unglücklich, ja fast schon irreführend gewählt: Denn Wirkung und Anwendungsmöglichkeiten der intelligenten Tabellen gehen weit über die Formatierungsunterstützung hinaus. Deutlich wichtiger sind die zusätzlichen/eigentlichen Funktionalitäten, die einhergehende Objektorientierung, die die Tabellen wirklich intelligent machen: Die Tabelle wird durch Eingabe eines neuen Zellenwerts (in der Spalte rechts oder der Zeile darunter) automatisch erweitert. Damit werden Bezugsfehler (beim Einfügen neuer Werte) unwahrscheinlicher und auch der sonst notwendige Anpassungsaufwand bei der Erweiterung von Bezügen entfällt.
Dies kann das Arbeiten mit Formeln, Diagrammen und Pivot-Tabellen erheblich erleichtern. Auch die Formatierung der Tabelle kann durch Ziehen an der rechten unteren Tabellenecke automatisch erweitert werden. Tabellen erhalten zudem automatisch einen Namen, der selbstverständlich geändert werden kann – dieser (und nicht mehr etwaige Zellbezüge) dienen dann als Referenz (z.B. für Pivot-Tabellen, siehe auch die Abbildung unten).
43. Intelligente Tabellen einsetzen III – strukturierte Verweise
Neben dem Formatierungs- und Erweiterungseffekt der intelligenten Tabellen (siehe die zwei vorherigen Tipps) ergeben sich auch Änderungen/Vorteile beim Arbeiten mit Formeln:
Durch Verwendung von intelligenten Tabellen (unter: START → ALS TABELLE FORMATIEREN oder über Shortcut STRG+t) entstehen beim Arbeiten mit Formeln sog. strukturierte Verweise (anstatt einfacher Zellbezüge) und somit sprechende Namen (beispielsweise Preis*Menge anstelle A2*B2), die Excel-Reports und Formel-Konstrukte verständlicher – und damit auch besser nachvollziehbar – machen können (siehe Abb.).
Weitere Vorteile der intelligenten Tabellen sind:
- Bei Eingabe einer Formel in die erste Zelle einer Spalte wird die restliche Spalte automatisch berechnet. DAS AUTOAUSFÜLLEN/„Herunterziehen“ entfällt somit.
- Einfach Teilergebnisse auswerten durch Hinzufügen einer Ergebniszeile.
Weitere Vorteile ergeben sich zudem auch in Verbindung mit Diagrammen (siehe nächster Tipp).
44. Intelligente Tabellen einsetzen IV – Diagramme
Die bereits vorgestellten intelligenten Tabellen (siehe die vorherigen Tipps) bieten auch nützliche Anwendungsmöglichkeiten zur Dynamisierung bzw. interaktiven Nutzung von Diagrammen:
- Einfache Erstellung von dynamischen, z.B. monatlich „mitwachsenden“ Diagrammen aufgrund der automatischen Erweiterung des Diagramms bei Tabellenerweiterung.
- Werden Daten in der Tabelle gefiltert, spiegeln Diagramme, die auf dieser Tabelle basieren, automatisch auch die gefilterten Daten wider. Eine „normale Filterung“ in der intelligenten Tabelle schlägt sich auf das Diagramm durch: Es können also </> oder auch Top-X-Analysen (siehe Tipp Nr. 1) für die Zahlenbasis durchgeführt und mit dem Diagramm visualisiert werden (siehe Abb.).
Ab Excel 2013 können zudem auch Datenschnitte auf intelligente Tabellen angewendet werden (siehe nächster Tipp).
45. Intelligente Tabellen einsetzen V: Datenschnitte als Mittel zur interaktiven Analyse in Reports/Dashboards (ab Excel 2013)
Datenschnitte bieten die Möglichkeit zur dynamischen und interaktiven Analyse von Daten durch visuelle Filter. Dabei werden alle Elemente und aktiven Filterkriterien im Live-Modus angezeigt. Datenschnitte sind daher ein überaus nützliches Tool, gerade um schnell und flexibel verschiedene Analysen durchführen zu können! Sie eignen sich somit auch hervorragend zum Einsatz in Verbindung mit intelligenten Tabellen/Diagrammen (ab Excel 2013) sowie mit aus Pivot-Tabellen und -Charts erstellten Analyse-Reports/Dashboards (ab Excel 2010). Sie können den Empfängern eines Reports eine einfache interaktive Steuerung bzw. im Rahmen eines Meetings (z.B. zur Ergebnisbesprechung) dem Controlling auch das spontane Beantworten spezifischer Fragen ermöglichen.
Um Datenschnitte in Verbindung mit normalen Diagrammen in Excel 2013 einzusetzen, ist im Menü TABELLENTOOLS (erscheint bei Klicken in die intelligente Tabelle) DATENSCHNITT EINFÜGEN zu wählen. Hier kann dann aus den vorhandenen Spaltenüberschriften gewählt werden. Im Beispiel ist „Standorte“ anzuklicken und mit OK zu bestätigen. Es erscheint dann der Datenschnitt mit allen Standorten. Wählt man nun einen oder mehrere (durch Halten von STRG) Standorte aus, so aktualisiert sich die intelligente Tabelle – und damit auch ein auf ihr basierendes Diagramm.
Ab Excel 2010 können Datenschnitte (in einer Pivot-Tabelle stehend) unter PIVOTTABLE → TOOLS → ANALYSIEREN → FILTERN → DATENSCHNITT EINFÜGEN eingefügt werden. Datenschnitte stellen also eine sehr einfache Möglichkeit dar, um interaktive Diagramme zu erstellen.
46. Einstellen der Standard-Anzahl von Tabellenblättern in einer neuen Arbeitsmappe
Standardmäßig enthält jede neue Arbeitsmappe drei leere Tabellenblätter. Wird eines davon (oder vielleicht auch zwei) nicht benötigt, so sollten diese gelöscht werden. Damit muss der Empfänger eines Berichts sich nicht erst durch die leeren Tabellenblätter klicken, um zu erkennen, dass hierin keinerlei Informationen enthalten sind.
Es kann daher empfehlenswert sein, die Standard-Einstellung zu ändern und nur ein oder zwei Tabellenblätter in einer neuen Arbeitsmappe zu haben. Falls weitere benötigt werden, lassen sich diese dann später (durch Klick auf das Plus-Zeichen in der Leiste) hinzufügen.
Diese Standard-Einstellung kann geändert werden unter: DATEI → OPTIONEN → ALLGEMEIN → SO VIELE ARBEITSBLÄTTER EINFÜGEN
47. Kopieren von Tabellenblättern
Um ein bereits bestehendes Tabellenblatt zu kopieren, klickt man mit einem Rechtsklick auf das entsprechende Tabellenblatt am unteren Rand der Datei und wählt dann VERSCHIEBEN ODER KOPIEREN …
In dem sich öffnenden Menü muss nun im Feld KOPIE ERSTELLEN ein Häkchen gesetzt werden; unter „Einfügen vor“ kann ausgewählt werden, vor welchem bestehenden Tabellenblatt die Kopie eingefügt werden soll. Excel erstellt dann die gewünschte Kopie des ausgewählten Tabellenblatts.
Schneller können Sie eine Kopie zudem mit der Taste STRG (Ctrl auf englischen Tastaturen) erstellen. Halten Sie diese gedrückt hält und ziehen Sie das gewünschte Tabellenblatt an eine andere Stelle der Leiste. Die Kopie wird dann eingefügt.
48. Entfernen der Gitternetzlinien
Unter ANSICHT können die Gitternetzlinien durch Wegnahme des Häkchens ausgeblendet werden. In vielen Fällen ist dies empfehlenswert und übersichtlicher. Außerdem werden die Gitternetzlinien nicht mitgedruckt; die Ansicht ohne Gitternetzlinien vermittelt auch einen besseren Einblick darüber, wie eine Excel-Tabelle in gedruckter Form aussieht.
49. Mit STRG+1 einfach formatieren
Stehen Sie in einer Zelle, können Sie mit STRG+1 den Formatierungsdialog aufrufen (START→ FORMAT → ZELLEN FORMATIEREN).
Extra-Tipp: Dasselbe gilt aber auch bezüglich des Formatierungs-Menüs jedes anderen beliebigen Objekts in Excel, wie insbesondere bei Diagrammen, aber auch Formen etc. Gerade bei der Formatierung von Datenreihen in Diagrammen kann das Selektieren mit der linken Maustaste und Drücken von STRG+1 deutlich schneller/einfacher sein als das Anklicken mit der rechten Maustaste und der Auswahl des entsprechenden Unter-Menüs.
50. Druckbereiche definieren und aufheben
Ein typisches Problem: Sie möchten (nur) einen bestimmten Bereich Ihrer Tabelle ausdrucken – beispielweise den Output einer Berechnung. Auf demselben Blatt befinden sich aber noch Nebenkalkulationen etc., die nicht für den Druck benötigt werden. Hierfür können Sie – einfach, aber effektiv! – Druckbereiche einsetzen:
- Dafür markieren Sie zunächst den gewünschten Bereich.
- Anschließend wechseln Sie in das Register SEITENLAYOUT. Unter „Seite einrichten“ klicken Sie auf die Schaltfläche und wählen DRUCKBEREICH FESTLEGEN. Unter DRUCKBEREICH AUFHEBEN kann dieser wieder aufgehoben werden.
Nun wird Excel nur den markierten Bereich – und nicht das ganze Arbeitsblatt – drucken!
Dipl.-Kfm. Daniel Unrein, Leiter Corporate Controlling in einem internationalen Dienstleistungs-Unternehmen, Verfasser des bei Vahlen erschienenen Fachbuchs „Excel im Controlling”
BC 2015 / 2016 / 2017 / 2018 / 2019
becklink422514