Mit den heutigen Excel-Tipps verhält es sich fast so wie mit einem Überraschungsei. Es gibt nämlich gleich drei Dinge auf einmal:
- Quickie 133: Komfortgewinn bei der Datenüberprüfung („Schokolade…“)
- Quickie 134: Einen mysteriösen Bug überwinden („…und was Spannendes…“)
- Quickie 135: Konzentration auf das Wesentliche („…und was zum Spielen!“)
Na gut, das war jetzt vielleicht etwas an den Haaren herbeigezogen. Aber vielleicht freust du dich ja trotzdem über die aktuellen Excel-Quickies 🙂
Excel-Quickie Nr. 133: Eigene Tabellenformatvorlage lässt sich nicht mehr ändern?
Wenn du mit formatierten („intelligenten“) Tabellen arbeitest, dann weißt du vermutlich, dass man neben den vordefinierten Tabellenformatvorlagen auch eigene Vorlagen erstellen und diese nach seinem Geschmack anpassen kann. Falls dir das bisher noch nicht bekannt war: Ein Rechtsklick auf eine vorhandene Vorlage und der Menüpunkt „Duplizieren“ erzeugt eine Kopie der entsprechenden Vorlage
Formatvorlage duplizieren
Diese Kopie taucht dann in der Kategorie „Benutzerdefiniert“ auf und kann über einen weiteren Rechtsklick und „Ändern…“ angepasst werden:
Eigene Formatvorlage anpassen
Allerdings gibt es einen etwas mysteriösen Bug, auf den man auch mit keinem Hinweis aufmerksam gemacht wird: Sobald auch nur für irgendein Arbeitsblatt in der aktuellen Arbeitsmappe der Blattschutz aktiviert ist, lässt sich eine Tabellenformatvorlage nicht mehr ändern. Und das muss nicht einmal das Arbeitsblatt mit der formatierten Tabelle sein. Es gibt keine Fehlermeldung und der entsprechende Menüpunkt zum Ändern ist noch vorhanden und auch nicht ausgegraut, aber beim Anklicken passiert einfach nichts.
Falls du also auch schon einmal an diesem Problem verzweifelt bist: Einfach bei allen Blättern kurzfristig den Blattschutz deaktivieren und dann die gewünschten Änderungen an der Formatvorlage vornehmen. Hinterher kann der Blattschutz dann wieder aktiviert werden.
Excel-Quickie Nr. 134: Definierte Namen komfortabel in Datenüberprüfung einfügen
Arbeitest du auch gerne mit definierten Namen („Formeln | Namen definieren“)?
Namensmanager: Übersicht aller definierten Namen
Und nutzt du auch hin und wieder die Datenüberprüfung, zum Beispiel um Dropdown-Felder zu definieren? Dann hast du dich vielleicht auch schon mal darüber geärgert, dass einem in der Datenüberprüfung die zuvor definierten Namen nirgendwo als Datenquelle angeboten werden. Man muss sich immer an den vergebenen Namen erinnern und ihn dann von Hand eintippen.
Oder man verwendet den folgenden simplen Trick.
Man kann nämlich auch bei geöffnetem Fenster „Datenüberprüfung“ in das Menü „Formeln“ wechseln. Wählt man dort die Schaltfläche „In Formel verwenden“ aus, bekommt man eine Liste aller vorhandenen definierten Namen und kann den gewünschten bequem per Mausklick auswählen und in die Datenüberprüfung übernehmen:
Vorhandene Namen aus Liste auswählen
Einfacher geht’s nicht!
Excel-Quickie Nr. 135: Konzentration auf das Wesentliche
Wir werden ohnehin mit zu vielen Informationen zugeschüttet, daher sollten wir es dem Benutzer unserer Excel-Anwendungen so einfach wie möglich machen, sich nur auf die relevanten Bereiche zu konzentrieren. Dabei hilft es, dass man sämtliche nicht benötigten Zeilen und Spalten einfach ausblendet, so dass nur der eigentliche Eingabebereich sichtbar bleibt.
(Weitere Tipps für ein besseres Tabellendesign findest du in diesem Artikel)
Am Schnellsten geht das per Tastatur: Stelle die aktive Zelle rechts neben deine Datentabelle, und zwar in die Spalte, die als erste ausgeblendet werden soll. Über die Tastenkombination Strg + Umschalt + Pfeil nach rechts werden sämtliche Zellen bis zur letzten Spalte XFD markiert (die Umschalt-Taste liegt über der Strg-Taste). Jetzt drückst du die Tastenkombination Strg+8 (hier ist die Taste mit der Ziffer „8“ über den Buchstaben gemeint, nicht die Funktionstaste F8 und auch nicht die 8 auf dem Ziffernblock rechts!). Und schon werden sämtliche zuvor markierten Spalten ausgeblendet.
Nun stellst du die aktive Zelle in eine Zeile unterhalb deiner Tabelle und markierst sämtliche Zeilen bis ans untere Ende über die Tastenkombination Strg + Umschalt + Pfeil nach unten. Und über die Tastenkombination Strg + 9 (wieder die 9 über dem Buchstabenfeld) werden sämtliche markierten Zeilen ausgeblendet.
Wenn du jetzt noch die Gitternetzlinien abschaltest („Ansicht | Gitternetzlinien“), dann wird aus der ursprünglichen Ansicht mit dem kompletten Tabellenblatt…
Tabellenblatt in der Gesamtansicht
… die deutlich aufgeräumtere Ansicht
Nur der relevante Bereich bleibt sichtbar
Zum erneuten Einblenden der Zeilen und Spalten markierst du einfach die komplette Tabelle über einen Klick auf den Schnittpunkt oberhalb der Zeile 1 und links von Spalte A. Jetzt kannst du über einen Rechtsklick auf einen beliebigen Spaltenbuchstaben (bzw. auf eine beliebige Zeilennummer) und die Option „Einblenden“ den kompletten Bereich wieder anzeigen lassen.
Spalten und Zeilen wieder einblenden
So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!
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.
Zu 134: „Einfacher geht’s nicht!“
Doch: Einfach F3 drücken. 🙂
Können Sie mir einen Tipp geben, wie ich meinen Hyperlink aus Excel betr. E-Mail mit Betreff an eine bestimmte Person wieder zum laufen bringen kann. Derzeit springt der Link einfach auf die Google-Internetseite
Hallo Ernst,
Öffne mal die Eigenschaften des Links (rechte Maustaste „Hyperlink bearbeiten“).
Im Dialogfenster „Hyperlink bearbeiten“ muss ganz links in der Liste „Link zu“ der Eintrag „E-Mail-Adresse“ ausgewählt sein. Ansonsten wähle diesen aus.
Dann einfach E-Mail-Adresse und Betreff erfassen und schon sollte es gehen.
Guten Morgen Martin,
vielen Dank für deine Excel-Tipps und „Fresh-Ups“. Habe über deinen Newsletter schon viele neue Dinge kennenlernen können!
Eine kleine Ergänzung zu deinem Excel-Quickie Nr. 134:
Es geht tatsächlich noch ein wenig schneller (zumindest für Tastatur/Shortcut-Liebhaber), nämlich mit der Taste F3, die den Dialog „Namen einfügen“ aufruft. Das funktioniert auch meist, während andere Dialog-Fenster (wie Datenüberprüfung, Funktionsargumente etc.) geöffnet sind…
Beste Grüße,
Daniel
Hallo Sven und Daniel,
vielen Dank für den F3-Tipp! An diese Funktionstaste habe ich überhaupt nicht mehr gedacht, die war bei mir komplett in Vergessenheit geraten. Aber wenigstens denken meine Leser mit 🙂
Schöne Grüße,
Martin
Hallo Martin,
danke für die wieder einmal hilfreichen kleinen Tricks.
Gerade habe ich in einem anderen Tutorial gelernt, wie es beim Quickie Nr. 134 noch einfacher / schneller geht: einfach F3 drücken, wenn der Cursor in dem Feld zur Eingabe der Liste steht.
Gruß Andreas
Hallo Andreas,
danke für die Ergänzung, das hatten Sven und Daniel oben in den Kommentaren auch schon vorgeschlagen.
Schöne Grüße,
Martin
Hallo Martin,
ich kann auch noch etwas zum „Excel-Quickie Nr. 135″ beitragen.
Für den Fall, dass der Ersteller der Excel-Tabelle nicht der unbedingt der Nutzer dieser ist und das Ausblenden der leeren Zeilen und Spalten dem Nutzer überlassen werden soll, kann man die leeren Zeilen und Spalten auch mittels Formel berechnen und per Hyperlink markieren (viele Excel-Nutzer beschäftigen sich ja eher nicht mit Tipps rund um Excel und sind somit nur einfache Anwender). Anhand Deiner Beispieltabelle könnte man die Zellen E1 bis E4 benutzen und folgende Formeln eintragen:
– In E1 werden die leeren Zeilen mit folgender Formel ermittelt: =SUMMENPRODUKT(MAX(($A:$A““)*ZEILE($A:$A))+1)&“:1048576″
– In E2 werden die leeren Spalten mit folgender Formel ermittelt: =WECHSELN(ADRESSE(2;SUMMENPRODUKT(MAX(($1:$1″“)*SPALTE($1:$1))+1);4);2;)&“:XFD“
– In E3 wird ein Hyperlink zum markieren der leeren Zeilen mit folgender Formel erzeugt: =HYPERLINK(„#Leere_Zeilen_markieren“;“Leere Zeilen zum ausblenden markieren“)
– In E4 wird ein Hyperlink zum markieren der leeren Spalten mit folgender Formel erzeugt: =HYPERLINK(„#Leere_Spalten_markieren“;“Leere Spalten zum ausblenden markieren“)
Damit die Markierung der ermittelten leeren Zeilen und Spalten mittels der beiden Hyperlinks auch funktioniert, müssen im Namens-Manager (Aufruf mit Strg + F3) die beiden folgenden Namen angelegt werden:
Name: Leere_Zeilen_markieren
Bezieht sich auf: =INDIREKT(Tabelle2!$E$1)
Name: Leere_Spalten_markieren
Bezieht sich auf: =INDIREKT(Tabelle2!$E$2)
Mit einem Rechts-Klick auf eine der markierten Zellen kann man dann die Ausblendung vornehmen bzw. mit Deinem Tipp „Strg + 8 oder Strg + 9“.
Ich wünsche gutes Gelingen beim Ausprobieren.
Hallo Sven,
vielen Dank für die interessante Ergänzung. Die Idee klingt gut, nur funktionieren bei mir die beiden Formeln zum ermitteln der leeren Zeilen und Spalten nicht. Irgendwo ist da der Wurm drin und ich konnte ihn auf die Schnelle auch noch nicht finden. Vielleicht kannst du selbst nochmal einen Blick darauf werfen.
Schöne Grüße,
Martin
Hallo Martin,
ich habe Dir meine Beispieldatei mal per E-Mail gesandt, in dieser funktioniert die von mir beschriebene Variante. Du kannst diese gern hier beifügen.
VG
Sven
Hallo Sven,
vielen Dank für die Beispieldatei, jetzt sehe ich auch, wo das Problem lag. Die Blogsoftware hat aus deinem Kommentar die „<>„-Zeichen aus den Formeln entfernt. Hier sind nochmal die beiden Formeln:
=SUMMENPRODUKT(MAX(($A:$A<>"")*ZEILE($A:$A))+1)&":1048576"=WECHSELN(ADRESSE(2;SUMMENPRODUKT(MAX(($1:$1<>"")*SPALTE($1:$1))+1);4);2;)&":XFD"Und hier ist der Download-Link zu deiner Beispieldatei, falls sich die anderen Leser die Tipperei sparen möchten:
Beispieldatei
Vielen Dank nochmal und schöne Grüße,
Martin
Excel-Quickie Nr. 134 geht mit F3 einfacher!
Deine Tipps sind wie immer, sehr hilfreich.
Ich nutze eigene Tabellemformatvorlagen schon länder.
Hast Du eins Lösung, wie man die in eine andere Mappe übertrage kann?
Bei den Zellenformatidrung gibt es ja einen Menü-Eintrag. Für o. g. Funktion finde ich nichts.
Wűrde mich über eine Lösung freuen.
Versuche über Vorlagen (xltx) ein Arbeitsblatt mit den entspr. Inhalten zu laden, funktionieren ni ht wirklich.
Hallo Gerhard,
der einzig mir bekannte Weg ist, das Arbeitsblatt, welches die formatierte Tabelle mit der eigenen Vorlage enthält, in die neue Mappe zu kopieren. Damit wird auch die Formatvorlage übernommen. Anschließend kann man das kopierte Arbeitsblatt wieder löschen, die Formatvorlage bleibt trotzdem erhalten.
Schöne Grüße,
Martin
Hallo!
Ich bräuchte Hilfe…
Problem 1: Über die Datenüberprüfung versuche ich den Tabellenanwendern ein Datumformat vorzugeben. So soll Eingabe nur im Format TT.MM.JJJJ. möglich sein.
Problem 2: Qualífizierungen laufen nach x Tagen ab. Über die bedingte Formatierung wollte ich das Ampel-Symbol nutzen, um dem Anwender den Status mitzuteilen (grün = Qualifizierung aktuell, gelb = Qualifizierung auffrischen, rot = Qualifizierung abgelaufen). Als ersten Schritt habe ich vom eingegebenen Datum das heutige abgezogen. Die Tage seit der Qualifzierung werden dann allerdings als Negativwert angezeigt, was nicht mit der bedingten Formatierung funktioniert.
Hallo Matthias,
zu 1:
Daten | Datenüberprüfung | Zulassen: Datum (dann noch einen Gültigkeitsbereich eingeben und fertig)
zu 2:
Da gibt es verschiedene Möglichkeiten, abhängig vom Aufbau und Inhalt deiner Tabelle.
Variante 1: Du verwendest eigene Formatierungsregeln mit Formeln. Dann kannst du auch mit negativen Werten arbeiten. Allerdings funktionieren dann die Symbolsätze nicht mehr, sondern nur noch Zellfarben
Variante 2: Was ist, wenn du die Reihenfolge in der Berechnung änderst? Also vom heutigen Datum das Qualifizierungsdatum abziehst? Damit erhältst du immer positive Werte.
Schöne Grüße,
Martin
Danke Martin… 🙂
Mit Excel zu arbeiten fällt mir nicht so einfach. Daher viel Dank für die Tipp, wie man besser mit Excel zurecht kommt. Es ist ein guter Tipp, sich auf das wesentliche zu konzentrieren. Man möchte immer gleich so viele Date wie möglich sehen, dabei ist das so viel zu unübersichtlich.