How to create a dynamic table in Excel
A simple table is quickly created in Excel, and using a few filters to sort the data is not a problem for most.
However, the function of having Excel generate a dynamic table from existing data is rarely used, as many do not really know what is possible with this option and what advantages it offers.
In this article, we would like to describe how you can turn static tables into dynamic ones, which possibilities and advantages a dynamic table offers, and also how you can convert them back into a normal static table.
How to create a dynamic table in Excel
A simple table is quickly created in Excel, and using a few filters to sort the data is not a problem for most.
However, the function of having Excel generate a dynamic table from existing data is rarely used, as many do not really know what is possible with this option and what advantages it offers.
In this article, we would like to describe how you can turn static tables into dynamic ones, which possibilities and advantages a dynamic table offers, and also how you can convert them back into a normal static table.
1. Creating a dynamic table
1. Creating a dynamic table
Creating a dynamic spreadsheet in Excel is actually very easy. You can do this by simply clicking in any cell and then on the “Insert” tab on “Table”.
Or you have already created a static table with headings and want to convert it to a dynamic table. To do this, simply mark the table area that is to be converted and then go to “Insert” and “Table”.
See fig.(click to enlarge)
When you insert the table, you will also be asked whether the table has headings, and you may have to confirm this with a tick. If you already have headings, as in our example, you should also check the box, otherwise Excel will insert an additional line with column headings above your headings that is not required.
You can now change the dynamic table created in this way from one of the predefined color patterns in the layout, or you can also create your own color pattern. To do this, simply click in the table and then in the “Table Tools” – “Design” tab on the table format templates.
See fig.(click to enlarge)
Creating a dynamic spreadsheet in Excel is actually very easy. You can do this by simply clicking in any cell and then on the “Insert” tab on “Table”.
Or you have already created a static table with headings and want to convert it to a dynamic table. To do this, simply mark the table area that is to be converted and then go to “Insert” and “Table”.
See fig.(click to enlarge)
When you insert the table, you will also be asked whether the table has headings, and you may have to confirm this with a tick. If you already have headings, as in our example, you should also check the box, otherwise Excel will insert an additional line with column headings above your headings that is not required.
You can now change the dynamic table created in this way from one of the predefined color patterns in the layout, or you can also create your own color pattern. To do this, simply click in the table and then in the “Table Tools” – “Design” tab on the table format templates.
See fig.(click to enlarge)
2. Calculating with dynamic tables
2. Calculating with dynamic tables
With the newly created dynamic table, it is now also relatively easy to calculate.
In our example, we simply created different products and calculated the gross price. To do this, we click in the cell in which the result should be and start with an “=”, as with any formula. Then we click in the first cell with the net price and multiply this value by 1.19 to calculate the 19% mark-up.
What is immediately noticeable in the formula bar is that the cell is not specified as the reference point, as in a static table, but the column heading. In this way, the formula in each new row for this area is inherited within the dynamic table, so that it does not have to be re-entered.
Of course, a table created in this way offers a number of other options and possibilities, which you can find in the “Design” tab under “Table tools”.
See fig.(click to enlarge)
A notice:
You can either extend the table by pressing the TAB key once in the last cell of the last row of the table, or you can simply drag down the lower right edge with the left mouse button.
With the newly created dynamic table, it is now also relatively easy to calculate.
In our example, we simply created different products and calculated the gross price. To do this, we click in the cell in which the result should be and start with an “=”, as with any formula. Then we click in the first cell with the net price and multiply this value by 1.19 to calculate the 19% mark-up.
What is immediately noticeable in the formula bar is that the cell is not specified as the reference point, as in a static table, but the column heading. In this way, the formula in each new row for this area is inherited within the dynamic table, so that it does not have to be re-entered.
Of course, a table created in this way offers a number of other options and possibilities, which you can find in the “Design” tab under “Table tools”.
See fig.(click to enlarge)
A notice:
You can either extend the table by pressing the TAB key once in the last cell of the last row of the table, or you can simply drag down the lower right edge with the left mouse button.
3. Converting a dynamic table to a convertible range
3. Converting a dynamic table to a convertible range
If at some point you want to convert the table back into a static table, you can do this again via the “Design” tab under “Table tools”. Simply select the entire dynamic table and then click “Convert to range”. You then only have to confirm a security query, and the dynamic table is resolved with all functions.
All previously created calculations are of course retained.
See fig.(click to enlarge)
If at some point you want to convert the table back into a static table, you can do this again via the “Design” tab under “Table tools”. Simply select the entire dynamic table and then click “Convert to range”. You then only have to confirm a security query, and the dynamic table is resolved with all functions.
All previously created calculations are of course retained.
See fig.(click to enlarge)
Popular Posts:
So sieht das wirklich perfekte Homeoffice-Setup für 2026 aus
Ihr Homeoffice ist veraltet? Steigern Sie 2026 Produktivität & Gesundheit. Unser Guide zeigt das perfekte Setup: von ergonomischen Stühlen und 4K-Webcams bis zu Mesh-WLAN und Kabelmanagement. So investieren Sie in Ihre Karriere.
Internet, Telefon & TV: Die besten Komplettpakete
Internet, TV & Telefon aus einer Hand? Wir vergleichen die Komplettpakete von Telekom, Vodafone, O2 & 1&1. Wer punktet bei Speed, TV-Komfort (MagentaTV vs. GigaTV) und Preis? So finden Sie das beste Triple-Play-Angebot für Ihre Adresse.
5 Anzeichen, dass dein Chef dich im Homeoffice digital überwacht
Nur weil du im Homeoffice bist, heißt das nicht, dass dir niemand zusieht. "Bossware" ist auf dem Vormarsch. Wir zeigen 5 subtile Anzeichen für digitale Überwachung – von "Teams-Status"-Kult bis zu verdächtigen IT-Tools. So erkennst du die Warnsignale und schützt deine Karriere.
Schluss mit dem Tool-Chaos: 5 Apps, die Euer Office wirklich produktiver machen
Schluss mit digitaler Reibung: Diese 5 Apps machen Ihr Büro wirklich produktiver. Entdecken Sie, wie Asana, Loom, Notion, Zapier und Slack die Effizienz steigern, Meetings reduzieren und Ihr Team smarter vernetzen – wenn man sie richtig einsetzt.
AI in everyday office life: Your new invisible colleague
AI won't replace you – but those who use it will have a competitive edge. Make AI your co-pilot in the office! We'll show you four concrete hacks for faster emails, better meeting notes, and solved Excel problems. Get started today, no IT degree required.
Fünf vor Zwölf: Wie Sie erkennen, dass Sie kurz vor dem Burnout stehen
Erschöpfung ist normal, doch wenn das Wochenende keine Erholung mehr bringt und Zynismus die Motivation ersetzt, stehen Sie kurz vor dem Burnout. Erfahren Sie, welche 7 Warnsignale Sie niemals ignorieren dürfen und warum es jetzt lebenswichtig ist, die Notbremse zu ziehen
Offers 2024: Word & Excel Templates
Popular Posts:
So sieht das wirklich perfekte Homeoffice-Setup für 2026 aus
Ihr Homeoffice ist veraltet? Steigern Sie 2026 Produktivität & Gesundheit. Unser Guide zeigt das perfekte Setup: von ergonomischen Stühlen und 4K-Webcams bis zu Mesh-WLAN und Kabelmanagement. So investieren Sie in Ihre Karriere.
Internet, Telefon & TV: Die besten Komplettpakete
Internet, TV & Telefon aus einer Hand? Wir vergleichen die Komplettpakete von Telekom, Vodafone, O2 & 1&1. Wer punktet bei Speed, TV-Komfort (MagentaTV vs. GigaTV) und Preis? So finden Sie das beste Triple-Play-Angebot für Ihre Adresse.
5 Anzeichen, dass dein Chef dich im Homeoffice digital überwacht
Nur weil du im Homeoffice bist, heißt das nicht, dass dir niemand zusieht. "Bossware" ist auf dem Vormarsch. Wir zeigen 5 subtile Anzeichen für digitale Überwachung – von "Teams-Status"-Kult bis zu verdächtigen IT-Tools. So erkennst du die Warnsignale und schützt deine Karriere.
Schluss mit dem Tool-Chaos: 5 Apps, die Euer Office wirklich produktiver machen
Schluss mit digitaler Reibung: Diese 5 Apps machen Ihr Büro wirklich produktiver. Entdecken Sie, wie Asana, Loom, Notion, Zapier und Slack die Effizienz steigern, Meetings reduzieren und Ihr Team smarter vernetzen – wenn man sie richtig einsetzt.
AI in everyday office life: Your new invisible colleague
AI won't replace you – but those who use it will have a competitive edge. Make AI your co-pilot in the office! We'll show you four concrete hacks for faster emails, better meeting notes, and solved Excel problems. Get started today, no IT degree required.
Fünf vor Zwölf: Wie Sie erkennen, dass Sie kurz vor dem Burnout stehen
Erschöpfung ist normal, doch wenn das Wochenende keine Erholung mehr bringt und Zynismus die Motivation ersetzt, stehen Sie kurz vor dem Burnout. Erfahren Sie, welche 7 Warnsignale Sie niemals ignorieren dürfen und warum es jetzt lebenswichtig ist, die Notbremse zu ziehen
Offers 2024: Word & Excel Templates
