Google Sheets Mastery - Self-generating Drop Down List

Sometimes, you need a drop-down list that automatically updates as you enter data. This guide will show you how to create a dynamic drop-down using data validation and a compound formula.

Here is a preview:

Example: Tracking Books

We’ll set up a sheet that records book titles, authors, and series while generating an auto-updating author list.

Steps to Set Up the Drop-Down List

1. Create a new sheet and name it "Authority".
2. In cell A1 of the Authority sheet, enter "Author" as the column title.
3. Note: You can keep this on the main sheet, but using a separate sheet makes it easier to manage and hide.
4. In cell A2 of the Authority sheet, enter this formula: =SORT(UNIQUE(Sheet1!B2:B))
5. This pulls unique author names from column B in Sheet1 and sorts them.
6. Switch back to Sheet1.
7. Click on column B (Author) to select it, then hold Ctrl and click B1 to exclude the header.
8. In the menu, go to Data → Data Validation.
9. In the side panel, click "+ Add rule".
10. Under Criteria, choose "Drop-down (from a range)".
11. Click on the four boxes icon (Select data range).
12. In the pop-up window, click inside the input box.
13. Switch to the Authority sheet.
14. Click on column A, then hold Ctrl and click A1.
15. Click OK. Once author data is entered in Sheet1, the drop-down list will populate.
16. Scroll down in the Data validation rules window and click Advanced options.
17. Under "If the data is invalid," select "Show a warning".
18. (Optional) Under "Display style," select "Arrow".
19. Click Done.

Try It Out

Start entering author names in column B of Sheet1—the drop-down will update automatically.

Next Challenge

Think you’ve got it? Now, set up column C in Sheet1 for the book series using this spreadsheet: spreadsheet template

In the comments below, tell me how you are going to use it.