If you regularly use spreadsheet programs to process large datasets, you will welcome anything that helps. This is where drop-down lists in Excel come in. These allow you to conveniently add content with just one click so you don’t have to type the same thing again and again. We show you how to insert a drop-down in Excel.
Spreadsheet programs help present complicated information clearly and concisely. When sorting values and data, it can be very helpful to assign attributes. To avoid having to manually associate attributes and cell values every time, you can use drop-down lists. These lists allow users to choose from a pre-defined list of attributes. This article explains how to create and edit drop-down lists in Google Sheets.
Excel with Microsoft 365 and IONOS!
Use Excel to create spreadsheets and organise your data - included in all Microsoft 365 packages!
Why are drop-down lists so useful?
If a cell contains a drop-down list, users can select a value from a pre-defined list with just one mouse click. The selected value is then displayed in the cell. Selecting a value with the mouse is much easier than having to type the value manually. Drop-down lists also help keep your data accurate and consistent. If multiple people are working on the same spreadsheet, using drop-down lists allows you to ensure that users only enter accepted values. This results in fewer typos and greater time savings which are among the many good reasons for using drop-down lists in Google Sheets.
Check out our comparison of Google Sheets vs. Excel to learn about the differences between the Google and Microsoft spreadsheet programs.
Creating drop-down lists in Google Sheets: a step-by-step guide
Google has made it as easy as possible for users to add drop-down lists to individual cells. To add a drop-down list, follow the steps below:
Step 1: Choose a cell, then right-click and select “Data validation”:
Step 2: In the “Criteria” section, choose “List of items”:
Step 3: Once you’ve selected “List of items”, enter all the items for your list in the free-text field. The items must be separated using commas. Now click on “Save”.
Step 4: The drop-down list will now be added to the cell you selected. You can see this because there will be a small down-arrow in the cell.
Step 5: If you click on the arrow, the drop-down list will open. Check that the items appear in the right order and that everything is spelt correctly. If you want to make changes, simply open the “Data validation” window again and edit the list in the “Criteria” section.
Step 6: In the “Data validation” window, there is also an optional “Appearance” check box: “Show validation help text”. In the free-text field just below this, you can enter a message that will be displayed whenever someone hovers the cursor over the cell containing the drop-down list.
If you enter a help text, it will be shown as follows:
Step 7: If you want to delete a drop-down list, simply go back into the “Data validation” menu and click on the “Remove validation” button. The formatting is cleared from the selected cell, so you can now enter other values.
You might also find our article on "Google Sheets: conditional formatting explained" interesting.
Google Workspace by Google with IONOS
Working together just got easier! Configure Gmail for your domain and benefit from the familiar Google tools all in one place.