Dynamic Tables 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

• What Is Dynamic Table In Excel?
  • How To Create Dynamic Tables In Excel Using “Tables”?
  • How To Create Dynamic Tables In Excel Using OFFSET Function?
  • Insert Pivot Table By Using Dynamic Named Range
  • Important Things To Note
  • Frequently Asked Questions (FAQs)
  • Download Template
  • Recommended Articles

  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.

  

  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.