Die „Suchen & Ersetzen“-Funktion ist eine hinlänglich bekannte und nützliche Funktion in Excel, wenn im großen Stil bestimmte Inhalte einer Tabelle durch andere Inhalte ersetzt werden sollen.
Was viele aber nicht wissen: Mit dieser Funktion lassen sich nicht nur Werte und Zahlen austauschen, sondern auch Formatierungen! So kann man auf die Schnelle z.B. Schriftarten, Umrahmungen oder Farben ersetzen und so seiner Tabelle ruck-zuck ein neues Aussehen verpassen.
Und so geht’s:
Suchen und Auswählen
Als einfaches Beispiel dient mir die Sporttabelle aus dem Artikel zum SUMMENPRODUKT().
Hier hatte ich ja die verschiedenen Einträge farblich markiert.
Farblich formatierte Tabelle
Auch wenn die Tabelle nicht sonderlich schön und groß ist, reicht sie zur Veranschaulichung völlig aus.
Ziel soll es sein, den orange markierten Einträgen in einem Aufwasch einen neuen Anstrich zu verpassen. Dazu rufen wir die normale „Ersetzen“-Funktion auf, die in der Schaltfläche „Suchen und Auswählen“ zu finden ist (alternativ über die Tastenkombination STRG+F):
Suchen und Auswählen
Damit wird die allseits bekannte Suchen-und-Ersetzen-Maske aufgerufen. Hier trägt man ja üblicherweise die Werte ein, nach denen man sucht. Die wenigsten Anwender werden sich dieses Fenster jedoch genauer angesehen haben. Rechts neben den Feldern für die Werteeingaben gibt es nämlich etwas unscheinbar eine „Format…“-Schaltfläche. Klicke jetzt auf den kleinen Pfeil, der sich am rechten Rand der Schaltfläche befindet und wähle den Eintrag „Format von Zelle wählen…“ aus:
Suche nach Formaten
Das Eingabefenster verschwindet nun und der Mauszeiger verwandelt sich in ein weißes Kreuz mit einer kleinen Pipette. Klicke damit in irgendeine der orangen Zellen, die wir verändern wollen. Nun erscheint das Eingabefenster wieder und man sieht in dem kleinen Vorschaufeld die Formatierung der ausgewählten Zelle:
Gesuchtes Format
Als nächstes geben wir an, durch welches Format wir das vorhandene ersetzen wollen:
Format ersetzen
Und jetzt sind unserer Fantasie keine Grenzen gesetzt: Wir wollen der Zelle nicht nur eine andere Farbe, sondern auch eine andere Schrift, ein Muster und einen Rahmen:
Schrift ändern
Rahmen hinzufügen
Füllfarbe und -muster
Ein Klick auf „OK“ zeigt uns nun die Vorschau des neuen Formats, das wir über die Schaltfläche „Alle ersetzen“ auf unsere Tabelle anwenden können:
Formatvorschau
Und so sieht das Ergebnis aus:
Ergebnis: Die ersetzten Formate
Zugegeben, nicht schön, aber man sieht den Effekt. Aber warum wurden nicht alle orangen Zellen neu formatiert?
Formatierung manuell anpassen
Die Antwort ist recht einfach: Da wir das Format einer bestehenden Zelle gewählt hatten, wurden natürlich auch sämtliche Formatierungen dieser Zelle für die Suche übernommen. Und somit auch die Ausrichtung, die in meiner Beispielzelle eben horizontal zentriert war. Da die Spalte mit den Disziplinen aber linksbündig formatiert ist, werden diese Zellen beim Ersetzen nicht berücksichtigt.
Doch dieses Problem lässt sich leicht beheben.
Im Suchen-Dialog klicken wir, nachdem die Formatierung der Zelle übernommen wurde, einfach nochmal direkt auf den Format-Knopf:
Gesuchtes Format anpassen
Nun klicken wir im Register „Ausrichtung“ einfach auf den Knopf „Inhalte löschen“ und entfernen damit die entsprechenden Formatierungen:
Ausrichtung: Inhalte löschen
Ausrichtung wird nicht berücksichtigt
Und schon haben wir auch für die Disziplin-Spalte die „wunderschöne“ Formatierung übernommen.
Korrigierte Version
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.
Hallo,
tolle Seite!!!
Ich habe ein Problem, vielleicht gibt auch da eine Lösung:
Wenn ich in einer Zelle 2 Worte stehen habe, eins ist schwarz eins ist rot. Und ich möchte das rote Wort durch ein anderes ersetzen.
Wie kann ich erreichen, dass das neue Wort (und nur das!) wieder rot ist?
Standardmäßig macht excel alle Worte nach der „Ersetzen-Funktion“ schwarz. Oder, wenn man es wie oben beschrieben, anders definiert eben anders.
Also so:
schwarz rot —> schwarz ist schwarz rot ist rot
schwarz red —> schwarz bleibt schwarz red bleibt rot
Viele Grüße und Danke, Klaus
Hallo Klaus,
das wäre tatsächlich schön, geht aber leider nicht. Jede Formatänderung bezieht sich immer auf die gesamte Zelle.
Schöne Grüße,
Martin
Hallo Martin, zunächst „Danke“ für die Anleitung.
Ich habe das mal mit Zellen ausprobiert, die rot gefüllt sind und wollte sie nun grün einfärben.
Kannst du dir erklären, weshalb – wenn ich „Format von Zelle wählen“ benutze und ich dann eine Zelle mit Schriftschnitt „Standard“ auswähle – auch Zellen grün gefärbt werden, die eine fette Schrift haben?
Bedeutet der Schriftschnitt „Standard“ einfach nur „egal, ob fett oder kursiv“? Oder ist das ein Bug?
Hallo Thomas,
es kommt darauf an, wo du „Format von Zelle wählen“ benutzt hast: Im Bereich „Suchen“ nach oder in „Ersetzen durch“? Idealerweise verwendest du in beiden Fällen „Format von Zelle wählen“. Dann kannst du zumindest sicher sein, dass ausschließlich die entsprechend formatierten Zellen gefunden werden und dass alle Formate aus der „Suchen“-Zelle übertragen werden.
Schöne Grüße,
Martin
Sehr gut aber wenn ich nur einen Teil in der Zelle ändern/formatieren möchte.
Hallo Maik,
für diesen Fall gibt es leider keine Automatik. Wenn nur Teile einer Zelle umformatiert werden sollen, geht das nur manuell.
Schöne Grüße,
Martin