Home > Validation > Drop Downs > Sort Latest Excel Drop Down - Latest Items at TopProduct drop down on Orders sheet shows latest used product names at the top, with remaining products below that, listed alphabetically. Set the number of top products by typing a number on the Lists sheet.
|
Latest Items at TopIn this Microsoft Excel data validation drop down list, the most recently ordered products are at the top. The rest of the product names are below those, at the end of the list, with list items in alphabetical order. You can set the number of "top" items, by typing a number on the worksheet. This technique uses SORTBY and MAXIFS, which are available in Excel 365. Here's what the drop down looks like, in the Product column on the Orders worksheet.
There are steps below, if you'd like to build the drop down list yourself. You can also get the completed Excel file, to see how it works. |
Product List TableIn the sample workbook, on a sheet named Lists, there is a named Excel table, with 4 columns:
Because this list is in a named table, it will change size automatically, if you add a new item in the row below the last item, or delete an existing item from the data. |
How Many Top Items?Also on the sample file's Lists sheet, there's a cell named TopNum. In that cell (B2), you can type the number of "top" items that you want to show in the drop down list. For example, type 3 in the TopNum criteria cell, and:
TIP: To see the steps for naming a cell, go to the Naming Ranges page |
Latest Date FormulaTo calculate the latest date that each product was ordered, this MAXIFS formula checks the Orders table (SalesData).
The formula returns:
NOTE: The MAXIFS function is available in Excel 2019, and Excel 365. You can read more about MAXIFS on the Microsoft site. That page explains how the function works, and shows 6 examples of how to use it. |
Date Rank FormulaThere's a simple RANK formula in the DateRank column:
It compares each product's latest date, with all the other product dates, and returns its rank, in descending order. So, the product with the most recent date is ranked 1, and so on.
TIP: See more RANK examples, and ways to break ties, when needed. |
Sort FormulaThe final column in the product table has the heading, Sort. Its formula returns a number that's used for the first sort in the product list for the drop down.
Here's how it works:
|
Make a Sorted Product ListThe List sheet also has a spill formula, in cell B5, to create the sorted product list. The formula uses the SORTBY function, which is available in Excel 365.
The formula returns all the product names:
Here is the list created by the spill formula, with the top 3, then the other products, sorted A-Z. |
Change Number of Top ItemsIf you change the number in the TopNum cell, the product list in cell B5 changes immediately. In this screen shot, the Top number was changed to 2, so there are 2 items at the top of the list, with the rest sorted alphabetically, NOTE: The spill formula's list will also update automatically when you select product names on the Orders sheet. |
Create a Named RangeThe product list is used for a data validation drop down, on the Orders sheet. To make it easy to refer to the list, a name, ProdList, was created, using the spill operator
You can see the ProdList name and its formula in the Name Manager. |
Create Drop Down ListOn the Orders sheet, there's a data validation drop down list in the Product column. This video shows how to set up a drop down list, using a different workbook as an example. The written steps for this workbook are below the video. |
To set up the dropdown list,
|
Use the Drop Down ListOn the Orders sheet, when you select a cell in the Product column, the drop down arrow appears. Select one of the recently ordered products, at the top of the list, or scroll down, to find a product name in the A-Z section. Note: Because the order list is in a named table, the drop down list should be added automatically, if you add new data in the row below the last item. |
Get the Practice FileTo see the drop down list example from this page, download the Excel Drop Down List Top Items workbook. The zipped file is in xlsx format, and does not contain any macros. |
Related LinksData Validation Drop Down List Fix Drop Down Opens with Blank Selected NOTE: The technique on this page uses the new SORTBY and MAXIFS spill functions (Excel 365). Go to the Excel Spill Functions page, for more examples of what you can do with these powerful new functions. |
Last updated: December 21, 2022 3:17 PM