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: This technique 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.

Latest Items at Top

In this data validation drop down list, the most recently ordered products are at the top. The rest of the product names are below those, 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 - 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"

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 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 the 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 drop down list,

1. Select all the data cells in the Product column
2. On the Excel Ribbon's Data tab, click Data Validation
3. In the Allow drop down, select List
4. In the Source box, type: =ProdList
5. Click OK, to create the drop down list.

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.

Get the Sample File

To see the drop down list example from this page, get the Excel Drop Down List Top Items workbook.

The zipped file is in xlsx format, and does not contain any macros.

More Tutorials

Data Validation Drop Down List

Named Ranges

Named Excel Table