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…“
Zahlenformate auswählen
Damit gelangt man zum Fenster mit den verschiedenen Standard-Kategorieren für Zahlenformate:
Zahlenformate nach Kategorie
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:
Anpassung des Währungsformats
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:
Geändertes Währungsformat
Und schon ist das gewünschte Ergebnis erreicht, wie man in Zelle A2 sieht:
Vorangestelltes Währungszeichen
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:
Eigene Währung definieren
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:
Verwendung von Farben
Dieses Format wollen wir uns zum besseren Verständnis etwas näher ansehen:
Aufbau des Zahlenformats
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:
Weitere benutzerdefinierten Zahlenformate
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.
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 Martin,
ja… der Umbruch nach einer bestimmten Anzahl Zeichen trifft es.
Schade… aber danke für die Antwort 🙂
Schöne Grüße,
Robert
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
Hallo Martin,
ist es möglich benutzerdefiniert eine Kalenderwoche zu einem Datum, welches in der Zelle steht, ausgeben zu lassen? ich möchte gern die Kalenderwoche, den Wochentag und das 8 stellige Datum auf einmal angezeigt bekommen.Vielen Dank schonmal. LG Monika
Hallo Monika,
der Wochentag lässt sich zusammen mit dem Datum mit folgenden benutzerdefinierten Zahlenformaten ausgeben:
TTTT, TT.MM.JJJJ = Freitag, 03.02.2023
TTT, TT.MM.JJJJ = Fr, 03.02.2023
Mit der Kalenderwoche geht das leider nicht. Hier musst du auf die Funktion ISOKALENDERWOCHE zurückgreifen, die du in eine separat Zelle neben das Datum schreiben musst.
Schöne Grüße,
Martin
vielen Dank,
das hab ich schon so gemacht, da ich mir dachte, dass die Kalenderwoche nicht benutzerdefiniert einzubinden geht.
Ansonsten bin ich ein großer Fan deiner Kalender aller Art. Ich habe mir schon viele verschiedene Formate und designs erstellt. (Wochen- Monats- 2-Monats und Jahreskalender, die allesamt dynamisch sind). Und mit jedem Kalender verstehe ich die vielen Formeln immer besser. letzes feature was ich eingebaut habe ist die textverketten-funktion. Ging leider erst mit MS office 2019. in 2016 war es sehr müßig, da ich erst eine hilfeseite erstellen musste, und das nicht dynamisch verwenden kann.
Vielen Dank für soviel Fachwissen, das du mit uns teilst.
LG Monika
Hallo Monika,
sehr gerne geschehen, es freut mich, wenn du bei meinen Tipps fündig wirst.
Schöne Grüße,
Martin
guter Artikel, Danke, Martin
Ich habe die Zellen mit Zahlenformaten bereits öfter genutzt.
Was ich nicht finde ist, dass ich einen Text anstelle einer Zahl eingeben kann und der Titel dennoch bestehen bleibt.
Geht das?
Beispiel:
Ich formatiere „Projekt“ #.# wenn ich dem Projekt eine Nummer gebe, erhalte ich: Projekt 1.2
Wie formatiere ich, um Projekt Bettwäsche
zu erhalten?
Hallo Daniel,
ein benutzerdefiniertes Zahlenformat kann Formatierungen für bis zu 4 Szenarien definieren. Getrennt durch ein Semikolon und in genau dieser Reihenfolge:
Positive Werte; negative Wert; Nullwerte; Text
Wenn es in deinem Fall nur positive Werte und Texte gibt, dann ginge beispielsweise dieses Format:
”Projekt ”#.#0;;;”Projekt ”@
Die Formate für negative Werte und Nullwerte sind hier nicht definiert, das @-Zeichen steht als Platzhalter für Text.
Schöne Grüße,
Martin
vielen Dank, Martin – lange nachher komme ich wieder dazu, zu testen – ich habe erst einmal alles schreibmaschinenmässig eingegeben.
Übers Wochenende habe ich nun alles probiert, was mir irgendwie möglich war. Es scheint, was ich benötige, gibt es nicht.
Ich möchte nämlich nicht eine Zahl dazuschreiben, sondern wenn ich einen Text eingebe, immer im Feld vor dem Wort „Projekt“ stehen haben.
-> wenn ich im Feld schreibe „Bettwäsche“, steht im Feld „Projekt Bettwäsche“ (hab ich nicht geschafft)
-> wenn ich im Feld schreibe „0“, steht im Feld „Projekt 0“ (schaffe ich immer 🙂 )
Wenn es dennoch öppis gibt, und Du kennst den Kniff, bin ich glücklich, wenn nicht, kann ich damit leben.
Schönen Aabe und e liebe Gruess
Daniel
Hallo Daniel,
genau das macht das Zahlenformat (ich habe jetzt nur noch den Bereich für Nullwerte ergänzt):
”Projekt ”#.#0;;”Projekt ”0;”Projekt ”@
Eingabe: 0
Ausgabe: Projekt 0
Eingabe: 1
Ausgabe: Projekt 1
Eingabe: Bettwäsche
Ausgabe: Projekt Bettwäsche
Tipp:
Wenn du den Formatcode kopierst, musst du die doppelten Anführungszeichen nochmal von Hand eingeben. Hier von der Website werden sie nicht korrekt übernommen.
Schöne Grüße,
Martin
Hallo Martin,
Gibt es eine Möglichkeit für ein benutzerdefiniertes Format welches alle folgenden Eingabearten (genauso) darstellt ohne Fehler (wie zB Fehler als Text gespeicherte Zahl):
1
01
01.01.01
01.01.01A.
01.01.
01.01.01.
01.01.01.1
01.01.0A.
01.01.1C.
Über eine Lösung würde ich mich sehr freuen!
Lg Sarah
Hallo Sarah,
vielleicht verstehe ich die Anforderung nicht richtig, aber meines Erachtens reicht es doch, die Eingabespalte einfach als Text zu formatieren?
Schöne Grüße,
Martin
Hallo Martin,
ich möchte Zahlen benutzerdefiniert formatieren und es soll mit 0-en die Stelligkeit ggf. aufgefüllt werden:
0,000 bis 0,999 mit drei Stellen nach dem Komma
1,00 bis 9,99 mit zwei Stellen nach dem Komma
10,0 bis 99,9 mit einer Stelle nach dem Komma
>=100 ganzzahlig mit 1000er-Punkt
Danke im Voraus und Grüße
Barbara
Hallo Martin,
ich habe es für zwei Bedingungen geschafft: [=10]#,0
Es fehlen aber noch die Bedingungen für =100; gibt es dafür auch eine Lösung oder kann man nur zwei Bedingungen definieren? Bekomme bei weiteren Schritten immer eine Fehlermeldung.
Grüße
Barbara
… sorry, hatte in vorherigem Kommentar nicht richtig kopiert; es soll in zweiter Zeile heißen: [=10]#,0
Grüße
Barbara
… das Kopierte wird tatsächlich nicht richtig gesendet; gebe es nochmal manuell ein:
[=10]#,0
… wurde wieder nicht richtig gesendet … 🙁
Hallo Barbara,
mit benutzerdefinierten Zahlenformaten wird das vermutlich nicht zu lösen sein. Aber es geht mit bedingter Formatierung. Du musst hier einfach nur 4 Regeln aufstellen und dann das gewünschte Zahlenformat festlegen:
Schöne Grüße,
Martin
Hallo Martin Namensvetter,
ich möchte in Excel mit der bedingten Formatierung einstellen, dass die Einträge in einer Zeile aber mehreren Spalten unterschiedliche Formatierungen haben, abhängig von dem Eintrag in der ersten Spalte/Zeile.
Also die Einträge in Zelle B2:B10 sollen bspw. kursiv, rot formatiert werden, wenn in B1 der Wert „0“ eingetragen ist. Ist der Wert „1“ dann soll der Text in fett, orange formatiert sein. Geht das?
Vielen Dank!
Hallo Holger,
klar geht das, du musst eben nur für jede gewünschte Formatierungsvariante eine eigene Formatierungsregel erstellen. Jede Regel bezieht sich dabei immer auf den gleichen Bereich, nur die Bedingung ist dann immer anders:
$B1=0
$B1=1
usw…
Schöne Grüße,
Martin
Hallo Martin,
kann man das benutzerdefinierte Format dauerhaft speichern? Mein Problem ist, dass ich Downloads aus SAP in .xls-Format mache und dabei stets führende Nullen verliere. Daher gebe ich über Zellen formatieren/Benutzerdefiniert einerseits 9 Nullen bzw. 7 Nullen vor.
Grüße
Sandra
Hallo Sandra,
ich fürchte, da sieht es schlecht aus. Denn in deinem speziellen Fall wird die Datei ja schon von SAP generiert, in nicht erst in Excel. Daher sehe ich eigentlich keine Möglichkeit, das Zahlenformat dauerhaft da reinzubringen.
Schöne Grüße,
Martin
Hallo Zusammen,
ich würde die Datei aus SAP nicht direkt be-/verarbeiten, sondern mit Power-Query in eine andere Datei einlesen, in der die Formate definiert sind.
Gruß Siegbert
Hallo Siegbert,
ja, das ist eine sehr gute Idee!
Danke,
Martin
Hallo Tabellenexperte
Zunächst herzlichen Dank für das Teilen Deines grossen Wissens!
Hier eine weitere Knacknuss: Wie kann man einzelne Textzeichen ansteuern?
Konkret habe ich die Absicht, das erste Zeichen so zu verbergen, dass ich damit eine bedingte Formatierung auslösen kann, ohne, dass das Zeichen angezeigt wird.
Z. B.: Eingabe: „3Orange“ – gewünschte Ausgabe: „Orange“ (damit ich über bedingte Formatierung über =LINKS(A1;1)=3 die Zellfarbe bedingt auswählen kann.
Geht das irgendwie?
Beste Grüsse,
Andy
Hallo Andy,
es ist leider nicht möglich, nur ein einzelnes Zeichen innerhalb eines Textes per bedingter Formatierung oder Zahlenformat auszublenden. Die Regel gilt immer für die gesamte Zelle. Du könntest stattdessen den Wert zur Steuerung der Zellfarbe in eine eigene Spalte schreiben und auf dieser Basis die Farbe ändern. Diesen Wert (z.B. die Ziffer 3) kannst Du per Zahlenformat ;;; unsichtbar machen.
Schöne Grüße,
Martin
Herzlichen Dank, Martin
Das bestätigt meine Befürchtungen.
Beste Grüsse,
Andy
Hallo Martin,
ich habe eine Zelle, in der hätte ich gerne ein Zahlenformat wie in etwa so:
[„“]“Eier“;[=1]“ Ei“;[>1]“ Eier“
Also zu Deutsch: Wenn die Zelle leer ist, soll „Eier“ drin stehn, wenn 1 dann „1 Ei“ und wenn 2 dann „2 Eier“. Geht sowas?
Viele Grüße
Rupert
PS: Besten Dank für das Teilen Deines beeindruckenden Fachwissens, und das auch noch in einer für den Laien sehr gut verständlichen Form!
Hallo Rupert,
ein Format für leere Zellen gibt es leider nicht. Aber für die Unterscheidung zwischen Einzahl und Mehrzahl kannst du folgendes Format verwenden:
[=1]0″ Ei“;0″ Eier“
Bei Menge 1 wird daraus 1 Ei, in allen anderen Fällen wird daraus … Eier
Schöne Grüße,
Martin