Wer eine Extrawurst braucht: Benutzerdefinierte Formate 23

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.

23 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

    • Martin Weiß Beitragsautor

      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

  • Laura

    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!

    • Laura

      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

      • Martin Weiß Beitragsautor

        Hallo Laura,

        das Feld für die Pivot-Tabelle bekommt man am leichtesten so weg:

        • den weißen Bereich markieren
        • Menüband: Start | Löschen | Alle löschen

        Das war’s.

        Schöne Grüße,
        Martin

    • Martin Weiß Beitragsautor

      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:
      Text-Platzhalter

      Detaillierte Infos zu den Platzhaltern gibt es auch auf der Support-Seite von Microsoft.

      Schöne Grüße,
      Martin

      • laura

        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.

        • verflixt

          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.

          • Martin Weiß Beitragsautor

            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

          • laura

            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.

  • Dieter Smets

    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

    • Martin Weiß Beitragsautor

      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