Verborgene Talente in Excel: Als Tabelle formatieren 22

Die Funktion "Als Tabelle formatieren" kann deutlich mehr, als nur Listen optisch ansprechender zu gestalten.
 

Mancher Excel-Anwender hat sich vielleicht schon einmal gefragt, warum es einen Menüpunkt gibt, der da heißt “Als Tabelle formatieren”. Da man in einer Tabellenkalkulation zwangsläufig mit Tabellen arbeitet, erschließt sich der Sinn hinter dieser Option nicht auf Anhieb.

Gut, damit lassen sich mit einem Mausklick die Tabellen etwas ansprechender gestalten: Farbige Streifen und hervorgehobene Überschriften machen die oft langweiligen Listen zugegebenermaßen deutlich besser lesbar. Und dass ein Autofilter gesetzt wird, ist sicherlich auch bequem.

Hinter der unscheinbaren Schaltfläche “Als Tabelle formatieren” verbergen sich aber mächtige und hilfreiche Funktionen, die man sich als Excel-Geschädigter auf jeden Fall näher ansehen sollte!

Und so geht’s:

Formatvorlagen

Beginnen wir mit dem Naheliegenden: Neben den vordefinierten Vorlagen, die beim Klick auf die Schaltfläche angezeigt werden, hat man auch die Möglichkeit, eigene Vorlagen zu definieren. Dabei gibt es zwei grundsätzliche Möglichkeiten:

1. Eine Vorlage von Grund auf neu definieren:

Formatvorlagen erstellen

Formatvorlage neu erstellen

2. Eine vorhandene Vorlage kopieren und anpassen:

Vorhandene Vorlagen duplizieren

Vorhandene Vorlagen duplizieren

In beiden Fällen öffnet sich ein neues Fenster, in dem ich nun mit meinen Einstellung beginnen kann. Dabei lassen sich für jedes einzelne Tabellenelement unterschiedliche Formatierungen definieren. Das Vorgehen ist immer identisch:

  • Einen Namen für das neue Format vergeben
  • Das zu ändernde Tabellenelement auswählen
  • Über den Knopf “Formatieren” die gewünschte Formatierung festlegen (Farben, Rahmen, Schattierungen etc)
Format für jedes Element bestimmen

Format für jedes Element bestimmen

Wenn man damit fertig ist, steht die neue benutzerdefinierte Vorlage im normalen Auswahlfenster zur Verfügung:

Benutzerdefinierte Vorlage

Benutzerdefinierte Vorlage

Tabellentools

Wenn sich die aktive Zelle innerhalb der zuvor formatierten Tabelle befindet, gibt es ein neues Register “Tabellentools – Entwurf” im Menüband, welches vielfältige Optionen bietet:

Neues Register "Tabellentools - Entwurf"

Neues Register “Tabellentools – Entwurf”

1.Tabellengröße ändern

Angenommen, ich habe ein paar Spalten weiter rechts eine neue Summenspalte ergänzt, kann ich die Größe der Tabelle ändern, um diese neue Spalte in den Tabellenbereich aufnehmen zu lassen:

Tabelle erweitern

Tabelle erweitern

Excel formatiert auch die dazwischenliegenden leeren Spalten und ergänzt auch gleich die Überschriften:

Angepasste Tabellengröße

Angepasste Tabellengröße

2.Optionen anpassen

Hier lassen sich über die entsprechenden Checkboxen Summenzeilen ein- oder ausblenden, die letzte und erste Spalte entsprechend formatieren oder auch die Auswahllisten für den Autofilter ein- und ausblenden:

Tabellen-Optionen anpassen

Tabellen-Optionen anpassen

Angezeigte Elemente anpassen

Angezeigte Elemente anpassen

3. Dubletten entfernen

Eine sehr nützliche Funktion ist das automatische Entfernen von Dubletten in einer formatierten Tabelle.

Dubletten entfernen

Ausgangsliste mit Dubletten

Nach einem Klick auf die Schaltfläche “Duplikate entfernen” gibt man an, welche Felder für die Dublettensuche relevant sind. In meiner kleinen Adressentabelle wähle ich alle Felder aus, um nur 100%ige Dubletten zu entfernen:

Feldauswahl für die Dublettenerkennung

Feldauswahl für die Dublettenerkennung

Nach einem Hinweis, wieviele doppelte Werte gefunden und entfernt wurden, erhält man die bereinigte Tabelle:

Ergebnis der Bereinigung

Ergebnis der Bereinigung

Dublettenbereinigte Tabelle

Dublettenbereinigte Tabelle

Einfacher geht’s wohl kaum!

Automatische Erweiterung

Wenn man unmittelbar unterhalb oder rechts von einer formatierten Tabelle Daten eingibt, wird die Tabelle automatisch erweitert. Das Besondere daran:
Es werden nicht nur die Formate übernommen, sondern auch der Zellenbereich, auf den sich der Tabellenname bezieht, wird angepasst.

Zur Info:
Wenn eine Liste als Tabelle formatiert wurde, erhält diese automatisch einen Namen. In meinem Beispiel also Tabelle5 (diesen Namen kann man bei Bedarf einfach durch einen aussagefähigeren ersetzen):

Name der Tabelle

Name der Tabelle

Ich kann nun in Formeln anstelle der Zellbezüge auch den Namen angeben:
Statt
=ANZAHL2(B2:E17)
verwendet man also besser
=ANZAHL2(Tabelle5)

Fügt man nun neue Adressen unten an die Tabelle an, so umfasst der Name “Tabelle5” anschließend automatisch auch diesen erweiterten Bereich:

Automatische Anpassung der Bereiche

Automatische Anpassung der Bereiche

Man braucht also seine Formeln nicht mehr mühsam anzupassen, nur weil vielleicht neue Datensätze dazugekommen sind.

Tipp:
Diese Funktion bietet sich auch im Zusammenhang mit Dropdown-Listen an: Wenn man nämlich im Listenbereich anstelle der Zellbezüge mit den Quelldaten stattdessen den Namen der formatierten Tabelle verwendet, werden auch die in der Dropdown-Liste angebotenen Werte automatisch angepasst, sobald neue Elemente dazukommen.

Puh, das war ziemlich viel Information in einem Artikel und ich hoffe, du hast bis hierher durchgehalten. Aber es war mir wichtig, dir zu zeigen, welche Möglichkeiten sich hinter diesen unscheinbaren formatierten Tabellen verbergen.

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

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.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

22 Gedanken zu “Verborgene Talente in Excel: Als Tabelle formatieren

  • --Fragezeichen--

    Hallo Martin,
    habe hier Deinen Tipp zu den Dropdown Listen gefunden.

    Leider kann ich das nicht ganz nachvollziehen:
    Daten -> Datenüberprüfung -> Liste -> “Quelle” dort dann “=Tabelle5” eingeben funktioniert bei mir leider gar nicht (Excel 2007-2013).
    Irgendetwas mache ich falsch, nur was ist es?
    Im Namensmanager ist die Tabelle zu sehen und in Formeln funktioniert das auch, aber nicht wie beschrieben im “Dropdown”
    Kannst Du mir da bitte weiterhelfen?

    Beste Grüße
    –Fragezeichen–

    • Martin Weiß Beitragsautor

      Hallo -Fragezeichen-,

      ich hätte mich im Artikel-Tipp etwas genauer ausdrücken sollen: Die formatierte Tabelle an sich lässt sich nicht direkt im Dropdown als Quelle verwenden. Du musst einen neuen Namen definieren, der sich auf eine einzelne Spalte in der Tabelle bezieht:
      Namen definieren

      Im Namensmanager tauchen somit die ursprüngliche Tabelle und der neue Name auf, der sich auf die Tabellenspalte bezieht:
      Namensmanager

      Und diesen neuen Namen (hier “Vorname”) verwendest du dann als Quelle für das Dropdown-Feld in der Datenüberprüfung:
      Datenüberprüfung

      Damit hast du indirekt die Tabelle als Quelle und profitierst davon, dass sich der Bezug dynamisch anpasst, wenn die Tabelle länger wird.

      Schöne Grüße,
      Martin

      • --Fragezeichen--

        Hallo Martin,
        vielen Dank! Das wars doch 🙂 vom Knie aus in der Nase gebohrt typisch MS.
        Kannst Du mich bitte in diesen Zusammenhang dann noch mit den “Special” Funktions dazu aufklären: [#Alle] [#Daten] …? und den Semikolons, die da noch möglich sind? Oder habe ich dazu eine Anleitung von Dir noch nicht gesehen 🙂 ?

        Beste Grüße
        –Fragezeichen–

  • Petra

    Hallo Martin!
    Das ist eine super Beschreibung! Aber…
    Ich arbeite momentan viel mit Tabellen, die ich auch als Tabellen formatiert habe. Teilweise habe ich DropDown-Listen hinterlegt, teilweise sind Rechenformeln hinterlegt, in andere Spalten kann man frei eintragen. Mein Problem nun: wenn ich unten an die Tabelle einen weiteren Datensatz anhänge, dann übernimmt es mir zwar automatisch das Tabellenformat, aber die Spalten mit DropDown sind leer oder es sind falsche Listen darin und auch die Rechenformeln übernimmt es mir nicht in die neuen Zeilen. Was mache ich verkehrt??? Hast Du eine Idee?
    Vielen Dank,
    Petra
    P.S. Ich habe mehrere Tabellen gemacht, in manchen funktioniert es, in anderen nicht. Deshalb bin ich total ratlos, warum es in manchen nicht geht.

    • Martin Weiß Beitragsautor

      Hallo Petra,

      das liegt vermutlich daran, dass Du zuerst alles als Tabelle formatiert hast und erst später die Formeln und Dropdown-Listen definiert hast. Am besten ist, Du konvertierst die Tabelle wieder in einen Bereich zurück, baust dann die Dropdown-Listen und Formeln ein und wandelst erst zum Schluß alles in eine formatierte Tabelle um. Dann müssten bei neuen Einträgen auch die Formeln übernommen werden.

      Grüße,
      Martin

      • Petra

        Hallo Martin!

        Vielen Dank für die Antwort! Auch wenn sie mich nicht glücklich macht, weil das recht viel Arbeit ist… Aber bei der nächsten Tabelle werde ich es so machen, wie Du geschrieben hast.

        Liebe Grüsse,
        Petra

  • Jörg W.

    Ich habe eine Verknüpfung zu einer externen CSV Datei hergestellt. Wenn ich die importierten Daten in eine Tabelle formatieren möchte kommt die Fehlermeldung “Ihre Auswahl überlappt mindestens einen externen Datenbereich. Möchten Sie Ihre Auswahl in eine Tabelle konvertieren und alle externen Verbindungen entfernen?” Wo könnte der Fehler liegen? Wenn ich die CSV direkt in eine Tabelle formatiere funktioniert es einwandfrei. Ich bekomme jeden Monat neue CSV Dateien und möchte diese dann über Pivot auswerten.

    Danke vorab.

    • Martin Weiß Beitragsautor

      Hallo Jörg,

      formatierte Tabellen und externe Daten schließen sich gegenseitig aus. Das sollte aber für die von Dir beschriebenen Zwecke kein Problem sein. Wenn Du die CSV-Daten also über eine externe Verbindung importiert hast, dann hat der Datenbereich ohnehin einen eigenen Namen (zu finden unter Formeln – Namensmanager). Diesen Namen kannst Du als Datenquelle in der Pivottabelle angeben und hast damit immer den aktuellen Bereich abgedeckt.

      Schöne Grüße,
      Martin

  • Bastian K.

    Hallo Martin,

    in meiner Tabelle arbeite ich viel mit Hintergrundfarben, die ich manuell zeilenweise formatiere. Seit einer Weile ändert sich die Hintergrundfarbe automatisch, wenn ich neue Zeilen hinzufüge. Das betrifft die neuen Zeilen und auch alte, aber nicht alle und auch nicht alle Spalten der neuen Zeilen, die sich färben. In einer anderen Tabelle ändert sich bei einer neuen Zeile immer das Format in einer Spalte aber hier nur bei dem neuen Datensatz. Das Zahlenformat springt von Standard auf Datum und die Hintergrundfarbe wird grün. In beiden Tabellen liegt es nicht an einer bedingten Formatierung. Hast du eine Idee woran das liegen könnte?

    Danke
    Bastian

    • Martin Weiß Beitragsautor

      Hallo Bastian,

      ich vermute mal, es handelt sich um formatierte Tabellen (Menü “Start – Als Tabelle formatieren”). Diese formatierten Tabellen haben eine gewissen “Intelligenz”, wozu auch Zahlenformate und Farben gehören. Je nach gewählter Formatvorlage ändern sich die Farben und Formate bei neu hinzugefügten Datensätzen. Das ist in der Regel sehr praktisch, kann aber manchmal auch zu unerwünschten Effekten führen.

      Grüße,
      Martin

      • Bastian K.

        Hallo Martin,

        danke für deine Antwort. Genau es geht um eine formatierte Tabelle. Sie ist generell auf hellblaue und dunkelblaue abwechselnde farben eingestellt. Standard Tabellenformat Mittel 9. Das ist soweit gewünscht, wie du schon schreibst. Was nicht gewünscht ist, dass die neuen Datensätze bis zu Spalte I rot markiert werden und danach die eine der beiden blauen Farbetöne haben. Und auch bei den alten Datensätzen werden einige, aber nicht alle bis Spalte I rot markiert. Ich kann mir das durch die formatierte Tabelle nicht erklären…
        Wenn das so nicht verständlich ist, kann ich dir die Datei mal irgendwie schicken?

        Danke
        Bastian

        • Martin Weiß Beitragsautor

          Hallo Bastian,

          dann wurde offensichtlich die rote Formatierung irgendwie in die Automatik der formatierten Tabelle übernommen. Du könntest versuchen, die formatierte Tabelle wieder zurück in einen Bereich zu konvertieren und anschließend erneut als Tabelle formatieren. Wenn das auch nichts hilft, habe ich momentan leider auch keine bessere Idee.

          Grüße,
          Martin

          • Bastian K.

            Hallo Martin,

            mansche Lösungen sind auch zu einfach. Hat so funktioniert, vielen Dank!
            Bastian

  • Marius

    Hallo Martin,

    habe ein eher einfaches Problem, wozu ich die einfachste Lösung einfach nicht finde.

    Ich habe auf einem Sheet mehrere als Tabelle formatierte Bereiche. Ganz oben in die erste Tabelle tragen meine Mitarbeiter ein was ihnen übers Jahr auffiel.

    Ich geh dann hin und schiebe diese Auffälligkeiten in bestimmte Bereiche (Bereich 1 – 6, jeder Bereich hat wieder ne neue Tabelle).

    Wie geh ich am besten vor wenn ich aus der oberen Tabelle eine Zeile inklusive Formeln auschneiden und sie im entsprechenden Bereich einfügen möchte?

    Bisher hat ich das Problem, dass bei strg+x die obere Tabelle dann ein “Loch” hat und bei strg+c und anschließendem entfernen der ausgeschnittenen Zeile die Formeln dann logischerweise verschwinden.

    Was kannst du mir empfehlen?

    Gruß Marius

    • Martin Weiß Beitragsautor

      Hallo Marius,

      im Grunde bleiben nicht viel mehr Möglichkeiten, als die von Dir schon genannten. Du kannst höchstens überlegen, ob es nicht vielleicht über Formeln möglich ist, die Daten in den nachgeordneten Tabellen aus der ersten Tabelle zu holen.

      Schöne Grüße,
      Martin

  • Laie

    Hallo Martin,
    ich bin fasziniert von deinem Artikel! Er hat mir soooo viel weitergeholfen! Besten Dank!
    Eine Frage habe ich allerdings: In meiner Tabelle befinden sich zwei Spalten mit Datenüberprüfungen. Hier dürfen nur Werte eingegeben werden, wenn eine andere Spalte ein Kriterium erfüllt. Erweitere ich meine Tabelle nun um einen neuen Datensatz/eine neue Zeile, so werden diese hinterlegten Datenüberprüfungen in den beiden Spalten nicht mit übernommen. Gibt es hierfür eine Lösung?

    • Martin Weiß Beitragsautor

      Hallo Laie,

      das freut mich sehr, wenn der Artikel hilft. Formatierte Tabelle sind wirklich genial.
      Auch Datenüberprüfungen oder etwaige Formeln werden grundsätzlich in neue Zeilen übernommen, das ist ebenfalls eine der angenehmen Eigenschaften. Vielleicht hattest Du beim Einrichten der Datenüberprüfung nicht die komplette Spalte markiert? Ansonsten empfehle ich, dies einfach nochmal zu versuchen, denn funktionieren müsste es.

      Schöne Grüße,
      Martin

  • Konrad Roider

    hallo,
    da ich einabsoluter laie bin suche ich nach hilfe.vielleicht bekomme ich sie bei dir.habe es irgendwie geschaft das ich die automatische erweiterung der tabelle ausschalte (war nicht mein vorhaben).auch wenn ich es neu öffne/starte oder neue tabelle erstelle erweitert sie sich nicht mehr.wie kann ich das wieder zurückstellen das sie sich wieder erweitert??

    • Martin Weiß Beitragsautor

      Hallo Konrad,

      mir ist nicht bekannt, dass man die automatische Erweiterung einer formatierten Tabelle überhaupt ausschalten kann. Außer, man konvertiert die formatierte Tabelle wieder in einen normalen Bereich zurück (Tabellentools – Entwurf – In Bereich konvertieren).

      Mit welcher Excel-Version arbeitest Du denn?

      Schöne Grüße,
      Martin