Wer eine Extrawurst braucht: Benutzerdefinierte Formate 12

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.



Kommentar erstellen

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

12 Gedanken zu “Wer eine Extrawurst braucht: Benutzerdefinierte Formate

  • Irmtraud

    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?

    • Martin Weiß Beitragsautor

      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

  • Thomas

    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.

    • Martin Weiß Beitragsautor

      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

      • Klexy

        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.

        • Martin Weiß Beitragsautor

          Hallo Klexy,

          richtig, mit VBA gibt’s natürlich auch elegante Lösungen. Danke dafür!

          Schöne Grüße,
          Martin

    • Stefan

      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

  • Rene

    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

    • Stefan

      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

  • Ramona

    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

    • Stefan

      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

  • Lisa

    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