Easily Create and Customise Drop-Down Menus in Google Sheets

Drop-down menus in Google Sheets simplify data entry and enhance accuracy by allowing users to select predefined options in a cell. Whether you’re creating a static list or a dynamic one that updates automatically, drop-downs save time and make analysis easier. This guide walks you through creating, customising, and maximising the use of drop-down menus.

How to add drop-down menus

To insert drop-down menus:

1. Right-click on the selected cell or range and choose Drop-down.

2. Alternatively, go to Insert > Drop-down.

Once added, the Data validation rules sidebar appears. Here, you define the options, colours, and other settings for your drop-down menu.

Customising options and colours

In the sidebar:

• Enter options directly under “Option 1”, “Option 2”, etc. Click Add another item for more.

• Set unique colours for each option using the default palette or the Customise tool.

Advanced options

Here you can add help text, enforce specific inputs by rejecting invalid entries or showing a warning, and also change the style of the drop-down menu.

Using options from a cell range

Instead of manually entering options, you can reference a range of cells. Select your range and open the drop-down sidebar. Then under Criteria, select Drop-down (from a range) and specify your range. For dynamic lists that update as new items are added, use an open-ended range like A1:A.

Sorting and dynamic lists

To alphabetise options, use the SORT function on your referenced range and use this sorted range for the drop-downs.

Allowing users to add options

For continuously growing lists, combine SORT with UNIQUE to remove duplicates.

If users need to add new items directly through the drop-down menu:

1. Set up the SORT and UNIQUE formula which looks at the range where the drop-downs will be.

2. Set up the drop-downs and point them to the range where the SORT and UNIQUE formula will populate. I usually use an open-ended range here to not limit the options.

3. Set the advanced option to Show a warning for invalid entries, as this allows users to enter a value that isn’t in the drop-down menu.

As new entries are added, they will automatically appear in the drop-down menu and where the formula is. This approach enables user-generated lists that expand as data is added, making drop-downs flexible and adaptable.

Drop-down menus are a powerful tool in Google Sheets, helping streamline data entry while maintaining accuracy. By combining static options, dynamic lists, and advanced features, you can tailor them to suit any workflow.

Check out this video where I dive more deeply into drop-down menus:

To see more tips on how to use Google Sheets here.

If you want to learn Google Sheets well, consider doing my Google Sheets — From Novice to Ninja course on Udemy. 😊