Md Shamse Tabrej
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.
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.
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.
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.
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.
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.
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.
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
