Google Sheets power tips: How to use dropdown lists
Google Sheets lets you design spreadsheets with sophisticated features, and one of the most useful to know is dropdown lists. You can add a dropdown list to a cell (or to a range of cells), and when you or another person with access to your spreadsheet clicks the cell, a dropdown will open that shows a list of numbers or words that they can select. The number or word that’s selected will then appear inside the cell.
Some use case examples:
- You need co-workers to enter very specific numbers or words into your spreadsheet. Providing dropdown lists makes this more convenient for them and eliminates the risk of mistyped entries.
- You add a dropdown list containing number presets that immediately change a chart embedded on your spreadsheet.
- You create a spreadsheet to track a project, in which co-workers select their work progress status from a dropdown list.
You can create two kinds of dropdown lists in Google Sheets: The first lists specific numbers or words that you’ve entered as preset choices, while the second lists data that currently appears in a range of cells in your spreadsheet. This guide walks you through the basic steps of creating both types of dropdowns and adding color to them.
Create a dropdown that lists numbers or words
Select the cell or range of cells where you want the dropdown to be on your spreadsheet. Then, on the toolbar above your spreadsheet, click Insert > Dropdown. The “Data validation rules” sidebar opens to the right.
IDGType in each item you want to appear in the dropdown list. (Click image to enlarge it.)
To add items to your dropdown: Inside the boxes “Option 1” and “Option 2,” type the number or word options that you want included in your dropdown list. Click Add another item to add another option to the dropdown.
IDGYou can assign colors to the items in a dropdown. (Click image to enlarge it.)
To move an item in the dropdown list: Click-and-hold the six-dot icon to the left of an item, drag it up or down, and release it in a new spot on the dropdown list.
To assign a color to an item in the list: Click the gray circle to the left of an item. From the “Colors” panel that opens, click the color you want for the item’s background. If you want to select a color you don’t see or change the item’s text color, click Customize.
When the item is selected from the dropdown on your spreadsheet, the cell and its text display its assigned color. In addition to making your spreadsheet more visually interesting, unique colors can help differentiate items or signify the importance of an item.
To change the dropdown display style: Click Advanced options and scroll down in the “Data validation rules” sidebar until you see “Display style.” The options here let you control how the dropdown appears in the cell you’ve selected in the spreadsheet. If you’ve selected multiple cells, each cell will have its own dropdown indicator.
IDGTwo chip style dropdowns on the left, two arrow style dropdowns on the right.
- Chip: The dropdown appears as a button with a down arrow at its right. This is the default option. Users click the button to open the dropdown list.
- Arrow: This type of dropdown is indicated only by a down arrow at the right edge of the cell. Users click the arrow or double-click anywhere in the cell to open the dropdown list.
- Plain text: This type of dropdown has no indicator. To open the dropdown list, users double-click the cell or type the first letter or number of an item in the dropdown.
To insert your dropdown in the spreadsheet: When you’re finished building your new dropdown, click the Done button at the bottom of the sidebar.
Now when a user clicks (or double-clicks) one of the cells you selected in the first step, a dropdown will open that lists the numbers or words you typed in above. When one of these items is selected, it will appear inside the cell.
IDGIn the spreadsheet, click the down arrow in a cell with a dropdown list to see the available options (left); the item you select will appear in the cell (right).
IDGThe chip and arrow display styles for dropdown lists with colored items selected.
If you assigned a color to an item and chose the chip style display, the color appears on the button in the cell after a user selects it. If you chose the arrow or plain text style, the entire cell shows that color.
Create a dropdown that lists data from your spreadsheet
Select the cell or the range of cells where you want the dropdown to be on your spreadsheet. Then, on the toolbar above your spreadsheet, click Insert > Dropdown to open the “Data validation rules” sidebar to the right.
Under “Criteria,” click Dropdown. From the list that opens, select Dropdown (from a range).
To manually enter a range of cells: Inside the entry box that appears below “Dropdown (from a range),” type the range of cells that you want to appear as items in the dropdown list. For example, if you type in A1:A10, the data inside cells A1 to A10 on your spreadsheet appear as the 10 items in the dropdown list.
To select a range of cells: Click the grid icon inside the entry box below “Dropdown (from a range).” The “Select a data range” panel will open over your spreadsheet. With this panel open, you can scroll through your spreadsheet. Select a range of cells in the spreadsheet; their letter-number designations (for example, C4:G4) appear in the panel’s entry box.
IDGSelecting a range of cells as the source data for a dropdown. (Click image to enlarge it.)
Click the OK button. In the “Data validation rules” sidebar, your selected range of cells appears inside the entry box below “Dropdown (from a range),” and the data in those cells appears as the items in your dropdown list.
To change the dropdown’s appearance: Just as for the first type of dropdown, you can rearrange items in the list, assign colors to items in the list, and change the look of the dropdown indicator. See the previous section of the story for full instructions.
To insert your dropdown in the spreadsheet: When you’re finished building the dropdown, click the Done button at the bottom of the sidebar.
Now when a user clicks (or double-clicks) one of the cells you selected in the first step, a dropdown will open that lists the current data (numbers or words) from the range of cells that you selected. When one of these items is selected, it will appear inside the cell.
IDGThe dropdowns in column I show a list of items from the selected cell range (C4 to G4). (Click image to enlarge it.)
If the range of cells you selected includes formulas, the current number appearing in a cell that’s calculated by a formula will appear as an item on the dropdown list. If the range of cells you selected contains words, those words will appear in the dropdown list. You can even select a range of cells that contains a mix of formulas, numbers, and words. The dropdown will list whatever currently appears in each cell in the range of cells you selected.
Edit or delete a dropdown
Click (or double-click) a cell that has a dropdown in it. On the dropdown menu that opens, click the pencil icon on the lower right. This opens the “Data validation rules” sidebar, and you can make changes to the dropdown, such as changing items’ values or colors.
To delete the dropdown from your spreadsheet, click Remove rule at the bottom of the sidebar.
IDGView all the dropdowns in a spreadsheet by selecting Data > Data validation.
If you have two or more dropdowns on your spreadsheet, you can see them all listed in the “Data validation rules” sidebar. From the toolbar above your spreadsheet, select Data > Data validation. On the sidebar that opens, click the dropdown you want to edit to open it in the sidebar, or click the trashcan icon next to it to delete it from your spreadsheet.
Apply conditional formatting to a dropdown
As mentioned previously, assigning different colors to the items in a dropdown can indicate their relative importance. Another way to do this is to assign background colors using conditional formatting rules. This gives you some additional options for highlighting the status of items in the list, including triggering colors based on formula calculations and displaying items along a color scale.
Note: Applying conditional formatting to dropdowns should not be used in addition to assigning colors to the items in a dropdown as described above, but as an alternative approach. Also, it works best with the arrow or plain text dropdown display styles. If you use the default chip style, the button will obscure most of the background color in the cell.
Assign color triggers to items in a dropdown
You can assign colors to numbers or number ranges that may show up when a formula in your dropdown list calculates them. For example, if the formula for an item in your dropdown list calculates the number 90 or above, then the cell background color could become green when the item is selected from the dropdown list. If the value for an item is calculated from 20 through 89, then the cell background color would become yellow.
Select the cell or cell range that contains the dropdown that you want to apply color triggers to. Then, on the toolbar above your spreadsheet, click Format > Conditional formatting. The “Conditional format rules” sidebar opens along the right.
IDGUse the “Conditional format rules” sidebar to assign background colors to items in a dropdown if they meet certain criteria. (Click image to enlarge it.)
In the sidebar under the “Format rules” header, click Is not empty. From the long list that opens, select Greater than or equal to.
An entry box with the words “Value or Formula” appears. Type in a value — in this example, type 90.
Next, below the bar labeled “Default,” click the Fill color icon, a paint can. A color selection panel opens. For our example, select bright green.
IDGDesignate the formatting rules (left), value (middle), and fill color (right) for an item in the dropdown. (Click image to enlarge it.)
Near the bottom of the sidebar, click Add another rule.
Inside the entry box under “Greater than or equal to,” this time type 20. Then click the paint can icon and select the color yellow.
Finally, click the Done button.
Now if a formula in your selected cell range for this dropdown calculates 90 or above and the number is selected from the dropdown list, the background color of the cell will become green. If the formula calculates 20 or above (up to 89) and the number is selected from the dropdown list, the background color of the cell will become yellow.
IDGWhen the value of the list item selected is 90 or above, the cell is colored green. When it’s 20 or above (but less than 90), the cell is yellow.
Of course, you aren’t limited to “Greater than or equal to” rules or even numerical values. When you click Is not empty, the list that opens contains several other rules that you can apply to trigger a change in background color, based on the text, date, or numerical value of the data in the cell.
Assign a color scale to items in a dropdown
You can assign a range of background colors to the items in your dropdown list. For example, you could set it so that if the user selects 100 from your dropdown list, the cell background color turns green. For 60, the cell background turns yellow. For 10, the cell background turns red. And for any numbers on your dropdown list that fall between two of these three, the background will appear as an intermediate shade between the two colors. This provides a visual indicator for the values of items in a dropdown list.
To illustrate this, let’s create a dropdown list that contains ten numbers (10, 20, 30, etc.) that can be selected.
Select the cell or cell range that contains the dropdown list, then click Format > Conditional formatting to open the “Conditional format rules” sidebar. On the sidebar, click the Color scale tab on the upper right. The sidebar will switch to the “Color scale” panel.
Next, under the “Format rules” heading, click Min value under “Minpoint” to open its dropdown menu. For our example, select Number. Type 10 in the entry box to the right.
Click the paint can icon to the right. From the color selection panel that opens, select the color red.
IDGTo set up a color range, assign a Minpoint, Midpoint, and Maxpoint value and color. (Click image to enlarge it.)
Select Number from the dropdown lists under “Midpoint” and “Maxpoint” too, and type in 60 and 100, respectively. Click their paint can icons and select the color yellow for Midpoint and green for Maxpoint. As you set these points and colors, you’ll see a preview of the whole color range just above.
Click the Done button.
Now when the number 60 is selected from the dropdown list, the cell’s background color turns to yellow. When you select 70, the background color turns to a yellow that has a tinge of green mixed in. When you select 100, the cell’s background color will be fully green.
IDGUsing a color scale provides a visual indicator for the values of items in a dropdown list. (Click image to enlarge it.)
Manage conditional formatting colors
If you want to change the background colors you’ve assigned to the items in a dropdown list, click to select the cell that contains the dropdown list, then click Format > Conditional formatting to open the “Conditional format rules” sidebar. In the sidebar you’ll see a list of the color assignments you’ve made for the dropdown list, each with its own color swatch.
IDGClick the trashcan to remove a conditional color assignment.
To remove a color: Move the pointer over the color swatch and click the trashcan icon that appears to the right of the swatch.
To change a color: Click the color swatch. If it’s a single color, the sidebar will switch to the “Single color” panel. If it’s a range of colors, the sidebar will switch to the “Color scale” panel. Click the paint can icons on either panel to change colors.
To add a new color: Click Add another rule. The sidebar will switch to the “Single color” panel. If you want to assign a range of colors to the items in your dropdown list, click Scale color on the upper right to switch to this panel.
This article was originally published in November 2022 and updated in April 2023.
Read this next: Google Sheets power tips: How to use filters and slicers
Copyright © 2023 IDG Communications, Inc.
