6 Quick Ways to Create Dynamic List from Table in Excel

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.

dataset for creating 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.

Applying Filter and OFFSET Functions to create Dynamic List

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)

Results after creating Dynamic List

  • 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.

Only 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)

Output showing Assistance List

  • 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.

Inserting Data Validation

  • 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.

Dynamic List as Output

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))

Using SEQUENCE function to create Dynamic List

  • 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))

Using LARGE Function

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

Dragging Fill Handle

🔨  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)))

Insert LARGE, SMALL, INDEX, IF, COUNTIF and ROWS functions

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

Press Enter to create a dynamic list

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.

Insert pivot Table to create a Dynamic List

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

Steps to create a Pivot Table

  • 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.

Options to Create different Dynamic List

  • 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

(Visited 86 times, 1 visits today)
Md Shamse Tabrej

Md Shamse Tabrej

Hi, I'm Shamse Tabrej. I'm an admirer of Excel who enjoys sharing and solving problems in Excel. I make an effort to investigate all of the choices so that I may point readers toward the most viable solutions. Both the globe and its problems are boundless. Let's assist one another in developing. Reader questions and suggestions are most welcome. 

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo