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.
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 Herr Weiß,
Meine Frage ist ob Sie wissen wie das bei Tablet aussieht leider funktioniert Excel nicht auf meinen Tablet da ich sonst dafür bezahlen müsste.
Zudem habe beruflich nichts weiter mit Excel zu tun daher bin ich ausgewichen auf Google Tabellen sollte doch eigentlich gleich sein oder?
Daher stellt sich meine Frage ich möchte gerne eine Autogas/Benzinverbrauch erstellen leider bekomme ich es nicht hin bei Google Tabelle z.b. nach den gefahrenen Kilometer Anführungsstrichen Kilometer einzutragen bei benutzerdefiniert versucht habe ich: #0 „km“ oder =0 „km“
oder 0.00 „km“ oder oder habe schon einiges versucht.
ps habe von einen Bekannten ein Mail erhalten wo er mir von Excel fertige Formel geschickt. Habe diese auch einfügen können ,jedoch benönntige ich auch Erweiterungen (L) (liter) usw, jedoch sobald ich die Auslese möchte und die Formel für mich erweitern möchte funktioniert es leider nicht bzw kann ich der niedergeschriebe Formel auf einmal so gar nicht anwenden und ist somit für mich komplett unbrauchbar …..
was mache ich falsch?
Vielen lieben , besten Dank im vorraus.
Hallo Herr (oder Frau?) Kleemann,
zu Google Tabellen kann ich leider überhaupt nichts sagen, da ich ausschließlich Excel einsetze. Vieles davon wird vermutlich auch möglich sein, aber das kann ich nicht beurteilen. Da müssten Sie eher auf einem Google-Forum nachfragen.
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 Hr. Weiß!
In MS Excel 2016 kann man in den Excel-Optionen und dem Menüpunkt „Erweitert“ das Dezimaltrennzeichen definieren. Standardmäßig ist hier der Punkt „Trennzeichen vom Betriebsystem übernehmen“ angehakt.
LG
Klaus
Hallo Herr Zagorz,
vielen Dank für den Tipp, das war mir bisher völlig entgangen.
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
Schöne Beispiele, gut erklärt!
Ich benötige folgende Extrawurst, die ich benutzerdefiniert nicht hinbekomme.
Die Einheit für Volumnia ist m³ (m + ALT0179) und läßt sich so auch als benutzerdefiniertes Zellenformat eingeben.
Die Einheit für Trägheitsmomente ist m4. Wie kann ich die 4 als benutzerdefiniertes Zellenformat schreiben dass bei Eingabe eines
Wertes von 3,75 in der Zelle 3,75 m4 mit hochgestellter 4 angezeigt wird.
Im voraus vielen Dank.
Hallo Rudi,
eine hoch gestellte 4 lässt sich meines Wissens in Excel nicht als benutzerdefiniertes Zahlenformat darstellen.
Das geht nur über eine manuelle Formatierung direkt in der Zelle: Die „4“ mit der Maus markieren – Rechtsklick – Zellen formatieren – Schrifteffekt: Hochgestgellt
Schöne Grüße,
Martin
Guten Tag Herr Weiss. Hier in der Schweiz haben wir die Tausender-Trennzeichen, also Format 1’234.56.
Wenn ich z.B. aus Deutschland eine Tabelle erhalte, sind die Zahlen 1,234.56 formatiert. Diese Zahlen kann ich dann nur mit der Autosumme addieren, wenn ich bei jeder Tausenderzahl das Komma von Hand lösche.
Gibt es eine einfachere Methode, die betreffenden Zellen alle zusammen anzupassen?
Schöne Grüsse aus der Schweiz
Nicolo Paganini
Hallo Herr Paganini,
mich wundert es ein wenig, dass Excel das Zahlenformat nicht selbst korrekt interpretiert. Allerdings irritiert es mich auch, dass Sie aus Deutschland das Zahlenformat 1,234.56 erhalten, denn üblich wäre eigentlich 1.234,56
Im Zweifelsfall könnten Sie aber immer die Suchen&Ersetzen-Funktion nutzen:
Suchen Sie nach dem Komma und lassen Sie das Feld „Ersetzen durch“ einfach leer. Dann sollte das lokal auf Ihrem Rechner eingestellte Zahlenformat greifen.
Schöne Grüße,
Martin
Hallo,
ich würde gerne eine bestimmte Teilenummer in ein passendes Format bringen, schaffe das aber bis jetzt nicht.
Beispiel einer Teilenummer wäre 4K0131962Q und ich will, dass sie folgendermaßen formatieren: 4K0 131 962 Q.
Da auch immer Buchstaben in der Teilenummer vorhanden sind, funktioniert das benutzerdefinierte Zahlenformat nicht.
Kann man dies irgendwie hinbekommen?
Grüße
Oliver
Hallo Oliver,
ich fürchte, das wird sich mit benutzerdefinierten Formaten nicht lösen lassen. Da bleibt wohl nur die Option einer Hilfsspalte, in der du mit den Text-Funktionen die Teilenummer in das gewünschte Format bringst. Also etwa so:
=LINKS(A1;3)&“ „&TEIL(A1;4;3)&“ „&TEIL(A1;7;3)&“ „&RECHTS(A1;1)
(es handelt sich oben immer um doppelte Anführungszeichen; die werden hier leider nicht korrekt dargestellt)
Schöne Grüße,
Martin
Hallo Herr Weiß,
vielen Dank für die tolle und interessante Extrawurst-Anleitung!
Sie hatten in einer Antwort schon geschrieben, dass dies nur mit den Farben geht, aber ich wollte dennoch einmal nachfragen, ob Sie eine Möglichkeit über die Wert-Formatierung sehen, die Zahlen vor dem Komma in einer größeren Schriftgröße (z.B. Schriftgröße 12) und die Zahlen nach dem Komma in einer kleineren Schriftgröße (z.B. 10 oder 8) darzustellen?
Gibt es hierfür eventuelle andere Mittel? Vielen Dank!
Hallo Alex,
unterschiedliche Schriftgrößen für Vor- und Nachkommastellen lassen sich weder über die Zahlenformate noch über eine bedingte Formatierung darstellen. Wenn überhaupt, müsste hier mit VBA-Programmierung gearbeitet werden. Mit normalen Excel-Bordmitteln ist das jedoch nicht möglich.
Schöne Grüße,
Martin
Hallo Herr Weiss,
Sie haben bestimmt sofort eine brauchbare Antwort für mich? Ich finde einfach kein Format (ich bin kein Excel Freak) für die Zahl 0012.3456.
Was ich auch ausprobiere, die Nullen vorne sind immer weg…
Ich brauche das für einen SVerweis mir einer Vielzahl von Zahlen, die jetzt als Text vorliegen. So geht SVerweis natürlich nicht..
Vielen Dank
Hallo Herr Blumentritt,
wenn die Zahlen, die sie vergleichen wollen, als Text vorliegen, dann müssen Sie sie auch als Text formatieren. Dann bleiben auch die führenden Nullen erhalten.
Schöne Grüße,
Martin
Sehr hilfreiche Erläuterungen. Vielen Dank hierfür. Mich drückt aber der Schuh an anderer Stelle:
Mir schwebt vor, eine lange Kette von Ziffern (keine Zahl) zu erfassen und nach dem Return die Darstellung der Zeichenkette nach jeweils vier Ziffern von einem Leerzeichen unterbrechen zu lassen (z. B. „0123 4567 8901 2345 6789“). Zur Erläuterung: Der String besteht ausschließlich aus Ziffern, damit bei der Erfassung der Ziffernblock nicht verlassen werden muss. Die optische Einfügung der Leerzeichen die Zeichenkette „lesefreundlicher“ gestalten.
Dafür habe ich das benutzerdefinierte Format ####“ „####“ „####“ „####“ „#### gewählt. Dies hat jedoch zur Folge, dass der Text als Zahl interpretiert wird und somit die letzten vier Ziffern jeweils mit einer „0“ dargestellt werden.
Wie sieht eine Lösung aus?
Vielen Dank im voraus!
Hallo Joachim,
das beschriebene Problem lässt sich mit einem benutzerdefinierten Format leider nicht lösen. Eine Alternative könnte sein, die Dateneingabe als Text in einer Zelle vorzunehmen und dann diese Eingabe in der Spalte daneben per Textformeln in das gewünschte Anzeigeformat bringen.
Also beispielsweise so:
=LINKS(A1;4)&“ „&TEIL(A1;5;4)&“ „&TEIL(A1;9;4)&“ „&TEIL(A1;13;4)&“ „&TEIL(A1;17;4)
Nicht optimal, aber eine bessere Lösung fällt mir nicht ein.
Schöne Grüße,
Martin
Hallo,
ich habe ein anderes Problem – und noch keine Lösung gefunden.
Das Format kommt so an: 6,72905E+12. Ich habe es schon benutzerdefiniert umformatiert in: 6729053715704.
Aber: die letzte Ziffer muss noch weg (das ist eine Prüfziffer, die mit ankommt – in meinem System aber nicht hinterlegt ist – daher kann das System diese Zelle nicht zuordnen).
Wie bekomme ich diese eine letzte Ziffer „wegformatiert“ ?
Vielen Dank.
Hallo Silke,
mit wegformatieren geht hier leider nichts. Du musst die letzte Stelle zum Beispiel per Formel abschneiden. Angenommen, die Zahl steht in A1:
=WERT(LINKS(A1;LÄNGE(A1)-1))
Damit bist du hinsichtlich der Länge der Zahl flexibel und es wird immer nur die letzte Stelle abgeschnitten. Wenn die Zahl jedoch beispielsweise immer 13stellig ist, geht es ein wenig kürzer:
=WERT(LINKS(A1;12))
Hinweis: Die umschließende WERT-Funktion ist notwendig, damit das Ergebnis auch wieder als Zahl erkannt wird. Ansonsten wird durch die LINKS-Funktion ein Text, der nur wie eine Zahl aussieht.
Schöne Grüße,
Martin
Moin,
Alternativ wäre auch Teilen und Abrunden möglich.
=ABRUNDEN(A1/10;0)
Dann bleibt es bei einer Zahl.
Gruß M.T-S (Marcel)
Hallo Marcel,
richtig, ABRUNDEN wäre natürlich auch eine gute Alternative 🙂
Viele Grüße,
Martin
Hallo Martin,
leider komme ich mit meinen Kenntnissen und dem, was im WWW (nicht) beschrieben wird, am Ende und bitte um Hilfe.
Unter Bezugnahme auf eine Zelle, in der als Berechnungsergebnis z.B. 100 Stunden im Format [hh]:mm korrekt angezeigt werden, möchte ich an anderer Stelle den Gegenwert der 100 Stunden in Minuten anzeigen lassen. Das Format [mm] greift auch soweit, aber ich hätte die Anzeige gerne mit Tausendertrennzeichen.
Viele Grüße
Hans-Jürgen
Hallo Martin,
ich habe eigene Formate wie m, m2, m3, etc. und hätte diese gerne im Menuband um einfacher darauf zugreifen zukönnen.
Eine Idee wie man die in das Menüband in das Register Zahl bekommt 🙂
Gruß
Peter
Hallo Peter,
da sieht es schlecht aus. Eigene benutzerdefinierte Formate bekommt man nicht ins Menüband oder in die Schnellzugriffsleiste – zumindest ist mir kein Weg bekannt.
Der schnellste Weg wäre vermutlich noch über die Tastenkombination Strg+1. Damit wird zumindest schon mal das Auswahlfenster mit den Zahlenformaten aufgerufen.
Schöne Grüße,
Martin
Hallo,
ich würde gerne auf das Format [ZEICHEN/ZEICHEN/LEERZEICHEN/ZAHL/ZAHL/PUNKT/ZAHL] prüfen.
Also Beispiel KX 30.1
Wobei KX, das Leerzeichen und der Punkt fest steht. Die „30“ und die „1“ im Beispiel sind variabel/ändern sich.
Wie lautet die Syntax für die Datenprüfung/Zellenformatierung? @;@;@;0;0@;0 ?
Grüsse
Hallo Olli,
mit einem benutzerdefinierten Zahlenformat geht das nicht. Aber in einer Datenprüfung könntest du folgende Formel anwenden (im Beispiel für Zelle A1):
=UND(LINKS(A1;3)=“KX „;ISTZAHL(WERT(TEIL(A1;4;2)));TEIL(A1;6;1)=“.“;ISTZAHL(WERT(RECHTS(A1;1)));LÄNGE(A1)=7)
Schöne Grüße,
Martin
Hey Martin,
vielen dank für die Erklärung, aber nun zu meiner Frage.
Kann ich benutzerdefinierte Formate auch als Symbol auf die Menüleiste setzten, wenn ja wie geht das?
Vielen lieben Dank schon einmal im Voraus.
LG
Sandra
Hallo Sandra,
die Frage hatte ich weiter oben schon mal beantwortet: Nein, benutzerdefinierte Formate lassen sich leider nicht als Symbol in die Menüleiste übernehmen.
Schöne Grüße,
Martin
Hallo zusammen,
Wenn ich eine WENN/DANN Formel habe, kann ich durch Benutzerdefinierte Formate irgendwie kenntlich machen, mit welcher Wenn/Dann Bedingung das Zelle Ergbnis Zustande gekommen ist?
Also mit anderen Worte, kann ich das irgendwie so formatieren, das z.B. ein „A“ an das Ende des Wertes angefügt wird, wenn das Ergebnisse das Produkt aus der WENN Bedingung ist und ein „B“, wenn das Ergebnis ein Produkt aus der DANN Bedingung ist?
Somit könnte ich über die bedingte Formatierung die Tabelle nach A & B durchsuchen und unterschiedlich einfärben zu lassen. Somit hätte man sofort einen Überblick, welches Ergebnis aufgrund welcher Bedingung zu Stande gekommen ist.
geht so etwas? Vielen Dank für die Antwort.
Hallo JB,
ich bin mir nicht sicher, ob ich die Frage richtig verstanden habe. Wenn es schon eine WENN-Formel in der Zelle gibt, warum hängst du dann dort nicht auch gleich das „A“ oder „B“ an den Wert an? Mit einem benutzerdefinierten Zahlenformat lässt sich das leider nicht abbilden.
Schöne Grüße,
Martin
Hallo Martin,
Ich möchte folgendes Format verwenden: ####-####-####-####
Eingabe: 1224-5678-1234-5678
aber dann ist die
Ausgabe: 1234-5678-1234-5670
Egal welche Zahlen genommen werden, die letzte Zahl wird immer auf Null (0) gesetzt.
Wie kann man dies beheben? Die IBAN-Nummer kann auch nicht dargestellt werden oder doch?
Vielleicht gibt es ja einen Trick? oder Excel 2016 einen Buck? oder ?
Der-vor dem-Computer-sitzt
Horst
Hallo Horst,
das Problem liegt darin, dass es sich um ein Zahlenformat handelt und Excel keine Zahlen darstellen kann, die länger als 15 Stellen sind. Einfach mal ohne dieses Format eintippen und oben in die Bearbeitungszeile schauen: Alles was nach der 15. Stelle kommt, wird durch eine Null ersetzt.
Das ist eine „natürliche“ Grenze in Excel und da kenne ich auch keinen Trick, wie man das umgehen könnte (und somit lassen sich auch keine IBAN-Nummern entsprechend formatieren.
Schöne Grüße,
Martin
Hallo Martin,
interessanter Beitrag.
Ich tüfftel gerade um, wie ich wohl in eine mit Prozent formatierte Zelle noch einen Text bekomme.
Das sollte so aussehen:
0,00 % TEXT
Kannst du mir helfen?
Danke
Mel
Hallo Mel,
wenn es ein statischer Text sein soll, dann erstellst du folgendes benutzerdefiniertes Zahlenformat:
0% „TEXT“
(oder was auch immer als Text da stehen soll)
Alternativ kannst du einfach die TEXT-Funktion nutzen. Angenommen, der Prozentwert steht in A1, dann gib ein eine andere Zelle ein
=TEXT(A1;“0%“)&“ TEXT“
Und da sind deiner Fantasie keine Grenzen gesetzt.
Schöne Grüße,
Martin
Hallo Martin,
alles bisher super erklärt und ich habe einiges für mich aus den Beiträgen mitnehmen können, da ich sehr oft und gern mit individuellen Zahlenformaten arbeite. Ich hoffe, ich habe keinen mein Problem betreffenden Beitrag überlesen.
Kann ich den Text in einer Zelle als Zahlenformat in einer anderen Zelle benutzen??? Z.B.:
in A1: Krank — in B1: 5 — in C1: 5 Krank (in der Form z.B. in einem Diagramm verwendbar)
in A1: Urlaub — in B1: 12 — in C1: 12 Urlaub
Schöne Grüße und nochmal danke
Micha
Hallo Micha,
wenn ich dein Beispiel richtig verstanden habe, ist das keine Frage der Formatierung, sondern eine Kombination aus den Werten in den beiden anderen Zellen. Dazu würdest du in C1 einfach folgende Formel schreiben:
=A1&B1 (oder =A1&“ „&B1)
Schöne Grüße,
Martin
Hallo Martin,
leider nicht, die Verknüpfung mit „&“ ist mir klar, dadurch entsteht aber ein Text, ich muss mit C1 aber weiter rechnen können bzw. den Wert in einem Diagramm darstellen 🙁
Schöne Grüße
Micha
Hallo Martin,
Nochmal vielen Dank für Deine Bemühungen. Ich habe gestern noch mal versucht, mehrere Stunden in meiner Excel-Trickkiste zu wühlen und bin letztendlich zu dem Schluss gekommen, dass das, was ich (meine Vorgesetzten) brauche, so nicht funktioniert und stelle das Ganze jetzt über bedingte Zell-Formatierungen dar. Das kommt rein optisch dem Gewünschten (Diagramm) am nächsten und ich muss nicht erst wieder über den Umweg WERT() o.Ä. gehen. Außerdem kann jetzt jede Kategorie mehrfach an unterschiedlicher Stelle in der Datenreihe vorkommen und trotzdem immer die gleiche Farbe erhalten.
Vielen Dank und schönes Wochenende
Micha
Hallo Micha,
dann fällt mir noch die bedingte Formatierung ein. Du müsstest halt für jede Kategorie (Urlaub, Krank etc) eine eigene Formatierungsregeln anlegen.
Regel 1:
=A1=5
Formatierung:
Zahlenformat | Benutzerdefiniert: #“ – Krank“
Regel 2:
=A1=12
Formatierung:
Zahlenformat | Benutzerdefiniert: #“ – Urlaub“
Schöne Grüße,
Martin
Hallo Martin,
ich habe das in etwa auf diesem (o.g.) Weg geklärt. Hätte Dir gern mal einen Bildschirmausschnitt von der Lösung geschickt. Vielleicht hättest du ja noch eine Idee gehabt, wie es besser geht.
Schöne Grüße,
Micha
Hallo Martin!
Gibt es eine Möglichkeit eine Zelle so zu formatieren, dass sie ( oder der Inhalt ) blinkt, wenn ein bestimmter Wert erreicht ist? Ich bedanke mich schon mal im Voraus!
Hallo Gerhard,
nein, eine solche Möglichkeit gibt es (zum Glück!) nicht in Excel 🙂
Du musst dich leider mit normalen Einfärbungen begnügen.
Schöne Grüße,
Martin
Hallo Martin,
ich möchte in Excel Zellen so formatieren, dass wenn ich eine 12-stellige Zahl eingebe, die Punkte dann an folgenden Positionen sind:
#.####.######.#
Wenn ich das so bei der Benutzerdefinierten Kategorie eingebe, ändert er immer automatisch auf ###.###.###.###.
Kannst Du mir hier weiterhelfen?
Grüße Dominik
Hallo Dominik,
mit den Punkten wird es nicht funktionieren, denn die werden von Excel immer als Tausendertrennzeichen interpretiert. Wenn Du stattdessen Bindestriche verwendest, klappt es.
Schöne Grüße,
Martin
Es geht mit dem folgenden Zahlenformat auch mit Punkten
0″.“0000″.“000000″.“0
Hallo Claus,
sehr cool, vielen Dank. Wieder was dazugelernt 🙂
Schöne Grüße,
Martin
Hallo Martin,
gute Zusammenfassung, danke!
Ich scheitere noch bei einem Problem, das wie folgt aussieht.
Zelle hat einen Wert einer Projektwoche von z.B. -2 bis in den positiven Bereich (z.B. +10).
Über den Benutzerdefinierten Typ kommt dann ein „Projektwoche“ ## dazu.
Bei positiven Werten gibt es es korrekt aus = Projektwoche 10; bei negativen erscheint -Projektwoche 2.
Wie kann ich Ihm denn das Minuszeichen nach hinten ziehen damit Projektwoche -2 erscheint?
Ich vermute DANN funktionieren die Farben auch wie von Dir beschrieben 😉
Danke,
Floh
Hallo Floh,
du musst dein benutzerdefiniertes Format nur explizit für negative Werte erweitern, zum Beispiel so:
„Projektwoche „##;“Projektwoche „-##
Der erste Teil ist für die positiven Werte, der zweite für die negativen Werte
Schöne Grüße,
Martin
Hallo,
wie kann ich in Excel eine achtstellige Zahl bei „Benutzerdefiniert“ so formatieren, dass diese wie folgt dargestellt wird? 20205601 soll in 2020.5601 formatiert werden – alles ohne Lehrzeichen. Die ersten vier Ziffern sind immer eine Jahreszahl und die anderen vier Ziffern sind fortlaufende Nummern.
Danke für die Hilfe
Hallo Bernhard,
versuch’s mal mit diesem benutzerdefinierten Formatcode:
0000″.“0000
Schöne Grüße,
Martin
Hallo Martin,
ja das funktioniert. Vielen Dank, das ist sehr wertvoll für meine Arbeit. Danke auch für die Schnelligkeit.
Liebe Grüße Bernhard
Hallo,
kann man bei benutzerdefinierten Formatierungen auch Tiefstellungen erreichen (z.B. wenn ich H2O oder 0.0 n(tiefgestellt)n+x als Schrift vor/nach Zahl haben muss)
Grüße,
Hallo,
nein, solche Tiefstellungen sind über benutzerdefinierte Formate leider nicht möglich. Das geht nur manuell direkt in der Zelle.
Schöne Grüße,
Martin
Hallo Martin!
Zum Jahresbeginn eine neue Exelaufgabe: Darin: Die Null ist mein Problem!
Es sollen NULL-Werte in einen Auswertungsbogen übernommen werden in dem Format I – I
Was jetzt erscheint ist I 0,00 I
Es sollen Summenwerte z.B. 6,00 Std. (auch so formatiert) aus einem diesem Blatt mit =Blattnahme!C! in eine Auswertung übernommen werden. Wenn im Ursprungsblatt dann 0,00 Std. steht soll in dem Auswertungsbaltt bitte I – I erscheinen. Bisher finde ich da keine Formatierung außer der Buchhaltung ohne Währungssymbol. Damit allerdings bekomme ich Probleme mit der Spaltenbreite.
Megatolle Infoseite, die Du da aufgebaut hast!
Ich bin sehr froh! Kommt jetzt als Button auf die Leiste! Vielen Dank dafür!
Die-vor-der-Tastatur-sitzt
Sille
Hallo Sille,
versuch’s mal mit diesem benutzerdefinerten Format:
0,00″ Std.“;-0,00″ Std.“;“|-|“
Schöne Grüße,
Martin
😀
Hallo Martin,
jetzt ist das H mit Ohren
“|-|“
in den Zellen, wo vorher eine 0 war… *kraisch*
Ich probier noch was rum… ich hab wohl die Mechanik verstanden (glaub ich)
…. Zeit vergeht
tschakka! ich hab’s
0,00;-0,00;-
…das ist die Formatierung, die ich brauche.
In den Zellen steht jetzt schön 2,00 oder 1,75 (ohne die Std.) und wenn der Wert aus dem anderen Tabellenblatt =NULL ist, dann kommt der Strich (Minuszeichen).
Das ist für den schmalspaltigen Abrechnungsbogen für Kurzarbeit, den die öffiziöse Agentur entworfen hat. Ich brauche das jetzt nur noch zur Ausgabe und Berechnung der Differenz (=Fehlstunden, bzw. zu kurz gekommene Arbeit)
Darf ich Dich bei facebook empfehlen?
Ich bin begeistert! (… bin aber auch heimliche Exel-Spielerin)
Sille
Hallo Sille,
schön zu hören, dass du jetzt eine Lösung hast. Und über eine Empfehlung – ganz gleich wo – freue ich mich natürlich immer 🙂
(auch wenn ich selbst nicht mehr auf Facebook vertreten bin)
Schöne Grüße,
Martin
Hallo Herr Weiß,
erstmal vielen Dank, dass Sie Ihr Wissen hier zur Verfügung stellen. Mir hat Ihre Homepage schon in einigen Fällen sehr geholfen. Ich arbeite häufiger mit benutzerdefinierte Formaten, konnte mir aber bislang die Bedeutung des @-Zeichens bei den Statthaltern nicht erklären. Können Sie mir da weiter helfen?
Viele Grüße von der schönen Bergstraße
Uwe Turner
Hallo Herr Turner,
freut mich zu hören, wenn Ihnen die Artikel hier gelegentlich weiterhelfen. Was das @-Zeichen angeht: Das steht für einen Textplatzhalter. Probieren Sie folgendes benutzerdefinierte Zahlenformat aus:
0;-0;0;“–>“@“<--" Wenn Sie jetzt in die Zelle beispielsweise abc eintippen, wird nun -->abc<-- angezeigt. Das @ steht also für den Zelleninhalt, wenn es sich um einen Text handelt. Schöne Grüße, Martin
Hallo Martin,
ich habe eine Frage:
ich weis nicht, wie weit Sie sich mit Ablaufsteuerungen auskennen. SPS? Steuerungen im Maschinenbau?
Ich suche eine Tabelle, Möglichkeit, unter Angabe von Zeit Tabelle und Diagramm zu erzeugen.
Ein Ablauf beging mit 0sec. und benötigt 15sec. Wenn ich diesen Betrag (Zeit) eingebe, baut sich gleichzeitig ein Balken auf, der die Zeit wiedergibt. Ein Ablauf ist ein Prozeß von aufeinander und parallelen Zeiteinheiten.
Gibt es sowas? Kann das was mit Grantt zu tun haben? Würde mich über eine Aussage freuen, Danke.
Hallo Falk,
die Frage hat zwar nichts mit dem Artikel hier zu tun, aber ein paar kleine Tipps für weitere eigene Recherchen möchte ich dennoch geben. Es gibt verschiedene Möglichkeiten:
– Ein Balkendiagramm
– Sparklines
– Bedingte Formatierung mit Datenbalken
– Ein Pseudo-Balkendiagramm innerhalb einer Zelle mit Hilfe der WIEDERHOLEN-Funktion
Für die meisten der genannten Möglichkeiten gibt es hier auf dem Blog eigene Artikel. Einfach die Suchfunktion rechts oben nutzen.
Vielleicht hat das ein wenig weitergeholfen.
Schöne Grüße,
Martin
Hallo Martin, ich möchte eine Word-Tabelle in Exel einfügen, was bisher fast geklappt hat. Ich habe in der 2. Spalte Werte in folgendem Format: 6.01.2 oder 9.45.3 welche nach dem kopieren nicht so übernommen werden. Ich habe versucht ein benutzerdefiniertes Zahlenformat #0.00.0 einzustellen, was leider nicht übernommen wird. Was kann ich noch tun?
Hallo Martin,
hier noch zur Ergänzung der Link zur Tabelle: https://www.lars-baumgarten.de/alphabetische-%C3%BCbersicht/
Herzliche Grüße
Tabelle über Excel mit PowerQuery laden und entsprechend bearbeiten. Den Schritt der automatischen Konvertierung löschen, dann bleibt das Zahlenformat so wie in der Vorschau (als Textformat).
Beste Grüße
KD
Hallo,
leider finde ich keine Lösung über die ‚bedingte Formatierung‘ das von mir benötigte Format zu erstellen.
Ich arbeite mit Materialnummern, die so (##.##.###.###.###) aussehen. Excel nimmt wohl an, dass die Punkte Tausendertrennzeichen sind und baut die Ziffernkombination immer um.
Welche Formel muss ich eingeben, damit es läuft? Vielen Dank schonmal!
MFG
Jochen
Ich habe gerade weiter oben die Lösung gefunden.
Ein kleines Problem bleibt für mich noch: Es gibt für die Materialnummern oft verschiedene Entwicklungsstände, die als Index hinter der Ziffernfolge stehen in Form von Buchstaben. Wie bekomme ich die hin?
Hallo Jochen,
wenn Zahlen mit Buchstaben kombiniert werden, dann fürchte ich, werden solche Zahlenformate scheitern. Mir ist jedenfalls keine Lösung bekannt.
Schöne Grüße,
Martin
Hallo Jochen,
könnte das, was Du als Endprodukt brauchst, nicht auch über 2 Spalten zu realisieren sein?
LG Sille
Hallo Zusammen
meine lieben Arbeitskollegen missbrauchen das Datumsformat mit falscher Schreibweise. Bei der falschen Schreibweise soll die Zelle Rot ausgefüllt werden und bei der richtigen Schreibweise bedarf es keiner Füllfarbe. Kann mir jemand Helfen.
Richtige Schreibweise:
DD.MM.JJ
KW../JJ
Falsche Schreibweise:
KW7 / 2023
KW 02 2023
Q1/21
KW 04/19
KW08-12/21
ca. April 2021
12.01-20.02.2021
Frühling 2022
KW30 / 2023
kein Wert
Hallo Älpler,
ich fürchte, das lässt sich nur mit einer bedingten Formatierung und dort mit einer ziemlich komplizierten Formeln umsetzen. Ich probier’s mal:
Relevante Zellen markieren
Menü „Start | Bedingte Formatierung | Neue Regel“
Regeltyp = „Formel zur Ermittlung der zu formatierenden Zeilen verwenden“, dann die folgende Formel eingeben:
=NICHT(ODER(ISTZAHL(C1);UND(LINKS(C1;2)=“KW“;ISTFEHLER(FINDEN(“ „;C1));LINKS(RECHTS(C1;3);1)=“/“;LÄNGE(C1)<=9))) Diese Formel ist sicherlich nicht wasserdicht, aber fängt zumindest die von dir genannten falschen Schreibweisen ab. Schöne Grüße, Martin
Ja, Excel ist so vielfältig und da das richtige Netzwerk zu finden ist schon eine Aufgabe.
Danke für diesen Artikel!
In meinem Fall fehlt mir noch eine Antwort, ob man PLZ und Ort in einer gleichen Zelle eintragen kann, wobei die ersten 6 Zeichen (PLZ + Leerzeichen) ausgeblendet sind. Nullen kann man wohl ausblenden, aber wie ist das in dieser Frage?
Hintergrund ist, dass ich eigentlich nach aktuellem Stand nur den Ort also Stadt/Dorf benötige. Aber zwecks Abgrenzung wollte ich sicherheitshalber doch noch die PLZ hinterlegen.
Vielleicht hat da jemand was für mich als Lösung, ohne Abschneiden von Links und Zusatzzellen und Formeln, im ersten Lösungsansatz.
Freundliche Grüße
Marcotsch
Hallo Marcotsch,
tut mir leid, aber für diese Anforderung sehe ich leider keine Lösungsmöglichkeit über ein benutzerdefiniertes Format oder ähnliches.
Schöne Grüße,
Martin
Ok, aber Dankeschön.
VG
Marcotsch
Hallo Martin,
ich kämpfe mich hier gerade durch Deine Tips. Herzlichen Dank.
Ich stehe vor einem, für dich kleinen Problem, dass ich einige Zellen formatieren möchte. Dank der Hilfe hier habe ich schon geschafft Leerstellen einzufügen.
DE0362694021 steht in der Zelle und ich suche eine Lösung mir eine Standarformatvorlage zu basteln für:
DE 03 626 94021 ! wobei die letzten 5 Zahlen einen Schriftgrad grösser und FETT sein sollen.
Über die benutzerdefinierte Formatierung komme ich nicht weiter, da hier die Buchstaben im Weg sind.
Dann habe ich es mit Text in Spalten getrennt ( hier verschwinden leider die Nullen, was auf keinen Fall passieren darf) dann formatiert.
Mit der Formel: = Teil( Zelle; Zahlposition ;wie viele Ziffern) &“ “ & usw. habe ich die Leerzeichen fest aber die letzte 5 nicht formatiert und das ganz noch nirgendwo fest abgespeichert .
Ist aber keine schöne Lösung und ich bin faul, weswegen ich gern mit Excel arbeite:-)
Git es eine einfache Lösung für diese Situation? Ja- wie sieht diese aus ?
Danke Silke
Hallo Silke,
ich fürchte, da hast du schlechte Karten. Mit einem benutzerdefinierten Format bekommt man so etwas nicht hin. Bleibt also nur die Formellösung:
=LINKS(A1;2)&“ „&TEIL(A1;3;2)&“ „&TEIL(A1;5;3)&“ „&RECHTS(A1;5)
Aber eine abweichende Formatierung der letzten Stellen ist auch damit nicht möglich. Das geht bestenfalls per VBA.
Schöne Grüße,
Martin
oh meine schlimmsten Befürchtungen sind wahr geworden 🙂
ganz herzlichen Dank Martin. Nun muss ich nicht mehr suchen und probieren.
Danke für Deinen tollen Newsletter. Der bringt immer wieder frischen Wind in den Alltag.
Silke
Vielen Dank für die guten Hinweise.
Ich würde gern mein von mir definiertes benutzerdefinierte Format dauerhaft in die Formatliste aufnehmen, da ich es häufig nutze. Bisher sehe ich mich bei jeder neuen Tabelle dazu gezwungen, das gewünschte Format auch wieder neu zu definieren.
Im Voraus vielen Dank.
Dieter
09/2021
Hallo Dieter,
du kannst dir eine Standardvorlage-Mappe erstellen, in der du das Format definierst. Danach enthalten alle zukünftigen Excel-Mappen die dort eingestellten Formate.
Im Excel-Quickie Nr. 96 ist beschrieben, wo und wie diese Vorlagenmappe zu speichern ist:
https://www.tabellenexperte.de/excel-quickies-im-maerz-2017/
Schöne Grüße,
Martin
Hallo, so weit so gut. Hättest du eine Lösung (ohne VBA) für Dezimalstellen bei Bedarf? Sprich…benutzerdefiniertes Format.
Es sollten dabei die tausender Trennzeichen dennoch zu sehen sein. Bsp., 2.548.254,000340021 und wenn alles Null hinter dem Komma dann eben nur 2.548.254 (ohne Komma wie bei #.##,###).
Ich habe schon alles versucht und gegoogelt, aber es ist nichts zu finden was vernünftig ist.
Danke
Hallo René,
ich fürchte, das lässt sich mit einem benutzerdefinierten Format nicht lösen.
Schöne Grüße,
Martin
Hallo,
wie kann man die hier vorgestellten benutzerdefinierten Formate von Zellen in anderen Zellen abrufen/auslesen?
Ich habe verschiedene Währungen (z.B. AUD) fix vor einer Zahl in einer Tabelle angegeben. Wenn ich diese Zahl(-en) über eine Formel abrufe/auslesen möchte, dann nimmt Excel dieses benutzerdefinierte Format (AUD) nicht mit, sondern nur die Zahl.
Gibt es eine Möglichkeit, auch dieses benutzerdefinierte Format mit der Zahl gemeinsam abzurufen?
Danke!
LG!
Hallo Oliver,
nein, das ist so nicht möglich, ausgelesen wird immer nur der Wert der Quellzelle, nicht das Format. Du könntest dir also bestenfalls in der Zielzelle beispielsweise über eine WENN-Abfrage in Kombination mit der TEXT-Funktion das entsprechende Format zurechtbasteln.
Schöne Grüße,
Martin
Hallo Martin
Ich habe eine Tabelle mit mehreren Artikeln. bei einigen ist die Stückzahl 1 bei den anderen eine mehrzahl.
Im endergebniss brauche ich aber das zum Beispiel stuckzahl 5 auf 5 zeilen verteilt wird, komplett mit dazugehörigen Spalten. Geht das ?
Es ist immer sehr mühsam erst lehre zeilen einfügen, dann den Inhalt kopieren und ind die Zeilen eintragen und dann die Mängen auf 1 setzen.
Mit freundlichen Grüßen
Peter
Hallo Peter,
deine Frage hat jetzt zwar nichts mit dem Artikel oben zu tun, aber die Antwort ist:
Nein, leider geht so etwas nicht. Zumindest nicht mit „normalen“ Mitteln. Da müsste man vermutlich mit VBA-Programmierung ran.
Schöne Grüße,
Martin
Hallo Herr Weiss,
sehr gut präsentiert.
Ich vermisse nur eine kleine Angabe bei dieser Formatierung:
#0,0. „km“ der Zellwert beleibt 5500 und wenn ich dann mit der Zelle in gutem Glauben da sehe ich 5,5 km, weiterrechne
führt eine Multiplikation zu einem falschen Ergebnis weil ja mit 5500 gerechnet wird.
Hallo Herr Ulm,
das haben Sie natürlich recht, hier müsste neben dem Eingabefeld irgendwo ein Hinweis hin, dass die Eingabe in Meter erfolgt ist. Das ist in der Tat ein Nachteil, wenn man damit weiterrechnen muss.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine wertvollen Tipps!
An einer Stelle hackt es bei mir leider immer. Im deutsch eingestellten Excel sollen die Trennzeichen in Diagramm unter Achsenoptionen Zahl Rubrik Benutzerdefiniert mit Punkt statt Komma eingestellt werden können (also 0.0 statt 0,0). Es werden immer wieder beide Formate benötigt, weshalb es keinen Sinn macht, die Excel Sprache umzustellen. Mein Versuch
Typ 0.000 endet leider in Zahlen von 0.040 statt 40.0
Typ ##.#00 endet in 40 statt 40.0
Gibt es dafür eine Lösung?
Hallo Nadine,
ich fürchte, du musst dich für eine Variante entscheiden. Der Dezimaltrenner und der Tausendertrenner können in den Excel-Optionen unter der Kategorie „Erweitert“ zwar individuell eingestellt werden. Aber diese Einstellung gilt dann halt auch Excel-weit. Ich wüsste keine Lösung, wie man das umgehen könnte.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den hilfreichen Beitrag.
Meine Anwendung geht vermutlich noch etwas weiter.
Ich habe in einer Übersicht verschiedene Kosten pro Einheit aufgelistet. (0,23€/kWh / 0,23 €/kg…), die mit benutzerdefinierten Zahlenformatierungen festgelegt wurden. Nicht von mir… Ich möchte das jetzt durch die Angabe anderer Währungen erweitern (USD; AUD…)
Hierfür habe ich eine Dropdownliste erstellt, in der alle Währungen hinterlegt sind.
Mein Ziel ist es, dass wenn ich eine andere Währung auswähle, diese auch in der benutzerdef. Formatierung angezeigt werden.
Also: Dropdown USD -> Änderung der Zelle in 0,23 USD/kWh statt 0,23 €/kWh.
Kann man die Formel der benutzerdef. Formatierung so abändern, dass sich der „Währungsteil“ der Formatierung aus der Dropdown-Zelle zieht und aktualisiert? Also quasi eine Verknüpfung innerhalb der Formatierung? Oder ist das nicht möglich?
-> Eine Umrechnung ist nicht nötig, nur der reine „Währungstext“
Vielen Dank im Voraus.
Gruß Luis
Hallo Luis,
so etwas lässt sich nicht über ein benutzerdefiniertes Zahlenformat abbilden. Aber du könntest stattdessen eine bedingte Formatierung einrichten, dort ist man wesentlich flexibler und es sollte kein Problem sein.
Schöne Grüße,
Martin
Hallo Herr Weiß,
vielen lieben Dank für die hilfreicheb Tipps. Leider konnte ich für mein Problem keine Lösung finden. Ich habe einzelne Zahlen, mal eine mal fünf und alles sollen am Ende auf insgesmt 9 Ziffern mit Nullen aufgefüllt werden. Können Sie mir helfen? Herzlichen Dank!
Hallo Melanie,
verwenden Sie einfach folgendes benutzerdefinierte Zahlenformat:
000000000
Damit wird jede Zahl 9stellig mit entsprechend vielen führenden Nullen.
Schöne Grüße,
Martin