In most cases, information is taken from data and then used to create a list. Dynamic lists are those that get updates automatically. We’ll show you how to create a dynamic list from a table in Excel quickly and easily. To create a dynamic list from the table in Excel we will apply **INDEX**, **OFFSET**, **COUNTIF**, **FILTER**, **DATA VALIDATION**, **SEQUENCE**, and **LARGE **functions.

## 📁 Download Excel File

Download the free practice book from here.

## What Is a Dynamic List in Excel?

A dynamic list in **Excel **is a feature that allows users to create a drop-down list from data that is stored in another worksheet or workbook. When any value in a data set is modified, or when new values are added to the data set, the resulting list, which is called a dynamic list, is automatically updated. The list updates automatically when the source data changes. Moreover, This feature for data validation, allowing users to select from a predefined list of values. It is also used to create interactive menus and drop-down lists for user input.

## Learn to Create Dynamic List from Table in Excel with These 6 Ways

To demonstrate the methods, we’ll look at a data set that lists how many of each supermarket item.

We’ll look at a grocery store’s data set, which includes a variety of fields like **Items**, **Unit Price**, **Quantity**, and **Total Amount**. We will use this data set to create a dynamic list.

**method**

### 1. Applying FILTER and OFFSET Functions with Criteria

We will create a dynamic list that contains sales above 3000 dollars. To do this we will use** FILTER**, **OFFSET**, and **COUNTA **functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**G6**. - Insert the following Formula:

**=FILTER(OFFSET($B$6,0,0,COUNTA(E:E)-1,1),OFFSET($E$6,0,0,COUNTA(E:E)-1,1)>=3000)**

- Then press
**Enter**to see the output of the formula.

That’s how a dynamic list of sales of more than **3000** dollars is created.

**🔨 Formula Breakdown**

👉 **COUNTA(E:E)-1,1: **will return the number of rows after subtracting the heading.

👉 **OFFSET($B$6,0,0,COUNTA(E:E)-1,1): **will return the name of items in the dataset of column **E**.

👉 **OFFSET($E$6,0,0,COUNTA(E:E)-1,1)>=3000: **will return the accepted items containing a total amount equal to or more than **3000** dollars.

👉 **FILTER(OFFSET($B$6,0,0,COUNTA(E:E)-1,1),OFFSET($E$6,0,0,COUNTA(E:E)-1,1)>=3000): **will give the list of items containing total amount equal or more than 3000 dollars.

**method**

### 2. Applying INDEX, COUNTIF, OFFSET, COUNTA, and MATCH Functions Combined

Now we will try to create another dynamic list of sales containing amounts of more than 500 dollars in a different approach using **INDEX**, **OFFSET**,** COUNTA**, **MATCH**, and **SMALL **functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select cell
**H6**. - Insert the following Formula:

**=INDEX(OFFSET($C$6,0,0,COUNTA(C:C)-1,1),MATCH(SMALL(IF(OFFSET($F$6,0,0,COUNTA(F:F)-1,1)>=500,OFFSET($F$6,0,0,COUNTA(F:F)-1,1),””),ROW(B1:INDIRECT(“A”&COUNTIF(F:F,”>=500″)))),OFFSET($F$6,0,0,COUNTA(F:F)-1,1),0),1)**

- Then hit
**Enter**to see the outcome of the formula.

As a result, a dynamic list of sales of amounts more than 500 is created.

**🔨 Formula Breakdown**

👉 **COUNTA(C:C)-1,1: **returns the number of row after subtracting the header row.

👉 **OFFSET($C$6,0,0,COUNTA(C:C)-1,1): **will pass the number of items excluding the heading from Column** C**.

👉 **MATCH(SMALL(IF(OFFSET($F$6,0,0,COUNTA(F:F)-1,1)>=500: **will match the criteria of items total amount value more than 500.

👉 **ROW(B1:INDIRECT(“A”&COUNTIF(F:F,”>=500″): **will return the rows that assert the condition.

**method**

### 3. Applying Data Validation Feature

The **UNIQUE **function and **Data Validation **are tools we may use to generate a dynamic **Drop-Down list **from a table. To convert to a Spill Range in **Data Validation **window, it needs **UNIQUE **function.

**Office 365**supports the

**UNIQUE**function. It isn’t available in other Office versions.

To implement the Spill Range feature, a column of Product helpers with the following formula must be added.

**⬇️⬇️ STEPS ⬇️⬇️**

- To implement the Spill Range feature, a column of Product helpers with the following formula must be added.
- Select cell
**H6**. - Then insert the following formula:

**=UNIQUE(B6:B21)**

- Then press
**Enter**to the type of product available in the grocery market and an assistance column is created. - Later select cell Go to
**Data**Tab >**Data Validation**(in**Data Tools**section). A window labeled “**Data Validation**” will pop up.

- From the
**Data Validation**window, Select**Settings**>**List**(pick drop-down menu)>**G6**, and insert the symbol (#) to convert it to a spill range. - Select the option
**OK**. In cell**G6**, the drop-down list will appear. Moreover, a dynamic list of the products from the table can be seen there.

Finally, we created the dynamic list.

**method**

### 4. Using SEQUENCE Function

The **INDEX **function is a frequent method for populating tables and lists in **Excel**. However, the **INDEX **function requires that you define row and column numbers, therefore you’ll need to include a “helper” column or row. So, the **INDEX **may return the data to the appropriate cells and populate them. The new **SEQUENCE **function in Excel eliminates the need for this intermediate step.

Let’s create a dynamic list using **INDEX **and **SEQUENCE **functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select the blank cell
**G6**. - Write down the formula below:

**=INDEX(B6:B21,SEQUENCE(16))**

- Press
**Enter**to see the result.

That’s how you can create a dynamic list.

**🔨 Formula Breakdown**

👉 **SEQUENCE(16): **will return the number of rows in the dataset.

👉 **INDEX(B6:B21,SEQUENCE(16)): **will return the List of all items from the dataset.

**method**

### 5. Create a Dynamic Top 5 List in Excel

We can create a top list in a grocery shop that denotes which product was sold most. No will create a dynamic list of the top 5 sold items in a grocery shop using **LARGE**, **SMALL**,** INDEX**, **IF**,** COUNTIF**, and **ROWS **functions.

**⬇️⬇️ STEPS ⬇️⬇️**

- Select the blank cell
**H6**. - Then write down the formula:

**=LARGE($E$6:$E$21,ROWS(E$6:E6))**

- Press
**Enter**and drag the**Fill Handle**to see the top five values from the**Total Amount**. Column.

**🔨 Formula Breakdown**

👉 **ROWS(E$6:E6)):** denotes number of rows a range from **E6 **to **E21** in dataset.

👉 **LARGE($E$6:$E$21,ROWS(E$6:E6)): **returns the largest ranked value from** E6**:**E21 **row range.

Now we will find the corresponding item’s name from the ranked total amount.

- Select the blank cell
**G6**. - And write down the following formula:

**=INDEX($B$6:$B$21,SMALL(IF($E$6:$E$21=H6,ROW($E$6:$E$21)-ROW($E$5)),COUNTIF($H$6:H6,H6)))**

- Press
**Enter**and Drag the**Fill Handle**to see the**top 5**sold items.

By this, we can create a dynamic list using the **LARGE, SMALL, INDEX, IF, COUNTIF, **and **ROW **functions.

**🔨 Formula Breakdown**

👉 Here, mainly **B5**:**B21** is the list of item names and **E5**:**E21 **is the list of amounts. And we will list cell range **H6:H10**, there the top 5 amounts. And now, using this formula corresponding list of 5 names will appear in cell range **G6:G10**.

👉 **COUNTIF($H$6:H6,H6): **returns the number of cells under the condition.

👉 **IF($E$6:$E$21=H6,ROW($E$6:$E$21)-ROW($E$5))**, check whether the logic condition **$E$6:$E$21=H6 **is true or not**.** If it comes out, it returns the value of a reference row.

👉 **SMALL(IF($E$6:$E$21=H6,ROW($E$6:$E$21)-ROW($E$5)),COUNTIF($H$6:H6,H6)): **will return the smallest value from the array.

👉 **INDEX($B$6:$B$21,SMALL(IF($E$6:$E$21=H6,ROW($E$6:$E$21)-ROW($E$5)),COUNTIF($H$6:H6,H6)): **will return a cell situated in the intersection of the 5th row of from **B6** to **B21** range.

**method**

### 6. Using PivotTable Option to Create Dynamic List

In Excel, dynamic tables are those that update automatically whenever a new value is added. Therefore, the size of the table modifies itself automatically. There are two ways to make a dynamic table in Excel: using the table section to construct a table with the data, or the offset function. Data in dynamic tables can be updated in real-time, and as a result, so can the accompanying reports and pivot tables.

In Excel, using tables, we can create a dynamic table on top of which to construct a pivot.

**⬇️⬇️ STEPS ⬇️⬇️**

- We will choose the data
**B5:E21**initially. - To do this, we’ll go to the “
**Insert**” tab and select “**Table**” from the “**Tables**” submenu. - Our next step is to pick the information. Then, select “
**PivotTable**” from Excel’s “**Tables**” section under the “**Insert**” tab. - A dialogue window, seen below, will appear; select “
**OK**” to continue.

- Due to the presence of headers in our data, it is imperative that we choose “
**My table has headers**” and then “**OK**” button.

- Now we have a greater dynamic range.
- Because of how we set it up, it can accommodate as much data as Table. To do so, select “
**OK**“. Then in the “**PivotTable**,” move “Items” to the rows and you can drag any column the values you want to calculate.

- Thus we created a list containing grocery items.
- An additional piece of information needs to be entered into the sheet where our table is located.
- Refresh the pivot table.
- Also in our dynamic Pivot Table, the information will change on its own.

## 📄 Important Notes

🖊️ See if you’ve used Absolute Reference where it counts.

🖊️ And always keep an eye out for methods that are simple to implement in a variety of settings.

🖊️ Also always give the keyboard shortcuts a shot before resorting to more complex methods.

## 📝 Takeaways from This Article

📌 Undoubtedly You can create a dynamic list in various approaches after reading this article.

📌 In addition, You can also create a dynamic table.

## Conclusion

To summarize, I’m expecting that the methods described above will suffice to create a dynamic list from a table in Excel. And please leave any questions or comments in the section below. Finally, go to** ExcelDen.com** to read more Excel-related articles.

## Related Articles

**4 Approaches to Capitalize All Letters in Excel Without Formula****7 Ways to Calculate Average Attendance with Formula in Excel****3 Steps to Group Age Range in Excel Using VLOOKUP****11 Examples to Do SUMIF in Date Range of Month and Year****7 Quick Ways to Calculate Average Percentage Increase in Excel**