Creating a dropdown list in an Excel sheet, Part 1

I am a member of an organisation which requires me to log my PD each year, using a web portal. However, the web portal itself has made the logging a tedious task. I wanted to create a simpler, less costly alternative for PD logging that I could share with the organisation, and reduce member effort.

However, as I was creating my log sheet, I realised that having some drop downs would simplify matters. By crikey, using drop down lists saves time and effort; not to mention standardising data entry, and so improving the quality of the resulting information.

This post will walk through setting up a simple drop down selection list in an Excel spreadsheet - not in a form, but a database format. Excel only lets us select one item from a drop down, unless we get into VB code (more on that in Part 2, later; Bansal, 2025).

Initially, I did quite a bit of work. I created an ".xlm" - macro enabled - workbook containing my working materials: a log sheet for my data entry database, put all my lists on another sheet with named ranges, then hid that sheet (and locked the cells on it in case anyone unhid it).

Once I had ensured I had my log sheet fields were ticketty boo, and had instructions embedded as notes at the top of each column, I set auto filters, view panes, and formatted the columns. Then I was ready!

Are we sitting comfortably? Then I will begin ;-)

1. Open our spreadsheet and click in the first cell we want our drop down list to appear in
2. To to the Data ribbon, Data Tools, and click on the Data Validation icon to open the Data Validation dialogue box
3. On the default tab, "Settings", go to Allow and select "List"; then in the Source field, we can key in our list items, separated by commas
4. Click OK.

We now have a simple dropdown list. To change any items, we just click in the cell, go back to data validation, and amend the Source field items (Stratvert, 2025; Wong, 2024).

But what if we already have a list which is a named range, as I did? Let's say that named range is "Competencies", and:

1. Go back to the Data Tools Tab | Data Validation
2. Click into the Source field and key "=Competencies"
3. Click OK.

Now we have can amend our list, not back in Data Validation, but in our named range on the hidden sheet whenever we need to. So long as we expand our named range, we can add as many items as we require.

Even better, we can create drop down lists with a multiple selection function - see Part 2 to find out how to do that!

Sam

References:

Bansal, S. (2025, June 12). Creating Multiple Selection Drop-Downs in Excel (It's Possible - Here's How). YouTube/TrumpExcel. https://youtu.be/KoTKTwx7cA0

Stratvert, K. (2025, February 4). How to Create a Drop-Down List in Excel (2025 Tutorial). YouTube. https://youtu.be/CaD4-5UAK0I

Wong, C. (2024, November 11). 2 Simple Ways to Create an Excel Drop Down List. SpreadsheetPoint. https://spreadsheetpoint.com/excel/drop-down-list/