How to Create Dynamic Table in Excel: 3 Quick Ways

Learn how to create a dynamic table in Excel with techniques for mastering dynamic tables in Excel through faster data administration.

overview to create a dynamic table in Excel.

Method 1 – Using Excel Table and Named Range Feature to Create Dynamic Table

We used a dataset having a sales report for a company.

dataset for creating dynamic table in Excel.

  • Select the whole dataset including the header row, and select Insert > Table from the ribbon.

inserting table

  • In the tab, write the cell range for the table if not selected earlier. Also, put a tick mark on My table has headers. Press OK.

confirming range

  • Select the whole table again and select the Formulas tab > Define Name.

defining name range

  • Give a name to the range in the appeared tab and press OK.

giving name to dynamic range

  • You created the dynamic table.

dynamic table with excel feature


Method 2 – Inserting Excel Pivot Table to Create Dynamic Table

  • Select the whole dataset including the header, and select Insert > Pivot Table > From Table/Range.

inserting pivot table

  • Select Existing Worksheet and write the cell reference where you want the pivot table. We have created it in cell B21. press OK.

selecting range for pivot table

  • You created the pivot table which is a dynamic table. Select the pivot table and from PivotTable Fields put Employee ID and Name fields in the Rows section, State in the Filter section, and Sales in the Values section.

dynamic table with pivot table


Method 3 – Using OFFSET Function to Create Dynamic Table

  • Define the Name Range for the table.
  • Go to the Formulas tab > Defined Names > Name Manager.

opening name manager

  • Select the unique name we defined for the table. Double-click on it. We have given the name Report_Sales.

selecting specific named range

  • In the Edit Name tab Referes to section, write the following formula and press OK.
=OFFSET(OFFSET!$B$2,1,0,COUNTA(OFFSET!$B:$B),2)
  • The table is made dynamic.

offset formula for dynamic table


How to Use Dynamic Table in Excel Formula

  • Use the following formula in cell C20. remember our condition State (Michigan) is included in cell C19.
=SUMIF(Table13[State], C19,Table13[Sales])
  • You’ll see the sum of Sales from Michigan State in the cell.

use of dynamic table

In the formula, we used the SUMIF function, the value of cell C19 is searched in the State column of the dynamic table and add the Sales for the state Michigan.

  • Let’s add another Sales data for Michigan State and check if the sum in cell C20 gets updated because the table is dynamic.
  • The sum result changed accordingly.

change in dynamic table reflects in the result


How to Convert a Dynamic Table to a Range

  • Select the table and right-click on it. Select Table > Convert to Range.

changing table to range

  • You’ll see our dynamic table changed to the normal range.

normal range from dynamic table


Download Practice Workbook

You can download the practice workbook from here.

Advantages of Creating Dynamic Table in Excel

  • Automatic resizing: To save time and effort, the table automatically contracts and expands as data changes.
  • Simple data entry: Since the table expands fluidly, it is easier to enter data outside of its current range.
  • Enhanced organization: For effective data organization, dynamic tables include built-in sorting, filtering, and formatting capabilities.
  • Structured referencing: Formulas are simpler to comprehend and maintain when meaningful names are used in place of cell references.
  • Formula Extension: The application of formulas is made easier since dynamic tables dynamically extend formulas as the amount of data increases, guaranteeing accuracy.
  • Validating data and drop-down menus: Enforce data consistency and give data entry possibilities that are already predetermined.
  • Integration with other features: For dynamic reports and visualizations, dynamic tables easily interface with PivotTables, charts, and other Excel capabilities.

Frequently Asked Questions

1. Can dynamic tables handle expanding datasets?

Ans: Yes, dynamic tables can easily accommodate expanding datasets by automatically adjusting their size to include new rows or columns.

2. Can dynamic tables be used for data analysis?

Ans:  Yes, dynamic tables can be used as a foundation for data analysis. They integrate well with other Excel features like PivotTables and charts, allowing for dynamic reports and visualizations.

3. How do dynamic tables save time and effort?

Ans: Dynamic tables automatically resize as data changes, reducing the need for manual adjustments. They also offer features like sorting, filtering, and structured referencing, which streamline data management tasks.


Things to Remember

  • In a few cases, we used cell references. Be Careful about using cell references so that it has to change according to your dataset.
  • Save the Excel file after bringing some changes to the worksheet.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo