New Automatic Searchable Dropdown in Excel [No More Formula-Based Solution]

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


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

Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *