Home > Validation > Drop Downs > Sort Latest

Excel Drop Down - Latest Items at Top

Product 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.

• Note: To fix a drop down list that opens at an empty cell, go to the Drop Down List Troubleshooting page

Latest Items at Top

In 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.

• The 3 latest product names at the top
• All other product names are below, listed alphabetically, A to Z

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 Table

In the sample workbook, on a sheet named Lists, there is a named Excel table, with 4 columns:

1. Product - product names (unique values) - manually entered
2. Latest - formula - latest date that the product was ordered
3. DateRank - formula -where that date ranks among all the latest dates
4. Sort - formula -how that product name should be sorted – top 3 or "all other"

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:

• The 3 most recently ordered products will be at the top of the drop down list
• All other products are below, listed A to Z

TIP: To see the steps for naming a cell, go to the Naming Ranges page

Latest Date Formula

To calculate the latest date that each product was ordered, this MAXIFS formula checks the Orders table (SalesData).

• =MAXIFS(SalesData[Date], SalesData[Product], [@Product])

The formula returns:

• the latest (maximum) date in the Orders table -- SalesData[Date]
• where the product name in the order table -- SalesData[Product]
• matches the product name in the current row of the product table -- [@Product]

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 Formula

There's a simple RANK formula in the DateRank column:

• =RANK([@Latest],[Latest],0)

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.

• NOTE: There could be ties in rank, if multiple products have the same date

TIP: See more RANK examples, and ways to break ties, when needed.

Sort Formula

The 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.

• =IF([@DateRank]<=TopNum,[@DateRank],$B$2+1)

Here's how it works:

• If DateRank is less than or equal to the TopNum value, the DateRank value is used
  • Bran is ranked 3rd, so its Sort number is 3
• All other products get a Sort value of the TopNum + 1
  • Arrowroot is ranked 6th, so its Sort number is 4 (3+1)

Make a Sorted Product List

The 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.

• =SORTBY(tblProd[Product], tblProd[Sort],1, tblProd[Product],1)

The formula returns all the product names:

1. sorted 1st by Sort number, ascending: tblProd[Sort],1
2. sorted 2nd by Product name, ascending: tblProd[Product],1

Here is the list created by the spill formula, with the top 3, then the other products, sorted A-Z.

Change Number of Top Items

If 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 Range

The 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

• ProdList: =Lists!$B$5#

You can see the ProdList name and its formula in the Name Manager.

Create Drop Down List

On 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,

1. Select all the data cells in the Product column
2. On the Excel Ribbon's Data tab, in the Data Tools group, click Data Validation
3. In the Data Validation dialog box, go to the Settings tab, if it's not already selected
4. In the Allow box, select List
5. In the Source box, type an equal sign, followed by the source list name: =ProdList
• OR, Instead of typing, press the F3 key, for a keyboard shortcut to open the Insert Name box. Then click on a name in the list, to insert it, and click OK to close the dialog box.
6. (optional) Go to the Error Alert tab, and select error settings for invalid data
7. (optional) Go to the Input Message tab, and create a message that shows when the drop down cell is selected settings
8. Click the OK button, to close the Data Validation window, and to create the dynamic drop down list in the selected range of cells.

Use the Drop Down List

On 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 File

To 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 Links

Data Validation Drop Down List

Fix Drop Down Opens with Blank Selected

Named Ranges

Named Excel Table

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.