Google Sheets enables you to design spreadsheets with refined options, and some of the helpful to know is dropdown lists. You possibly can add a dropdown record to a cell (or to a variety of cells), and if you or one other particular person with entry to your spreadsheet clicks the cell, a dropdown will open that exhibits a listing of numbers or phrases that they will choose. The quantity or phrase that’s chosen will then seem contained in the cell.
Some use case examples:
- You want co-workers to enter very particular numbers or phrases into your spreadsheet. Offering dropdown lists makes this extra handy for them and eliminates the chance of mistyped entries.
- You add a dropdown record containing quantity presets that instantly change a chart embedded in your spreadsheet.
- You create a spreadsheet to trace a mission, during which co-workers choose their work progress standing from a dropdown record.
You possibly can create two sorts of dropdown lists in Google Sheets: The primary lists particular numbers or phrases that you simply’ve entered as preset decisions, whereas the second lists information that presently seems in a variety of cells in your spreadsheet. This information walks you thru the essential steps of making each forms of dropdowns and including colour to them.
[ New to Google Sheets? See our Google Sheets cheat sheet ]
Create a dropdown that lists particular numbers or phrases
Choose the cell or the vary of cells the place you need the dropdown to be in your spreadsheet. Then on the toolbar above your spreadsheet, click on Knowledge > Knowledge validation.
On the “Knowledge validation” panel that opens, click on Record from a variety subsequent to “Standards.” From the menu that opens, choose Record of things.
IDG
Sort within the objects you need to seem within the dropdown record, separated by commas. (Click on picture to enlarge it.)
Now, within the field to the correct of “Record of things,” sort within the numbers or phrases that you really want your dropdown record to have. You’ll want to separate every quantity or phrase with a comma – however don’t sort in an area between the numbers or phrases.
Once you create a dropdown record, the cell that it’s in will likely be marked with a small down arrow. For those who don’t need this arrow to seem within the cell, uncheck Present dropdown record in cell.
Once you’re completed, click on Save on the decrease proper of the panel.
Now if you double-click this cell (or click on the down arrow inside it), a dropdown record will open that exhibits the numbers or phrases that you simply typed in above. When you choose one in every of these numbers or phrases, it is going to then seem contained in the cell.
IDG
Within the spreadsheet, click on the down arrow in a cell with a dropdown record to see the out there choices (left); the merchandise you choose will seem within the cell (proper). (Click on picture to enlarge it.)
Create a dropdown that lists information out of your spreadsheet
Choose the cell or the vary of cells the place you need the dropdown to be in your spreadsheet. Then on the toolbar above your spreadsheet, click on Knowledge > Knowledge validation.
On the “Knowledge validation” panel that opens, depart the “Standards” dropdown set to Record from a variety. Within the field to its proper, sort within the vary of cells that you simply need to seem within the dropdown record. For instance: For those who sort in A1:A10, the info in cells A1 to A10 of your spreadsheet will seem as ten objects within the dropdown record.
For those who click on the grid icon that’s inside this entry field, a small panel (“Choose a variety”) will open over your spreadsheet. With this panel open, you may scroll via your spreadsheet. Once you click on to pick a variety of cells, their letter-number designations will seem on this panel’s entry field. Click on OK and your chosen vary of cells will seem within the entry field to the correct of “Record from a variety.”
IDG
Choose the vary of cells whose information you need to seem within the dropdown record. (Click on picture to enlarge it.)
Once you’re completed designating a variety of cells, click on Save on the decrease proper of the “Knowledge validation” panel.
Now if you double-click this cell (or click on the down arrow inside it), a listing will open that exhibits the present information within the vary of cells that you simply chosen. When you choose one in every of these numbers or phrases, it is going to then seem contained in the cell.
IDG
The dropdown exhibits a listing of things which can be presently within the vary of cells you chose. (Click on picture to enlarge it.)
If the vary of cells you chose incorporates formulation, the present quantity showing in a cell that’s calculated by a components will seem within the dropdown record. If the vary of cells you chose incorporates phrases, these phrases will seem within the dropdown record. You possibly can even choose a variety of cells that’s a mixture of numbers, phrases, and formulation. The dropdown will record no matter is presently in every cell within the vary of cells that you simply chosen.
Edit or take away a dropdown
Choose the cell (or cells) that incorporates the dropdown record. Then, from the toolbar above your spreadsheet, click on Knowledge > Knowledge validation.
On the “Knowledge validation” panel, you may edit the record of numbers or phrases to the correct of “Record of things” or change the vary of supply cells subsequent to “Record from a variety.”
Or, to take away the dropdown record from the cell or cells, click on Take away validation on the backside of the panel.
Add colours to a dropdown
You’ve created your dropdown record. Now you may assign a colour to every merchandise on the record in order that when you choose one of many objects, the textual content or background of the cell it’s added to turns that colour. This will help make your spreadsheet extra visually fascinating, signify the significance of an merchandise on the record, or emphasize the place the merchandise falls in a variety.
Assign background colours to objects in your dropdown
Click on to pick the cell that incorporates the dropdown record. Then, on the toolbar above your spreadsheet, click on Format > Conditional formatting. A sidebar (“Conditional format guidelines”) will open alongside the correct.
IDG
Use the “Conditional format guidelines” sidebar to assign colours to your dropdown record. (Click on picture to enlarge it.)
Within the sidebar below the “Format guidelines” header, click on the field with the phrases Just isn’t empty. From the dropdown record that opens, choose Textual content is precisely.
An entry field with the phrases “Worth or System” seems. Sort in your entire quantity or phrase out of your dropdown record that you simply need to assign a background colour.
Subsequent, under the bar labeled “Default,” click on the Fill colour icon (a paint can). A colour choice panel will open. Click on to pick a colour.
IDG
Designate the formatting guidelines, textual content, and fill colour for an merchandise in your dropdown. (Click on picture to enlarge it.)
Lastly, click on the Completed button.
Now when you choose this merchandise from the dropdown record, the background colour of this cell will change to the one that you simply had chosen above. When you choose one other merchandise on this record, the background colour will return to the default colour.
IDG
If you choose Pending from the dropdown within the instance spreadsheet, its background will likely be yellow. (Click on picture to enlarge it.)
To assign a background colour for the opposite objects on this cell’s dropdown record, click on Add one other rule within the “Conditional format guidelines” sidebar, then repeat the above steps.
IDG
Now all three dropdown menu decisions have an applicable background colour. (Click on picture to enlarge it.)
When you grasp how the above works, you may assign totally different colours to particular numbers that will present up when a components in your dropdown record calculates them. For instance, if the components for an merchandise in your dropdown record calculates the quantity 90, then the cell background colour might develop into inexperienced when this merchandise is chosen from the dropdown record. If the quantity turns into 20, then the cell background colour might develop into crimson.
To do that: Following the steps above, sort in 90 after which assign the colour inexperienced to it. Then, within the “Conditional format guidelines” panel, click on Add one other rule. Comply with the steps above once more, sort in 20, and assign it the colour crimson.
Assign textual content colours to objects in your dropdown
You possibly can assign the objects in your dropdown record textual content colours as a substitute of or as well as background colours. Comply with the steps above, and within the “Formatting model” space of the “Conditional format guidelines” sidebar, click on the Textual content colour icon (a capital “A”) and select a colour. Click on the Fill colour icon and select a background colour or click on None.
Assign a colour scale to objects in your dropdown
You possibly can assign a variety of background colours to the objects in your dropdown record. For instance, you might set it in order that if the consumer selects 100 out of your dropdown record, the cell background colour turns inexperienced. For 60, the cell background turns yellow. For 10, the cell background turns crimson. And for any numbers in your dropdown record that fall between two of those three, the background will seem as an intermediate shade between the 2 colours.
As an example this, let’s create a dropdown record that incorporates ten numbers (10, 20, 30, and many others.) that may be chosen.
Click on to pick the cell that incorporates the dropdown record. Then on the toolbar above your spreadsheet, click on Format > Conditional formatting to open the “Conditional format guidelines” sidebar alongside the correct.
On this sidebar, click on the Coloration scale tab on the higher proper. The sidebar will change to the “Coloration scale” panel.
Subsequent, below the “Format guidelines” heading, click on Minpoint to open its dropdown menu. For our instance, choose Quantity. Sort 10 In within the entry field to the correct.
IDG
To arrange a colour vary, assign a Minpoint, Midpoint, and Maxpoint worth and colour. (Click on picture to enlarge it.)
Click on the paint can icon to the correct. From the colour choice panel that opens, click on to pick the colour crimson.
Choose Quantity from the dropdown lists for Midpoint and Maxpoint, too, and sort in 60 and 100 respectively. Click on their paint can icons, and choose the colour yellow for Midpoint and inexperienced for Maxpoint. As you set these factors and colours, you’ll see a preview of the entire colour vary simply above.
Click on the Completed button.
Now when the quantity 60 is chosen from the dropdown record, the cell’s background colour turns to yellow. When you choose 70, the background colour turns to a yellow that has a tinge of inexperienced combined in. When you choose 100, the cell’s background colour will likely be totally inexperienced.
IDG
Utilizing a colour scale supplies a visible indicator for the values of things in a dropdown record. (Click on picture to enlarge it.)
Handle the colours of things in your dropdown
If you wish to change the textual content or background colours you’ve assigned to the objects in a dropdown record, click on to pick the cell that incorporates the dropdown record, then click on Format > Conditional formatting to open the “Conditional format guidelines” sidebar. Within the sidebar you’ll see a listing of the colour assignments you’ve made for the dropdown record, every with its personal colour swatch.
Take away a colour: Transfer the pointer over the colour swatch and click on the garbage can icon that seems to the correct of the swatch.
IDG
Click on the garbage can to take away a colour project.
Change a colour: Click on the colour swatch. If it’s a single colour, the sidebar will change to the “Single colour” panel. If it’s a variety of colours, the sidebar will change to the “Scale colour” panel. Click on the paint can icons on both panel to alter colours.
Add a brand new colour: Click on Add one other rule. The sidebar will change to the “Single colour” panel. If you wish to assign a variety of colours to the objects in your dropdown record, click on Scale colour on the higher proper to modify to this panel.
