Office hacks: Excel smart drop-down menu

In Excel, we often use custom drop-down menus. The ordinary method of customizing the drop-down menu has a small disadvantage, that is, the drop-down menu can not automatically de-weight the list of data sources, and the use of functions are also more complex. In fact, as long as a good Power Query, plus INDIRECT function, you can easily create a smart drop-down menu can be automatically de-weighted (Figure 1).


Tip: The following operations are done in Excel 2019.

First, select any cell in the data area, switch to the “Data” tab, click “From Table”, in the pop-up dialog box directly click “OK” will appear “Power Query Editor” window. In this window, select the “Inbound Name” column, and under the “Home” tab, click “Delete Column→Delete Other Columns”, then only the “Inbound Name” column is included in the window. “Then click “Delete row → Delete duplicate”, so that there are no duplicate items left in the “Inbound name” column. Click “Close and Upload”, then a new worksheet will be created in the workbook, and this worksheet is saved without duplicate items in the “Inbound Name” column (Figure 2).


Next, select the original data worksheet (that is, Sheet1), switch to the “Formula” tab, click “Name Manager”, then you will see the “Name Manager” window There are two names in the “Name Manager” window, which “Sheet1_2” is the name used to create the drop-down menu (Figure 3).


Click the “Close” button. Select the cell that needs to display the drop-down menu (such as F2), switch to the “Data” tab, click “Data Validation → Data Validation”, in the pop-up window “Settings” tab “Validation conditions” of the “allowed” to select “series”, “source”, enter “= INDIRECT (“Table 1_2″)”, so that when you click on the F2 cell will appear the corresponding drop-down menu (Figure 4).


At this time, no matter adding or deleting incoming names in column B, just right-click column A in Sheet2 and select “Refresh”, then the drop-down menu at F2 will be updated in real time accordingly.

Finally, in the “total number” of the corresponding cell enter the formula “= SUMIF (B: B, $ F $ 2, D: D)”, you can press the drop-down menu options for statistics.

Leave a Comment