Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Basiswissen

Benutzerdefinierte Zellformate

Autor: Beate Schmitz - Erstellt: 2005-06      - Letzte Revision: ?
Benutzerdefiniertes Zellformat

Excel bietet über Menü > Format > Zellen.... > Registerblatt Zahlen schon einige Auswahl, wie wir Zellen formatieren können. Doch es kann vorkommen, dass Sie darüber hinausgehende Wünsche haben.
  • Das können Sie sich dann selbst einrichten, indem sie dort die Kategorie benutzerdefiniert auswählen und die gewünschte Formatierung in das Feld Typ einfügen.
  • Selbstsprechend ist, dass Sie die zu formatierenden Zellen vorher natürlich markieren müssen.
  • Ob Sie z.B. einen Tausenderpunkt einbinden möchten, keine oder eine andere Anzahl von Dezimalstellen wollen - in der Hinsicht können Sie die von mir gebrachten Beispiele natürlich varriieren.
  • Die Inhalte Ihrer benutzerdefinierten Formatierung sind völlig frei - wenn Sie Sonderzeichen benötigen, finden Sie über Menü > Einfügen > Symbol viele Sonderzeichen, die Sie für die benutzerdefinierte Formatierung einsetzen können (die können Sie dort herauskopieren), wie im Beispiel unten das Durchschnittszeichen. Symbolschriften eignen sich nicht, da dann die Zelle insgesamt in dieser Schriftart formatiert sein müßte und somit Ihre eigentlichen Daten unleserlich würden.

Benutzerdefinierte Zellformate können bis zu vier durch Strichpunkte abgetrennte Werte enthalten:

Positives Zahlenformat; Negatives Zahlenformat; 0-Format; Textformat
Diese Wertebereiche können wir ersetzen durch eigene Bedingungen in der Formel.


Vorteile der benutzerdefinierten Zellformatierung:
  • Sie können trotzdem mit den Zellwerten weiterrechnen, weil für Excel nur die reinen Zahlen drinstehen. Sie könnten das ja auch erreichen, indem sie z.B. Texte verbinden, dann müssten sie zum weiterrechnen aber den String wieder auflösen
  • Ist die Zelle leer, wird eventuell in der Formatierung enthaltener Text nicht angezeigt und die Zelle wird auch als leer behandelt

Noch ein Tipp: Wie fügt man in eine neue Datei schnell benutzerdefinierte Zellformate aus einer anderen Datei ein?
Kopieren Sie die Zellen mit den erwünschten benutzerdefinierten Formaten in der Ursprungsdatei und fügen Sie diese in einen ungenutzen Bereich der Zieldatei ein. Danach am Besten in der Zieldatei diese Zeilen komplett löschen. Die benutzerdefinierten Formate stehen Ihnen ab jetzt in der Zieldatei ebenfalls zur Verfügung. Speichern nicht vergessen!.
Sie finden nachstehend Beispiele zu folgenden benutzerdefinierten Formaten:

Zum Blattanfang

 
 ABCDEF
1      
2 Zeiten und DatenBenutzerdefiniertes Format 
3 Stundenzahlen über 24
(oft bei Addition über 24 Stunden benötigt, wenn man im Zeitformat bleiben möchte):
 35:12:14[h]:mm:ss 
4 Zeit mit Millisekunden 12:15:15,123hh:mm:ss,000 
5 Sie möchten die Uhrzeit im amerikanischen Format anzeigen lassen, also mit AM oder PMIn D5 steht 11:00:0011:00:00 AMhh:mm:ss AM/PM 
6 In D6 steht 23:00:0011:00:00 PM 
7 Datum mit ausgeschriebenem Monat und Uhrzeit 13.Januar 2004 19:37:09TT.MMMM JJJJ hh:mm:ss 
8 Wochentag linksbündig (kurz) und Datum rechtsbündig So                25.09.2005TTT * TT.MM.JJJJ 
9 Wochentag linksbündig (lang) und Datum rechtsbündig Sonntag        25.09.2005TTTT * TT.MM.JJJJ 
10 Ort und Datum Köln, den 25.09.2005"Köln, den "TT.MM.JJJJ 
11 Wochentag25.09.2005SonntagTTTT 
12 SoTTT 
13 Monat25.09.2005SeptemberMMMM 
14 SepMMM 
15 Jahr25.09.20052005JJJJ 
16 05JJ 
17 Kalenderwoche
(Formel entspricht DIN-Norm)
25.09.200538. KW0". KW" 
18      
Formeln der Tabelle
D11 : =C11
D12 : =C11
D13 : =C13
D14 : =C13
D15 : =C15
D16 : =C15
D17 : =KÜRZEN((C17-DATUM(JAHR(C17+3-REST(C17-2;7));1;REST(C17-2;7)-9))/7)
 

Zum Blattanfang

 
 ABCDE
1     
2 Führende NullenBenutzerdefiniertes Format 
3 z.B. bei PLZ0256300000 
4     
 

Zum Blattanfang

 
 ABCDEF
1      
2 Positive/Negative Werte / Währung
mit Farbunterschied
Benutzerdefiniertes Format 
3 Währung: >=0 schwarz / < 0 rot125,00 -75,00 #.##0,00 "";[Rot]-#.##0,00 "" 
4 Währung: >=0 schwarz / < 0 rot;
Währungszeichen linksbündig, Wert rechtsbündig
          125,00            -75,00""* #.##0,00;[Rot]""* -#.##0,00 
5 Währung: $ >=0 schwarz / $ < 0 rot;
statt dem Minuszeichen werden in USA negative Werte eingeklammert
125,00 $(75,00) $#.##0,00 "$";[Rot](#.##0,00) "$" 
6 Zahl >=0 schwarz / < 0 rot125,00-75,00#.##0,00;[Rot]-#.##0,00 
7 Für verschiedene Wertebereiche verschiedene Farben.
Hier: Für >100 blau, >0 grün, sonst rot
5,00-75,00[>100][blau]0,00;[>1][grün]0,00;[rot]0,00 
8 Werte > 0 werden blau, < 0 und 0 schwarz dargestellt.
Dieses Format eignet sich gut für die Achsenbeschriftung bei Diagrammen
50-5[Blau]#.##0;[Rot]-#.##0;#.##0 
9      
 


Wollen Sie das Vorzeichen ganz links angezeigt bekommen und die Zahl rechts, dann schauen Sie hier.

Zum Blattanfang

Um mit Temperaturangaben weiterrechnen zu können oder Diagramme daraus zu erstellen, müssen die Temperaturen Zahlen sein. Die Zeichen C würde aus den Zellen Text machen. Sie können die beiden Zeichen inkl. der +/- Vorzeichen aber benutzerdefiniert formatieren, dann können Sie diese Werte zur Weiterberechnung nutzen bzw. Diagramme daraus erzeugen:

 ABCDE
1     
2 TemperaturBenutzerdefiniertes Format 
3 hierdurch stehen die + bzw. - Zeichen direkt vor der Temperatur+15C+###C;-###C;_+0C 
4 -7C 
5  0C 
6 hierdurch werden die + und - Zeichen bei bis zu 3-stelligen Temperaturangaben bündig ausgerichtet - insofern Sie die Anzahl der Fragezeichen erhöhen, geht es auch für größere Zahlen+  15C+_-??0C;-_+??0C;_-_+??0C 
7 - 450C 
8     0C 
9     



Zum Blattanfang

 ABCDE
1     
2 Um Leerzeichen in der Breite eines Zeichens im Zahlenformat zu erstellen, geben Sie einen Unterstrich und dahinter das betreffende Zeichen einBenutzerdefiniertes FormatBemerkung 
3 2.563,00 #.##0,00_AEin Leerzeichen (entsprechend ein Unterstrich) vor dem Eurozeichen in der Breite eines "A" 
4 2.563,00   #.##0,00_____AFünf Leerzeichen (entsprechend fünf Unterstrichen) vor dem Eurozeichen in der Breite von fünf "A" 
5 2.563,00   #.##0,00_____IFünf Leerzeichen (entsprechend fünf Unterstrichen) vor dem Eurozeichen in der Breite von fünf "I" 
6 2.563,00     #.##0,00"     "Auch so erhalten sie 5 Leerzeichen vor dem Eurozeichen - aber sie haben keinen Einfluss auf die Breite des Leerzeichens, wie beim Unterstrich 
7 Die Unterstrich-Technik funktioniert mit allen im Beitrag genannten Formaten, das -Zeichen.wurde hier nur exemplarisch gewählt. 
8     


Zum Blattanfang

 ABCDE
1     
2 Runden (kaufmännisch)Benutzerdefiniertes Format 
3  In Spalte C steht der Wert: 1.578.5431.579 Tsd/ #.##0. "Tsd/ "  
4 In Spalte C steht der Wert: 4.453.7654 Mio/ 0.. "Mio/ "  
5 Achtung: Wird mit den Werten aus Spalte C weitergerechnet, nimmt Excel die genauen Werte, nicht die angezeigten Werte. 
6     

Zum Blattanfang

 BCDE
2Zellinhalt unsichtbar oderBenutzerdefiniertes Format
3bedingt unsichtbar
4Zahlen immer unsichtbar, Text sichtbar (in D3 steht eine Zahl - die man hier natürlich nicht sieht)Hund ;;
5Zahlen und Text immer unsichtbar (in C4 steht Text, in D4 eine Zahl)  ;;;
6Nullwerte unsichtbar, alle anderen Werte sichtbar - Text aber auch unsichtbar. Nützlich, wenn man 0-Werte nicht in Diagrammlabels erscheinen lassen möchte.25 0;-0;;
7Eine bestimmte Zahl (hier Beispiel 25 - steht in D6) unsichtbar machen12 [=25] ;-0;Standard
8Eine bestimmte Zahl (hier Beispiel 25 - steht in D7) soll durch irgendeinen Text ersetzt werden13Soll nicht angezeigt werden[=25]"Soll nicht angezeigt werden" ;-0;Standard
9Eine bestimmte Zahl (hier Beispiel 25 - steht in C5) soll durch irgendeinen Text ersetzt werden. In C8 und D8 steht eine Formel. Ansonsten hätte man dort mit einer WENN-Funktion operieren müssen: =WENN(C5=25;"Soll nicht angezeigt werden";C5)13Soll nicht angezeigt werden[=25]"Soll nicht angezeigt werden" ;-0;Standard
10Ab einer bestimmten Zahl, hier im Beispiel > 1.000 soll diese nicht angezeigt werden. In D9 steht 1.001. Das funktioniert auch mit >=; < und <=.999 [>1000]"";Standard
11z. B. für Spielergebnisse soll nicht nur das "-" bei negativen Ergebnissen angezeigt werden, sondern auch das "+" bei positiven Zahlen. Bei 0 soll "Unentschieden" erscheinen.1Unentschieden"+"0;-0;"Unentschieden"
12Weiterrechnen kann man ja mit diesen unsichtbaren Zahlen. Sie werden aber auch nicht mit ausgedruckt. Was z.B. für manche Druckmakros interessant sein könnte.

Zum Blattanfang

 ABCDE
1     
2 Rechts- / Linksbündigkeit
in zentriert formatierten Zellen
Benutzerdefiniertes Format 
3 Wochentag linksbündig, Datum rechtsbündig:Sonntag        25.09.2005TTTT * TT.MM.JJJJ 
4 oder umgekehrt:25.09.2005        SonntagTT.MM.JJJJ * TTTT 
5 Durchschnitt                          25,00"" * 0,00 
6 Längen-/Flächen-/Raummaßecm                     318,45"cm" * #.##0,00 
7 positve (schwarz) /negative (rot) Werte+                       678,00 +* #.##0,00 ;[Rot]-* #.##0,00 ;* 0 
8 Anmerkung: Diese Verteilung in der Zelle wird durch den "*" herbeigeführt. 
9     


Zum Blattanfang

 
 ABCDE
1     
2 Zentrierte Zellen mit Dezimalkomma am Komma ausrichtenBenutzerdefiniertes Format 
3 Zahlen bis 999,99     9,85??0,00 
4 125,78 
5 145,96 
6 Zahlen bis 999.999,99785.654,23???.??0,00 
7 159.470,96 
8   12.268,14 
9 Für Prozentwerte    3,12%_-??0,00%;-??0,00% 
10    18,00% 
11 100,00% 
12 Für Zahlen mit unterschiedlicher Anzahl von Dezimalstellen - dabei müssen so viele Fragezeichen eingegeben werden, wie max. Dezimalstellen vorhanden sind   45,120,??? 
13      23,896 
14 18,7 
15   
 



Zum Blattanfang

 
 ABCD
1    
2 Zellen auffüllen mit Zeichen
(z.B. beim Drucken von Schecks)
Benutzerdefiniertes Format 
3 Fünfundzwanzig,dreißig-----------------------------------------------------@*- 
4 Achtzehn,fünfzig===============================@*= 
5 Anmerkung: Das @-Zeichen erreichen Sie auf der Tastatur durch gleichzeitiges Drücken von AltGr + Q 
6    
 

Zum Blattanfang

 ABCDEF
1      
2 Das @-Zeichen als Platzhalter für Variable nutzen,
dabei kann es sich um Wert oder Text handeln.
Der Text, der immer angezeigt werden soll, gehört in Anführungszeichen ins benutzerdefinierte Format
Anzeige in der ZelleBenutzerdefiniertes FormatZellwert 
3 Der eingegebene Wert soll am Ende stehen24V AC"24V "@AC 
4 Der eingegebene Wert soll am Anfang stehen1,5 Volts@" Volts"1,5 
5 Der eingegebene Wert soll in der Mitte stehen1,5V LR6 Batterie"1,5V "@" Batterie"LR6 
6 Anmerkung:
Das @-Zeichen erreichen Sie auf der Tastatur durch gleichzeitiges Drücken von AltGr + Q
 
7      


Zum Blattanfang

 
 ABCDE
1     
2 Singular/Plural wird berücksichtigtBenutzerdefiniertes Format 
3 1 Palette5 Paletten[=1]0 "Palette";0 "Paletten" 
4 1 Tüte5 Tüten[=1]0 "Tüte";0 "Tüten" 
5     
 


Zum Blattanfang
 
 ABCDE
1     
2 Längen-, Flächen- und RaummaßeBenutzerdefiniertes Format 
3 Liter5,60 l#.##0,00" l" 
4 mm138,50 mm#.##0,00" mm" 
5 mm50,80 mm#.##0,00" mm" 
6 mm280,00 mm#.##0,00" mm" 
7 cm75,00 cm#.##0,00" cm" 
8 cm156,80 cm#.##0,00" cm" 
9 cm951,60 cm#.##0,00" cm" 
10 m15,80 m#.##0,00" m" 
11 m16,70 m#.##0,00" m" 
12 m23,85 m#.##0,00" m" 
13 km6,80 km#.##0,00" km" 
14 km23,89 km#.##0,00" km" 
15 Anmerkung: Die Zeichen und erreichen Sie auf der Tastatur durch gleichzeitiges Drücken der AltGr-Taste + 2 bzw. AltGr-Taste + 3 
16     
 

Zum Blattanfang

 
 ABCD
1    
2 Durchschnitt / MittelwertBenutzerdefiniertes Format 
3   12,00" " 0,00 
4   125" " 0 
5 Anmerkung: Das Durchschnittszeichen können Sie sich natürlich hier herauskopieren. Sie finden es aber auch in der Zeichentabelle z.B. in der Schrift Arial. 
6    
 

Zum Blattanfang

 
 ABCDE
1     
2 Ins Verhältnis setzenBenutzerdefiniertes Format 
3 Vermögen 12.500,00  
4 Verbindlichkeiten 7.600,00  
5 Verhältnis:1,64 :1,000,00" :1,00" 
6     
Formeln der Tabelle
C5 : =C3/C4
 

Zum Blattanfang

 
 ABCDEF
1      
2 1. April - oder vielleicht entdeckt jemand einen seriösen Verwendungszweck:tatsächlich steht in der linken Nachbarzelle:Benutzerdefiniertes Format 
3 kehrt Vorzeichen um - Weitergerechnet wird mit der eingegebenen Zahl, nicht mit der angezeigten.-1515-0;0;Standard 
4 15-15 
5 Positive Zahlen werden immer als 25 angezeigt; 0 sowie Text ist unsichtbar; negative Zahlen werden immer als -25 angezeigt. Weitergerechnet wird mit der eingegebenen Zahl (siehe Bearbeitungsleiste), nicht mit der angezeigten. Eine Möglichkeit, den echten Zellinhalt zu tarnen!251225;-25;; 
6 2525 
7  0 
8 -25-5 
9 Sobald eine Zahl in der Zelle steht, erscheint ein Smilie, weitergerechnet wird aber mit der eingegebenen Zahl, die in der Bearbeitungsleiste sichtbar ist. Die Zelle ist in der Symbolschriftart Wingdings formatiert.K18K;Standard 
10      
 

Zum Blattanfang

Fragen stellen im Online-Excel Forum

Neu in Online-Excel: Online-Excel Newsletter

Weitere Artikel der Gruppe: Basiswissen Aus Excel Standard
Nach oben