When you are managing a project tracker, an inventory sheet, or a team schedule, relying on human data entry is a recipe for disaster. If you ask five different team members to type a status update, you will inevitably end up with “Done,” “Complete,” “Finished,” “done”, and “Cmpelted.” This lack of uniformity makes it impossible to sort, filter, or build accurate charts from your data.
The solution is data validation. By learning how to create a drop-down list in Google Sheets, you can force users to select from a pre-approved menu of exact options. This not only eliminates spelling mistakes but also drastically speeds up the data entry workflow.
How to Insert a Basic Drop-Down Menu
Google recently overhauled the drop-down interface in Sheets, making it incredibly intuitive and visual. You no longer have to memorise complex formulas to build a list.
- Open your spreadsheet and click on the cell (or highlight a range of cells) where you want the drop-down menu to appear.
- In the top navigation menu, click on Insert.
- Select Dropdown from the list.
A new side panel labelled Data validation rules will instantly slide out from the right side of the screen. By default, Google will pre-fill two options (“Option 1” and “Option 2”) and assign them grey pill-shaped backgrounds.
Customising Your Drop-Down Options
Now that the rule is active, you need to replace the default text with your actual project requirements.
- In the right-hand panel, look under the Criteria section. It should be set to “Drop-down”.
- Click into the text box that says “Option 1” and type your first choice (e.g., Not Started).
- Click into the “Option 2” box and type your second choice (e.g., In Progress).
- To add more choices to your list, click the Add another item button at the bottom of the panel (e.g., Complete, Blocked).
Adding Colour Codes
One of the best features of Google Sheets drop-downs is the ability to assign custom colours to each option. This provides instant visual feedback when scanning a massive spreadsheet.
To the left of each option you just typed, you will see a small coloured circle. Click this circle to open the palette. You can assign red to “Blocked,” yellow to “In Progress,” and green to “Complete.”
Once you have finished adding your options and colours, click the blue Done button at the bottom of the panel.
How to Pull Drop-Down Options from Another Sheet
Manually typing options into the sidebar is fine for a simple “Yes/No” menu. However, if your drop-down list needs to contain 50 employee names or 200 product SKUs, typing them by hand is highly inefficient.
Instead, you can instruct Google Sheets to build the drop-down menu by reading a list of data located elsewhere in your document.
- Select the cell where you want the drop-down.
- Click Insert > Dropdown to open the right-hand panel.
- Under the Criteria section, click the dropdown menu that currently says “Drop-down” and change it to Drop-down (from a range).
- A new text box will appear below it. Click the small grid icon (Select data range) inside this box.
- A popup will ask what data to use. Click and drag your mouse over the list of employee names or SKUs you have already typed out in your spreadsheet (even if they are on a different tab).
- Click OK, then click Done.
Your drop-down menu is now populated directly from that column. Best of all, if you add a new employee name to the bottom of that column later, your drop-down menu will automatically update to include them.
How to Remove a Drop-Down List
If you made a mistake or no longer need data validation in a specific cell, do not just press the Delete key. Pressing Delete only clears the currently selected text; it does not remove the underlying drop-down rule.
To completely remove a drop-down list, click the cell, press the Backspace key to clear the text, then click on the drop-down arrow and click the small pencil icon (Edit button) that appears. When the right-hand panel opens, click the Remove rule button at the very bottom.
