Dynamic Tables In Excel

What Is Dynamic Table In Excel?

Dynamic Table in Excel accepts the new values added to the list dynamically, whenever the existing data table is modified.

Dynamic Tables are commonly referenced by a table name, and when data is added, the dynamic tables expand their range to newly added data. Any formula or pivot table applied based on the dynamic table takes the reference of the data by the dynamic table name.

Key Takeaways
  • The Excel Table format makes the data structure dynamic, and all the referenced formulas and pivot tables get updated dynamically.
  • No need to select the entire data range while inserting the pivot table for the Excel Table format.
  • Using the OFFSET function, we can create dynamic named ranges.
  • To get the updated data from the dynamic table or Excel Table, we should refresh the pivot table manually.
  • We do not have to refresh all the formulas that reference the Excel Table because any changes or modifications will reflect in the formula results immediately.

How To Create Dynamic Tables In Excel Using “Tables”?

Applying table format for data structure will make the data sources dynamic.

For instance, we have the following data in an Excel spreadsheet.

Dynamic Tables in Excel.png

To summarize the above data, let us apply the pivot table.

Choose the entire data range, A1:B8 → select the “Insert” tab → go to the “Tables” group → click the “Pivot Table” option drop-down → select the “From Table/Range” option, as shown below.

How to create Dynamic Tables in Excel - Using Tables - Step 1

The “Pivot Table from table or range” window appears. Choose the destination of the pivot table as “Existing Worksheet”, choose an empty cell as a reference, here, cell F1, and click “OK”.

Using Tables - Step 2

The working pivot table is ready, as shown below.

Using Tables - Step 3

Drag and drop the product column to the rows, and the sales column to the values section of the pivot table.

Using Tables - Step 4

We have got the sales summary of the products. Now, let us add a few more entries to the existing list.

Using Tables - Step 5

We have added Cosmetics and Foods data to the existing data range. Select any cell in the pivot table range, right-click, and click on the “Refresh” option.

Using Tables - Step 6

After refreshing the pivot table, any modification in the data range will reflect in the pivot tables summary.

Using Tables - Step 7

There is no change in the pivot table compared to the previous one. This is because when we applied the pivot table, the data range selected was A1:B8, so anything added outside this range will not alter the pivot table.

To fix the data range to newly added rows, click on the existing pivot table, 2 new tabs, i.e., “PivotTable Analyze” and “Design”, appear on the ribbon.

Select the “PivotTable Analyze” tab → go to the “Data” group → click the “Change Data Source” option drop-down → select the “Change Data Source…” option, as shown below.

Using Tables - Step 8

The “Change Pivot Table Data Source” window appears, and it shows the current data range from A1:B8.

Using Tables - Step 9

Change the source to include new rows, and click “OK”.

Using Tables - Step 10

We will see the new products in the summary table.

Using Tables - Step 11

This manual process is time-consuming. Hence, we will make the data source dynamic by converting the data range into the Excel Table format.

The steps to convert the data range to Excel Table format are listed here:

  • Step 1: Select the “Insert” tab → go to the “Tables” group → click on the “Table” option, as shown.

[Note: The shortcut key to create a table is “Ctrl + T”.]

How to create Dynamic Tables in Excel - Making the data source dynamic - Step 1
  • Step 2: The “Create Table” window appears. Click “OK”.
Making the data source dynamic - Step 2

The Excel Table format is applied, and the data range is converted to an Excel Table, as shown below.

Making the data source dynamic - Step 3
  • Step 3: Click on the Excel table, and a new tab, i.e., “Table Design”, appears on the ribbon.
Making the data source dynamic - Step 4
  • Step 4: Now, select the “Table Design” tab → go to the “Properties” group →  type “Sales_Table” in the “Table Name:” box, as shown below.
Making the data source dynamic - Step 5

Now we can call this Excel Table by the name “Sales_Table”.

The steps to insert a pivot table based on this Sales_Table Excel Table are,

  • Step 1: In an Excel Table, we do not have to select the entire data range to insert the pivot table, rather, select any of the cells in the Excel Table format range.
How to create Dynamic Tables in Excel - Pivot Table - Step 1
  • Step 2: Select the “Insert” tab → go to the “Tables” group → click the “Pivot Table” option drop-down → select the “From Table/Range” option, as shown below.
Pivot Table - Step 2
  • Step 3: The “Pivot Table from table or range” window appears. Previously it was showing the range of cells from A1:B8, but now, it shows the Excel Table name, i.e., “Sales_Table”.
Pivot Table - Step 3
  • Step 4: Select the “Existing Worksheet” as the destination of the pivot table, and click on “OK” to create the pivot table.
Pivot Table - Step 4
  • Step 5: Create a product sales summary, as we have created previously.
Pivot Table - Step 5
  • Step 6: Now, add a few more lines of the data after the last row of the Excel Table.
Pivot Table - Step 6
  • Step 7: After adding the data, right-click on the pivot table, and click “Refresh”.
Pivot Table - Step 7

After refresh, the pivot table shows the newly added rows dynamically.

Pivot Table - Step 8

In this way, we can create a Dynamic Table in Excel using the Excel Table feature.


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Create Dynamic Tables In Excel Using OFFSET Function?

By creating a dynamic named range, we can make the data table dynamic and work efficiently in Excel.

Take the same data from the above example.

How to create Dynamic Tables in Excel - Using OFFSET Function

Select the entire data range from A1:B8, and name it “Sales_Range”.

How to create Dynamic Tables in Excel - Using OFFSET Function - Step 1

To test out how this works, enter an equal sign in an empty cell, and type the name given in the previous step.

Using OFFSET Function - Step 2

We can see the Excel IntelliSense list shows the named ranges. Then, choose the “Sales_Range”.

Using OFFSET Function - Step 3

When we select the named range as “Sales_Range”, it references the range of cells from A1:B8.

However, if we add anything after row 8, this named range will not take the newly added data.

Using OFFSET Function - Step 4

As you can see in the above image, we have added new data in rows 9 and 10, but our named range “Sales_Range” does not reference the newly added rows.

The steps to create a dynamic table using the OFFSET function are,

  • Step 1: Select the “Formulas” tab → go to the “Defined Names” group → click the “Define Name” option drop-down, as shown below.
OFFSET Function to create Dynamic Tables in Excel- Step 1
  • Step 2: The “New Name” window appears. Enter the name as “Dynamic_Table” in the “Name:” box.
OFFSET Function to create Dynamic Tables in Excel- Step 2
  • Step 3: In the “Refers to:” box, enter the formula

=OFFSET(OFFSET_Function!$A$1,0,0,COUNTA(OFFSET_Function!$A:$A),COUNTA (OFFSET_Function!$1:$1)), and click “OK”, as shown below.

OFFSET Function to create Dynamic Tables in Excel- Step 3
  • Step 4: The named range is ready.
OFFSET Function to create Dynamic Tables in Excel- Step 4
  • Step 5: To test how this named range works enter an equal sign in any empty cell, and type the named range name “Dynamic_Table”.
OFFSET Function to create Dynamic Tables in Excel- Step 5

We can see that it has taken the reference starting from first cell A1, all the rows below, and all the columns to the right. Press the “Enter” key, and we will get the table reference from A1:B10.

OFFSET Function to create Dynamic Tables in Excel- Step 6

Now enter a few more records after the last record in the 8th row.

OFFSET Function to create Dynamic Tables in Excel- Step 7

As soon as we add 2 new records in rows 9 and 10, the dynamic named range takes the reference, and dynamically includes the newly added items.

However, there is a drawback to this named range. For instance, we have added a new column after leaving one column empty.

OFFSET Function to create Dynamic Tables in Excel- Step 8

Instead of returning the newly added column “Profit”, it has returned zeros in the dynamic table range.

To understand how this works, let us understand the formula.

=OFFSET($A$1,0,0,COUNTA(OFFSET!$A:$A),COUNTA(OFFSET!$1:$1))

OFFSET($A$1,0,0, : OFFSET starts from cell A1, moves down zero rows, and right zero columns.

COUNTA($A:$A) : COUNTA excel function will return the count of rows filled up in column A.

COUNTA($1:$1) : COUNTA function will return the count of columns filled up in row 1.

Based on the row and column numbers provided by the COUNTA functions, the OFFSET function will offset those many rows and columns.

In the previous case of adding a new column, “Profit”, the COUNTA function (COUNTA($1:$1)) returns the number of filled columns as 3, so the OFFSET function starting from cell A1 offsets 3 columns, and the third column C is blank. Therefore, in the dynamic range, we got zero values.

OFFSET Function to create Dynamic Tables in Excel- Step 9

Hence, to make this named range dynamic table work properly, we should avoid empty rows and columns in the data table.

Insert Pivot Table By Using Dynamic Named Range

Insert Pivot Table by Using Dynamic Named Range

Choose a cell in the data range, select the “Insert” tab → go to the “Tables” group → click the “Pivot Table” option drop-down → select the “From Table/Range” option, as shown below.

Insert Pivot Table Using Dynamic Named Range - Step 1

The “Pivot Table from table or range” window appears. In the range box, enter the dynamic name range we have created in the previous steps.

Insert Pivot Table Using Dynamic Named Range - Step 2

Place your cursor inside the range box, and press the F3 shortcut key to bring the list of named ranges in the workbook.

Insert Pivot Table Using Dynamic Named Range - Step 3

Select the “Dynamic_Table”, and click “OK” to bring this to the range input box.

Insert Pivot Table Using Dynamic Named Range - Step 4

Click on “OK”, and the pivot table is ready.

[Note: When we use dynamic named ranges, always insert the pivot table in the new worksheet.]

Insert Pivot Table Using Dynamic Named Range - Step 5

Any change to the dynamic table reflects in the pivot table when we refresh it.

Important Things To Note

  • Ctrl + T is the shortcut key to create an Excel Table format.
  • ALT + A + R + R is the shortcut key to refresh the current pivot table.
  • ALT + A + R + A is the shortcut key to refresh all the pivot tables in the workbook.
  • The named Range should not contain any blank rows or columns, else we get the wrong references from the dynamic ranges.
  • In the named manager formula, the COUNTA function will return the count of rows and columns that are filled, irrespective of whether it is a continuous row or column.
  • The Excel Table expands the structured reference only to the rows or columns that are added immediately to the existing row or column.

Frequently Asked Questions (FAQs)

1. What is the Difference Between Static Table and Dynamic Table Types in Excel?

Static tables take cells or a cell range as a reference, and dynamic tables take a structured reference.

2. How do you reference a Dynamic Table in Excel?

Dynamic tables in Excel are referred to by the name provided in the Excel Tables. Any columns from this table get referenced using the column header.

3. How do you name a Dynamic Table in Excel?

Dynamic Tables, by default, take the names “Table1”, “Table2”, etc. However, we can change the name of the dynamic table in the “Table Design” tab.

Dynamic Tables in Excel - FAQ 3

Download Template

This article must help understand Dynamic Tables in Excel with its formulas and examples. You can download the template here to use it instantly.

Guide to Dynamic Tables In Excel. Here we create dynamic table with Tables, Pivot, Name-Range, OFFSET along with example & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *