Excel is the most useful tool when it comes to dealing with huge datasets. Normally we are used to creating a drop-down list but we often need to create a dynamic drop-down list in Excel to make our life easier. We can easily do so by applying the OFFSET function. In this article, I will show you how to create a dynamic drop-down list in Excel with OFFSET function.
This is the dataset that I am going to use to illustrate how to create a dynamic drop down list in Excel with the OFFSET function. We have some sports Event(s) and the List of Winners. We will sort the winners to the respective events by creating a dynamic drop down list.
Dynamic Drop Down List Using Excel OFFSET: 3 Methods
1. Create Dynamic Drop Down List in Excel with OFFSET and COUNTA Functions
Here, I will illustrate how to create a dynamic drop down list in Excel using the OFFSET and COUNTA functions. I need to create a dynamic drop down list in the range C4:C11. I will select the Winner from the List of Winners.
STEPS:
➤ Select the range C4:C11. Then go to the Data tab >> Data Tools >> Data Validation >> Data Validation.
➤ Data Validation dialog box will pop up. Select List from the drop-down in that dialog box.
➤ In the Source box, write down the following formula.
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
Formula Breakdown
➥ COUNTA($E$4:$E$100) ➜ Returns the number of cells that are not empty in the range E4:E100
Output ➜ {4}
➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ Returns a range based on the row and column of a given reference.
➥ OFFSET($E$4,0,0,4,1)
Output âžœ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
Explanation: The reference is E4. Since the row is 0 and the column is 0 ultimately with a height of 4 cells, we will have the values from cell E4:E7.
➤ Select OK.
Excel will create a drop-down box in each cell of the range C4:C11.
Notice that the options in the drop-down box are exactly like the ones of List of Winners. Now, to check whether this is a dynamic drop-down box or not, let’s assume that the Winner of the Event Shooting is James. Since James is not on the List of Winners, let’s add his name and see what happens.
As soon as we added the name of James in the List of Winners, Excel automatically updated the options in the drop-down options. So these drop-down lists are dynamic in nature.
➤ Now select the remaining Winner.
NOTE: Remember that the range we have selected in the COUNTA Function is E4:E100. That’s why Excel will update the drop-down options as long as we add or update the cells in range E4:E100.
2. Way to Create a Dynamic Drop Down List in Excel with OFFSET and COUNTIF Functions
We can also create a dynamic drop down list in Excel using the OFFSET and COUNTIF functions.
STEPS:
➤ Bring the Data Validation dialog box like method-1. In the Source box, write down the following formula
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
Formula Breakdown
➥ COUNTIF($E$4:$E$100,”<>”)âžœ Returns the number of cells that are not empty in the range E4:E100
Output ➜ {4}
➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,”<>”)) âžœ Returns a range based on the row and column of a given reference.
➥ OFFSET($E$4,0,0,4,1)
Output âžœ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
Explanation: The reference is E4. Since the row is 0 and the column is 0 ultimately with a height of 4 cells, we will have the values from cell E4:E7
➤ Select OK.
➤ Excel will create a drop-down box in each cell of the range C4:C11.
To check whether this is a dynamic drop-down box or not, let’s assume that the Winner of the Event Shooting is James. Since James is not on the List of Winners, let’s add his name and see what happens.
As soon as we added the name of James in the List of Winners, Excel automatically updated the options in the drop-down options. So these drop-down lists are dynamic in nature.
➤ Now select the remaining Winner.
NOTE: Remember that the range we have selected in the COUNTIF Function is E4:E100. That’s why Excel will update the drop-down options as long as we add or update the cells in range E4:E100.
3. How to Create a Nested Drop-Down List Using a Combination of Functions
In this section, we will dive into creating a smarter and more advanced dynamic drop-down list, a nested one. We will use the OFFSET, COUNTA, and MATCH functions together. Let me explain what we are up for.
This is the dataset for this method that represents information of particular products.. Basically, we are going to create two drop-down lists in cells F3 and F4. Depending on the option selected in F3, Excel will update the options in F4. Let’s do it step by step.
STEP-1: Creating a Drop-Down list in F3
➤ Bring the Data Validation dialog box like method-1. In the Source box, do a cell reference, which are the table headers (cell B3:D3).
Excel will create a drop-down list in F3.
STEP-2: Creating a Dynamic Drop-Down list in F4
Now I will create another drop-down list in F4. The options in the drop-down list of F4 will depend on what we have selected in the drop-down list of F3. To do so,
➤ Bring the Data Validation dialog box like method-1. In the Source box, write down the following formula
=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)
Formula Breakdown
➥ MATCH($F$3,$B$3:$D$3,0) ➜ Returns the relative position of the cell value F3 from the range B3:D3
Output: {1}.
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ Returns a range based on the row and column of a given reference. The height is 10. That’s why output will be an array of 10 cell values starting from the reference.
Output: {“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}
➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ Returns the number of cells that are not empty in the selected range.
➥ COUNTA{“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}
Output: {4}
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH ($F$3,$B $3:$D$3,0)-1,10,1)),1) ➔ Returns a range based on the row and column of a given reference
➥ OFFSET($B$3,1,1-1,COUNTA{“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}),1)
➥ OFFSET($B$3,1,0,4,1)
Output: {“Sam”;”Curran”;”Yank”;”Rochester”}
Explanation: The reference is B3. Since the row is 1 and the column is 0 ultimately with a height of 4 cells, we will have the values from cell B4:B7.
 ➤ Select OK.
Excel will create a dynamic drop-down list in F4. The options will change depending on what you select on F3. For instance, when you select Name in the F3 drop-down list, the drop-down list in F4 will show the names available in the Name column.
Similarly, when you select Product in the F3 drop-down list, the drop-down list in F4 will show the products available in the Product column.
Now if you add or update the Name, Product, or Brand, Excel will update the drop-down list in F4. For example, I have added a new name Rock in the Name column and Excel has added the name in the drop-down list.
Read More: How to Create a Dynamic Top 10 List in Excel
Practice Workbook
As you can see, creating a dynamic drop down list in Excel with the OFFSET function is really tricky. So I suggest that you practice more and more. I have attached a practice sheet for you.
Download Practice Workbook
Conclusion
In this article, I have illustrated 3 methods to create a dynamic drop down list in Excel with the OFFSET function. I hope you will find this article helpful. Lastly, if you have any comments, please leave them in the comment box.
Related Articles
- How to Create Dynamic List From Table in ExcelÂ
- How to Create Dynamic List in Excel Based on CriteriaÂ
- How to Make Dynamic Drop Down List from Another Sheet in Excel
<< Go Back to Dynamic List Excel | Excel Drop-Down List | Data Validation in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
It doesn’t work in google sheets though. Any ideas on how to fix that?
Hi LILIA,
\
I hope you are doing well.
Please use the conditions as per the image and see if it works.
The range $E$4:$E$100 will allow you to have a list of 97 items. So, this can be used as a dynamic drop-down
Thank you.