Wer eine Extrawurst braucht: Benutzerdefinierte Formate 196

Mit benutzerdefinierten Formaten lassen sich in Excel auch ausgefallene Formatierungen für Zahlen umsetzen.
 

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

Zahlenformate auswählen

Damit gelangt man zum Fenster mit den verschiedenen Standard-Kategorieren für Zahlenformate:

Zahlenformate nach Kategorie

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

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

Geändertes Währungsformat

Und schon ist das gewünschte Ergebnis erreicht, wie man in Zelle A2 sieht:

Vorangestelltes Währungszeichen

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

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

Verwendung von Farben

Dieses Format wollen wir uns zum besseren Verständnis etwas näher ansehen:

Aufbau des Zahlenformats

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

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:
Und immer daran denken: Excel beißt nicht!

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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

196 Gedanken zu “Wer eine Extrawurst braucht: Benutzerdefinierte Formate

  • Avatar-Foto
    Robert

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Robert

        Hallo Martin,
        ja… der Umbruch nach einer bestimmten Anzahl Zeichen trifft es.
        Schade… aber danke für die Antwort 🙂

        Schöne Grüße,
        Robert

  • Avatar-Foto
    Dr. Ricco Lindner

    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Thomas Klein

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Annemarie Lang

    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

    • Avatar-Foto
      Martin Weiß

      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

    • Avatar-Foto
      Ferdinand Bogener

      Hallo Frau Lang!
      Das „Problem“ lieg wohl daran, dass Sie aus meiner Heimat Österreich stammen in der Verbindung mit den Ländereinstellungen „Österreich“. In Österreich wird das € Zeichen vor der Zahl gesetzt, in Deutschland und Luxemburg danach. Ich selbst verwende auch die Schreibweise aus Deutschland mit 0,00 € (Euro also nach der Zahl). Ich muss jedoch nichts umstellen, da die Systemeinstellungen bereits von Haus aus auf Deutschland einstellt sind und Excel „weiss“ das ich die Deutsche Schreibweise möchte.

      Am besten mal ausprobieren in den Zahlenformaten und dort bei Währung oder Buchhaltung die jeweilige Zahl einmal mit „€ Deutsch (Deutschland)“, „€ Deutsch (Luxemburg)“ und „€ Deutsch (Österreich)“ formatieren. In der Vorschau sieht man dann sofort, dass sich das € Symbol je nach Land dann vor der Zahl in Österreich bzw. nach der Zahl in Deutschland und Luxemburg zu sehen ist.

    • Avatar-Foto
      Ferdinand Bogener

      Nochmal die Formate bildlich dargestellt
      https://i.ibb.co/M73JBt1/Waehrung.jpg

      Kann man hier auch Bilder direkt einbinden?

      55,00 € _-€ * # ##0,00_-;-€ * # ##0,00_-;_-€ * „-„??_-;_-@_- Buchhaltung € (AT)
      € 55,00 € # ##0,00 Währung € – (AT)
      55,00 € _-* #.##0,00 [$€-de-LU]_-;-* #.##0,00 [$€-de-LU]_-;_-* „-„?? [$€-de-LU]_-;_-@_- Buchhaltung Luxemburg
      € 55,00 _-[$€-de-AT] * # ##0,00_-;-[$€-de-AT] * # ##0,00_-;_-[$€-de-AT] * „-„??_-;_-@_- Buchhaltung Österreich
      55,00 € _-* #.##0,00 [$€-de-DE]_-;-* #.##0,00 [$€-de-DE]_-;_-* „-„?? [$€-de-DE]_-;_-@_- Buchhaltung Deutschland
      -55,00 € #.##0,00 [$€-de-LU];[Rot]-#.##0,00 [$€-de-LU] Währung Luxemburg
      -€ 55,00 [$€-de-AT] #.##0,00;[Rot]-[$€-de-AT] #.##0,00 Währung Österreich
      -55,00 € #.##0,00 [$€-de-DE];[Rot]-#.##0,00 [$€-de-DE] Währung Deutschland
      55,00 € #.##0,00 [$€-de-DE] Währung Deutschland
      € 55,00 [$€-de-AT] # ##0,00 Währung Österreich

  • Avatar-Foto
    Monika B.

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Monika B.

        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

        • Avatar-Foto
          Martin Weiß

          Hallo Monika,

          sehr gerne geschehen, es freut mich, wenn du bei meinen Tipps fündig wirst.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Daniel

    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?

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Daniel

        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

        • Avatar-Foto
          Martin Weiß

          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

  • Avatar-Foto
    Sarah

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Barbara

    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

    • Avatar-Foto
      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

      • Avatar-Foto
        Barbara

        … sorry, hatte in vorherigem Kommentar nicht richtig kopiert; es soll in zweiter Zeile heißen: [=10]#,0
        Grüße
        Barbara

          • Avatar-Foto
            Martin Weiß

            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:
            Formatierungsregeln

            Schöne Grüße,
            Martin

  • Avatar-Foto
    Holger Weiß

    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!

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Frank

        Hallo Martin,
        Holger hatte ja nach „fett, orange“ gefragt. „Orange“ ist klar aber welche Syntax gibt es für „fett“?
        Viele Grüße
        Frank

        • Avatar-Foto
          Martin Weiß

          Hallo Frank,

          stimmt, das hatte ich übersehen. Nein, ein Fettdruck ist mit einem benutzerdefinierten Zahlenformat nicht zu machen, dazu bräuchte man dann eine bedingte Formatierung.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Sandra

    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Siegbert

        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

  • Avatar-Foto
    Andreas

    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Rupert

    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!

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Sebastian

    Hallo Martin,
    Vielen Dank für deine coolen Beiträge!

    Ich hätte eine frage zu meinem Problem und zwar will ich eine Formatierung erstellen wo ein bestimmter abschnitt Rot sein soll.

    Das wäre die Formatierung damit ich die „Formel =Heute“ benutzen kann um immer das richtige Datum zu erhalten, nur soll der Rückstand in Rot stehen.
    „Guthaben/Rückstand per“ TT.MM.JJJJ

    Vielen Dank im voraus

    Grüße,
    Sebastian

    • Avatar-Foto
      Martin Weiß

      Hallo Sebastian,

      da sieht es leider schlecht aus:
      Es ist weder mit benutzerdefinierten Zahlenformaten noch mit bedingter Formatierung möglich, nur Teile eines Textes einzufärben. Du könntest stattdessen mit einer WENN-Funktion prüfen, ob es sich um Guthaben oder Rückstand handelt und nur den entsprechenden Text ausgeben. Und den könntest du dann per bedingter Formatierung rot einfärben.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Ferdinand Bogener

    Hallo Herr Weiß,
    folgende Frage hätte ich zu den Zahlenformaten.
    Gibt es bei den Zahlenformaten eine Möglichkeit, dass sehr lange Zahlen vollständig dargestellt werden.
    z.B. die Zahl 12345678901234567890123456
    wird dann z.B. so dargestellt
    1,23456789012345E+25 Format: (0,00000000000000E+00)
    oder
    12.345.678.901.234.500.000.000.000 Format (#.##0)

    Ich würde allerdings gerne die vollständige Zahl in Dreierblöcken darstellen „12.345.678.901.234.567.890.123.456“

    Zwischenzeitlich habe ich mir so beholfen, dass die Zellen im Textformat sind (@). Dort kann ich wenigstens die vollständige Zahl darstellen „12345678901234567890123456“ allerdings nicht in Blöcken. Die Zahl als Text ist einmal grundsätzlich kein Problem, da damit nicht gerechnet werden muss. Gebe es also vielleicht eine Möglichkeit das Textformat entsprechend zu erweitern um zu den gewünschten Dreierblöcken zu kommen damit die Zahl (der Text) dann so aussieht: „12.345.678.901.234.567.890.123.456“

    Würde mich über eine Antwort freuen.

    Lg
    Ferdi

    • Avatar-Foto
      Martin Weiß

      Hallo Herr Bogener,

      es ist leider nicht möglich, solche großen Zahlen in Excel vernünftig als Zahl darzustellen. Der Grund dafür ist, dass Excel bei Zahlen eine maximale Genauigkeit von 15 Stellen hat, hier nachzulesen:
      Spezifikationen und Beschränkungen in Excel

      Um Ihre zweite Frage zu beantworten:
      Nein, Bilder können hier in den Kommentaren nicht direkt eingebunden werden.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Ferdinand Bogener

        Danke für Ihre Antwort. Das ich die Zahlen nicht vernünftig darstellen kann, davon bin ich schon ausgegangen.

        Noch meine Frage zum Text:
        Gebe es also vielleicht eine Möglichkeit das Textformat entsprechend zu erweitern /anzupassen um zu den gewünschten Dreierblöcken zu kommen damit die Zahl (der Text) dann so aussieht: „12.345.678.901.234.567.890.123.456“

        Lg
        Ferdinand Bogner

        • Avatar-Foto
          Martin Weiß

          Hallo Herr Bogner,

          tut mir leid, dafür habe ich leider auch keine vernünftige Lösung.
          Wenn die Zahlen immer gleich lang sind, könnte man mit Hilfe der TEIL-Funktion etwas in der Art basteln:
          =LINKS(A1;2)&“.“&TEIL(A1;3;3)&“.“&TEIL(A1;6;3)&“.“&TEIL(A1;9;3)&“.“&TEIL(A1;12;3)&“.“&TEIL(A1;15;3)&“.“&TEIL(A1;18;3)&“.“&TEIL(A1;21;3)&“.“&TEIL(A1;24;3)

          Aber Sie sehen schon, das wird ein ziemliches Formelmonster und hilft auch nicht weiter, wenn die Zahlen unterschiedliche viele Stellen haben.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Walter

    Hallo Martin,
    vielen Dank für den guten Beitrag. Ich habe noch MSO 2007 und lt. den MS-Spezifikation lassen sich benutzerdefinierte Formate nur in dem bearbeiteten Arbeitsblatt speichern. Kennst du vielleicht einen Trick mit dem man diese Einschränkung umgehen kann. Für mich ist das kein Problem, aber ich habe einen Chef (Kollegen), der ist damit echt überfordert, so was selber zu machen, insbesondere, wenn dann noch ein Sonderzeichen wie z.B. „Hoch 2“ für qm benötigt wird.
    Grüße Walter

    • Avatar-Foto
      Martin Weiß

      Hallo Walter,

      mir fallen zwei Möglichkeiten ein:
      Du könntest ein (leeres) Arbeitsblatt vorbereiten, in dem alle relevanten benutzerdefinierten Zahlenformate angelegt sind. Sozusagen als Vorlage. Und dieses Arbeitsblatt kopierst du dann in die Arbeitsmappe deines Chefs. Damit werden auch alle Formate übertragen.
      Oder du legst gleich eine leere neue Arbeitsmappe an, erstellst dort alle benutzerdefinierten Zahlenformate und speicherst die Mappe dann im XLTX-Format als Arbeitsmappenvorlage. Wenn diese Datei unter dem Namen Mappe.xltx im Startverzeichnis liegt, steht sie ab sofort für alle neuen Mappen zur Verfügung.
      Die Details habe ich in den Excel-Quickies 95 und 95 beschrieben:
      Excel-Quickies (Vol 28)

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Oliver

    Hallo Martin (oder Herr Weiß?),
    vielen Dank für die Erläuterungen! Ich stoße mit der benutzerdefinierten Formatierung gerade an eine Grenze, an der ich nicht weiterkomme: Ich würde gerne in einer Datums-Reihe nur jeweils den Monatsersten anzeigen, anderenfalls „“. Wenn ich jedoch eine Formel in eckigen Klammern eingebe (z.B. [=TAG(Zelle)=1]), so wird dies durch [=0] ersetzt. Sind Formeln hier grundsätzlich nicht möglich?

    Vielen Dank,
    Oliver

    • Avatar-Foto
      Martin Weiß

      Hallo Oliver,

      richtig, Formeln sind in benutzerdefinierten Zahlenformaten grundsätzlich nicht möglich, so etwas geht nur in bedingten Formatierungen. In deinem Fall wäre vermutlich eine Kombination aus bedingter Formatierung und benutzerdefiniertem Zahlenformat die Lösung. Das heißt, per Formel den Tag prüfen. Und wenn der nicht 1 entspricht, dann das benutzerdefinierte Zahlenformat ;;; verwenden. Das blendet den Zelleninhalt sozusagen aus.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Oliver

        Hallo Martin,
        vielen Dank, das funktioniert im Prinzip erstmal. Da hatte ich mich etwas verrannt.. Tatsächlich löst es aber noch nicht das eigentliche Problem, das mich hier umtreibt: Ich möchte für einen dynamischen Kalender in einer Zeile nur am Monatsersten den Monatsnamen anzeigen. Dabei soll die Spaltenbreite sehr klein sein, um eine Übersicht über einen längeren Zeitraum darstellen zu können. Da nun aber auch in den Zellen mit ausgeblendetem Inhalt eine Formel steht, funktioniert der Textüberlauf für den Monatsnamen nicht. Meine Hoffnung war, das mit dem Ausblenden des Zellinhalts beheben zu können..

        Beste Grüße,
        Oliver

  • Avatar-Foto
    Heiko Wesenberg

    Danke für die Optionen und den Tieferen Einblick in das Thema,
    Vielleicht hat ja noch jemand eine Idee zu einem anderen Problem..

    Ich habe Zellen die mit Inhalten gefüllt sind, etwa in dem Format
    4:12
    4:17
    4:20
    Wenn ich die Zellen in das Format [hh]:mm ändere ( Dieses Format benötige ich) passiert erstmal nix.
    Bearbeite ich die Zelle mit F2 und drücke Enter wird das Format automatisch übernommen.

    Angezeigt wird 04:12 und im Hintergrund ist 04:12:00 gespeichert.

    Ich muss sonst jede Zelle einzeln anfassen.

    Wie kann ich Excel dazu bewegen alle Zellen neu zu übernehmen?

    • Avatar-Foto
      Martin Weiß

      Hallo Heiko,

      ich bin nicht sicher, ob ich das Problem verstanden habe: Die Formatierung ist anscheinend richtig, oder? Und jetzt soll sie in alle Zellen mit einer Uhrzeit übernommen werden?
      Das geht nur, wenn die gewünschten Zellen erst markiert werden. Dann kann man in einem Rutsch das Format ändern. Es ist auch nicht möglich, ein generelles Zahlenformat für Uhrzeiten festzulegen, das dann immer verwendet wird.
      Oder habe ich dich falsch verstanden?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Stefan

    Hallo Martin,

    ich verwende öfters die wissenschaftliche Darstellung ##0,0E+0, die die Potenz immer in 1000er Schritten anzeigt (3,6,9,…). Meistens ändere ich die Stellenzahl noch auf 3 Stellen. Weiterhin füge ich oft physikalische Einheiten an dieses oder an das Standardformat, z.B. 0,00 „m/s“. Nun suche ich eine Möglichkeit diese häufig von mir benutzten Formate in die Standardvorlage zu intergieren, genauso wie es die anderen Formate sind. Gibt es eine sinnvolle Möglichkeit?

    • Avatar-Foto
      Martin Weiß

      Hallo Stefan,

      dazu musst du eine eigene Standardvorlage einrichten. Das heißt, die öffnest eine neue leere Arbeitsmappe und legst dort alle gewünschten Zahlenformate an. Dann speicherst du die Datei im *.xltx-Format mit dem Namen Mappe.xltx im Autostart-Verzeichnis von Excel (oder in einem persönlichen Autostart-Verzeichnis).
      Genaueres findest du in den beiden Excel-Quickies 95 und 96:
      https://www.tabellenexperte.de/excel-quickies-im-maerz-2017/

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Amelie

    Hallo Martin,

    ich würde gerne bei Rufnummern in Excel am Anfang eine 0 anzeigen lassen, sofern diese in der Rufnummer vorhanden ist.
    Ich habe bereits 0# probiert, allerdings wird hier bei jeder Zahl am Anfang eine 0 angefügt.

    Ich freue mich auf deine Antwort.
    Danke für deine Hilfe & Zeit.

    Mit lieben Grüßen
    Amelie

    • Avatar-Foto
      Martin Weiß

      Hallo Amelie,

      ja, das leidige Thema mit den führenden Nullen…
      Das Einzige, was zuverlässig hilft, ist, die betreffende Spalte vorher als Format „Text“ festzulegen. Wenn die Telefonnummern einschließend eingegeben werden, dann bleiben die Nullen erhalten – sofern man welche eingibt. Und ansonsten eben nicht.
      Mit einem benutzerdefinierten Format ist hier leider nichts zu holen.

      Falls du Microsoft 365 nutzt, gibt es noch eine gute Nachricht. Seit kurzem gibt es in den Excel-Optionen eine neue Einstellung, mit der man den Umgang mit führenden Nullen generell einstellen kann:
      „Excel-Optionen | Daten | Führende Nullen entfernen und in Zahl konvertieren“
      Diese Option muss nur abgewählt werden und alles ist ab sofort gut 🙂

      Ansonsten bleibt nur das Zellformat „Text“

      Schöne Grüße,
      Martin

  • Avatar-Foto
    markus brandlhofer

    hallo,

    ist es möglich mit excel zahlenreihen zu erstellen.
    z.b. 2 4 3 6 5 10 9 18 = 17 inzwischen anzeigt multipliziert+subtrahiert

    Vielen dank im voraus

    • Avatar-Foto
      Martin Weiß

      Hallo Markus,

      mir erschließt sich bisher noch nicht die Logik hinter dieser Zahlenreihe. Kannst du das bitte etwas näher erklären?

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas

    Hallo, gibt es auch eine Möglichkeit von einer 11 stellige Zahl nur die letzten 3 Stellen anzuzeigen, also für Wert 19504821620 soll in der Zelle 620 stehen. Wie die Funktion Rechts(A1;3). Die Funktion möchte ich nicht nutzen, da ich beim kopieren gerne die komplette Zahl in der Zwischenablage hätte. Natürlich gehen zwei Spalten, aber dann eventuell geht es ja doch in einer Kombination von ;; 000. Habe leider noch nichts passendes gefunden.

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      ich fürchte, das lässt sich nicht über ein Zahlenformat lösen, sondern nur über eine Funktion.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Andreas

        Hallo Martin. und Danke für Deine Antwort. Mach weiter so, ich stöbere sehr gerne mal in Deinen Seiten.

        Schöne Grüße,
        Andreas

  • Avatar-Foto
    Gerhard

    Hallo Martin, kannst du helfen?
    Welche Excel – Formel addierte folgende Ziffern?
    02:03:00
    Dieses Format bekomme ich, wenn ich ein Fußball – Ergebnis kopiere.
    Mich interessiert die gesamt Torzahl je Spiel.
    In dem Fall wären es 5 Tore.

    Freundliche Grüße
    Gerhard

  • Avatar-Foto
    Alexander Schütz

    Hallo Martin,
    vielen Dank für deine immer nützlichen Tipps und guten Erklärungen. Bei diesem Thema stört es mich seit längerem, das dies immer nur pro User funktioniert.
    Gibt es zwischenzeitlich die Möglichkeit dies für eine Gruppe, Team oder sogar ein ganzes Unternehmen einmal zu definieren und dann für alle zugänglich zu machen? Wenn ja, wie? Unser IT Dienstleister hat hier keinen Ansatzpunkt.
    Danke und Gruß

    • Avatar-Foto
      Martin Weiß

      Hallo Alexander,

      wenn bestimmte Formate generell verfügbar sein sollen, dann kannst du dafür eine eigene Standardvorlagenmappe definieren und die ins Autostart-Verzeichnis von Excel packen. Wie das geht, habe ich vor längerer Zeit mal hier beschrieben:
      Excel-Quickies (Vol 28)

      Diese Vorlage müsste dann für alle Anwender zur Verfügung gestellt werden. Vielleicht lässt sich so etwas auch per Policy zentral ausrollen, aber damit kenne ich mich leider nicht aus.

      Schöne Grüße,
      Martin