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:
Formatvorlage neu erstellen
2. Eine vorhandene Vorlage kopieren und anpassen:
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
Wenn man damit fertig ist, steht die neue benutzerdefinierte Vorlage im normalen Auswahlfenster zur Verfügung:
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“
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
Excel formatiert auch die dazwischenliegenden leeren Spalten und ergänzt auch gleich die Überschriften:
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
Angezeigte Elemente anpassen
3. Dubletten entfernen
Eine sehr nützliche Funktion ist das automatische Entfernen von Dubletten in einer formatierten Tabelle.
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
Nach einem Hinweis, wieviele doppelte Werte gefunden und entfernt wurden, erhält man die bereinigte Tabelle:
Ergebnis der Bereinigung
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
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
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.
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.
Pingback: Sparklines: Mini-Diagramme mit Maxi-Wirkung | Der Tabellen-Experte
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–
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:
Im Namensmanager tauchen somit die ursprüngliche Tabelle und der neue Name auf, der sich auf die Tabellenspalte bezieht:
Und diesen neuen Namen (hier „Vorname“) verwendest du dann als Quelle für das Dropdown-Feld in der 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
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–
Hallo -Fragezeichen-
es gibt tatsächlich einen Artikel, in dem ich etwas über diese Spezialelemente ([#Alle] etc.) geschrieben haben:
Ordnung ins Chaos bringen: Strukturierte Verweise
Schau‘ einfach mal rein.
Schöne Grüße,
Martin
Hallo Martin,
Du bist ja ein richtiger Schatz 🙂
Danke Dir
Beste Grüße –Fragezeichen–
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
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.
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
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
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.
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
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
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
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
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
Hallo Martin,
mansche Lösungen sind auch zu einfach. Hat so funktioniert, vielen Dank!
Bastian
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
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
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?
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
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??
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
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?
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
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.
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?
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
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.
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
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
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
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
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
Wie ich das sehe, gibt es einen Unterschied zwischen copy&paste und dem „ziehen“ mit der Mouse an der rechten unteren Ecke einer Zelle.
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)
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
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…,
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
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.. 😉
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
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
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
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
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
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
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
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
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
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
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
Ich arbeite viel und gerne mit den „formatierten“ Tabellen.
Heute wollte ich die Bezüge „unbeweglich“ haben (also quasi „verdollarn“).
Dabei hat mir diese Seite sehr geholfen:
http://www.excelcampus.com/tips/absolute-formula-references-excel-structured-table/
Ich schreibe dies hier rein, weil ich bei meiner Suche viele andere Foreneinträge mit ähnlicher Fragestellung gesehen habe, aber kaum irgendwo eine gute Antwort.
Hallo Exceltrickser,
danke für den Link, das wird sicherlich einigen Lesern helfen.
Schöne Grüße,
Martin
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.
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
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!
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
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
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
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
Konnte es selber lösen.
mfg Uli
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
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
Wie wäre da die Funktion? Oder geht das nur über VBA?
Gruß Thomas
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
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
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
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
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
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
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
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ß
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
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
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