Excel bietet standardmäßig eine Vielzahl an Möglichkeiten, um Zahlen in der gewünschten Weise zu formatieren, wie z.B. als Währung, Datum, Prozent und so weiter.
Manchmal reichen die Standardformate aber nicht aus, weil man z.B. eigene Einheiten an seine Zahlen anhängen möchte. Oder weil man besonders lange Zahlen zwecks besserer Lesbarkeit optisch trennen möchte.
Für diese Fälle gibt es in Excel eine einfache Lösung: Benutzerdefinierte Zahlenformate. Damit sind der Fantasie (fast) keine Grenzen gesetzt, sein Datenmaterial im gewünschten Format darzustellen.
Und so geht’s:
Mit dem Standard geht’s los
Die verschiedenen Zahlenformate erreicht man entweder über die entsprechende Auswahlliste im Start-Register oder über einen Rechtsklick auf eine Zelle und den Menüpunkt „Zelle formatieren…“
Damit gelangt man zum Fenster mit den verschiedenen Standard-Kategorieren für Zahlenformate:
Um nun ein eigenes Zahlenformat zu definieren empfiehlt es sich, zunächst aus den vorhandenen Kategorien ein möglichst ähnliches Format auszuwählen. Denn in vielen Fällen kann ein bestehendes Zahlenformat als Ausgangsbasis für die Anpassungen dienen. Im Bild oben habe ich als Beispiel ein Währungsformat ausgewählt, das ich im nächsten Schritt anpassen möchte.
Zahlenformat anpassen
Das Euro-Zeichen wird standardmäßig hinter die Zahl angehängt, soll in meinem Beispiel aber vorangestellt werden. Dazu wähle ich nun die Kategorie „Benutzerdefiniert“ aus:
Wie man sieht, werden im Feld „Typ“ die Formatierungszeichen angezeigt, die ich nun ändern kann. Dazu klicke ich einfach in diese Zeile und stelle das Eurozeichen an die erste Stelle:
Und schon ist das gewünschte Ergebnis erreicht, wie man in Zelle A2 sieht:
Genauso gut hätte ich auch eine völlig neue Währung definieren können. In diesem Fall hänge ich die Währungsangabe einfach in doppelte Anführungszeichen hinten an die Formatierungszeichen an:
Und schon haben wir eine Alternative für den Euro geschaffen!
Jetzt kommt die Extrawurst
Damit ist aber noch lange nicht das Ende erreicht, denn die benutzerdefinierten Formate bieten noch viel mehr Möglichkeiten. So kann ich z.B. für positive und negative Zahlen unterschiedliche Farben festlegen und einen Hinweis ausgeben, wenn es sich bei der Eingabe um keine Zahl handelt:
Dieses Format wollen wir uns zum besseren Verständnis etwas näher ansehen:
Die Formatierungszeile ist in 4 Bereiche unterteilt, die ich zur besseren Unterscheidung farbig markiert habe. Dabei werden die Bereiche in genau dieser Reihenfolge definiert: positive Werte, negative Werte, Null und Texte. Die einzelnen Bereiche werden durch ein Semikolon voneinander getrennt.
Die Farbbezeichnungen müssen dabei jeweils in eckigen Klammern vorangestellt werden. Folgende Farbangaben können verwendet werden: Schwarz, Grün, Weiß, Blau, Magenta, Gelb, Zyan und Rot.
Die folgende Beispieltabelle zeigt, was noch alles mit benutzerdefinierten Formaten möglich ist:
Auf einige Besonderheiten möchte ich noch kurz eingehen:
Texte sind immer in doppelte Anführungszeichen zu setzen, wie wir schon in unserem Währungsbeispiel gesehen haben.
Das Rautensymbol # steht immer für eine Zahl, wobei nicht signifikatente Nullen weggelassen werden. Wenn man die Nullen auf jeden Fall anzeigen möchte, gibt man das Zeichen „0“ an.
Man kann auch mit Bedingungen arbeiten, die dann in eckige Klammern gesetzt werden. Dies ist bei der Unterscheidung von Einzahl und Mehrzahl ganz hilfreich: [=1] wird somit für den Wert 1 (also die Einzahl) angewendet [<>1] für alle Werte, die ungleich 1 sind, also die Mehrzahl.
Über das Stern-Symbol wird das nachfolgende Zeichen solange wiederholt, bis die komplette Zelle gefüllt ist.
Das könnte dich auch interessieren:
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
sehr interessanter Beitrag, vielen Dank dafür.
Ich würde in die Benutzerdefinierte Formatierung gerne einen Zeilenumbruch einbringen… geht so etwas?
Grüße,
Robert
Hallo Robert,
im Moment kann ich mir noch nicht so richtig vorstellen, wie dieser Zeilenumbruch in einer benutzerdefinierten Formatierung überhaupt eingesetzt werden sollte. Woran genau hättest du da gedacht? Einen Text nach einer bestimmten Anzahl von Zeichen umzubrechen? Falls das dein Anliegen wäre, dann sieht es eher schlecht aus.
Schöne Grüße,
Martin
Hallo Weiß,
vielen Dank für den Artikel, er hätt gern auch länger sein können, denn die verwendete Synthax in den benutzerdefinierten Formatierungen ist für den normalen fortgeschrittenen User nicht sehr zugänglich. Die eingebaute Hilfefunktion von Excel ist natürlich auch sehr dürftig.
Für mich interessant: Gelingt es auch eine dynamische benutzerdefinierte Formatierung anzuwenden? Ich möchte gern in mehreren gleichen Tabellenblättern (je Mitarbeiter) ein jeweils unterschiedliches Namenskürzel als Präfix vor die Projektnummer schreiben lassen, ohne dafür in jedem Tabellenblatt die benutzerdef. Formatierung manuell anzupassen.
Hallo Herr Lindner,
solche dynamischen Formatierungen sind mit benutzerdefinierten Zahlenformaten nicht möglich. Dafür müssten Sie auf die bedingte Formatierung zurückgreifen und entsprechende Formatierungsregeln einrichten.
Schöne Grüße,
Martin
Hallo Martin,
ich möchte WZ-Codes (= Klassifikation der Wirtschaftszweige gemäß Statistischem Bundesamt) von 01 bis 99990 in Excel erfassen und in diesem Format darstellen. Am besten als Zahl da eine Sortierung gewünscht ist.
Ich freue mich auf eine Antwort.
Thomas
Hallo Thomas,
wenn es nur darum geht, die führende Null bei zweistelligen Ziffern darzustellen, dann wäre das benutzerdefinierte Format 0#
Schöne Grüße,
Martin
Hallo Martin,
bei mir („Excel im Office 365“) steht das Euro-Zeichen standardmäßig VOR der Zahl.
Wie bekomme ich es nach hinten?
Vielen Dank im Voraus,
Grüße
Annie
Hallo Annie,
warum das Euro-Zeichen bei dir standardmäßig vor der Zahl steht, kann ich leider auch nicht sagen. Du kannst aber ein benutzerdefiniertes Format anlegen und folgenden Typ angeben:
#.##,0,00 €
Schöne Grüße,
Martin