How to Create a Dropdown List in Google Sheets

Maintaining data consistency is one of the biggest challenges when multiple people collaborate on a single Google Sheet. If you ask a team to enter the status of a project, you might end up with “In Progress,” “Working on it,” “Started,” and “Ongoing.” This lack of uniformity breaks formulas, filters, and pivot tables.

The most effective way to enforce data consistency is by creating a dropdown list. By restricting data entry to a predefined set of options, you eliminate typos and variations, ensuring your spreadsheet remains clean and functional.

In this guide, you will learn how to create a dropdown list in Google Sheets using the Data Validation tool, how to populate the list using data from another sheet, and how to colour-code the options for better visibility.

Method 1: Creating a Simple Dropdown List

If you only have a few options (e.g., “Yes,” “No,” “Maybe”) and they are unlikely to change, manually entering the list is the fastest method.

1. Select the cell or range of cells where you want the dropdown list to appear. (You can click and drag, or select an entire column by clicking its letter at the top).
2. In the top menu, click Data, then select Data validation.
3. A panel will appear on the right side of the screen. Click + Add rule.
4. Under the Criteria section, ensure the dropdown menu is set to Dropdown.
5. You will see two default options (“Option 1” and “Option 2”). Click in the text box and replace them with your desired choices (e.g., “Approved” and “Rejected”).
6. To add more choices, click the Add another item button.
7. Click Done at the bottom of the panel.

Your selected cells will now display a pill-shaped dropdown button containing your specific options.

Method 2: Creating a Dropdown List from a Range

Manually typing options is inefficient if your list contains 50 different employee names, or if the list changes frequently. Instead, you can link the dropdown to a separate list of cells. When you update those cells, the dropdown updates automatically.

1. First, type your list of options in an empty column (preferably on a separate worksheet tab named “Data Options” to keep things organised).
2. Select the cell or range of cells where you want the dropdown list to appear.
3. Go to Data > Data validation and click + Add rule.
4. Under Criteria, click the dropdown menu that says “Dropdown” and change it to Dropdown (from a range).
5. A small grid icon will appear in the text box below. Click this grid icon.
6. A dialog box will ask you to select a data range. Click into your spreadsheet, navigate to the list you created in Step 1, and highlight all the options. Click OK.
7. Click Done.

If a new employee joins the company, you simply add their name to the bottom of the list in your “Data Options” tab, and they will immediately appear in the dropdown menu.

How to Colour-Code Dropdown Options

Adding colours to your dropdown options makes a spreadsheet much easier to read at a glance, especially for status tracking (e.g., Green for “Complete,” Red for “Delayed”).

1. Click on a cell that already contains your dropdown list.
2. A small pencil icon (Edit button) will appear at the bottom of the dropdown menu. Click it to reopen the Data Validation panel.
3. Next to each option in the list, you will see a small grey circle. Click the circle.
4. A colour palette will appear. Select a colour for that specific option.
5. Repeat this for all other options in your list.
6. Click Done.

Now, when a user selects an option, the entire cell will be highlighted in the chosen colour.

How to Remove a Dropdown List

If you no longer need the dropdown list and want to return the cell to a standard text input:

1. Select the cell(s) containing the dropdown list.
2. Go to Data > Data validation.
3. In the right-hand panel, click the rubbish bin icon (Remove rule) next to the specific rule.

The dropdown menu will disappear, but any text that was previously selected will remain in the cell.

By implementing dropdown lists via Data Validation, you significantly reduce data entry errors and ensure your Google Sheets remain robust and ready for advanced analysis.