Excel benutzerdefinierte Zahlenformatierung ist die Kleidung für Daten in Excel-Zellen. Diese können Sie nach Belieben anziehen. Alles, was Sie brauchen, ist ein wenig Know-how über die Funktionsweise des benutzerdefinierten Zahlenformats von Excel.
Mit der benutzerdefinierten Zahlenformatierung in Excel können Sie die Anzeige der Werte in den Zellen ändern, aber gleichzeitig den ursprünglichen Wert beibehalten.
Zum Beispiel kann ich einen Datumswert in verschiedenen Datumsformaten anzeigen lassen, während der zugrunde liegende Wert (die Zahl, die das Datum darstellt) gleich bleibt.
In diesem Tutorial werde ich alles über das benutzerdefinierte Zahlenformat in Excel behandeln und Ihnen auch einige wirklich nützliche Beispiele zeigen, die Sie in Ihrer täglichen Arbeit verwenden können.
Also lasst uns anfangen!
Excel benutzerdefiniertes Zahlenformat-Konstrukt
Bevor ich Ihnen die Großartigkeit zeige, möchte ich kurz erklären, wie die benutzerdefinierte Zahlenformatierung in Excel funktioniert.
Excel kann standardmäßig die folgenden vier Datentypen in einer Zelle akzeptieren:
- Eine positive Zahl
- eine negative Zahl
- 0
- Textstrings (dies kann sowohl reine Textstrings als auch alphanumerische Strings umfassen)
Für jede Zelle in einem Arbeitsblatt in Excel können Sie angeben, wie jeder dieser Datentypen in der Zelle angezeigt werden soll.
Und unten ist die Syntax, in der Sie dieses Format angeben können.
;;;
Beachten Sie, dass alle diese durch Semikolons (;) getrennt sind.
Alles, was Sie in eine Zelle in Excel eingeben, fällt in eine dieser vier Kategorien und daher kann ein benutzerdefiniertes Format dafür angegeben werden.
Wenn Sie nur erwähnen:
- Ein Format: Wird auf alle vier Abschnitte angewendet. Wenn Sie beispielsweise nur Allgemein schreiben, wird dies für alle vier Abschnitte angewendet.
- Zwei Formate: Die erste wird auf positive Zahlen und Nullen angewendet, die zweite auf negative Zahlen. Das Textformat wird standardmäßig zu Allgemein.
- Drei Formate: Der erste wird auf positive Zahlen angewendet, der zweite wird auf negative Zahlen angewendet, der dritte wird auf Null angewendet und der Text verschwindet, da für Text nichts angegeben ist.
Wenn Sie mehr über die benutzerdefinierte Zahlenformatierung erfahren möchten, empfehle ich Ihnen den Abschnitt Office-Hilfe.
Wie dies funktioniert und wo diese Formate eingegeben werden, wird später in diesem Tutorial beschrieben. Denken Sie vorerst daran, dass dies die Syntax für das Format für jede Zelle istSo funktioniert das benutzerdefinierte Zahlenformat in Excel
Dann öffnen Sie eine neue Excel-Arbeitsmappe, standardmäßig haben alle Zellen in allen Arbeitsblättern in dieser Arbeitsmappe das allgemeine Format, wobei:
- Positive Zahlen werden unverändert angezeigt (linksbündig)
- Negative Zahlen werden mit einem Minuszeichen (und linksbündig) dargestellt
- Null wird als 0 angezeigt (und nach links ausgerichtet)
- Text wird unverändert angezeigt (rechtsbündig)
Und falls Sie ein Datum eingeben, wird es basierend auf Ihrer regionalen Einstellung angezeigt (im Format TT-MM-JJJJ oder TT-MM-JJJJ).
Standardmäßig lautet die Zahlenformatierung der Zelle:
Allgemein;Allgemein;Allgemein;Allgemein
Wo jeder Datentyp als Allgemein formatiert ist (und die oben genannten Regeln befolgen, je nachdem, ob es sich um eine positive Zahl, eine negative Zahl, 0 oder Text handelt)
Jetzt ist die Standardeinstellung in Ordnung, aber wie ändert man das Format. Wenn ich beispielsweise möchte, dass meine negativen Zahlen in roter Farbe angezeigt werden oder meine Daten in einem bestimmten Format angezeigt werden, wie mache ich das?
Verwenden des Format-Dropdown-Menüs in der Multifunktionsleiste
Einige der am häufigsten verwendeten Zahlenformate finden Sie auf der Registerkarte Start in der Gruppe Zahlen.
Es ist eine Dropdown-Liste, die viele der häufig verwendeten Formate anzeigt, die Sie auf eine Zelle anwenden können, indem Sie einfach die Zelle auswählen und dann das Format aus dieser Liste auswählen.
Wenn Sie beispielsweise Zahlen in Prozentsätze oder Daten in ein kurzes Datumsformat oder langes Datumsformat konvertieren möchten, können Sie dies ganz einfach mithilfe der Optionen in der Dropdown-Liste tun.
Verwenden des Dialogfelds Zellen formatieren
Dialogfelder zum Formatieren von Zellen, in denen Sie maximale Flexibilität beim Anwenden von Formaten auf eine Zelle in Excel erhalten.
Es gibt bereits viele vorgefertigte Formate, die Sie verwenden können, oder wenn Sie Ihr eigenes benutzerdefiniertes Zahlenformat erstellen möchten, können Sie dies auch tun.
So öffnen Sie das Dialogfeld Zellen formatieren;
- Klicken Sie auf die Registerkarte Start
- Klicken Sie in der Gruppe Nummer auf das Symbol für das Startprogramm des Dialogfelds (der kleine geneigte Pfeil unten rechts in der Gruppe).
Dadurch wird das Dialogfeld Zellen formatieren geöffnet. Darin finden Sie alle Formate auf der Registerkarte Zahl (zusammen mit der Option, benutzerdefinierte Zellformate zu erstellen).
Sie können auch die folgende Tastenkombination verwenden, um das Dialogfeld Zellen formatieren zu öffnen:
STRG + 1 (alle STRG-Taste und dann die 1-Taste drücken).
Falls Sie einen Mac verwenden, können Sie CMD + 1
Sehen wir uns nun ein paar praktische Beispiele an, in denen wir unsere eigenen benutzerdefinierten Zahlenformate erstellen.
Beispiele für die Verwendung der benutzerdefinierten Zahlenformatierung in Excel
Sehen wir uns nun ein paar praktische Beispiele an, in denen wir unsere eigenen benutzerdefinierten Zahlenformate erstellen.
Ausblenden eines Zellenwerts (oder Ausblenden aller Werte in einem Zellbereich)
Manchmal müssen Sie möglicherweise den Inhalt der Zelle ausblenden (ohne die Zeile oder Spalte mit der Zelle auszublenden).
Ein praktischer Fall hierfür ist, wenn Sie ein Excel-Dashboard erstellen und einige Werte im Dashboard verwenden, die Benutzer jedoch nicht sehen sollen. so können Sie diese Werte einfach ausblenden
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten alle numerischen Werte ausblenden:
So machen Sie dies mit benutzerdefinierter Formatierung in Excel:
- Wählen Sie die Zelle oder den Zellbereich aus, für die Sie den Zellinhalt ausblenden möchten
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
;;;
Wie funktioniert das?
Das Format für vier verschiedene Arten von Datentypen wird durch Semikolons in folgendes Format unterteilt:
;;;
Bei dieser Technik haben wir das Format entfernt und nur das Semikolon angegeben, was angibt, dass wir keine Formatierung für die Zellen wünschen. Dies ist ein Hinweis für Excel, alle Inhalte in der Zelle auszublenden.
Beachten Sie, dass dies den Inhalt der Zelle zwar ausblendet, aber immer noch vorhanden ist. Wenn Sie also Zahlen haben, die Sie ausgeblendet haben, können Sie diese weiterhin in Berechnungen verwenden (was dies zu einem großartigen Trick für Excel-Dashboards und -Berichte macht).
Negative Zahlen in roter Farbe anzeigen
Negative Zahlen werden standardmäßig mit einem Minuszeichen (oder in einigen regionalen Einstellungen in Klammern) angezeigt.
Sie können die benutzerdefinierte Zahlenformatierung verwenden, um die Farbe der negativen Zahlen zu ändern und sie in roter Farbe anzuzeigen.
Angenommen, Sie haben ein Dataset wie unten gezeigt und möchten alle negativen Werte rot hervorheben.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie den Zellbereich aus, in dem negative Zahlen rot angezeigt werden sollen
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
Allgemein;[Rot]-Allgemein
Das obige Format lässt Ihre Zellen mit negativen Werten rot erscheinen.
Wie funktioniert das?
Wenn Sie nur zwei Formate als benutzerdefiniertes Zahlenformat angeben, wird das erste für positive Zahlen und 0 und das zweite für negative Zahlen berücksichtigt (der nächste Wert wird standardmäßig zum allgemeinen Format).
Im obigen Format, das wir verwendet haben, haben wir in eckigen Klammern die Farbe angegeben, die dieses Format annehmen soll.
Während die negativen Zahlen das allgemeine Format annehmen, werden sie in roter Farbe mit einem Minuszeichen angezeigt.
Falls Sie die negativen Zahlen in roter Farbe in Klammern (anstelle des Minuszeichens) stoppen möchten, können Sie das folgende Format verwenden:
Allgemein;[Rot](Allgemein)
Text zu Zahlen hinzufügen (z. B. Millionen/Milliarden)
Eine erstaunliche Sache bei der benutzerdefinierten Zahlenformatierung ist, dass Sie einer Zahl ein Präfix oder einen Suffixtext hinzufügen können, während Sie die ursprüngliche Zahl in der Zelle beibehalten.
Zum Beispiel kann ich "100" als "100 Millionen US-Dollar" anzeigen lassen, während ich immer noch den ursprünglichen Wert in der Zelle habe, damit ich ihn in Berechnungen verwenden kann.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten diese Zahlen mit einem Dollarzeichen und dem Wort Million davor anzeigen.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie den Datensatz aus, in dem Sie den Zahlen den Text hinzufügen möchten
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
$General "Million"
Beachten Sie, dass sich dieses benutzerdefinierte Zahlenformat nur auf Zahlen auswirkt. Wenn Sie Text in dieselbe Zelle eingeben, wird er so angezeigt, wie er ist.
Wie funktioniert das?
In diesem Beispiel habe ich dem allgemeinen Format das Wort Million in doppelten Anführungszeichen hinzugefügt. Alles, was ich dem Format in doppelten Anführungszeichen hinzufüge, wird so angezeigt, wie es in der Zelle ist.
Ich habe auch das Dollarzeichen vor dem allgemeinen Format hinzugefügt, damit alle meine Zahlen auch das Dollarzeichen vor der Zahl anzeigen.
Hinweis: Wenn Sie sich fragen, warum das Dollarzeichen nicht in doppelten Anführungszeichen steht, während das Wort Million in doppelten Anführungszeichen steht, liegt das daran, dass Excel Dollar als Symbol erkennt, das häufig in benutzerdefinierten Zahlenformatierungen verwendet wird und Sie nicht benötigt um doppelte Anführungszeichen hinzuzufügen. Sie können dem Dollarzeichen doppelte Anführungszeichen hinzufügen, aber wenn Sie das Dialogfeld Zellen formatieren schließen und erneut öffnen, werden Sie feststellen, dass Excel es entfernt hat
Zahlen und Text verkleiden
Mit der benutzerdefinierten Zahlenformatierung können Sie maximal zwei Bedingungen bewerten und basierend auf diesen Bedingungen können Sie der Zelle ein Format zuweisen.
Angenommen, Sie haben eine Liste mit Ergebnissen für einige Schüler, Sie möchten den Text Bestehen oder Nicht bestanden basierend auf der Punktzahl anzeigen (wobei jede Punktzahl unter 35 "Nicht bestanden" anzeigen sollte, andernfalls sollte "Bestanden" angezeigt werden.
Im Folgenden sind die Schritte aufgeführt, um Ergebnisse als Bestanden oder Nicht bestanden anstelle des Werts im benutzerdefinierten Zahlenformat anzuzeigen:
- Wählen Sie den Datensatz aus, in dem Sie die Ergebnisse haben
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
[<35]"Nicht bestanden";"Bestanden"
Unten sehen Sie, wie Ihre Daten aussehen würden, nachdem Sie das obige Format angewendet haben.
Beachten Sie, dass der Wert in den Zellen dadurch nicht geändert wird. Die Zelle weist weiterhin die Punktzahlen in numerischen Werten auf, während je nach Zellenwert „Bestanden“ oder „Nicht bestanden“ angezeigt wird.
Wie funktioniert das?
In diesem Beispiel muss die Bedingung in eckigen Klammern angegeben werden. Die benutzerdefinierte Zahlenformatierung wertet dann den Wert in der Zelle aus und wendet das Format entsprechend an.
Wenn der Wert in der Zelle also kleiner als 35 ist, wird die Zahl ausgeblendet und stattdessen der Text „Fail“ angezeigt, wobei alle anderen Fälle hinzugefügt werden, wird „Pass“ angezeigt.
Wenn Sie einen Zellbereich mit 0 und 1 in WAHR und FALSCH umwandeln möchten, können Sie das folgende Format verwenden:
[=0]"FALSCH";[=1]"WAHR"
Denken Sie daran, dass Sie in der benutzerdefinierten Zahlenformatierung nur zwei Bedingungen verwenden können. Wenn Sie versuchen, mehr als zwei Bedingungen zu verwenden, wird Ihre Eingabeaufforderung Sie darüber informieren, dass dieses Format nicht akzeptiert wird.
Text ausblenden, aber Zahlen anzeigen
Manchmal, wenn Sie Ihre Daten aus Datenbanken herunterladen, da leer sind, werden sie mit Werten wie Nicht verfügbar oder NA gefüllt oder -
Mit der benutzerdefinierten Zahlenformatierung können Sie alle Textwerte ausblenden, während die Zahlen unverändert bleiben. Dies stellt auch sicher, dass Ihre Originaldaten intakt sind (da Sie den Textwert nicht löschen, verstecken Sie ihn einfach)
Angenommen, Sie haben einen Datensatz wie unten gezeigt, in dem Sie alle Textwerte ausblenden möchten:
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie den Datensatz aus, in dem Sie die Daten haben
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
Allgemein; -Allgemein; Allgemein;
Beachten Sie, dass ich das allgemeine Format für Zahlen verwendet habe. Sie können jedes beliebige Format wählen (wie 0, 0.#, #0.0%). Beachten Sie außerdem, dass vor dem zweiten allgemeinen Format ein negatives Vorzeichen (-) steht, da es das Format für negative Zahlen darstellt.
Wie funktioniert das?
Da wir in diesem Beispiel alle Textwerte ausblenden wollten, haben wir einfach kein Format dafür angegeben.
Und da wir das Format für die anderen drei Datentypen (positive Zahl, negative Zahl und Null) angegeben haben, hat Excel verstanden, dass wir das Format für den Textwert absichtlich leer gelassen haben und es sollte nichts anzeigen, wenn eine Zelle einen Text enthält Schnur.
Zahlen als Prozentsätze anzeigen (%)
Mit der benutzerdefinierten Zahlenformatierung können Sie die Zahlen auch ändern und in Prozent anzeigen.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten diese Zahlen umwandeln, um den entsprechenden Prozentwert anzuzeigen:
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie die Zellen aus, in denen Sie die Zahlen haben (die Sie in Prozent umwandeln möchten).
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
0%
Dadurch werden die Zahlen in ihre Prozentsätze geändert. Sie ändert sich beispielsweise von 0,11 auf 11%.
Wenn Sie lediglich eine Zahl in den entsprechenden Prozentwert umwandeln möchten, können Sie dies besser tun, indem Sie die Prozentoption im Dialogfeld Zellen formatieren verwenden.
Eine benutzerdefinierte Formatierung kann nützlich sein, wenn Sie mehr Kontrolle darüber haben möchten, wie der Prozentwert angezeigt werden soll.
Wenn Sie beispielsweise zusätzlichen Text mit dem Prozentsatz anzeigen möchten, alle negativen Prozentsätze in roter Farbe anzeigen möchten, ist es besser, benutzerdefinierte Formatierungen zu verwenden
Zahlen in einer anderen Einheit anzeigen (Millionen als Milliarden oder Gramm als Kilogramm)
Wenn Sie mit großen Zahlen arbeiten, möchten Sie diese möglicherweise in einer anderen Einheit anzeigen. Wenn Sie beispielsweise über die Verkaufsdaten einiger Unternehmen verfügen, möchten Sie möglicherweise die Verkäufe in Millionen oder Milliarden anzeigen (wobei der ursprüngliche Verkaufswert in den Zellen beibehalten wird).
Glücklicherweise können Sie dies ganz einfach mit benutzerdefinierter Zahlenformatierung tun.
Angenommen, Sie haben einen Datensatz wie unten gezeigt und möchten diese Verkaufswerte in Millionen anzeigen.
Im Folgenden sind die Schritte dazu aufgeführt:
- Wählen Sie die Zellen aus, in denen Sie die Zahlen haben
- Öffnen Sie das Dialogfeld Zellen formatieren (Tastaturkürzel Strg + 1)
- Klicken Sie auf der Registerkarte Nummer auf Benutzerdefiniert
- Geben Sie im Feld Typ das folgende Format ein:
0.0,,
Das obige würde Ihnen das Ergebnis wie unten gezeigt geben (Werte in Millionen in einer Stelle nach dem Komma):
Wie funktioniert das?
Im obigen Format gibt eine Null vor dem Komma an, dass Excel die gesamte Zahl anzeigen soll (genau wie in der Zelle), und eine 0 nach dem Komma gibt an, dass nur eine signifikante Stelle nach dem Komma angezeigt werden soll.
0.0 wäre also immer eine Zahl mit einer Nachkommastelle.
Wenn Sie nach der 0.0 ein Komma hinzufügen, werden die letzten drei Ziffern der Zahl abgeschnitten. Zum Beispiel würde 123456 zu 123,4 werden (die letzten drei Ziffern werden entfernt, und da es 1 Stelle nach dem Komma anzeigen muss, wird 4 angezeigt).
Wenn Sie darüber nachdenken, ist es ähnlich, die Zahl durch 1000 zu teilen. Wenn Sie also eine Zahl in der Einheit "Millionen" anzeigen möchten, müssen Sie 2 Kommas hinzufügen, damit die letzten 6 Ziffern abgeschnitten werden.
Und wenn Sie auch das Wort Million oder das Alphabet M nach der Zahl anzeigen möchten, verwenden Sie das folgende Format
0.0,, "M"
