Verborgene Talente in Excel: Als Tabelle formatieren 75

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.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar

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

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

  • Avatar-Foto
    --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–

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        --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–

      • Avatar-Foto
        Carola Lück

        auch in der Version 365 im Jahr 2021 hat sich noch nichts daran geändert, dass das Gültigkeitsfenster von MS vergessen wurde, mit der strukturierten Syntax zu bestücken, selbst exakt für eine Spalte, und man weiterhin Namen dafür verwenden muss. Von hinten durch die Brust…

        Hat sich eigentlich schon eine Lösung für das Blattschutzproblem gefunden? Ich hab noch keine und verwende nach wie vor Farbe (grau) für Formelspalten: Eingabe auf eigene Gefahr…

        Gruß
        Carola

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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

  • Avatar-Foto
    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.

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        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

        • Avatar-Foto
          Martin Weiß

          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

          • Avatar-Foto
            Bastian K.

            Hallo Martin,

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

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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?

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    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??

    • Avatar-Foto
      Martin Weiß

      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

      • Avatar-Foto
        Andreas E.

        Bei einer meiner Tabellen scheint es so zu sein, dass die formatierte Tabelle nicht mehr automatisch erweitert wird weder nach unten, noch zur Seite, sobald ich das Arbeitsblatt schütze, selbst wenn keine einzige Zelle gesperrt ist. Gibt es wirklich keine Möglichkeit seine Formeln in einer formatierten Tabelle zu schützen?

        • Avatar-Foto
          Martin Weiß

          Hallo Andreas,

          die Formeln innerhalb einer formatierten Tabelle können schon geschützt werden. Hier gilt grundsätzlich der gleiche Zellschutz, wie in einer „normalen“ Tabelle. Die automatische Erweiterung funktioniert aber tatsächlich nicht, wenn der Blattschutz aktiviert ist. Diese beiden Funktionen schließen sich leider gegenseitig aus.

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Andreas E.

            Vielen Dank für die schnelle Antwort. Ich habe befürchtet, dass es so ist. Ich helfe mir jetzt weiter, in dem ich in den Zellen mit Formeln die Datenüberprüfung aktiviere, die nur eine bestimmte Zahl akzeptiert und die bei Auswahl der Zellen einen kleinen Hinweis anzeigt. Dadurch kann ich zumindest vermeiden, dass die Formeln aus Versehen gelöscht oder bearbeitet werden. Nur das Löschen über die „entf“-Taste lässt sich damit leider nicht vermeiden.

          • Avatar-Foto
            Uli

            Hallo Martin,
            genau das habe ich (leider) auch gemacht. Nachdem ich merkte, dass der Blattschutz das automatische Erweitern der Tabelle vernichtet hat, habe ich den Blattschutz wieder aufgehoben. Nun geht das wieder…
            Allerdings sind mir dabei auch elementare Auswahlmöglichkeiten der Tabelle (Spalte und/oder Zeile) verloren gegangen. Auch gehen bei der automatischen Erweiterung von Zeilen, bestimmte Formatierungen verloren.
            Die Spalte muss auswählen können, damit ich sie kopieren kann. In den Zellen darunter wird auf die Kopfzeile referenziert, das geht jetzt nicht mehr. Es bleibt der Kopfzeilenwert der kopierten Spalte in den Formeln.
            Irgendeine Ahnung wie ich das wieder hinbekommen könnte?

          • Avatar-Foto
            Martin Weiß

            Hallo Uli,

            dass durch das Setzen und das anschließende Entfernen das Blattschutzes Funktionen der formatierten Tabelle vernichtet werden, habe ich bisher noch nie gehört und konnte das bei mir auch nicht nachstellen. Das heißt, die Auswahl der Spalten und Zeilen sollte auch danach ganz normal funktionieren. Warum das bei dir nicht mehr so ist, kann ich leider nicht beantworten.

            Was die Referenzierung der Kopfzeile nach dem Kopieren angeht:
            Das hängt davon ab, wie kopiert wird. Wenn du eine Spalte markierst und mit Strg+C / Strg+V kopierst und an anderer Stelle wieder einfügst, dann bleibt die alte Spalte referenziert.
            Wenn du hingegen die Spalte nur markierst und in der rechten unteren Ecke der Tabelle über das kleine Ausfüllkästchen die Spalte nach rechts ziehst, wird sie kopiert und der Kopfbezug passt sich an. Das funktioniert allerdings nur dann, wenn es sich um die äußerste Spalte der Tabelle handelt und geht leider nicht, wenn die Spalte mittendrin liegt. Für diesen Fall bleibt nur der Weg, den Spaltenkopf über die Zelladresse zu referenzieren (also z.B. A$1) und nicht über den strukturierten Verweis, der durch reines Anklicken entsteht.

            Schöne Grüße,
            Martin

  • Avatar-Foto
    Dieter

    Hallo Martin,
    das hört sich alles sehr gut an. Ich habe ein Problem mit einer formatierten Tabelle und externe Bezüge.Vielleicht kannst du mir ja weiterhelfen und neue Gedankeimpulse geben.

    Ich habe zwei Excel-Dateien.
    1.) Quelldatei mit, nennen wir mal „Quelle“. In dieser ist auf dem Reiter „Tabelle1“ die Formartierte Tabelle A2:AP50.

    2.) Zieldatei, „Zieldatei“ Hier möchte ich mit dem Indexbefehl, die Zeilen und Spalten tauschen und nur ganz gezielte Werte auslesen.

    Jetzt kommst, wenn ich den Indexbefehl mit den festen Bezügen =Index([Dateipath]Tabelle1!A2:AP50;B3;A5) verwende, ist die Aktuallisierung kein Porblem. Hier ist das Porblem nur, sobald die Quelldatei erweitert wird, wird das Ende (Hier A50) nicht übernommen.
    Und somit fehlen diese Daten.
    Wenn ich statt dem festen Bezügen nun die formatierte Tabelle verwende, bekomme ich eine Fehlermeldung:
    Unter Verknüpfung bearbeiten, Stauts steht Fehler: Arbeitsblatt nicht gefunden.
    Hast du eine tipp wo ich suchen müßte?
    Oder wie könnte ich das Problem umgehen.

    • Avatar-Foto
      Martin Weiß

      Hallo Dieter,

      das Problem tritt dann auf, wenn die Quelldatei, auf die Du Dich in den Formeln beziehst, nicht geöffnet ist. Mit normalen Zellbezügen ist das kein Problem, mit strukturierten Verweisen klappt es nur bei geöffneter Datei.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Dieter

        Danke Martin,
        das habe ich leider auch so feststellen müssen.
        Hast du dafür evtl. einen Lösungsansatz? Wie man das Umgehen kann.

        Gruß und nochmals Besten Dank für deine Seite.
        Dieter

        • Avatar-Foto
          Martin Weiß

          Hallo Dieter,

          nein, hierfür habe ich leider auch keine Lösung. Wie gesagt, das ist eine technische Einschränkung von Excel, die sich vermutlich auch nicht umgehen lässt.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Gerhard Duscha

    Lieber Martin,
    ich suche nach der Möglichkeit, in formatierten EXCEL-Tabellen die Spaltennamen „absolut“ zu setzen. (wie bei Zellbezügen z.B.$B1)

    Hast Du eine Idee?

    Liebe Grüße

    • Avatar-Foto
      Martin Weiß

      Hallo Gerhard,

      die Spaltenbezeichnungen in formatierten Tabellen sind ja standardmäßig eigentlich absolut. Das ist ja meistens das Ärgerliche, wenn man eine solche Formel in eine Nachbarspalte kopiert und der Spaltenbezug aber unverändert bleibt. Oder habe ich die missverstanden?

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Gerhard Duscha

        Wie ich das sehe, gibt es einen Unterschied zwischen copy&paste und dem „ziehen“ mit der Mouse an der rechten unteren Ecke einer Zelle.

        • Avatar-Foto
          Gerhard Duscha

          Beispiel:
          =ZÄHLENWENN(tabDruckerStamm[Hersteller];tabKostenGes[[#Kopfzeilen];[HP]])
          Nach alter Lesart möchte ich beim copy den Bereich relativiert haben und den Suchbegriff mit „$“ versehen (Festsetzen)

          • Avatar-Foto
            Martin Weiß

            Hallo Gerhard,

            wie Du oben schon geschrieben hast, gibt es einen Unterschied zwischen Copy&Paste und dem Ziehen über die rechte untere Ecke:
            Beim normalen Kopieren und Einfügen bleit der strukturierte Verweis fix, d.h. er verweist immer auf die gleiche Tabellenspalte. Beim Ziehen über das kleine Quadrat rechts unten hingegen wird der Spaltenbezug angepasst. In vielen Fällen ist das auch so gewünscht.

            In Deinem besonderen Fall verwendest Du in der Formel jedoch zwei unterschiedlichen formatierten Tabellen (tabDruckerStamm und tabKostenGes). Damit führt das Ziehen nicht zum gewünschten Effekt. Hier bleibt nichts anderes übrig, als in der Formel an der entsprechenden Stelle normale absolute Zellbezüge (also mit $) einzutippen, und nicht die automatischen strukturierten Verweise zu verwenden.

            Schöne Grüße,
            Martin

  • Avatar-Foto
    Falten auf der Stirn

    Hallo lieber Martin,

    hier sitzt ein Problem vor meinem Rechner und ich frage ganz lieb, ob Du mir vielleicht helfen könntest nicht mehr so viele Falten auf der Stirn zu haben…

    Ich habe meine Excel-Liste als Tabelle formatiert. Excel war daraufhin so freundlich meine Formeln jeweils auf die entsprechende Spalte zu übertragen. Ansich super Idee. Jedoch in Spalte X befindet sich eine Formel (=V6/Q6-1), damit errechne ich das Delta zwischen angebotenen Preisen, die hin und wieder – z.B. in der nächsten Zeile (=V7/S7-1) lauten soll. Aber wann immer ich es ändere überträgt Excel die Formel auf die gesamte Spalte. Ich habe keine Idee wie ich das umgehen kann. Auch festsetzten mit $ hat die gleiche Wirkung.

    Über ein wenig Hilfe würde ich mich sehr freuen…,

    • Avatar-Foto
      Martin Weiß

      Hallo „Falten auf der Stirn“ (Namen gibt es, …)

      normalerweise wird in formatierten Tabellen beim Ändern einer Formelzelle ein kleines Autokorrektursymbol angezeigt, so dass man entscheiden kann, ob die Formel in alle Zeilen übernommen werden soll oder nicht. Vermutlich ist bei dir zusätzlich eine Einstellung in der Autokorrektur gesetzt. Ich kenne deine Excel-Version nicht, aber unter Excel 2016 findest du das so:
      Datei | Excel-Optionen | Dokumentprüfung | AutoKorrektur-Optionen… | Registerkarte „AutoFormat während der Eingabe“ | Häkchen gesetzt bei „Formeln in Tabellen füllen, um berechnete Spalten zu erstellen“ –> hier das Häkchen entfernen.

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Falten auf der Stirn

        Oh wow – Danke, diesen Weg hätte ich alleine nie gefunden! Vielen Dank!
        Und die Stirn ist wieder faltenfrei 😉 Meine Eltern tauften mich Christin – aber das hätte es nicht rübergebracht.. 😉

      • Avatar-Foto
        Carola Lück

        Wenn bei formatierten Tabellen eine Formel-Ausnahme eingetragen werden soll und Excel die Formel auf die gesamte Spalte überträgt – nicht rumdoktern, einfach RÜCKGÄNGIG machen. Das macht nicht die Eingabe sondern die darauf folgende Automatik rückgängig. Gilt für alle automatischen Aktionen in allen Microsoft Programmen lt. meiner Erfahrung. Die Option abschalten würde ich nur im äußersten Notfall.
        Wenn eine Ausnahme drin ist, weiß Excel dies und akzeptiert ohne Probleme weitere Ausnahmen für diese Spalte.
        Schönen Gruß
        Carola

  • Avatar-Foto
    Lothar Ahrens

    Hallo Martin,
    herzlichen Dank für deine Seite. Ich merke, man lernt nie aus;-)
    Eine Frage treibt mich um. Wenn ich eine Tabelle formatiert habe, über die Schaltfläche „als Tabelle formatieren“ und dann docj lieber meine Ursprungsformatierung haben möchte, wo kann ich das wieder rückgängig machen?
    Danke für deine Hilfe

    • Avatar-Foto
      Martin Weiß

      Hallo Lothar,

      um die Ursprungsformatierung zu erhalten, musst Du in den Tabellentools in der Registerkarte „Entwurf“ das Auswahlfenster für die Tabellenformatvorlagen öffnen. Dort gibt es ganz unten den Punkt „Löschen“, der dann die Formatierung aus der Formatvorlage entfernt.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Julian

    Hallo Herr Weiß,

    Die Erklärungen und Anwendungen von „als Tabelle formatieren“ sind mir nun geläufig.
    Ich arbeite derzeit im Controlling und habe eine solche benutzerdefinierte Übersicht erstellt. Ich habe auch gelesen, dass man diese in eine andere Datei „exportieren kann“.

    Ist es aber auch möglich, diese Übersicht in allen anderen Dateien/bzw. wenn eine neue Excel-Datei erstellt wird zu sehen? Also sozusagen als Standardformat, damit dieses immer ausgewählt werden kann.

    Vielen Dank für Ihre Antwort und Ihre tolle Seite!

    Freundliche Grüße

    Julian Schneider

    • Avatar-Foto
      Martin Weiß

      Hallo Herr Schneider,

      danke für das nette Feedback. Es ist möglich, dass Sie benutzerdefinierte Tabellenformatvorlagen erstellen. Danach können Sie die Datei als Arbeitsmappenvorlage speichern, so dass sie bei jedem Öffnen von Excel bereits zur Verfügung steht. Wie das geht, habe ich hier beschrieben.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Christian

    Guten Tag Martin,
    vielen Dank für Ihren Artikel, der mir schon sehr geholfen hat. Ich habe nun jedoch ein Problem mit der von Ihnen beschriebenen automatischen Erweiterung. In meiner Tabelle gibt es einige Spalten, in denen der Nutzer Werte eintragen soll. In einigen Spalten sollen jedoch entweder Daten aus anderen Tabellen eingespielt, oder aus den eingegebenen Daten berechnet werden. Diese Spalten möchte ich gerne über den Blattschutz schützen. Wenn ich dies tue, funktioniert leider die automatische Erweiterung nicht mehr, auch dann nicht, wenn ich in den Schutzoptionen „Zeilen einfügen“ zulasse. Können Sie mir hier einen Tipp geben, wie die automatische Erweiterung trotz Blatzschutzes weiterhin aktiv bleiben kann?
    Mit freundlichen Grüßen,
    Christian

    • Avatar-Foto
      Martin Weiß

      Hallo Christian,

      ich habe schlechte Nachrichten: der Blattschutz verträgt sich leider überhaupt nicht mit formatierten Tabellen. Das ist tatsächlich eine der größten Einschränkungen von formatierten Tabellen, über die ich mich (und viele andere Anwender) schon häufig geärgert habe. Ich nutze daher für die Formelspalten z.B. eine rote Schriftfarbe, um den Anwender zumindest optisch darauf aufmerksam zu machen. Oder ich blende solche Spalten komplett aus, sofern sie lediglich für Auswertungen benötigt werden. Aber ein Schutz ist leider nicht möglich (umständliche VBA-Programmierung einmal ausgenommen).

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Bohdana

    Hallo Martin,
    wenn ich in einer Tabelle nachträglich Formel in einer Spalte ändere übernimmt die Erweiterung leider nicht diese neue Formel sondern greift die alte ab. Ich habe schon überall gesucht wo diese Formeln festgeschrieben sind, habe aber nichts gefunden.
    Hättest du vielleicht Tipp für mich?
    Gruß
    Bohdana

    • Avatar-Foto
      Martin Weiß

      Hallo Bohdana,

      wenn Du nachträglich eine Formel änderst und mit der Eingabetaste übernimmst, erscheint normalerweise in der rechten unteren Ecke der Formelzelle ein kleines Tabellensymbol mit einem Formelzeichen. Wenn Du da draufklickst, gibt es die Option „Alle Zellen in dieser Spalte mit dieser Formel überschreiben“. Und die macht dann genau das.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Andreas

    Hallo!
    Ich habe mal eine Frage zu den Zellbezügen in einer intelligenten Tabelle.
    Worin besteht der Unterschied ob ich in dieser Tabelle einen Bezug mit bspw. =D10 mache oder der Bezug =[@Name] lautet?

    Viele Grüße
    Andreas

    • Avatar-Foto
      Martin Weiß

      Hallo Andreas,

      in erster Linie werden Formeln durch diese Schreibweise besser lesbar. Ein Bezug auf einen Namen ist leichter nachvollziehbar als auf eine Zelladresse.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Exceltrickser,

      danke für den Link, das wird sicherlich einigen Lesern helfen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Matthias

    Ja, und wie wird man dieses zusätzliche Register wieder los? Oder wie kann man es bei Tabellen einblenden lassen, die ohne die Nutzung der Entwurf-Funktion erstellt wurden?

    Die Funktion hat nicht nur Vorteile, zB. funktioniert Daten Sortieren anders bzw. nicht. deshalb würde ich das gerne loswerden.

    • Avatar-Foto
      Martin Weiß

      Hallo Matthias,

      man kann eine formatierte Tabelle wieder in eine normale Liste zurückverwandeln: Einfach in dem Register „Entwurf“ auf die Schaltfläche „In Bereich konvertieren“ klicken. Nach einer kurzen Rückfrage wandelt Excel alles wieder in einer normale Liste zurück. Lediglich die Formatierungen, wie z.B. das Streifenmuster, bleiben erhalten. Das muss bei Bedarf manuell entfernt werden.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Margit Albert

    Hallo Martin,
    vielen Dank für die Info zur Tabellenfunktion. Kann ich mittels vba die letzte Zeile einer solchen Tabelle finden, die zwar schon formatiert ist, aber keine Daten beinhaltet? Ich habe hier bislang nichts gefunden, außer mit einer LOOP-Schleife dies abzuprüfen, was aber sehr lange dauert!

    • Avatar-Foto
      Martin Weiß

      Hallo Margit,

      einer meiner Grundsätze hier auf dem Blog ist, dass es keine VBA-Tipps von mir gibt (habe ich hier beschrieben).

      Daher bitte ich um Verständnis, dass ich dir bei dieser Frage nicht weiterhelfen kann.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Peter Karrer

    Hallo Martin, such eine Möglichkeit die Zellumrandung mit dem Text im Winkel von 45° zu stellen. Habe diese Möglichkeit in einem Artikel gesehen, komme aber nicht hinter das Geheimnis. Musste die Überschriften der Tabelle aus Platzgründen „senkrecht“ stellen, was mitunter nicht leicht zu lesen ist. Eine Schrägstellung würde helfen.
    Hoffe du kannst mir helfen. Beste Grüße
    Peter

    • Avatar-Foto
      Martin Weiß

      Hallo Peter,

      um die Texte 45° zu drehen oder senkrecht zu stellen, gibt es im Register Start neben den Symbolen für die Ausrichtung (oben, unten, mittig) ein Symbol „abc“ mit einem Pfeil nach rechts oben. Dahinter gibt es dann die Möglichkeit, den Text z.B. gegen den Uhrzeigersinn zu drehen. Eine etwaige Zellenumrandung passt sich automatisch an eine 45°-Drehung an.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Uli Zürn

    Hallo Martin,

    klasse Artikel, hilft mir aber nicht ganz bei meinem Problem. Ich habe eine Tabelle in der verschiedene Kundenaufträge zeilenweise gelistet sind. Jeder Auftragsnummer kann 1 bis n Zeilen belegen, die unmittelbar untereinander stehen. Nun möchte ich, dass wenn die Auftragsnummer wechselt alternierend sich die Formatierung (Hintergrundfarbe) ändert.
    Sprich alle Zeilen des Auftrag01 weiss, alle Zeilen Auftrag02 hellblau, alle Zeilen Auftrag03 weiss, alle Zeile Auftrag04 hellblau, …

    Irgendeine Idee?
    Im Voraus schon mal Danke!

    Uli

  • Avatar-Foto
    Thomas

    Moin,
    super und einfach erklärt, klasse.
    Gibt es eine Möglichkeit mehrere Ergebniszeilen unter einer Tabelle zu haben? Die Zeilen müssen sich automatisch bei einem neuem Eintrag nach unten verschieben.

    Vielen Dank
    Gruß Thomas

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      dankeschöne 🙂
      Eine formatierte Tabelle an sich kann immer nur eine Ergebniszeile beinhalten. Du kannst aber jederzeit außerhalb der Tabelle (also darunter) eigene Ergebniszeilen erstellen, die dann auch automatisch nach unten verschoben werden, wenn die formatierte Tabelle sich verlängert.

      Schöne Grüße,
      Martin

        • Avatar-Foto
          Martin Weiß

          Hallo Thomas,

          schreibe einfach direkt in die nächste Zeile unterhalb der formatierten Tabelle die Formel, die du gerne hättest. Da ist kein spezieller Trick nötig.

          Schöne Grüße,
          Martin

      • Avatar-Foto
        Carola Lück

        noch besser finde ich die Ergebniszeile oben. Bei langen Tabellen in der Praxis oft verwendet. Dafür muss man keine Teilergebnisfunktion wissen, sondern einfach in der Ergebniszeile die gewünschten Formeln einstellen, hochkopieren – da gehen dann auch mehrere unterschiedliche – anschließend Ergebniszeile wieder ausschalten und nach Belieben Tabelle unten weiterführen mit aktuellen Ergebnissen oben, die auch filtersensitiv funktionieren. 😉

        Gruß
        Carola

        • Avatar-Foto
          Martin Weiß

          Hallo Carola,

          du hast völlig recht, oben ist die Ergebniszeile noch besser und man hat sie immer im Blick.

          Schöne Grüße,
          Martin

  • Avatar-Foto
    Jogi

    Hallo Martin,

    ich habe ein Problem mit dem Autofilter:

    Unter Excel 2010 war es möglich, einen Autofilter nicht nur für die Tabelle (bei mir eine Tabelle auf Basis einer externen Verbindung) zu setzen, sondern auch darüber hinausgehende Spalten mit in den Autofilter aufzunehmen.

    Wenn ich das in Excel 2016 versuche, ist das Menü für den Autofilter ausgegraut und auch über VBA kann ich den Autofilter auf den erweiterten Bereich nicht anlegen.

    Gibt es eine Möglichkeit, einen solchen Autofilter wieder zu nutzen oder wurde das von Microsoft unwiderruflich entfernt?

    Lieben Dank!
    Jogi

    • Avatar-Foto
      Martin Weiß

      Hallo Jogi,

      doch, das ist grundsätzlich auch in Excel 2016 möglich. Du musst halt nur zusätzlich den Autofilter für die separaten Spalten setzen. Es klappt nur nicht, wenn du in einem Rutsch die formatierte Tabelle und die separaten Spalten markierst.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Sabine

    Hallo Martin,
    deine Tipps haben mir schon oft weitergeholfen! Herzlichen Dank dafür!
    Mittlerweile bin ich aber auf der Suche für eine Lösung zu den Tabellen in Excel, die die Spaltenüberschriften betrifft. Diese möchte ich über Formeln vergeben. Bisher hab ich eine recht aufwändige Kapazitätsplanung in Excel, die ich für die Anwender über Power Apps attraktiver gestalten möchte (mit Power Apps kann ich aber nur „echte Tabellen“ verknüpfen. Hast du hier irgendeinen Lösungsansatz?
    Ich habe Zeitraumdaten (von … bis) und über diesen sollen Stunden verteilt werden. Diese sollten nun in der Tabelle rechts immer für die nächsten 24 Monate angezeigt werden und hier bräuchte ich das Datum als Spaltenüberschrift, flexibel vom aktuellen Monat ausgehend… Formeln werden hier aber leider nicht „gefressen“ vom Excel! Sehe ich irgendeine Möglichkeit noch nicht, oder geht es definitiv nicht?
    Ich bin auf deine Antwort gespannt!

    Viele Grüße
    SABINE

    • Avatar-Foto
      Martin Weiß

      Hallo Sabine,

      da habe ich leider eine schlechte Nachricht für dich: Als Überschriften in formatierten Tabelle können tatsächlich nur statische Werte verwendet werden, Formeln werden definitiv nicht unterstützt. Eine kleine Krücke wäre folgende Möglichkeit, die ich bei mir auch schon hin und wieder eingesetzt habe:

      Verwende die erste Datenzeile als Überschriftenzeile, hier kannst du ja Formeln eintragen. Die echte Kopfzeile kann dann beliebige Spaltennamen enthalten, denn man kann sie einfach ausblenden: Menü „Tabellenentwurf“ (oder Tabellentools, je nach Excel-Version) und dort das Häkchen bei „Kopfzeile“ entfernen. Für den Anwender sieht die Tabelle erst einmal „normal“ aus, im Hintergrund wird aber trotzdem die echte Kopfzeile mitgeführt.

      Das ist zugegebenermaßen keine ideale Lösung, aber die einzige, die mir dazu einfällt.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Wolfgang C

    Ich habe die Tabelle „als Tabelle formatiert“ um korrekt auf die Tabelle zugreifen zu können.
    Hallo Martin,
    ein Problem für mich ist, das wenn ich die Formatierung in der ersten Zeile beginne, beim sortiere derselben, die erste Zeile (Überschriften) in die Sortierung einfließt. Ich werde beim einrichten gefragt „Enthält Überschriften) was ich bestätige, beim anschließenden sortieren wird sie mit in die Sortierung übernommen.

    Meine Vorstellung ist, das die Überschrifteinzeile von der Sortierung ausgenommen ist, was aus meiner Sicht auch so sein sollte, aber so nicht berücksichtigt wird.

    Konnte im Netz zu dem Thema nichts aufklärendes finden. Kennt sich aus der Runde hier jemand mit dem Thema aus, und könnte mir da weiter helfen? Ist vielleicht eine Erklärung per Rück.- Info mittel E-Mail möglich. Gruß

    • Avatar-Foto
      Martin Weiß

      Hallo Wolfgang,

      du liegst mit deiner Vorstellung genau richtig: Wenn die Daten als Tabelle formatiert sind, dann sollten die Überschriften bei einer Sortierung auch ausgenommen sein. Genau genommen ist es meiner Erfahrung nach gar nicht möglich, eine formatierte Tabelle so zu sortieren, dass die Überschriften in die Sortierung einfließen. Daher bin ich gerade selbst etwas ratlos…

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Nicole

    Hallo Martin

    Eine sehr verständliche Anleitung! Vielen Dank.

    Frage: Neue Zeilen können innerhalb des als Tabelle formatierten Bereichs nicht eingefügt werden?

    Mit Dank & Gruss

    • Avatar-Foto
      Martin Weiß

      Hallo Nicole,

      freut mich zu hören, gern geschehen. Natürlich lassen sich in formatierten Tabellen auch neue Zeilen einfügen. Der einzige Unterschied zu einer „normalen“ Excel-Liste ist, dass es in einer formatierten Tabelle immer zwangsläufig eine komplette Zeile ist.

      Schöne Grüße,
      Martin