Welcome to Excel Jet Consult blog post. In this tutorial, we are going to learn how to create automatic searchable drop-down in Microsoft Excel without using formulas and VBA. Let’s get started
Video
Sample Data
In the captions below, we have Sales Data in the Data sheet and unique Payment Type in the Table sheet respectively
Create List Data Validation
In the past, creating searchable drop-down list requires writing technically complex formula or having to write VBA which not straight-forward to an average Excel users These difficulty has finally been laid to rest with the new feature in Excel. Albeit, the new automatic searchable dropdown is currently available to users in the Beta Channel of M365 as at thee time of writing this blog. The new feature will certainly be available to the General Channel of M365 in due course
Based on our dataset as seen above, we want to create dropdown list in cell I1 of the Data sheet. To do that:
Select cell I1
In the Data Tools group of the Data tab, click on Data Validation
In the Settings tab of the Data Validation dialogue box, select List from the dropdown
Click inside the Source box and select the unique Payment Type located in A1:A17 of the Table worksheet
Click OK
Next, we want to search by typing letters that can be found from any of the Payment Type. In this example, I typed “go” which are letters that can be found in gold. From the screenshot below, we can see that all Payment Type that contains go were able from the search.! This is absolutely awesome
SUMIFS Calculation
Finally, we want to write a single criteria SUMIFS formula in cell J1. To do that,
In cell J1, execute the formula: =SUMIFS(F2:F22,C2:C22,I1)
Click Enter.
From the caption below, we have the Total for Gold Verve Payment Type
When we search and select another Payment Type from the dropdown list, the formula automatically recalculates. This is simply awesome new feature in Excel
See you in the next video