Google’s online spreadsheet is a powerful tool for entering and analyzing data. Like its competitors, it has functions for creating drop-down lists which greatly speed up and make data entry more reliable.
Drop-down lists (or menus) are an essential data entry and control method for any good spreadsheet, improving both the speed and reliability of data entry. The principle: allow the user to choose the data to enter in a cell from a list of pre-recorded values, with the possibility of prohibiting the entry of a value not included in the list. All the most popular spreadsheets, such as Microsoft Excel or LibreOffice Calc, offer more or less complex drop-down list functions, but it is clear that Google Sheets has made this tool easier to use and more flexible than ever. Note that in its office suite, and unlike its competitors, Google calls this function “drop-down menu” instead of “drop-down list”.
Google’s online spreadsheet allows you to create, in just a few clicks, drop-down menus that are visually attractive and easy to update. In use, this function proves particularly practical for speeding up and securing data entry in spreadsheets shared between several users, a working method which has become the norm in the era of cloud and online collaborative tools, and which constitutes the very essence of Google Sheets.
In this article, we’ll show you how to create drop-down lists with Google Sheets, how to customize them with colors and a help message, and how to update them by adding or removing values as needed.
If you want to quickly create a small drop-down list with a few values in Google Sheets, follow these steps:
► Create a new Google Sheets workbook or open the one where you want to insert a drop-down list. Then select the cell or range of cells in which you want to insert a drop-down list, then right-click the mouse and click Scrolling menu in the context menu.
► The cell or range of cells you previously selected will now fill with a small gray area containing a black downward arrow (called a “chips” in Google Sheets terminology) and a vertical panel named Data validation rules opens on the right of your screen.
► The panel on the right displays the settings and options for the drop-down list you just created. By default, Google Sheets has inserted a two-option drop-down list in the cell or range of cells you previously selected. You can expand or reduce this area by clicking in the field Apply at the beach then selecting a new area in the spreadsheet.
► When created, the drop-down list contains two values named “Option 1” and “Option 2”. You can change the names of the values by clicking on them in the right panel and writing the desired text. You can also assign a color to the chips present in the cells by clicking on the gray circle to the left of the value names.
► If you click on one of the chips gray in the worksheet, you will see that a drop-down list appears, with the values and colors you defined in the side panel. All you have to do is click on the desired value to enter it in the cell.
► To add new values to the drop-down list, simply click on the button Add another item in the side panel on the right of the screen, give a name and assign a color to the new value, then validate by pressing the green button OK at the bottom of the panel. The new element is then immediately added to the values available in the drop-down list of each cell.
► To remove an item from the drop-down list, click on the trash can icon to the right of its name in the side panel on the right of the screen, then validate by pressing the green button OK. The value will then no longer be available in the drop-down list of each cell. Please note, if the elements were selected in certain cells before their deletion from the list, they are not deleted in the spreadsheet. There chips in cells containing deleted items turns gray again and displays a warning message indicating that the entered value is no longer among the allowed items.
If you already have a list of data in a spreadsheet, such as a series of places, products, or people, and you want to make it a drop-down list that you can use in other cells in your GoogleSheets workbook, follow these steps. follows:
► Select the cell or range of cells in which you want to insert a drop-down list. It can be on the same sheet as that containing the list data or on another sheet of the workbook. Right-click on your selection then click Menu drop-down in the context menu. In the side panel that opens to the right of the screen, in the field Criteriaselect the option Drop-down menu (from a range).
► In the empty field below Drop-down menu (from a range)click the gray four-box table icon to bring up a window named Select a data range. Using the mouse, you can then select any range of cells, in any sheet of your Google Sheets workbook. Once you have correctly selected the area containing the data you want to add to your drop-down list, click the green button OK in the lower right corner of the window Select a data range.
► In the right side panel, you will see that all the data in your list has been added as drop-down list items. You can assign a particular color to each element by clicking on the gray circle to the left of its name. On the other hand, unlike the method seen in the previous chapter, you cannot modify the name of the elements in the side panel. To do this, you must edit the data directly in the spreadsheet, which serves as the source for the drop-down list.
► The data from your source then becomes available elements in the drop-down list that you created on the cells selected in the first step.
► Adding new items to the drop-down list created via the option Drop-down menu (from a range) is more rigid than with the option Scrolling menu. If you add new values at the end of your data source, they will not be automatically taken into account, and you will need to manually edit your data validation rule. A better way to do this is to insert rows in the middle of your data source, they will then automatically be taken into account in your drop-down list.
To help users of your Google Sheets workbook use the drop-down lists you created, you can add a help message that will appear if they are entered incorrectly.
► Open a spreadsheet in which you have inserted one or more drop-down lists, and then click menu Data at the top of the window and finally on Data Validation.
► In the side panel that appears to the right of the window, click the data validation rule corresponding to the drop-down list to which you want to add a help message.
► In the panel, then click on the button Advanced options located after the last item in the drop-down list, to bring up different options. Check the box Show a custom validation message then write the help message of your choice in the field just below. You can also choose between showing a warning or refusing entry when the user enters a value not included in the drop-down list (the help message will be displayed in both cases). Validate your changes by pressing the green button OK.
► So, if you or another user tries to enter a value that does not match any item in the drop-down list, a window containing the help message you wrote will appear on the screen.
You now know how to create, customize and update simple drop-down lists with Google Sheets, to save time and make data entry into your spreadsheets more reliable. It is possible to generate even more sophisticated drop-down lists, for example whose content adapts according to a value entered in a cell (what we call dynamic or cascading drop-down lists), but these possibilities require complex calculation formulas, interweaving several functions together, and go beyond the scope of this article. However, the simple drop-down lists that we have just seen are already extremely practical and should allow you to make life much easier for colleagues or friends using your Google Sheets spreadsheets!