Tag Archives: bedingte Formatierung

Es ist nie zu spät, die gleichen Fehler immer wieder zu machen

In der Excelschulung zeige und erkläre ich den Manager für Regeln zur Bedingten Formatierung.

Später erläutere ich die Datenüberprüfung. Da kommt die Frage einer Teilnehmerin, ob Excel für die Datenüberprüfung auch einen solchen Manager zur Verfügung stellt. Finden kann man sie ja – mit Hilfe der Schaltfläche „Suchen und Auswählen“. Leider nein, lautet meine Antwort.

Wo ist mein Geld? … Versoffen!!! … Puh und ich dachte ich habs verloren…

Ein bisschen komisch ist es schon.

Wenn man eine Pivottabelle erstellt, auf die Pivottabelle eine bedingte Formatierung aufsetzt – genauer: eine Bedingung: „alle Zellen mit Summe der Werte UND den Kategorien“ und eine Bedingung „nur Werte über oder unter dem Durchschnitt formatieren“, beziehungsweise „nur obere oder untere Werte formatieren“, dann stellt Excel die Option (in einem Dropdownfeld) zur Verfügung: für den ausgewählten Bereich für: alle Werte / jede Spaltengruppe / jede Zeilengruppe:

Das Ergebnis irritiert: Ich wähle „Spaltengruppe“ und erhalte mehrere Werte pro Spalte. Excel geht in der Pivottabelle davon aus, dass Werte spaltenweise nebeneinander gruppiert werden – das heißt: Spaltengruppe bedeutet „in jeder Zelle einer Zeile in jeder Spalte“. Ein bisschen verwirrend die Logik – vielleicht wird es deutlich, wenn man sich darüber im Klaren wird, dass Werte in der Spalte gruppiert werden, das heißt NEBENEINANDER (und nicht untereinander dargestellt werden):

Wenn ich nur darf, wenn ich soll aber nie kann, wenn ich will dann mag ich auch nicht, wenn ich muss. Wenn ich aber darf, wenn ich will dann mag ich auch, wenn ich soll und dann kann ich auch …

Man kann einer einspaltigen Liste einen Namen zuweisen (Hier: „Einwohner“) und anschließend damit rechnen:

=Einwohner*1,1

Excel verwendet hierbei den relativen Bezug.

Will man jedoch auf die gleiche Art in der Bedingten Formatierung damit rechnen, scheitert man:

Ausgewertet wird der Ausdruck

=Einwohner>1000000

Das Ergebnis ist falsch; es wird keine Formatierung angezeigt.

Schade und nicht konsequent.

Ein Dankeschön an den Tabellenexperten Martin Weiß, der diese Inkonsistenz auf den Excellent Days 2018 vorgestellt hat.

Für Mädchen ist es besser nicht zu heiraten, aber Junx brauchen jemanden zum Putzen.

Ein Vortrag von Martin Weiß über Bedingte Formatierungen auf den Excellent Days. Er zeigt, wie man mit der Funktion ZÄHLENWENN alle Zahlen einfärben kann, mehr als drei mal vorkommen. Die Formel in der Bedingte Formatierungen lautet:

=ZÄHLENWENN($A$3:$O$32;A3)>3

Alle machen brav mit. Mein Nachbar wundert sich, warum bei ihm keine Zelle rot eingefärbt wird. Er schaut in den Manager für Regeln zur Bedingten Formatierung und wundert sich über die Formel:

=ZÄHLENWENN($A$3:$O$32;XEQ1048550)>3

Der Grund ist einfach: Er hat den Bereich von rechts unten nach links oben markiert. Deshalb wird ein relativer Bezug A3 in der Zelle A3 umgesetzt in: 30 Zeilen nach oben und 15 Spalten nach links. So kommt XEQ1048550 zustande.

Damit so etwas Nervendes nicht passiert, schlage ich ein anderes Vorgehen vor:

Ich beginne im ersten Schritt mit einer Zelle, beispielsweise A3 (oder O32) und aktiviere dort die Bedingte Formatierung:

Im zweiten Schritt aktiviere ich erneut den Regelmanager (Start / Bedingte Formatierung / Regeln verwalten) und dehne nun den Bereich aus:

Das klappt:

Okay – ein Schritt mehr – aber ich bin auf der sicheren Seite. Damit Excel nicht mehr nervt!

 

Ich liebe diesen langen Sonntagsspaziergänge. War schon im Bad, kam am Kühlschrank vorbei, bin jetzt auf dem Weg zum Sofa. Wetter spielt auch mit.

Merkwürdig:

Während man in der Bedingten Formatierung die Werte bei Datenbalken und Symbolsätzen ausblenden kann („nur Symbol anzeigen“)

ist dies bei Farbskalen nicht möglich.

Vergessen oder gewollt?

Danke an Martin Weiß für diesen Hinweis.

Suche neue Freunde.Die alten wissen zuviel

Ich verstehe es nicht. In einer (großen) Datei befinden sich mehrere Verknüpfungen. Ich lösche sie. Alle – bis auf eine. Sie ist störrisch und lässt sich nicht löschen:

Die Suche in der Datei liefert keinen Erfolg:

Es gibt auch keinen Namen, keine bedingte Formatierung, keine Datenüberprüfung, in der die Verknüpfung zu finden wäre.

Ich öffne das XML-Archiv und suche dort. DORT werde ich fündig:

Den Knoten löschen, das Archiv zippen – schon ist die Verknüpfung weg. Ich wüsste ja gerne wie so etwas passieren kann. Und – gibt es nicht einen bequemeren Weg die Verknüpfungen zu entfernen?

Ich möche ja nicht behaupten, dass es in meiner Wohnung heiß ist, aber hier haben gerade zwei Hobbits einen Ring reingeworfen.

Heute in der Excelschulung bemängelte ein Teilnehmer, dass man Datenüberprüfungen nicht über die ganze Arbeitsmappe suchen kann.

Er hat recht: Während man Texte, Kommentare und Bedingte Formatierungen dateiweit suchen kann, funktioniert dies leider für die Datenüberprüfung nicht. Schade!

Das einzige Problem beim Nichtstun ist, dass man nie weiß, wann man fertig ist.

Hallo Herr Martin,

ich habe eine kleine Tabelle:

Erste Zeile Datum

Zweite Zeile Gewicht

 

Mit der bedingten Formatierung wird in der ersten Zeile das Gewicht unter 80 kg rot und fett dargestellt.

Zu diesen Daten gibt es ein Liniendiagramm.

Wenn ich auf die Zahlen (Datenbeschriftung) im Diagramm klicke, ist die Funktion Bedingte Formatierung deaktiviert.

Gibt es dennoch eine Möglichkeit bei der Datenbeschriftung eine bestimmte Art einer bedingten Formatierung anzuwenden? Also wenn man in der zweiten Zeile eine Zahl unter 80 einträgt, soll im Liniendiagramm diese Zahl (Datenbeschriftung) ebenfalls automatisch rot und fett werden.

#####

Hallo Herr F.,

Diagramme kennen keine bedingten Zahlenformate. Aber Sie können die „alten“ benutzerdefinierten Zahlenformate verwenden, beispielsweise:

[Rot][>80]0;Standard

Leider geht dies nur mit Farben – nicht mit fett

Schöne Grüße

Rene Martin

Verzweifle nicht, wenn du kein Profi bist. Ein Amateur hat die Arche gebaut; ein Profi die Titanic.

Sehr geehrter Herr Dr. Martin,

ich bin großer Fan Ihrer Tutorials (und Ihrer Westen) früher auf video2brain, heute auf LinkedIn.

Heute wende ich mich mit einer Frage an Sie, auf die ich in den Weiten des Internets keine Lösung gefunden habe.

Ich habe ein EXCEL Datei, mit der wöchentlich die „Performance“ an verschiedenen Parametern gemessen wird.

Hier wird (auf ca. 50 Tabellenblättern in jeweils ca. 40 Zellen) mit „Bedingter“ Formatierung“ gearbeitet, um optisch die jeweiligen Werte zu bewerten.

Gibt es eine Möglichkeit, die bestehende „Bedingte Formatierungsregel“ zeitsparend für die ca. 2.000 Zellen zu ändern?

Vielen Herzlichen Dank im Voraus für Ihre Hilfe.

Hallo Herr S.,

ich fürchte, DA bringen Sie Excel an die Grenzen. Man kann so etwas programmieren (wenn Sie sehr viele einzelne bedingte Formatierungen haben). Sie könnten das Dokument „entzippen“ und dort in der XML-Datei des Tabellenblattes die bed. Formatierung ändern (kennen Sie das? – die conditional formating befindet sich ganz am Ende – muss allerdings auch für jeder Formatierung einzeln vorgenommen werden):

Sonst weiß ich leider keine Möglichkeit ALLE bedingten Formatierungen schnell zu ändern.

 

Blöder Tipp: manchmal geht es schneller, wenn sie alle bed. Formatierungen löschen und dann neu erstellen.

Die Pflicht ruft, wir rufen zurück.

Letzte Woche in der Excelschulung.

Ich zeige – wie immer – ein paar (wie ich finde) wichtige Tastenkombinationen:

[Strg] + [.]: aktuelles Datum

[⇑] + [Leertaste]: markiert die Zeile

[Strg] + [*]: der aktuelle Bereich wird markiert

[⇑] + [Strg] + [6]: Zahlenformat: Standard

und so weiter. Damit mache ich Menschen immer glücklich – nützliche Sachen, die man brauchen kann. Einem Teilnehmer war dies nicht genug: er wollte eine Tastenkombination für:

  • Sortieren
  • Blatt schützen
  • Datenüberprüfung, Bedingte Formatierung
  • Inhalte einfügen als Werte (okay: man kann die Zellen am Rand mit der RECHTEN Maustaste verschieben und auf den gleichen Ort zurückfallen lassen. Dann bietet das Kontextmenü „Hierhin nur als Werte kopieren“ an. Bei einer Zelle kann man eine Formeln mit [F2] und anschließend [F9] in einen Wert umwandeln.)
  • nach rechts „ziehen“ (Reihe ausfüllen). Auch hierzu ist mir nichts Intelligentes eingefallen: Wenn bereits Werte in der Zeile stehen kann man sie mit [⇑] + [Strg] + [→] markieren. Wenn die Zeile leer ist, kann man sie schnell über das Namensfeld selektieren: Dort trägt man beispielsweise A2:Z2 ein. Und dann den Wert der ersten Zelle mit [Strg] + [R] oder [F2] und [Strg] + [Enter] beenden.

Ich habe mich amüsiert und war froh, als die Schulung zu Ende war, weil ich befürchtet hatte, dass er noch mehr Tasten haben wollte für Dinge, hinter denen keine Tastenkombination liegen. Ich habe Werbung für meinen VBA-Kurs gemacht und auf die Möglichkeit hingewiesen, dass man die Befehle des Menübandes per [Alt] + [Buchstabe erreichen kann.

Der Frühling ist da! Man hört schon die ersten Hochdruckreiniger und bald kommen auch die Rasenmäher aus dem Süden zurück.

Eine interessante Frage einer Schulungsteilnehmerin:

Man kann Formate löschen:

Man kann bedingte Formatierungen löschen:

Man kann Kommentare löschen:

Man kann Hyperlinks löschen:

Aber – wie löscht man eine Datenüberprüfung?

Ich zeigte ihr, dass man in der Datenüberprüfung auf „Jeden Wert“ zurücksetzen kann:

Dann allerdings bleibt die Eingabemeldung:

Oder man löscht alles – dann löscht man allerdings die Inhalte:

Eine Lösung, wenn man mehrere Datenüberprüfungen mit mehreren Eingabemeldungen (und Fehlermeldungen) hat: Start / Bearbeiten / Suchen und Auswählen / Datenüberprüfung). Dann erhält man die Meldung „Die Auswahl enthält mehr als eine Prüfungsart. Sollen die aktuellen Einstellungen gelöscht und dann fortgefahren werden?“ Diese kann man bejahen.

Eine andere Lösung wäre: man kopiert eine leere Zelle und fügt über „Inhalte einfügen / Gültigkeit“ die Datenüberprüfung ein.

Aber explizit löschen kann man Datenüberprüfungen nicht.

Schokoladenfondue macht man aus Schokoladenresten. – Ich verstehe diesen Satz nicht.

Ich verstehe es nicht. Ich erhalte eine Datei. In einer Spalte stehen die Zahlen (Zahlenformat: Standard) linksbündig in der Zelle. Die Zellen sind zentriert formatiert!

Auf den Zellen liegt eine bedingte Formatierung. Schalte ich sie aus, sieht das Ganze so aus:

Zellenformat links; Ausrichtung: zentriert. Schalte ich eine bedingte Formatierung ein, fällt auf, dass die Zellen, welche die Bedingung erfüllen, ihre Ausrichtung ändern ?!?

Ich erstelle ein neues Tabellenblatt – die Grundausrichtung ist – linksbündig!

Ich kopiere das neue Blatt in eine neue Arbeitsmappe und schaue mir den XML-Code an. Ich finde im XML-Code ZWEI Zellformate:

Normalerweise steht bei einer leeren Tabelle in der Datei „styles.xml“ im Knoten cellXfs nur die erste Zeile.

Nun meine Frage: Hat jemand eine Idee, wie man die Grundausrichtung eines Tabellenblattes ändern kann? Also eine Arbeitsmappe so manipulieren kann, dass ein neu erzeugtes Tabellenblatt in dieser Datei eine Grundausrichtung „links“ hat.

Und: nein – nicht über Zellformatvorlagen (habe ich nachgeschaut – die sind nicht angerührt):

Der Kühlschrank ist das beste Beispiel dafür, dass innere Werte zählen.

Perfide!

Versuchen Sie einmal Folgendes: erstellen Sie eine oder mehrere Listen in einer gespeicherten Excelmappe.

Wechseln Sie auf ein anderes Tabellenblatt und erstellen dort eine bedingte Formatierung und/oder eine Datenüberprüfung mit einem Bezug auf das erste Blatt:

Kopieren Sie das Blatt in eine neue Arbeitsmappe. Speichern Sie die neue Mappe, schließen alles und öffnen die neue Zieldatei. Excel weist darauf hin, dass sich in der Arbeitsmappe eine Verknüpfung befindet:

Über Datei / Informationen sieht man diese Verknüpfung, aber:

Man kann sie nicht löschen! Auch Datei / Informationen / Auf Probleme überprüfen weist nur darauf hin, dass es eine Verknüpfung gibt. Verrät weder wo, noch hilft der Assistent diese Verknüpfung zu finden:

Man muss wissen so sie stecken – zum Glück kann man ja Bedingte Formatierung und Datenüberprüfung suchen. Und dort findet man dann die korrupten Verknüpfungen:

Ein Dankeschön an Bettina, die sich mit diesem Problem herumgeärgert hat, für diesen wertvollen Hinweis.

Was passiert, wenn man sich zweimal halbtot gelacht hat?

Ich musste eine Weile suchen. Obwohl in der Zelle eine Formel (und eine bedingte Formatierung) war, habe ich den Ergebniswert der Formel nicht gesehen.

Nein – die Schriftfarbe der Zelle war nicht weiß; es lag kein Zahlenformat unter den Zellen. Dann habe ich es entdeckt: Im Dialogfeld der bedingten Formatierung befindet sich eine Option „nur Symbol anzeigen“. Und die war aktiviert.

Wie doof ist denn das immer noch?

Ich habe es noch einmal ausprobiert: anderer Rechner, anderes Betriebssystem – gleiches Phänomen.

Ich versuche es von Neuem: In A1 steht das Jahr 2017, in B1 der Monat 5. In C1 die Formel

=DATUM($A$1;$B$1;SPALTE()-2)

Rüberziehen bis AG1.

Die Datumsangaben werden als Tag mit TT formatiert.

In C1:AG38 wird folgende bedingte Formatierung verwendet:

=WOCHENTAG(C$1;2)>5

Sieht so aus:

Monatszahl in B1 ändern. Sieht so aus:

Ich kann auch ohne Alkohol lustig sein. Aber sicher ist sicher.

Über merkwürdige Prozente / Prozentwerte habe ich hier in diesem Blog schon einige Male mich geäußert. Nun wieder:

Geben Sie ein paar Prozentwerte ein. Legen Sie eine bedingte Formatierung drüber mit einem Symbolsatz. Das Ergebnis verblüfft:

Warum wird die Zelle, in der 70% steht mit einem vollen Kreis gekennzeichnet – 70% ist doch nicht >=80 Prozent?

Des Rätsels Lösung: Wenn Sie jeden Wert in Verhältnis zum größten Wert setzen, also beispielsweise

=D2/MAX($D$2:$D$16)

dann ergeben sich andere Werte – nämlich 88% bei 70%.

Das heißt: 80% heißt bei Excel:

80% des größten Wertes der Liste. Dabei wird die Liste dynamisch erweitert oder verkleinert wenn Sie Werte löschen oder hinzufügen. Warum sagen die das nicht gleich? So? Denn: wenn Wert >= 80% ist so falsch!

Ein großes Dankeschön an Peter, der mich auf diese Merkwürdigkeit, auf dieses verwirrende Phänomen und auf dieses auf den ersten Blick erstaunliche Verhalten hingewiesen hat. Er schreibt dazu:

„Es ist eben für den arglosen Benutzer nicht erkennbar, dass die Auswahl Prozent in der Symbolformatierung eine gänzlich andere Rechenlogik besitzt als die Formatierung Wert.“

Mein Leben ist ziemlich betrinkenswert.

Perfide!

Ich erstelle zwei bedingte Formatierungen. Die eine überprüft, ob in Spalte F ein Wert > 800 steht. Wenn ja, dann wird die Schriftfarbe auf blau gesetzt. Die zwei Bedingung wird auf die gesamte Tabelle angewendet. Sie formatiert die Hintergrundfarbe (mit einem anderen) Blau. Das Ergebnis sieht wie folgt aus:

Wechselt man in den Dialog „Formatieren“ auf die Registerkarte „Ausfüllen“, ist die Schaltfläche „Keine Farbe“ der Hintergrundfarbe unterlegt.

Begeht man jedoch den Fehler und klickt auf diese Schaltfläche, sieht man zwar auf dem Dialog keinen Unterschied – allerdings wird nun die Option „Keine Farbe“ aktiv – das heißt: die blaue Hintergrundfarbe wird von „keiner Farbe“ überschrieben.

Irgendwie doof …

Das Lästern ist die Wurzelbürste der Psyche

Excel treibt uns zum Wahnsinn? – Nö – WIR treiben jetzt mal Excel zum Wahnsinn! Und so geht es:
– neue Datei anlegen
– Formatvorlage „Standard“ mit irgendeiner Füllfarbe versehen – jetzt wird das ganze Tabellenblatt eingefärbt
– Tabellenblatt markieren und „Füllfarbe: keine Füllung“ zuweisen – alles wieder weiß
– Dann eine bedingte Formatierung mit Füllfarbe verwenden
– Bedingtes Format sieht man am Bildschirm
– Seitenansicht und im Ausdruck: keine Farbe
######
Wie kommen wir auf so etwas? Und: wer ist „wir“? Wir hatten gestern in München wieder unseren Excel-Stammtisch, bei dem ich einen Vortrag über Fehler, Ungenauigkeiten, Unschärfen und Probleme in Excel referiert habe (Motto: „Excel nervt“). Eine Datei hat uns alle beschäftigt: warum sehe ich die Farbe der bedingten Formatierung in Excel – im Ausdruck dagegen ist sie weg. Ich habe schon im XML-Code gewühlt und bemerkt, dass das Problem in der styles.xml lag. Genauer konnte ich es nicht einkreisen.
Hilfe kam von Pia Bork. Hier noch einmal ein dickes DANKESCHÖN an sie für das Aufspüren des Fehlers. Von ihr stammt die obige Beschreibung.
Und warum nerven wir nun Excel? Nun – probiert mal das Kochrezept von oben aus. Klickt nun auf eine andere Zelle, auf ein anderes Blatt, in ein anderes Programm und wieder zurück … Excel rechnet sich grün und blau! köstlich! Das ist die Strafe fürs mich-so-oft nerven!
PS: Pias Kommentar: „Herzlichen Glückwunsch an alle, die so einen Fehler suchen müssen!“

Die bedingte Formatierung in Excel

Die bedingte Formatierung in der Seitenansicht

Alkohol löst keine Probleme, aber das tut Milch ja auch nicht …

Guten Tag Herr Martin,
vielen Dank für Ihre informative Seite, welche mir nun bereits schon einige Male helfen konnte. Dieses Mal wurde ich leider trotz intensiver Suche im gesamten Internet nicht fündigt. Es geht um folgendes Problem:
Eine Kollegin hat eine Excelmappe in der mehrere Zellen über Bedingte Formatierungen eingefärbt werden. Die werden jedoch beim drucken nicht übernommen. Schon der Druckvorschau (und auch beim drucken selber die Farben nciht verwendet, sondern alle Zellen mit weißen Hintergrund dargestellt bzw. gedruckt.

die bedingte Formatierung

die bedingte Formatierung

aber leider nicht sichtbar ...

aber leider nicht sichtbar …

Hallo Herr O.,

Mir ist folgendes aufgefallen:

Wenn ich von Ihrer Datei ein Tabellenblatt in eine neue Datei verschiebe (egal welches Blatt), habe ich dort den gleichen Effekt. Oder umgekehrt: Bedingte Formatierung „reagiert nicht“.

Ich habe die Datei im xlsx-Format gespeichert, entzippt und „reingeschaut“ und verglichen mit einer „normalen“ Excel-Datei.

Ich weiß nicht genau an welcher Stelle, aber bei der styles.xml (im Ordner xl) finde ich Unterschiede zur „normalen“ Datei.

Ich habe nun die gesamte Datei durch „meine“ styles.xml ersetzt, geöffnet – damit sind leider sämtliche Formatierungen weg, aber nun funktioniert die bedingte Formatierung wieder.

Das heißt: durch irgendein Speichern/Austausch/Öffnen, … wurde etwas in den Formaten „zerschossen“. Und zwar in den Formaten der Datei (nicht des Tabellenblattes). Ich weiß nicht wann, bei welcher Aktion und nicht genau was. Aber wenn Sie die Datei neu formatieren, können Sie mit Ihr arbeiten.

Wenn Sie einen Blick in meine Datei werfen, erhalten Sie eine Meldung, dass etwas nicht stimmt. Klar: ich habe ja die styles.xml ausgetauscht.

schöne Grüße

Rene Martin

PS: Wenn ich ganz viel Zeit habe, schaue ich mir Ihre Styles-Datei noch einmal genauer an – vielleicht kann ich die Stelle lokalisieren, wo der Fehler auftritt.

Ich kenne Zebras, die freiwillig hinter Gittern sitzen, um wie weiße Pferde auszusehen.

Verblüfft war ich schon. Ein Teilnehmer der letzten Excel-Schulung zeigte mit eine Datei, die er aus dem USA erhalten hat. Darin befanden sich mehrere Zellen mit bedingten Formatierungen:

20161120bedingteformatierung01

Ein Klick auf die „rote“ Bedingung (Zellwert<=0,81) zeigte allerdings die „grüne“ Bedingung (Zellwert = 1) an. Ich war verblüfft!

20161120bedingteformatierung02

Die Lösung fand ich als ich das Dokument entzippte und mit die Datei sheet1.xml im Ordner xl\worksheets anschaute. Dort war bei mehreren Bedingungen (nicht bei allen!) das Attribut priority auf den gleichen Wert gesetzt: mehrmals auf 4, auf 5, auf 6, … Natürlich müssen dort unterschiedliche Werte stehen. Per Hand geändert, die Dateien gezippt – und schon lief die bedingte Formatierung wieder.

Die XML-Datei

Die XML-Datei

Ich weiß zwar nicht, wann das Problem entstanden ist (USA – Deutschland) oder Excel 2007 – 2010, … aber immerhin – Problemursache gefunden und Problem gelöst.

Ich brauche einen neuen Kopf. Der alte denkt zu viel.

Was mache ich falsch?

Ich wollte mal schnell einen Kalender erzeugen und die Wochenende mit einer grauen Zellfarbe hinterlegen.

Keine Wochenenden!

Keine Wochenenden!

Die Antwort: Sie haben als Formel

=WOCHENTAG(B2;2>5)

geschrieben. Die Klammer wurde falsch gesetzt. Es muss heißen:

=WOCHENTAG(B2;2)>5

20161012bedingteformatierung02

Ah, danke – jetzt funktioniert es!

Übrigens: 2>5 liefert den Wert FALSCH. FALSCH entspricht in Excel der Zahl 0. Hier wurde versucht Wochentag(B2;0) zu berechnen. Excel verlangt allerdings die Parameter 1, 2 oder 3 und liefert bei 0 einen Fehler. Da Fehler in der Bedingten Formatierung nicht angezeigt, sondern stillschweigend übergangen werden, wird keiner der Tage grau formatiert.

Wo ist die Mitte?

Hallo Herr Martin,

auf der Suche meines Excel-Problems bin ich über Ihre Seite gestolpert und würd mich freuen, wenn Sie mir ggf. helfen könnten: Ich habe hier eine ziemlich umfangreiche Pivot Tabelle und vermisse die Funktion, die es mir erlaubt anstatt des Min. oder Max-Wertes bei den Wertfeldeinstellungen Quartile auszugeben. Aber auch bei den Rohdaten tue ich mir schwer. Ich schaffe es zwar für eine vorgegebene Auswahl die Quartile zu berechnen, aber was ich benötige ist eine dynamische Ausgabe, z.B. bei Auswahl über den Auto-Filter. Auch mit Excel 2016 und Power-Pivot habe ich es bisher nicht geschafft. Haben Sie eine Idee wie so was gehen könnte?

Viele Grüße

#####

Hallo Herr B.,

das ist eine interessante Frage – danke dafür.

Ich habe heute auf dem Nachhauseweg geknobelt, wie man so etwas lösen wollte. Dachte an PowerPivot – aber wenn Sie schreiben, dass es DORT kein Quartil gibt, muss ich dort auch nicht mehr auf die Suche geben.

Wenn Sie eine einfache Pivottabelle hätten, könnten Sie Sie mit Matrixfunktionen oder der Funktion AGGREGAT nachbauen. Ich habe es mal versucht – siehe Anhang. Allerdings schreiben Sie „umfangreiche“ Pivottabelle – ich fürchte das Schlimmste.

20160926quartil

Übrigens hat Andreas Thehos ein Video dazu gemacht:

https://thehosblog.com/?s=median+pivot

Wem das Wasser bis zum Halse steht, der sollte den Kopf nicht hängen lassen

Warum klappt die Suche manchmal nicht?

Ich habe irgendwann entdeckt, dass man in Excel nach Farben suchen kann. Tolle Sache. Dennoch: Manchmal klappt es nicht. Dabei habe ich sogar die eine formatierte Zelle ausgewählt, um sicher zu sein, dass ich den korrekten Farbton treffe. Warum klappt es nicht?

Excel findet nichts.

Excel findet nichts.

Die Antwort: Die Zelle ist nicht formatiert. Sie wurde mit der bedingten Formatierung formatiert. Da Excel nicht „erkennen“ kann, welche Farbe die entsprechende Bedingung liefert, geht er von der Grundfarbe aus.

20160712Farbensuchen02

Humor ist der Knopf, der verhindert, dass uns der Kragen platzt. (Joachim Ringelnatz)

Nicht jede Formel darf sein!

Ich gestehe: Es ist mir lange Zeit nicht aufgefallen. Wahrscheinlich deshalb, weil ich die Symbole der Bedingten Formatierung in Excel nicht besonders schätze. Während man bei den Hintergrundfarben, Schriftfarben und Zellrahmen beliebige Formeln verwenden darf, ist dies bei den Skalen, Datenbalken und Symbolsätzen eingeschränkt. Genau:

Werte sind kein Problem

Werte sind kein Problem

Den Inhalt einer Zelle mit einem festen Wert abzugleichen ist kein Problem.

Absolute Bezüge geht auch

Absolute Bezüge geht auch

Den Wert auszulagern und mit einem absoluten Bezug darauf zuzugreifen ist auch kein Problem.

HEUTE() funktioniert

HEUTE() funktioniert

Auch Formeln wird HEUTE() stellen kein Problem dar.

Relative Bezüge leider nicht.

Relative Bezüge leider nicht.

Das Erstaunen ist groß, wenn man relative Bezüge verwendet. DAS ist verboten. Noch einmal hinschauen – das Kombination bietet „Formel“ an und nicht „Formel mit absolutem oder keinem Bezug“.

Okay – wahrscheinlich eine Kurzschreibweise …

Punkt, Punkt, Komma, Strich

Kennen Sie das? Sie erstellen eine bedingte Formatierung für eine Zelle, beispielsweise für H12. Sie möchten nun diesen Bereich auf die ganze Spalte ausdehnen – klicken in das Textfeld des Managers für Regeln zur bedingten Formatierung, den Sie über Start / Formatvorlagen / Bedingte Formatierung / Regeln verwalten aufrufen. Aber: Doppelpunkt ist nicht! Sie müssen den Bezug löschen und neu eingeben. Oder zuvor die Funktionstaste [F2] drücken.

20160218BedingteFormatierung01

Nach dem Doppelpunkt ...

Nach dem Doppelpunkt …

Bedingte Formatierung kaputt

Hallo Herr Martin,

in einer Datei darf ich die Bedingte Formatierung nicht verwenden. Warum?

Keine Bedingte Formatierung

Keine Bedingte Formatierung

Die Antwort finden Sie in der Titelzeile: Sie haben die Datei freigegeben (Registerkarte „Überprüfen“). Entweder – oder – übrigens einige andere Funktionen (Zellen verbinden, dynamische Tabelle, Zeilen und Spalten einfügen, …) funktionieren in diesem Modus auch nicht.

Kleine Symbole würden mir genügen …

Schade, dass ich die Symbole in der bedingten Formatierung nicht verkleinern kann:

Keine Option für die Größe

Keine Option für die Größe

Die Antwort: Doch – es funktioniert mit einem Trick. Sie können eine Spalte einfügen und dort die Werte mit der Formel =S2 wiederholen. Dort schalten Sie die bedingte Formatierung mit den Symbolen ein und formatieren die Schriftfarbe weiß. Anschließend verkleinern Sie die Spaltenbreite:

Noch einmal nebenan

Noch einmal nebenan

Mindestens ein Symboldatenbereich überlappt

Was sagt mir dieser Hinweis: „Mindestens ein Symboldatenbereich überlappt. Passen Sie die Werte des Symboldatenbereichs an, sodass die Bereiche nicht überlappen.“

Bedingte Formatierung

Bedingte Formatierung

Die Antwort: Wenn Sie genau diese Daten anschauen, stellen Sie fest, dass Sie für die grünen Pfeile als Bedingung >= 20000 eingetragen haben. Für die gelben jedoch >=100000. Ein Tippfehler? Die Bedingte Formatierung verlangt eine absteigende Wertereihenfolge, beispielsweise:

>=200.000

>=100.000

Die bedingte Formatierung zeigt nicht alle Farben!

Ich verstehe es nicht. Ich wollte in der bedingten Formatierung mehrere Farben verwenden. Konkret: Wenn der BMI < 18, soll eine Farbe gewählt werden. Klappt! Wenn der BMI > 25, dann hätte ich gerne eine Schriftfarbe rot. Klappt auch. Wenn der Wert aber > 30 möchte ich zur Schriftfarbe noch eine Hintergrundfarbe. Klappt nicht! Ich habe nachgeschaut – der Haken „Anhalten“ ist nicht eingeschaltet! Was mache ich falsch?

Die dritte Farbe funktioniert nicht.

Die dritte Farbe funktioniert nicht.

Excel hat bei der bedingten Formatierung eine merkwürdige Anomalie. Probe auf Exempel. Erstellen Sie Ihre Tabelle neu. Dann müsste alles klappen. Ändern Sie die Hintergrundfarbe der zweiten Bedingung. Dann wird diese Hintergrundfarbe verwendet. Das ist verständlich – einer muss gewinnen – in diesem Beispiel gewinnt die zweite Option. Ändern Sie die Hintergrundfarbe nun wieder auf „Keine Farbe“, so wird nun diese Option verwendet und nicht die Farbe der dritten Bedingung.

Lösung 1: Löschen Sie die zweite Bedingungen und erstellen Sie sie neu.

Lösung 2: Ändern Sie die Bedingung von: Zellwert > 25 in Zellenwert zwischen 25 und 30. Und verwenden Sie die Schriftfarbe ein weiteres Mal bei der dritten Bedingung (Zellwert > 30)

Obwohl "keine Farbe" gewählt wurde überschreibt diese Einstellung die dritte Option.

Obwohl (oder weil?) „keine Farbe“ gewählt wurde überschreibt diese Einstellung die dritte Option.

 

Keine bedingte Formatierung

Mein Kollege hat in Excel eine bedingte Formatierung eingeschaltet. In meinem Excel taucht sie aber nicht auf:

Bedingte Formatierung

Bedingte Formatierung

Keine Bedingte Formatierung

Keine Bedingte Formatierung

Die Antwort: Der Kollege hat die bedingte Formatierung in Excel 2013 erstellt. In Excel 2007 konnte eine bedingte Formatierung noch nicht per Verweis auf ein anderes Tabellenblatt arbeiten – man musste dazu einen Namen erstellen; dann hat es in Excel 2007 funktioniert. Erst ab Excel 2010 konnte man direkt in der bedingten Formatierung auf ein anderes Tabellenblatt zugreifen.

Da Ihr Kollege nun eine Formel erstellt hat (=ZÄHLENWENN(Tabelle1!…) wird diese Formel (und damit auch die bedingte Formatierung) gelöscht, wenn Sie diese Datei in Excel 2007 öffnen.

Die bedingte Formatierung funktioniert nicht

Warum kann ich nicht mit der bedingten Formatierung bestimmte Länder hervorheben?

Die bedingte Formatierung funktioniert nicht

Die bedingte Formatierung funktioniert nicht

Wenn Sie eine Formel verwenden, die einen Zelle mit einem Text vergleicht, muss der Text in Anführungszeichen geschrieben werden. Dann funktioniert es:

Texte immer in Anführungszeichen!

Texte immer in Anführungszeichen!

Zugegeben: An vielen Stellen ist Excel nicht konsequent. Wenn Sie sich die Texte über die „Regeln zum Hervorheben von Zellen“ anzeigen lassen, müssen – ja DÜRFEN – Sie keine Anführungszeichen setzen:

Hier ohne Anführungszeichen!

Hier ohne Anführungszeichen!

Zuviel ist zuviel

Eine Exceltanelle hat 1.048.576 Zeilen und 16.384 Spalten. Das ist Augenwischerei. Er geht schon vorher „in die Knie“.

In einer Liste befinden sich 10.000 Datensätze (in 16 Spalten). In einer Spalten wird mit der bedingten Formatierung alle doppelten Werte mit einer Farbe gekennzeichnet.

Duplikate finden

Duplikate finden

Wenn Sie nun die Liste nach den Farben sortieren oder filtern möchten (was eigentlich recht flott geht), wird Excel sehr, sehr langsam. Häufig zeigt er „keine Rückmeldung“ an. Manchmal „fängt“ er sich wieder, manchmal stürzt er an dieser Stelle ab.

Ich vermute, dass beim Sortieren und Filtern im Hintergrund eine permanante Neuberechnung der bedingten Formatierung geschieht, so dass Excel an seine Auslastungsgrenzen gerät.

Keine Rückmeldung

Keine Rückmeldung

Formate gehen nicht weg!

Jetzt versuche ich es schon seit einer halben Stunde – ich bekomme einfach die blaue Hintergrundfarbe nicht weg. Ist da etwas einprogrammiert?

Farbe geht nicht weg.

Farbe geht nicht weg.

Schauen Sie mal in der Bedingten Formatierung nach (Start / Formatvorlagen / Bedingte Formatierung). Vielleicht wurde die blaue Farbe als Regel eingestellt. Man kann sie so entfernen oder auch über Start / Bearbeiten / Löschen / Formate löschen.

Natürlich kann man die Formate löschen.

Natürlich kann man die Formate löschen.

Bedingte Formatierung funktioniert nicht

Jetzt habe ich schon mehrmals die bedingte Formatierung eingeschaltet – aber sie funktioniert einfach nicht. Oder nicht richtig!

Das Problem liegt darin, dass ein Einschalten der bedingten Formatierung nicht bereits vorhandene bedingte Formate löscht. Sie bleiben bestehen. Welche der Bedingungen Excel verwendet, kann man leicht einsehen unter Start / Formatvorlagen / Bedingte Formatierung / Regeln verwalten / Dieses Arbeitsblatt.

Eine bedingte Formatierung schaltet die anderen nicht aus.

Eine bedingte Formatierung schaltet die anderen nicht aus.