Powerful Tips for Google Sheets: How to Use Dropdown Lists

Google Sheets is a game-changer when it comes to designing sophisticated spreadsheets. Among its many powerful features, dropdown lists are an absolute must-know. With dropdown lists, you can add a menu of options to a cell or range of cells. When you or someone else clicks on the cell, a dropdown menu appears, offering a list of numbers or words to choose from. The selected item will then appear in the cell. It’s that simple!

Why Use Dropdown Lists?

Dropdown lists in Google Sheets have multiple applications:

• Ensuring accurate data entry: If you need your colleagues to enter specific numbers or words into your spreadsheet, dropdown lists simplify the task and eliminate the risk of incorrect input.

• Updating charts in real time: You can create a dropdown list that contains numerical parameters. When someone selects an option from the dropdown, it immediately modifies a chart embedded in your spreadsheet.

• Tracking project progress: Imagine creating a spreadsheet to track a project. With a dropdown list, your colleagues can easily select their progress status from a predefined set of options.

Creating a Dropdown List with Specific Choices

To create a dropdown list that includes specific numbers or words, follow these simple steps:

1. Select the cell or range of cells where you want the dropdown list to appear.

2. In the toolbar above your spreadsheet, click on Insert > Dropdown list. This action will open the “Data validation rules” sidebar on the right.

3. In the “Option 1” and “Option 2” fields, enter the numbers or words you want to include in your dropdown list. Click on Add another item if you want to add more options.

4. If you want to rearrange the items in the dropdown list, simply click and hold the six-dot icon to the left of an item, drag it up or down, and release it in a new position.

5. You can even assign colors to individual items by clicking on the gray circle to the left of each item. Play around with the “Colors” panel that opens to customize the background color of each item.

6. If you want to change the display style of the dropdown list, including the appearance of the dropdown indicator, click on Advanced options and explore the available options in the “Data validation rules” sidebar.

7. Finally, when you’re satisfied with your dropdown list, click the Done button at the bottom of the sidebar to insert it into your spreadsheet.

Now, whenever someone clicks on one of the cells with the dropdown list, they’ll see the options you specified. When they select an item, it will appear in the cell.

Creating a Dropdown List Based on Data in Your Spreadsheet

Sometimes, you may want to create a dropdown list that dynamically pulls data from a range of cells in your spreadsheet. Here’s how you can do it:

1. Select the cell or range of cells where you want the dropdown list to appear.

2. In the toolbar above your spreadsheet, click on Insert > Dropdown list to open the “Data validation rules” sidebar.

3. Under “Criteria,” click on Dropdown list, and then select Dropdown list (from range).

4. To manually enter a range of cells, type the range (e.g., A1:A10) into the input field under “Dropdown list (from range)”. Alternatively, click on the grid icon to select a range of cells from your spreadsheet.

5. Once you’ve defined the range, click the OK button. The selected range of cells will now be the items in your dropdown list.

6. Like with the previous type of dropdown list, you can rearrange items, assign colors, and customize the appearance of the dropdown indicator. Refer to the previous section for detailed instructions.

7. When you’re done, click the Done button to insert the dropdown list into your spreadsheet.

Now, the dropdown list will display the current data from the selected range of cells. Whether your cells contain formulas, numbers, or words, the dropdown list will accurately reflect the contents of each cell.

Editing or Removing a Dropdown List

To edit a cell with a dropdown list, simply click on it. In the dropdown menu that appears, click on the pencil icon in the bottom right corner. This will open the “Data validation rules” sidebar, allowing you to make changes to the dropdown list, such as modifying values or colors.

To remove a dropdown list from your spreadsheet, click on Remove rule at the bottom of the sidebar.

If you have multiple dropdown lists in your spreadsheet, you can manage them all from the “Data validation rules” sidebar. To access the sidebar, go to Data > Data validation. From there, you can modify or remove each dropdown list as needed.

Applying Conditional Formatting to a Dropdown List

Assigning different colors to items in a dropdown list can be an effective way to indicate their importance. By using conditional formatting, you can take this a step further and assign background colors to dropdown list items based on specific rules. Here’s how you can do it:

Assigning Color Triggers to Dropdown List Items

You can assign colors to numbers or ranges of numbers based on the results of your formulas. For instance, if a formula calculates a value of 90 or more, the background color of the cell could turn green when that item is selected in the dropdown list. If the value falls between 20 and 89, the background color would turn yellow.

To assign color triggers to dropdown list items, follow these steps:

1. Select the cell or range of cells containing the dropdown list you want to apply color triggers to. Then, click on Format > Conditional formatting in the toolbar. This will open the “Conditional formatting rules” sidebar.

2. Under the “Format rules” header, click on Is not empty, and then select Greater than or equal to from the list.

3. Enter a value in the “Value or formula” field. For example, let’s use 90 for this demonstration.

4. Click on the paint can icon next to the “Default” bar and choose a color. Let’s go with bright green.

5. To create additional color triggers, click on Add another rule. Repeat the process, specifying different values and colors for each rule.

6. Finally, click the Done button to apply the color triggers.

Now, when the formula in the selected range of cells calculates a value of 90 or more, the background color of the cell will turn green. Similarly, if the value falls between 20 and 89, the background color will be yellow. You can customize the rules to suit your own requirements.

Assigning a Color Scale to Dropdown List Items

Assigning a color scale to dropdown list items is another great way to visualize the values. With this approach, different background colors are assigned based on a range of values. Let’s say you want 100 to be green, 60 to be yellow, and 10 to be red. For values between these numbers, the background color will blend accordingly.

To set up a color scale for dropdown list items, follow these steps:

1. Select the cell or range of cells containing the dropdown list. Then, click on Format > Conditional formatting to open the “Conditional formatting rules” sidebar. Switch to the Color scale tab in the top right corner.

2. Under the “Format rules” header, click on Minimum value and select Number. Enter 10 into the input field.

3. Click on the paint can icon next to “Minimum value” and choose red as the color.

4. Select Number for both “Midpoint” and “Maximum point”. Enter 60 and 100 respectively. Assign yellow to the midpoint and green to the maximum point.

5. Click the Done button to apply the color scale.

Now, when someone selects a number from the dropdown list, the background color of the cell will change accordingly. For example, selecting 60 will turn the background yellow, while selecting 100 will turn the background green. Values in between will display a blended color, indicating their position on the scale.

You can always manage the assigned colors by going to Format > Conditional formatting. From there, you can remove or modify the colors assigned to the dropdown list items.

Final Thoughts

Dropdown lists are a powerhouse feature in Google Sheets, empowering you to create interactive and user-friendly spreadsheets. Whether you want to ensure accurate data entry, update charts in real time, or track project progress, dropdown lists are here to make your life easier.

Remember, you can create dropdown lists with specific choices or dynamically based on data in your spreadsheet. You can also take things up a notch by applying conditional formatting, assigning colors triggered by formulas or creating color scales to visualize values.

So, the next time you’re working on a Google Sheets project, give dropdown lists a try. They might just become your new best friend!

This article was originally published in November 2022 and updated in April 2023.

Read more: Google Sheets power tips: How to use filters and slicers