You may save time and reduce errors when entering data into Google Sheets by using the drop-down list. You can restrict the options that can be selected in a cell by using a drop-down list. This is useful for columns that require uniform data entry (for example, those including nation names, product names, lead source identifiers, etc.). This article serves as a guide on how to create a dropdown in your Google Sheets. I also explain how you can make a multiple selection dropdown in your Google Sheets. Let’s dig in!
How to Create a Dropdown List in Google Sheets
Knowing how to create a dropdown list in your Google Sheets is really important. The following are steps to follow to create a dropdown list in Google Sheets:
#1. Verifying Data
You can restrict what users can type into a cell using Google Sheets’ data validation tool. In Google Sheets, a drop-down list can also be made with the help of data validation. That is to say:
- A drop-down menu can be made in any of the selected cells.
- Click Data Validation.
- Select List from a range from the Criteria menu.
- List items can be found in a certain range of cells, which can be specified in the Source box.
- Click Save.
Data validation can also be used to generate a select box from a comma-separated list of options. That is to say:
- A drop-down menu can be made in any of the selected cells.
- Validate your data by clicking Data.
- Select a list of things from the drop-down menu labeled Criteria.
- Separate each item in the list with a comma and type it into the Source field.
- Click Save.
#2. INDIRECT Function
Using Google Sheets’ INDIRECT function, you may compile a choice box from the data in a range of cells. That is to say:
- A drop-down menu can be made in any of the selected cells.
- Validate your data by clicking Data.
- For the criterion, select “Custom formula” from the selection.
- This formula should be entered in the Formula box: =INDIRECT(A1).
- List items can be found in a certain range of cells, which can be specified in the Source box.
- Click the Save option.
The INDIRECT function can be used to generate a drop-down menu with options that vary in appearance depending on the value in another cell. Using the INDIRECT function, you may make a drop-down list that displays either the list of nations or the list of states or provinces based on the contents of a cell on the same page, for instance.
How to Create a Dropdown List in Google Sheets with Preset Values
Follow the following steps to create a dropdown in your Google Sheets with preset values:
- Open your spreadsheet.
- Choose the cell (or cells) where you want the menu to appear.
- Select Data from the main menu. Choose Data validation after that.
- You can also add a dropdown by right-clicking the cell and choosing Dropdown.
- Dropdown choices should be entered as individual values and given their own colors in the Data validation rules panel. Select the Add another item button to keep adding to your list.
- Click Done.
Google Sheets will now only allow you to choose from the available alternatives in the dropdown list in certain cells, preventing you from entering any “invalid” data. So, this is why validating data is done.
How to Create a Dropdown List in Google Sheets with Existing Data
Let’s assume you’ve already populated your spreadsheet with the choices you intend to utilize for the dropdown. If you already have values in Google Sheets, here’s how to quickly make a dropdown in your Google Sheets:
- Choose the cells containing the information for your dropdown menu.
- A dropdown will appear as a menu option if you right-click the highlighted cells.
- The Data validation rules panel’s dropdown menu will immediately update with the values of the selected cells. Each choice has a default light grey tint by design.
- After making your selections, select the Done button.
Also Read: HOW TO EDIT A DROP DOWN LIST IN EXCEL: Easy Tricks & Tips
Build a Dropdown with Your Spreadsheet’s Information
Choose the cell or range of cells on your spreadsheet to insert the drop-down menu. To access the “Data validation rules” sidebar, select Insert > Dropdown on the toolbar above your spreadsheet. So, to access the drop-down menu, select Criteria. When the drop-down menu appears, choose Dropdown (from a range).
#1. To Manually Enter a Range of Cells
Under “Dropdown (from a range),” enter the range of cells that will be used to populate the dropdown menu. If you input A1:A10, for instance, the contents of cells A1 through A10 in your spreadsheet will be displayed as a drop-down menu.
#2. To Select a Range of Cells
Select “Dropdown (from a range)” and then click the grid symbol that appears in the entry field. Over your spreadsheet, a panel titled “Select a data range” will appear. If you also have your spreadsheet open, you can use this panel to navigate it. The letter-and-number combinations of the selected cells in the spreadsheet will show up in the panel’s input box.
Simply select the OK option. If you pick a range of cells in the “Data validation rules” sidebar, that range will display in the entry box underneath “Dropdown (from a range),” and the values in those cells will be used to populate the dropdown list.
#3. To Change the Dropdown’s Appearance
You can rearrange the list’s contents, colorize the list’s elements, and customize the dropdown indicator just like in the first dropdown type. Instructions are in the preceding part of the story.
#4. To insert Your Dropdown in the Spreadsheet
Select the Done button at the right end of the sidebar when you are creating the dropdown. A drop-down menu with the current data (numbers or words) from the range of cells you selected in the first step will now appear when the user clicks (or double-clicks) one of the cells you specified. The contents of the cell will also change to reflect the item selected.
If the range of cells you choose includes cells with formulas, the value currently displayed in a cell where the formula is being used will be included in the list of options from which to choose. Furthermore, words from the selected range of cells will be available as options. You can also pick a range of cells that includes not just numbers and text but even formulas. The drop-down will show you a list of whatever is in each cell in the range you picked right now.
Multiple Selections Dropdown in Google Sheets
Dropdown menus in cells are a breeze to design in Google Sheets. This is a fantastic method to add interactivity to your data and give people agency without giving them access to the raw data. The drop-down menu also has the drawback that you can only choose one option at a time.
But with a little Google Apps Script magic, you can get around it and make multiple selections from a dropdown menu in your Google Sheets.
Here’s how to select multiple selections dropdown list in Google Sheets:
Step 1
To begin, pick the cell that will house the drop-down menu.
Step 2
Access the Data validation selection from the Data menu. Specify the range of cells that will include the drop-down menu’s options by selecting Dropdown (from a range) as the criterion.
Step 3
A pull-down menu with your choices should now be available. However, you can only choose one choice from the drop-down menu.
Step 4
With some custom Apps Script code, we can make it so that users can choose numerous options from a drop-down menu. Select Extensions from the menu bar, and then click on the Apps Script submenu item.
Step 5
Adding your own bits of code to the Apps Script project is the next step. Copy and paste the following code into the Code.gs file:
function onEdit(e) {
var oldValue;
var newValue;
var ss=SpreadsheetApp.getActiveSpreadsheet();
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 4 && activeCell.getRow() == 1 && ss.getActiveSheet().getName()==”Sheet1″) {
newValue=e.value;
oldValue=e.oldValue;
if(!e.value) {
activeCell.setValue(“”);
else {
if (!e.oldValue) {
activeCell.setValue(newValue);
else {
activeCell.setValue(oldValue+’, ‘+newValue); }
Check that the code’s sixth line points to the cell containing the drop-down menu.
The code verifies the spreadsheet cell that is now selected.
Cell “D1” in column “4” and row “1” (D1) on the sheet “Sheet1” triggers the execution of the macro.
If a cell has no data, it will be deleted. The new value will also be attached to the existing one in the cell, separated by a comma if the cell already contains data.
Step 6
Click on the Save button in the top panel.
Step 7
You may also rename the current script.
Step 8
So, you can now make multiple selections in the dropdown list of your Google Sheets.
How to Use Your New Dropdown List
Using a Google Sheets dropdown list is not data science (though it can be useful in some data science applications). To fill in a cell with a selection from your list, click the arrow that appears next to the bubble’s shading.
Most of the time, you’ll want to select multiple cells in a row or column from the dropdown list. Either copy and paste the contents of the cell into other cells or drag the dropdown menu’s bottom-right corner to the cells you want to populate with the same list.
How to Change the Width of a Dropdown List in Google Sheets
When you create a dropdown in a Google Sheets cell, its width will automatically adjust to fit the cell. The Column Width setting also allows you to modify the column width of the list. That is to say:
- Choose the cells where you wish to modify the drop-down menu’s width.
- Choose the Column width format from the menu.
- Simply type your width requirements into the Column width field.
- Click Save.
How to Change the Number of Rows in a Dropdown List in Google Sheets
Follow the following steps:
- In Google Sheets, a drop-down list has 10 rows by default. The Row Height option allows you to modify the current number of rows. That is to say:
- Select the cells where you want to make the row count adjustment, and then make your selection.
- Click Format Row height.
- If you want more columns, change the number in the Row height box.
- A save button is available, so click it.
How to Change the Font Size of a Dropdown List in Google Sheets
The Font Size option in Google Sheets also allows you to modify the text size of a select box. That is to say:
- Choose the cells where you want to adjust the font size of the pull-down menu.
- Adjust the font size by selecting Format Font Size.
- Simply adjust the Font size slider to your preferred setting.
- Click Save.
How to Change the Background Color of a Dropdown List in Google Sheets
The Background Color option in Google Sheets also allows you to customize the look of drop-down lists. That is to say:
- You can customize the color of the drop-down menu by selecting the cells where it appears.
- Choose Background Format and then Color.
- Select the color you want by clicking on the color picker.
- Click Save.
Google Sheets: Changing the Border Color of a Dropdown List
Using Google Sheets’ Border Color option, you may customize the outline color of a selected box. That is to say:
- You can customize the drop-down menu’s border color by selecting the cells where it appears.
- Select the desired border color by clicking the Format button.
- Select the color you want by clicking on the color picker.
- Click Save.
Apply Conditional Formatting to a Dropdown
Dropdown menu elements can be assigned different colors to highlight their significance. Assigning background colors using conditional formatting rules is another option. This also allows you to display objects along a color scale, activate colors based on formula computations, and highlight items based on their status in the list.
#1. Assign color triggers to items in a dropdown
Numbers and ranges of numbers that may be calculated by a drop-down list formula can be given custom colors. Choose the cell or set of cells that includes the dropdown you wish to assign colors to. Then, select Format > Conditional formatting from the menu bar above your spreadsheet. Along the right-hand side, the “Conditional format rules” sidebar slides open.
- Look for the “Format Rules” section of the sidebar and select the “Is not empty” option. Choose “Greater than or equal to” from the drop-down menu that appears.
- The words “Value or Formula” appear in an input field. Enter a number here.
- Afterwards, in the space directly beneath where it says “Default,” select the paint bucket-shaped Fill color icon. The Color Picker window loads.
- Select Add another rule from the sidebar’s near-bottom section.
- Under “Greater than or equal to,” enter 20 this time. To use the yellow hue, go ahead and click the paint bucket symbol.
- The last step is to click the “Done” button.
If a formula in the range you’ve chosen for this dropdown produces a result of 90 or more when you make your selection, the background color of the cell will change to green. Selecting a number from a drop-down list that results in a value of 20 or higher (up to 89) causes the cell’s background color to change to yellow.
No need to stick to strict “Greater than or equal to” standards or even hard and fast numerical thresholds. If the cell has text, a date, or a number, you can use one of the additional rules that appear when you click Is not empty to change the background color of the cell.
#2. Assign a color scale to items in a dropdown
Each option in your drop-down menu can have its own unique background color. If the user chooses the number 100 from your drop-down list, for instance, you could make the cell background color green. Yellowing of the cell backdrop after 60 seconds. At the end of 10, the background of the cell changes to a fiery red. In addition, the background color will look like a grayscale version of the two colors if any of the numbers in your drop-down list fall in the middle of the two. This gives you a visual representation of the values of the options in a select box.
- Click Format > Conditional formatting to bring up the “Conditional format rules” menu after selecting the cell or cell range that houses the dropdown menu. Select the Color Scale tab on the right side of the sidebar. The “Color scale” panel will now be displayed in the sidebar.
- Then, select a new format rule by clicking the Min value link in the “Minpoint” section of the “Format rules” section.
- Just use the paint bucket over there. In the new window that opens, choose the color red from the palette.
- Also, choose Number from the “Midpoint” and “Maxpoint” dropdown menus and enter 60 and 100, respectively. Select the color yellow for the Midpoint and the color green for the Maxpoint by clicking on the corresponding paint can icons. You can see a preview of the entire color spectrum immediately above when you set these points and colors.
- Then, select the Done option.
The background color of the cell will now change to yellow when the number 60 is chosen in the drop-down menu. When the number 70 is chosen, a greenish hue is used as the backdrop. Choosing 100 causes the entire cell backdrop to be colored green.
#3. Manage Conditional Formatting Colors
To modify the dropdown list’s background color, pick the cell containing the list and go to Format > Conditional formatting to access the “Conditional format rules” submenu. You can also view all of the dropdown list color assignments you’ve made, each with its own swatch, on the sidebar.
- To get rid of a color: To delete a color, hover the mouse pointer over the swatch and then click the trash can icon that appears to the right of the swatch.
- To remove a color: To change the color, select the swatch. The “Single color” panel will appear in the sidebar if there is just one color. If it’s a color gradient, the “Color scale” panel appears on the right. To modify the color palette, select the paint bucket icons on each side.
- To change a color: Click Add another rule. The “Single Color” window will open in the sidebar. To access this panel, select Scale Color in the panel’s upper right corner and use it to apply a color scale to the items in your dropdown menu.
How to Edit or Delete a Dropdown
To access a drop-down menu, select (or double-click) a cell containing one. To edit the settings, use the pencil icon in the right-hand corner of the new menu. You can also modify the dropdown, including its contents and formatting, in the new “Data validation rules” sidebar that shows up. If you no longer need the drop-down menu, you may delete it by selecting the Remove rule at the foot of the sidebar.
However, if your spreadsheet contains many dropdown menus, you may view them all in the “Data validation rules” column. You can verify your spreadsheet’s data by selecting Data > Data validation from the menu bar. To make changes to a dropdown menu or to remove it entirely from your spreadsheet, simply click the menu’s name in the opened sidebar and then either the pencil icon or the trash can.
What Is a Dropdown in Spread Sheets?
Google Sheets’ drop-down list function allows you to compile a selection menu.
What Is the Use of Dropdown?
The following are the uses of Dropdown:
- People can enter data faster and more precisely when a drop-down list is used to restrict what can be typed into a cell.
- The user can make a selection from the drop-down menu by selecting a cell and then clicking the down arrow.
- Spreadsheets with built-in drop-down lists are great for maintaining data integrity.
What Is Data Validation in Google Sheets?
Google Sheets’ Data Validation feature allows you to restrict the types of data that can be entered into a cell or a range of cells.
How Does Data Validation Work?
Follow the following steps to see how Data Validation works:
- Data validation rules allow you to restrict the information that can be entered into a form cell.
- In order to guarantee the accuracy of your form data, you can establish one or more rules.
- Each worksheet column in which a user’s input must be restricted requires its own unique data validation rule.
Final Thoughts
This guide is here to guide you through how to create dropdown list in your Google Sheets. And I do hope you find it helpful.
- HOW TO CREATE A RULE IN OUTLOOK: EASY Guide
- THE 7 BEST SMALL BUSINESS CELL PHONE PLANS OF 2023
- THE ULTIMATE GUIDE FOR TEXTEDIT ON MAC
- HOW TO MERGE CELLS IN GOOGLE SHEETS: Detailed Guide
- How to Set Print Area in Google Sheets: Step-By-Step Guide
- HOW TO PRINT ON ENVELOPES: Step-By-Step Guide
References
