Die Top 10 Excel Tipps für Finanzkalkulationen

Bild: “
Excel” von Jonathan. Lizenz: CC BY 2.0

Tipp 1: Markieren Sie in Excel stets genau die Daten, die sie für den nächsten Schritt im Controlling gebrauchen

Für Formatierungszwecke, für Kopierzwecke und für das Darstellen von Diagrammen ist stets auf die richtige Markierung zu achten, um umständliche Doppelarbeiten und langwieriges Nacharbeiten zu vermeiden. Man markiert in Excel einfach einen zusammenhängenden Bereich, in dem man von links oben nach recht unten die Maus zieht. Nicht zusammenhängende Bereiche können über die STRG-Taste zu einer gemeinsamen Markierung gebracht werden. Spalten und Zeilen werden im Spalten und Zeilenbereich markiert; auch hier ist die STRG-Taste hilfreich, wenn die Bereiche der Markierung nicht zusammenhängen. Mit STRG und Taste A wird das gesamte Arbeitsblatt markiert.

Tipp 2: Nutzen Sie absolute, relative und gemischte Bezüge

Sehr häufig gibt es in Kalkulationstabellen die Situation, dass das Kopieren gefundener Excel-Formeln nicht die richtigen Ergebnisse liefern kann. Dies hängt meist damit zusammen, dass Excel stets beim Kopieren von normal eingetippten Formeln von relativen Bezügen ausgeht. Die Verweise in Formeln werden also so abgeändert, dass die Abstände zu den entsprechenden Zellen gleich bleiben. Das funktioniert in vielen Fällen gut, doch was ist zu tun, wenn es mal nicht klappt?

Hierzu ein Beispiel:

Die Zelle B10 erhält beim Anklicken des Summenzeichens automatisch die Formel = SUMME(B5:B10). Beim Kopieren dieser Formel nach rechts ändert Excel automatisch den Bezug auf B in C, D und E ab. B5 bzw. B10 in der ursprünglichen Formen sind also relative Bezüge. Berechnet man allerdings den Anteil in H5 mit relativen Adressen (= G5 / G10), dann ergeben sich beim Kopieren nach unten Fehlermeldungen. Denn der Bezug auf G10 soll auch bei den darunterliegenden Formeln erhalten bleiben. Gut funktioniert hier aber die Formel H5 = G5 / $G$10. Das Dollar-Zeichen $ sorgt dafür, dass Excel beim Kopieren den Bezug auf G10 stets beibehält. Das Dollarzeichen muss stets vor dem Spalten- bzw. Zeilenbezug verwendet werden. Damit wird dann der Bezug absolut. Man hätte auch nur ein Dollar-Zeichen verwenden können, dann wäre ein gemischter Bezug entstanden: H5 = G5 / G$10 funktioniert auch, weil der Bezug auf die Spalte beim Kopieren nach unten nicht verändert werden braucht.

Tipp 3: Verwenden Sie für Konstanten Namensfelder

Deutlich klarer könnte die Formel in H5 werden, wenn der Bezug auf die Kostensumme in G10 als Konstante deutlich werden würde. Dies kann man einfach in Excel umsetzen, wenn man der Zelle G10 einen Namen zuordnet. Dafür muss man lediglich links oben, wo die Adresse steht, einen Namen eintippen (hier KoSUM für „Kostensumme“). Die neue Formel in H5 lautet dann: = G5 / KoSUM. Diese Formel kann bedenkenlos nach unten kopiert werden, denn Excel weiß dann, dass es sich stets auf die Zelle beziehen muss, die mit KoSUM benannt wurde.

Tipp 4: Nutzen Sie die bedingte Formatierung, um Zusammenhänge deutlich werden zu lassen

Beim ersten Beispiel oben, sind auch Verluste in einem Quartal aufgetreten, ohne das dies deutlich in der Kalkulation hervorgehoben wird. Hier bietet sich die bedingte Formatierung an, die den Vorteil hat, dass in Abhängigkeit von der jeweiligen Situation die Formatierung einer Zelle geändert wird.

Falls die Zelle B17 negativ wird, dann soll der Zellhintergrund Rot werden. Man klickt die Zelle an, wählt oben in der Werkzeugleiste die Bedingte Formatierung aus und legt anschließend fest, dass der Zellhintergrund abzuändern ist, wenn die 0 unterschritten wird. Kopiert man die Zelle anschließend nach rechts, dann wird das Quartal mit den Verlusten deutlich sichtbar gemacht.

Tipp 5: Unterdrücken Sie überflüssige Nullen in langen Tabellen

Sehr häufig kommt es in Finanzkalkulation vor, dass große Tabellen entstehen, bei denen in sehr vielen Zellen nur 0 steht. Man kann in Excel für jedes Arbeitsblatt einzeln festlegen, dass Nullwerte nicht angezeigt werden sollen. Stattdessen erscheinen dann leere Zellen im Tabellenarbeitsblatt. Hierzu ist links oben auf die Schaltfläche von Microsoft-Office zu klicken. Dort wählt man dann > Erweitert. Nun muss man die Optionen für das Arbeitsblatt ansteuern und das Kontrollkästchen für Zellen mit Nullwerten abwählen.

Tipp 6: Nutzen Sie die Logikfunktion WENN, um wichtige Sachverhalte als Text auszugeben

Beim Controlling gibt es oft etwas zu tun, wenn bestimmte Ereignisse eingetreten sind. Diese Ereignisse hängen von Zahlenwerten ab, die erst in der Zukunft bekannt werden. Doch man kann bereits jetzt die Funktion WENN nutzen, um kurze Textinformationen nach dem Eintreten der Bedingungen anzeigen zu lassen.

Für ein einfaches Beispiel soll nochmals auf die obige Kalkulation eingegangen werden. Bei 20000 oder mehr Gewinn soll eine Prämie ausgegeben, bei Verlust soll eine Strafe verhängt werden. Und dies als Text auszugeben, verwendet man die Funktion WENN:

In Zelle B18 gibt man ein: =WENN(B17>20000;“Prämie“;WENN(B17<0;“Strafe“;““)).

Zunächst wird geprüft, ob der aktuelle Wert über 20000 liegt. Ist dies der Fall, dann wird hinter dem ersten Semikolon die Textausgabe „Prämie“ festgelegt. Hinter dem zweiten Semikolon wird dann abgeprüft, ob der Verlustfall vorliegt. Die Textausgabe von leeren Text („“) nach dem letzten Semikolon ist notwendig, um Excel daran zu hindern, Wahrheitswerte in der Tabelle anzuzeigen.

Tipp 7: Richtiges Markieren vereinfacht die Diagrammerstellung

Sehr häufig entsteht aufwendiger Nachbearbeitungsaufwand bei Diagrammen, weil nicht die richtigen Daten vor der Diagrammerstellung markiert wurden. Um dies zu vermeiden, gibt es einen einfachen Trick:

Markieren Sie ausschließlich rechteckige Bereiche, wo darzustellende Zahlen und Achsenwerte vorkommen. Markieren Sie keine Leerzellen ohne Inhalte. Verwenden Sie bei Leerzellen zwischen Daten die STRG-Taste, um eine Mehrbereichsmarkierung vorzunehmen (siehe Tipp 1).

Will man beispielsweise die Kosten in Abhängigkeit von den Quartalen darstellen, dann sollte nur die Texte und die Zahlen in den Bereichen A3:E3 und A5:E5 markieren, bevor man das Diagramm erstellt. Um eine solche Markierung über nicht zusammenhänge Zellbereiche durchführen zu können, braucht man die STRG-Taste.

Tipp 8: Perfekte Dokumentation mit Formeln

Häufig macht es Sinn, dass man wichtige Zusammenhänge mit Kommentaren versieht. So sollte man für Dokumentationszwecke die Formeln hinter Zellen als Zusatzkommentare anzeigen können. Dies geht recht schnell mit der Kommentarfunktion. Zunächst sucht man die betreffende Zelle auf und kopiert die Formel in die Zwischenablage. Anschließend wählt man mit der rechten Maustaste „Kommentar einfügen“. Nun erscheint eine gelbe Fläche, in die man die Formel aus der Zwischenablage und weiteren Text einfügen kann. Über eine Funktion der Office-Schaltfläche können Kommentare auch dauerhaft nach vorne gebracht werden.

Tipp 9: Nutzen Sie die Autoausfüllfunktion für lange Daten- oder Textreihen

Sehr häufig kommt es vor, dass lange Reihen gleichartiger Daten untereinander stehen müssen. Hier hilft oft die Autoausfüllfunktion von Excel weiter, um viel Tipparbeit zu sparen. Beim Kopieren eines Wochentags oder einer Monatsangabe erkennt Excel automatisch die dahinterstehende Komponente und ergänzt richtig. Aber auch, wenn man zwei Termine mit einem bestimmten Abstand eingibt, dann führt das Kopieren der beiden Zellen automatisch dazu, dass die Abstände zwischen den Terminen erkannt und automatisch verlängert werden.

Tipp 10: Organisieren Sie Textumbrüche nach Wunsch

Gibt man längere Texte in einer Zelle ein, dann organisiert Excel den Textumbruch nach eigenen Vorstellungen. Die müssen nicht immer zum gewünschten Ergebnis führen. Man verwendet während der Texteingabe die ALT-Taste gemeinsam mit der Zeilenschalttaste, um einen Umbruch zu erzwingen, der aber nicht zum Verlassen der Tabelle führt. Eine nachträgliche Einstellung des Zeilenumbruchs ist eine Option der Formatierung. Im Register „Ausrichtung“ wird festgelegt, ob der komplette Zellinhalt mit Umbrüchen darstellt oder der Text rechts außerhalb der Zelle angezeigt werden soll.

Weiteres Excel-Know-How bekommen Sie in unserem Excel Tutorial: die Pivot Tabelle.