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.
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.
Super erklärt! Und wie kann ich nun den in Anführungszeichen angegebenen Text am Ende der Formel als das erscheinen lassen, als das er normalerweise erscheinen würde (in diesem Fall ein SVERWEIS)?
Meine Formel lautet: ##.#0;-##.#0;[weiß]##.#0; und jetzt?
Hallo Irmtraud,
Du musst nur dieses benutzerdefinierte Format auf die Zelle mit dem SVERWEIS anwenden, dann sollte es schon greifen. Also Zelle markieren, Rechtsklick, Zellen formatieren, Format auswählen…
Schöne Grüße,
Martin
Hallo Martin
Danke für diese Erklärungen. Ich habe eine Frage, die zu diesem Thema passt: ich möchte in Excel in einer bestimmten Zelle jeweils eine Zahl von 1 bis 12 eingeben und die Formatierung dieser Zelle so machen, dass es mir dann den jeweiligen Monatsnahmen hinschreibt. Also wenn ich in Zelle A1 die Zahl 5 eintippe und ich dann ENTER drücke, soll in der Zelle A1 Mai stehen.
Ich möchte das nicht über eine Formel machen, die sich auf die Zelle A1 bezieht (also so im Sinn von in der Zelle A2 die Formel =DATUM(2016;A1;1) hinschreiben und dann die Zelle A2 benutzerdefiniert mit MMMM formatieren. Das Ziel wäre dass das alles in A1 passiert. Geht so eine Formatierung? Danke im Voraus für die Hilfe.
Hallo Thomas,
das lässt sich mit einer reinen benutzerdefinierten Formatierung nicht lösen. Ich fürchte, Du wirst um die Formellösung nicht herumkommen.
Schöne Grüße,
Martin
Formel wäre hier aber ungünstig, weil sie nicht im Feld des Eintrags zum tragen kommt.
In diesem Fall hülfe ein Makro, was bei der Eingabe der Zahl in die Zelle anspringt.
So geht’s:
– Mit Alt + F11 den VBA-Editor öffnen
– in der Baumstruktur links das gewünschte Tabellenblatt anklicken
– rechts ins (weiße) Editorfeld folgenden Code eingeben:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Select Case Target.Value
Case 1: Target.Value = “Januar”
Case 2: Target.Value = “Februar”
Case 3: Target.Value = “März”
Case 4: Target.Value = “April”
Case 5: Target.Value = “Mai”
Case 6: Target.Value = “Juni”
Case 7: Target.Value = “Juli”
Case 8: Target.Value = “August”
Case 9: Target.Value = “September”
Case 10: Target.Value = “Oktober”
Case 11: Target.Value = “November”
Case 12: Target.Value = “Dezember”
Case Else: Exit Sub
End Select
End If
End Sub
Sobald in irgendeine Zelle der Spalte A (Target.Column = 1) eine Zahl zwischen 1 und 12 eingegeben wird, wird sie durch den Monatsnamen ersetzt.
Alle anderen Eingaben in Spalte A (Case Else) bleiben unverändert.
Hallo Klexy,
richtig, mit VBA gibt’s natürlich auch elegante Lösungen. Danke dafür!
Schöne Grüße,
Martin
Hallo Thomas,
wenn es nur um die Formatierung (also geänderte Anzeige) des Monatsnamens geht, kannst Du das über eine bedingte Formatierung mit 12 Regeln realisieren.
Beispiel für Zelle A1:
-> bedingte Formatierung
-> neue Regel
-> Formel zur Ermittlung der zu formatierenden Zellen verwenden
Formel: =A1=1
Formatierung: Zahlen: Benutzerdefiniert: Typ: “Januar”
-> neue Regel
Formel: =A1=2
Formatierung: Zahlen: Benutzerdefiniert: Typ: “Februar”
.
.
-> neue Regel
Formel: =A1=12
Formatierung: Zahlen: Benutzerdefiniert: Typ: “Dezember”
Wenn nach dem Drücken von Enter wirklich “Januar” anstatt “1” als Wert in der Zelle stehen soll, bleibt nur die VBA-Lösung von Klexy.
Gruß
Stefan
hallo martin,
beim importieren einer csv-datei zerschießt mir excel immer eine spalte, in der nummern einer liste drin sind, like:
0.1
0.2
1.0
1.1.0
1.1.1.0
gibt es dafür auch eine sinnvolle lösung?
danke und mfg
Hallo Rene.
Die Dateiendung von csv in txt ändern (lässt sich dann natürlich nicht mehr einfach durch Doppelklick öffnen).
Die txt-Datei in Excel öffnen und beim Import im Textkonvertierungs-Assistenten:
Schritt 1 von 3: “Getrennt” wählen
Schritt 2 von 3: “Semikolon” wählen
Schritt 3 von 3: Für die fragliche(n) Spalte(n) das Datenformat “Text” wählen.
Nicht besonders elegant, sollte aber funktonieren.
Gruß
Stefan
Hallo,
ich möchte in einer Tabelle negative Werte FETT anzeigen lassen. Gibt es da eine Möglichkeit?
Wie ich die ´Schriftfarbe ändere bei negativen zahlen weiß ich aber zusätzlich in Fett wäre auch super 🙂
Danke schonmal für die Hilfe
Hallo Ramona.
Das lässt sich über die “normale” Wertformatierung nicht mehr realisieren. Die “Extrawurst” funktioniert nur bei der Schriftfarbe. Um sonstige Formatierungen (Schriftart, Hintergrundfarbe, Zellrahmen etc.) abhängig von Werten (oder anderen Bedingungen) zu ändern, muss man Regeln mittels der bedingten Formatierung für die Zelle(n) anlegen.
Gruß
Stefan
Hallo, ich hätte folgende Frage:
Ich habe ein Feld in dem nur 4 Eingaben möglich sind: m, w, männlich, weiblich (das habe ich mit einer Gültigkeitsprüfung gelöst).
Nun muss ich die Zelle aber so formatieren, dass wenn der Benutzer m eingibt männlich angezeigt wird, bzw. wenn der Benutzer w eingibt weiblich angezeigt wird.
Leider komme ich nicht auf die richtige Lösung
Hallo Lisa,
der einzige Weg, der mir spontan einfällt, wäre die Autokorrektur. In den Excel-Option unter “Dokumentenprüfung” lässt sich das einstellen, dass ein “m” eben durch “männlich” ersetzt wird.
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
habe eine dringende Bitte. es gibt kaum Erklärungen zum Platzhalter @ in benutzerdefinierten zahlenformaten bzw. wenn man es nach einer der spärlichen anleitungen macht, funktioniert es nicht. wofür brauche ich den genau? es gibt auch anscheinend platzhalter wie _ , die nirgendwo erklärt werden. bin für jede hilfe dankbar!
und gleich noch eine Frage: wenn man auf Einfügen /Pivot Table geht, erscheint am Tabellenblatt ein Rechteck mit dem Text “PivotTable1 Wählen Sie zum Erstellen eines Berichts die Felder aus der PivotTable-Feldliste aus” mit eine Grafik darin. Kann man dieses Rechteck samt Inhalt löschen, wenn man es doch nicht sehen will am Blatt, wann ja, wie??? vielen Dank
Hallo Laura,
das Feld für die Pivot-Tabelle bekommt man am leichtesten so weg:
Das war’s.
Schöne Grüße,
Martin
Hallo Laura,
das @-Zeichen ist der Platzhalter für Text. Wenn er im benutzerdefinierten Format angegeben wird, bleibt der Text in der Eingabezeile auch stehen. Wird das @-Zeichen weggelassen und gibt man nur einen fixen Text an, bleibt nur dieser fixe Text in der Eingabezelle stehen. Eigene Eingaben werden nicht angezeigt.
Beispiel:
Detaillierte Infos zu den Platzhaltern gibt es auch auf der Support-Seite von Microsoft.
Schöne Grüße,
Martin
danke suuper das funktioniert und ist erst mal logisch. aber ich kann ja auch ohne das @ Text in die Zelle eintragen. Welchen Sinn hat also überhaupt folgende @-Setzung? 0;-0;0;@ nur mit 0;-0;0 funktioniert es ja genauso.
sorry wenn ich jetzt noch mal nerve, aber es muss sein, diese benutzerdefinierten Zahlenformate treiben mich in den Wahnsinn und ich habe außer der einen Antwort von Ihnen noch immer keine ordentliche Anleitung mit sinnvollen und nützlichen Beispielen gefunden, das von microsoft ist unverständlich und ohne gute Beispiele.
Habe jetzt folgendes Format erstellt. : 0;-0;”keine”;@ “Erdäpfel”
da sollte es doch statt Null immer “keine” schreiben, tut es aber nicht, es schreibt trotzdem 0. Was habe ich falsch gemacht ???!!!??? bin für jeden Hinweis dankbar.
Hallo Laura,
ich habe es gerade getestet und bei mir bringt dieses Format anstelle der Null auch “keine”. Steht in Ihrer Zelle tatsächlich eine glatte Null oder ist es das Ergebnis einer Berechnung, bei der es vielleicht noch einige Nachkommastellen nach der Null gibt? Ansonsten weiß ich es auch nicht…
Schöne Grüße,
Martin
danke für die Mühe. habe es nochmal getestet. wenn ich nur 0 eintippe, schreibt es “keine”. Wenn ich auch noch Text dazu schreibe, springt es aber wieder auf 0 um. Glatte Null, kein Ergebnis einer Berechnung.
Hallo Martin,
ich hoffe Du kannst mir helfen.
Mein Problem:
in einer Form habe ich eine Textbox in der ich einen Kilometerstand eingebe. Dieser soll in einer Zelle (Excel 2007) übernommen werden, die schon benutzerdefiniert #.##0″ KM” ist. In der betreffenden Zell erfolgt jedoch nur linksbündig eine Zahl z.B. 45000 ohne weitere Formatierung. Auch eine nachträgliche Formatierung bringt keinen Erfolg. Trotz wochenlangem googeln habe ich keine brauchbare Lösung gefunden.
Kennst Du vielleicht eine Möglichkeit?
MfG
Dieter Smets
Hallo Dieter,
ich vermute, Du musst bei Deiner Suche in Deiner Form ansetzen oder ggf. im VBA-Code, der dahinterliegt. Dort ist vermutlich irgendwo festgelegt, dass Text geschrieben wird.
Schöne Grüße,
Martin
Hallo, vielen Dank für den Beitrag und für die Mühe das zu erstellen.
Jedoch habe ich eine Frage:
Ich möchte die Zellen so formatieren, dass folgende Anordnung besteht:
0-170420-AA
Am Anfang darf nur eine Zahl eingetragen werden ( optimal wärs wenn man nur eine 0 oder eine 1 eintragen könnte)
der Bindestrich sollte immer bestehend angezeigt werden
danach folgen 6 weitere Zahlen ( bestenfall sollen zur zahlen eingetragen werden)
der zweite Bindestrich sollte ebenfalls bestehen
zuletzt sollen zwei Buchstaben eingetragen werden können ( bestenfalls nur buchstaben und nur zwei)
ist das irgendwie umsetzbar ohne ein Makro?
LG
Andreas
Hallo Andreas,
ein benutzerdefiniertes Format funktioniert hier nicht. Wenn ich es richtig verstehe, möchtest Du eigentlich eine Eingabeprüfung.
Richte also für die betroffenen Eingabezellen folgende Datenüberprüfung ein (Menü Daten | Datenüberprüfung):
Zulassen: Benutzerdefiniert
Formel: =UND(ODER(LINKS(A1;1)=”0″;LINKS(A1;1)=”1″);TEIL(A1;2;1)=”-“;TEIL(A1;9;1)=”-“;LÄNGE(A1)=11;NICHT(ISTFEHLER(WERT(TEIL(A1;3;6))));NICHT(ISTZAHL(WERT(RECHTS(A1;2)))))
Statt A1 gibst Du die jeweilige Eingabezelle an. Die Formel akzeptiert dann nur noch Eingaben, die folgendem Muster entsprechen:
– erstes Zeichen 0 oder 1
– Bindestrich an der 3. und 9. Stelle
– Gesamtlänge muss 11 sein
– die 6 mittleren Stellen müssen eine Zahl sein
– die beiden letzten Stellen dürfen keine Zahl sein
Schöne Grüße,
Martin
Ich bin kein Mensch vieler Worte aber ihre waren wirklich die hilfreichsten die ich im Netz gefunden habe, und dafür bedanke ich mich aufrichtig. Leider gibt es zuviele Köche im Internet die nur Sinnlosen Brei von sich geben, umso mehr weiss ich die Professionalität ihrer Arbeitsweise zu schätzen. Alles Gute machen sie bitte weiter so.
mit Freundlichen Grüßen
Robert Müller
Hallo Herr Müller,
vielen Dank für dieses schöne Feedback. Genau so etwas motiviert ungemein dazu, auch zukünftig hier mein Bestes zu geben.
Schöne Grüße,
Martin
Hallo Herr Weiß,
ich schließe mich an – sehr gut erklärt, vielen Dank!
Meine spezielles Format-Problem: ich muss sehr große und sehr kleine Zahlen in einer Tabelle darstellen und möchte die Lesbarkeit (und Vergleichbarkeit) erhöhen. Es sollen alle (=beliebig viele) Nachkommastellen in Dreiergruppen (Bsp. 0,000 000 007) dargestellt werden, große Zahlen mit Trennpunkt (Bsp. 123.000.000.000).
Gibt es dafür eine Format-Lösung?
Danke vorab & viele Grüße, pickUP
Hallo pickUp,
solch ein Zahlenformat könnte so aussehen:
#.##0,### ### ### ### ###
Damit werden vor dem Komma wenn nötig Tausendertrennzeichen gesetzt. Nach dem Komma werden die Zahlen in maximal 5 Dreiergruppen dargestellt. Das ist meines Wissens auch die maximale Genauigkeit von Excel (15 Stellen), mehr dürfte also ohnehin nicht gehen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für Deine Antwort – fast perfekt! Die großen Zahlen werden jetzt allerdings alle mit Komma dargestellt (16.000, ).
Mir fehlt quasi das Zeichen für “optionales Komma”. Hast Du so etwas ist der Trickkiste? 😉
Viele Grüße,
pickUP
PS: Ein weiterer Effekt, der mir auch vorher schon mal aufgefallen ist (auch wenn man in der Menüleiste die “000” für Tausendertrennzeichen anwählt): die Zelleinträge bekommen eine Abstand zur rechten Zellenbegrenzung. Aber das ist im Grunde nur ein “Schönheitsfehler”, oder eben Geschmackssache.
Hallo pickUp,
ich vermute, für ein optionales Komma gibt es keine Lösung. Du könntest höchstens noch immer eine Stelle nach dem Komma anzeigen lassen, dann steht das Komma nicht so verloren da:
#.##0,0## ### ### ### ###
Und der rechte Abstand kommt durch die 4 Leerzeichen im Formatcode zustande, auch dafür kenne ich leider keine Lösung. Aber vielleicht hat ein anderer Leser ja noch einen Tipp.
Schöne Grüße,
Martin
Super erklärt- hat mir direkt geholfen ohne große weitere Suche. Lieben Dank
Hallo Chris,
gern geschehen.
Schöne Grüße,
Martin
Hallo Herr Weiß,
da Sie das hier so super erklären, stelle ich Ihnen auch mal eine Frage:
wenn ich Spalten mit Zahlen von Excel nach InDesign kopiere, erscheinen sie in InDesign so wie sie in Excel formatiert waren, z.B. Hochkomma als Tausendertrennzeichen, Punkt als Dezimaltrennzeichen.
Jetzt möchte ich aber über die InDesign-Funktion „Datei – Platzieren…“ eine Datei einfügen mit solcherart formatierten Zahlen, weil ich da auch die Tabellenrahmen mit einstellen kann, bei Kopieren geht das nicht. Dummerweise wird das Format in InDesign geändert in Punkte als Tausendertrennzeichen, Komma als Dezimaltrennzeichen. Ich finde nirgendwo in InDesign, wo ich das einstellen kann. Aber sei’s drum, hier geht es ja um Excel und da ist meine Frage:
wie kann ich benutzerdefinierte Zellenformatierungen so einstellen, dass Hochkommata als Tausendertrennzeichen kommen und Punkte als Dezimaltrennzeichen. #’##0.00 funktioniert nicht. So wird es zwar angezeigt, wenn ich über „Datei – Optionen – Erweitert“ den Haken rausmache bei „Trennzeichen vom Betriebssystem übernehmen“ und bei Dezimaltrennzeichen „.“ und bei Tausendertrennzeichen „’“ eingebe. Selbst dann wird es in InDesign anders formatiert. Würde ich über „Region und Sprache – Formate“ „Deutsch (Schweiz)“ eingeben würde es eventuell auf meinem Rechner funktionieren (ich habe es nicht ausprobiert), aber das InDesign-Dokument soll später auf einem anderen Rechner geöffnet werden. Das mit dem Hochkomma bekomme ich noch hin, aber wie lautet der Befehl „mach für ein Dezimaltrennzeichen einen Punkt!“ im benutzerdefinierten Format. Die Einstellung Punkt und Komma soll bleiben.
Vielen Dank für Ihre Bemühungen, aber da ich selbst nach ziemlich langer Suche nichts gefunden habe, gehe ich davon aus, dass Sie das auch nicht rausfinden werden :’-( Schönen Tag noch!
Viele Grüße
-martin fydrich-
Hallo Herr Weiß,
manchmal hilft es, sein Problem einfach nochmal deutlich zu beschreiben. Wenn ich die Region für mein ganzes Windows auf „Deutsch (Schweiz)“ einstelle und die Datei in InDesign platziere, kommt sie da natürlich richtig. Aber wenn ich es ganz normal abspeichere, verändert es sich nicht mehr. Auch auf anderen Rechnern nicht. Juhu!
(Trotzdem wäre es schön zu wissen, wie ich einen benutzerdefinierten Punkt als Dezimaltrennzeichen hinbekomme.)
Mit freundlichen Grüßen
-martin fydrich-
Hallo Herr Fydrich,
freut mich, dass Sie das Problem noch lösen konnten.
Was ein benutzerdefiniertes Dezimaltrennzeichen angeht, muss ich allerdings passen. Mir ist zumindest nicht bekannt, dass so etwas möglich ist.
Schöne Grüße,
Martin
Hallo,
ich habe ein Diagramm, bei dem die Beschriftung der Y-Achse aus Zahlenwerten mit zwei Nachkommastellen besteht.
Dies stelle ich in meinem Excel 2016 ein, indem ich die Y-Achse mit der rechten Maustaste auswähle und dann
– Achse formatieren auswähle und dann im Menü rechts “Zahl” das entsprechende Zahlenformat auswähle.
Ich möchte jedoch hinter den Zahlenwerten ein VARIABLES Währungssymbol setzen.
Das Währungssymbol steht als Text in der Zelle K392 auf einem anderen Tebellenblatt namens “progress”.
Ich hatte gehofft, dass ich den Formatcode nun ganz einfach wie oben beschrieben mit dem nachfolgenden Zusatz ändern kann :
#.##0,00 & progress!K392
Das wäre aber offensichtlich zu einfach.
Egal, was ich probiere und mit “Hinzufügen” bestätige – das Währungszeichen taucht nicht im Diagramm auf.
Gibt es dafür eine Lösung?
Vielen Dank für alle, die sich meinen Kopf zerbrechen
Danke – hat sich gerade erledigt.
Ich habe das Problem per VBA gelöst.
Hallo Roy,
mit normalen Mitteln (also ohne VBA) wäre das vermutlich auch nicht zu lösen gewesen.
Schöne Grüße,
Martin