If you are creating an interactive spreadsheet, you may need a drop-down list so that users can choose between options. To do so, you can follow this tutorial to create a drop-down list in Google Sheets. You can create a single, colored as well as a nested drop-down menu with the help of this guide.
Like various programming languages, it is possible to include the if-else statement in a Google Sheets spreadsheet as well. Let’s assume that you are creating a spreadsheet for people who should select different options according to various criteria. At such a moment, it is wise to use a drop-down list so that you can provide more than one choice to the people.
How to create a drop-down list in Google Sheets
To create a drop-down list in Google Sheets, follow these steps-
- Select a cell and go to Data > Data validation.
- Select the List of items.
- Write down your items or options.
- Save your change.
First, select a cell in a spreadsheet and click the Data from the top navigation bar. After that, select the Data validation option from the list.
Now, expand the Criteria drop-down menu, and select List of items. Next, you need to write down all the options or items in the empty box.
At last, click the Save button to show the drop-down list in a cell.
Like Excel, Google Sheets shows a warning or error message for entering invalid data. By default, it shows a warning message and allows users to write custom text. If you want to prevent users from entering invalid data, you need to choose the Reject input option in the Data validation window.
Read: How to convert and open Apple Numbers file in Excel on Windows PC
How to create a dropdown list in Google Sheets with color
Just like Microsoft Excel, Google Sheets allows you to create a drop-down list with color-coded values. However, creating a colorized dropdown list is much easier in Google Sheets than in Excel. This is because Google Sheets has added a new feature to assign background colors to items while creating a dropdown list (this was earlier accomplished using conditional formatting, just like in Excel).
Let us see how to create the same dropdown list (as explained in the above section) in Google Sheets.
- Place your cursor in cell B2.
- Go to Data > Data Validation. The Data validation rules pane will open on the right side of the spreadsheet.
- Click on the Add rule button.
- Select the value Dropdown under Criteria. You will see 2 options. Rename Option 1 as ‘New’ and assign yellow color to the option using the Color dropdown.
- Rename Option 2 as ‘In Progress’ and assign blue color to the option.
- Click on the Add another item button twice to add 2 more list options.
- Rename the list items as ‘Done’ and ‘Not Done’ and change their background colors to green and red respectively.
- Click on the Done button to save the rule. You now have a color-coded dropdown list in cell B2.
- Take the mouse pointer to the bottom-right corner of the cell and as turns into a plus symbol, click and drag the cursor till cell B6. This will copy the data and data validation rule of cell B2 in cells B3 through B6.
This is how you can create a dropdown list with color-coded data in Google Sheets. I hope you find this post useful.
Read: How to connect Google Sheets with Microsoft Excel.
How to create a nested drop-down list in Google Sheets
It is almost the same as Excel, but the name of the option is different. You need to select the List from a range option from the Criteria list and enter a range according to your needs. You can enter a field like this-
=$A$1:$A$5
It will show all the texts from A1 to A5 cells in this drop-down list. As you have already guessed, you need to fill up all those selected cells first to show the options. By choosing this option, you are locking users to choose only from the selected cells.
Read: How to insert Multiple Blank Rows in Excel at once.
How to create Yes or No Dropdown list with color in Google Sheets?
Place the cursor on the cell where the drop-down list should appear. Select Data > Data validation. Click on the Add rule button in the right side. Select Dropdown in ‘Criteria’. Rename ‘Option 1’ as Yes. Rename ‘Option 2’ as No. Assign colors to the options to give them an eye-catching look. Click on the Done button.
Read: How to create a drop-down list in Excel with color.
Leave a Reply