Tip of the week # 658
10 June 2021

Create an alphabetical drop-down list that automatically updates when data is added to your table (Microsoft Excel 365) *

Excel provides more than 450 Functions - a special type of formula that produces a specific result. There are a lot of complicated Formulas, but I'm a great believer that a Formula doesn't have to be complex. Just break it down!

Preparing for a new webinar for the Cambridge Science Park - Ten Excel Functions everyone should know (and those you shouldn't) - I stumbled upon two new Functions ... UNIQUE and SORT. The UNIQUE function returns a list of unique values in a list or range and if you combine it with the SORT function you can create an alphabetical drop-down list (data validation) from data in an existing table.

I realise it's a rather specific tip, but knowing quite a few of my subscribers personally I think this could be useful. I've broken it down to make it easier to explain and understand. You can also combine step 2 and 7, but as promised I wanted to make it easy to explain and understand. (The Dutch don't just show they are clever clogs ... they make them! ;)

Here's how:

  1. Select cell H1.
  2. Type: =UNIQUE
  3. Press TAB.
  4. Click and drag over C2:C15. (For now, pretend row 16 doesn't exist.)
  5. Press ENTER.
  6. Select cell H1.
  7. In the formula bar click immediately after the open bracket and type SORT(
  8. Press ENTER.
  9. Press ENTER or click Yes to accept the correction to include two close brackets.

Next, you can create a drop-down list that automatically updates whenever you add a new person with a new job, like I did in row 16. See tip_608.php.

With special thanks to Paul Barnhurst for writing on LinkedIn about UNIQUE and Mark Robson for SORT.

Related tips

  1. Create a drop-down list with text to ensure accurate and consistent data entry : tip_608.php
  2. Find and select all cells with data validation : tip_598.php

* Unless stated otherwise this tip is written for Microsoft 365 desktop apps and Windows 10 users, but might also be useful in Office 2010, 2013 and 2016.