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.
Table of contents
- 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.
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.
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”.
The working pivot table is ready, as shown below.
Drag and drop the product column to the rows, and the sales column to the values section of the pivot table.
We have got the sales summary of the products. Now, let us add a few more entries to the existing list.
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.
After refreshing the pivot table, any modification in the data range will reflect in the pivot tables summary.
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.
The “Change Pivot Table Data Source” window appears, and it shows the current data range from A1:B8.
Change the source to include new rows, and click “OK”.
We will see the new products in the summary table.
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”.]
- Step 2: The “Create Table” window appears. Click “OK”.
The Excel Table format is applied, and the data range is converted to an Excel Table, as shown below.
- Step 3: Click on the Excel table, and a new tab, i.e., “Table Design”, appears on the ribbon.
- Step 4: Now, select the “Table Design” tab → go to the “Properties” group → type “Sales_Table” in the “Table Name:” box, as shown below.
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.
- 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.
- 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”.
- Step 4: Select the “Existing Worksheet” as the destination of the pivot table, and click on “OK” to create the pivot table.
- Step 5: Create a product sales summary, as we have created previously.
- Step 6: Now, add a few more lines of the data after the last row of the Excel Table.
- Step 7: After adding the data, right-click on the pivot table, and click “Refresh”.
After refresh, the pivot table shows the newly added rows dynamically.
In this way, we can create a Dynamic Table in Excel using the Excel Table feature.
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.
Select the entire data range from A1:B8, and name it “Sales_Range”.
To test out how this works, enter an equal sign in an empty cell, and type the name given in the previous step.
We can see the Excel IntelliSense list shows the named ranges. Then, choose the “Sales_Range”.
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.
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.
- Step 2: The “New Name” window appears. Enter the name as “Dynamic_Table” in the “Name:” box.
- 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.
- Step 4: The named range is ready.
- 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”.
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.
Now enter a few more records after the last record in the 8th row.
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.
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, : 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.
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.
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.
Place your cursor inside the range box, and press the F3 shortcut key to bring the list of named ranges in the workbook.
Select the “Dynamic_Table”, and click “OK” to bring this to the range input box.
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.]
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)
Static tables take cells or a cell range as a reference, and dynamic tables take a structured reference.
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.
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.
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 –