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:

• Zeiten und Daten
• Führende Nullen
• Positive/Negative Werte (auch inkl. Währung) mit Farbunterschied
• Zellinhalt unsichtbar oder bedingt unsichtbar
• Rechts- / Linksbündigkeit in zentriert formatierten Zellen
• Zentrierte Zellen mit Dezimalkomma (auch Prozentwerte) am Komma ausrichten
• Temperatur
• Leerzeichen hinzufügen
• Runden kaufmännisch
• Zellen auffüllen mit Zeichen (z. B. für Scheckdruck)
• Das @-Zeichen als Variable nutzen
• Singular/Plural berücksichtigen
• Längen-, Flächen- und Raummaße
• Durchschnitt Ø
• Ins Verhältnis setzen
• 1. April!

Zum Blattanfang

A B C D E F 1             2   Zeiten und Daten Benutzerdefiniertes 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,123 hh:mm:ss,000   5   Sie möchten die Uhrzeit im amerikanischen Format anzeigen lassen, also mit AM oder PM In D5 steht 11:00:00 11:00:00 AM hh:mm:ss AM/PM   6   In D6 steht 23:00:00 11:00:00 PM   7   Datum mit ausgeschriebenem Monat und Uhrzeit   13.Januar 2004 19:37:09 TT.MMMM JJJJ hh:mm:ss   8   Wochentag linksbündig (kurz) und Datum rechtsbündig   So                25.09.2005 TTT * TT.MM.JJJJ   9   Wochentag linksbündig (lang) und Datum rechtsbündig   Sonntag        25.09.2005 TTTT * TT.MM.JJJJ   10   Ort und Datum   Köln, den 25.09.2005 "Köln, den "TT.MM.JJJJ   11   Wochentag 25.09.2005 Sonntag TTTT   12   So TTT   13   Monat 25.09.2005 September MMMM   14   Sep MMM   15   Jahr 25.09.2005 2005 JJJJ   16   05 JJ   17   Kalenderwoche (Formel entspricht DIN-Norm) 25.09.2005 38. KW 0". 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

A B C D E 1           2   Führende Nullen Benutzerdefiniertes Format   3   z.B. bei PLZ 02563 00000   4

Zum Blattanfang

A B C D E F 1             2   Positive/Negative Werte / Währung mit Farbunterschied Benutzerdefiniertes Format   3   Währung: € >=0 schwarz / € < 0 rot 125,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 rot 125,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:

	A	B	C	D	E
1
2		Temperatur		Benutzerdefiniertes Format
3		hierdurch stehen die + bzw. - Zeichen direkt vor der Temperatur	+15°C	+###°C;-###°C;_+0°C
4			-7°C
5			0°C
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	+  15°C	+_-??0°C;-_+??0°C;_-_+??0°C
7			- 450°C
8			0°C
9

Zum Blattanfang

	A	B	C	D	E
1
2		Um Leerzeichen in der Breite eines Zeichens im Zahlenformat zu erstellen, geben Sie einen Unterstrich und dahinter das betreffende Zeichen ein	Benutzerdefiniertes Format	Bemerkung
3		2.563,00 €	#.##0,00_A€	Ein Leerzeichen (entsprechend ein Unterstrich) vor dem Eurozeichen in der Breite eines "A"
4		2.563,00   €	#.##0,00_____A€	Fünf Leerzeichen (entsprechend fünf Unterstrichen) vor dem Eurozeichen in der Breite von fünf "A"
5		2.563,00   €	#.##0,00_____I€	Fü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

	A	B	C	D	E
1
2		Runden (kaufmännisch)		Benutzerdefiniertes Format
3		In Spalte C steht der Wert: 1.578.543	1.579 Tsd/ €	#.##0. "Tsd/ €"
4		In Spalte C steht der Wert: 4.453.765	4 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

	B	C	D	E
2	Zellinhalt unsichtbar oder			Benutzerdefiniertes Format
3	bedingt unsichtbar
4	Zahlen immer unsichtbar, Text sichtbar (in D3 steht eine Zahl - die man hier natürlich nicht sieht)	Hund		;;
5	Zahlen und Text immer unsichtbar (in C4 steht Text, in D4 eine Zahl)			;;;
6	Nullwerte 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;;
7	Eine bestimmte Zahl (hier Beispiel 25 - steht in D6) unsichtbar machen	12		[=25] ;-0;Standard
8	Eine bestimmte Zahl (hier Beispiel 25 - steht in D7) soll durch irgendeinen Text ersetzt werden	13	Soll nicht angezeigt werden	[=25]"Soll nicht angezeigt werden" ;-0;Standard
9	Eine 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)	13	Soll nicht angezeigt werden	[=25]"Soll nicht angezeigt werden" ;-0;Standard
10	Ab 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
11	z. B. für Spielergebnisse soll nicht nur das "-" bei negativen Ergebnissen angezeigt werden, sondern auch das "+" bei positiven Zahlen. Bei 0 soll "Unentschieden" erscheinen.	1	Unentschieden	"+"0;-0;"Unentschieden"
12	Weiterrechnen 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

	A	B	C	D	E
1
2		Rechts- / Linksbündigkeit in zentriert formatierten Zellen		Benutzerdefiniertes Format
3		Wochentag linksbündig, Datum rechtsbündig:	Sonntag        25.09.2005	TTTT * TT.MM.JJJJ
4		oder umgekehrt:	25.09.2005        Sonntag	TT.MM.JJJJ * TTTT
5		Durchschnitt	Ø                          25,00	"Ø" * 0,00
6		Längen-/Flächen-/Raummaße	cm²                     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

A B C D E 1           2   Zentrierte Zellen mit Dezimalkomma am Komma ausrichten Benutzerdefiniertes Format   3   Zahlen bis 999,99     9,85 ??0,00   4   125,78   5   145,96   6   Zahlen bis 999.999,99 785.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,12 0,???   13       23,896   14   18,7   15

Zum Blattanfang

A B C D 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

	A	B	C	D	E	F
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 Zelle	Benutzerdefiniertes Format	Zellwert
3		Der eingegebene Wert soll am Ende stehen	24V AC	"24V "@	AC
4		Der eingegebene Wert soll am Anfang stehen	1,5 Volts	@" Volts"	1,5
5		Der eingegebene Wert soll in der Mitte stehen	1,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

A B C D E 1           2   Singular/Plural wird berücksichtigt Benutzerdefiniertes Format   3   1 Palette 5 Paletten [=1]0 "Palette";0 "Paletten"   4   1 Tüte 5 Tüten [=1]0 "Tüte";0 "Tüten"   5

Zum Blattanfang

A B C D E 1           2   Längen-, Flächen- und Raummaße Benutzerdefiniertes Format   3   Liter 5,60 l #.##0,00" l"   4   mm 138,50 mm #.##0,00" mm"   5   mm² 50,80 mm² #.##0,00" mm²"   6   mm³ 280,00 mm³ #.##0,00" mm³"   7   cm 75,00 cm #.##0,00" cm"   8   cm² 156,80 cm² #.##0,00" cm²"   9   cm³ 951,60 cm³ #.##0,00" cm³"   10   m 15,80 m #.##0,00" m"   11   m² 16,70 m² #.##0,00" m²"   12   m³ 23,85 m³ #.##0,00" m³"   13   km 6,80 km #.##0,00" km"   14   km² 23,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

A B C D 1         2   Durchschnitt / Mittelwert Benutzerdefiniertes 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

A B C D E 1           2   Ins Verhältnis setzen Benutzerdefiniertes Format   3   Vermögen € 12.500,00     4   Verbindlichkeiten € 7.600,00     5   Verhältnis: 1,64 :1,00 0,00" :1,00"   6           Formeln der Tabelle C5 : =C3/C4

Zum Blattanfang

A B C D E F 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. -15 15 -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! 25 12 25;-25;;   6   25 25   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. K 18 K;Standard   10

Zum Blattanfang