Google Sheets energy ideas: How to make use of dropdown lists
Embedding dropdown lists in a spreadsheet saves time and ensures accuracy. Here’s the way to profit from them in Google Sheets.
Tonktiti / Shutterstock
Google Sheets enables you to design spreadsheets with subtle options, and one of the vital helpful to know is dropdown lists. You can add a dropdown checklist to a cell (or to a spread of cells), and if you or one other individual with entry to your spreadsheet clicks the cell, a dropdown will open that reveals 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. Providing dropdown lists makes this extra handy for them and eliminates the chance of mistyped entries.
- You add a dropdown checklist containing quantity presets that instantly change a chart embedded in your spreadsheet.
- You create a spreadsheet to trace a venture, during which co-workers choose their work progress standing from a dropdown checklist.
You can create two sorts of dropdown lists in Google Sheets: The first lists particular numbers or phrases that you just’ve entered as preset decisions, whereas the second lists information that presently seems in a spread of cells in your spreadsheet. This information walks you thru the essential steps of making each sorts of dropdowns and including coloration to them.
[ New to Google Sheets? See our Google Sheets cheat sheet ]
Create a dropdown that lists particular numbers or phrases
Select 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 Data > Data validation.
On the “Data validation” panel that opens, click on List from a spread subsequent to “Criteria.” From the menu that opens, choose List of things.
IDG
Type within the objects you wish to seem within the dropdown checklist, separated by commas. (Click picture to enlarge it.)
Now, within the field to the precise of “List of items,” sort within the numbers or phrases that you really want your dropdown checklist to have. Be positive to separate every quantity or phrase with a comma – however don’t sort in an area between the numbers or phrases.
When you create a dropdown checklist, the cell that it’s in might be marked with a small down arrow. If you don’t need this arrow to look within the cell, uncheck Show dropdown checklist in cell.
When 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 checklist will open that reveals the numbers or phrases that you just typed in above. When you choose considered one of these numbers or phrases, it’ll then seem contained in the cell.
IDG
In the spreadsheet, click on the down arrow in a cell with a dropdown checklist to see the accessible choices (left); the merchandise you choose will seem within the cell (proper). (Click picture to enlarge it.)
Create a dropdown that lists information out of your spreadsheet
Select 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 Data > Data validation.
On the “Data validation” panel that opens, go away the “Criteria” dropdown set to List from a spread. In the field to its proper, sort within the vary of cells that you just wish to seem within the dropdown checklist. For instance: If you sort in A1:A10, the info in cells A1 to A10 of your spreadsheet will seem as ten objects within the dropdown checklist.
If you click on the grid icon that’s inside this entry field, a small panel (“Select a range”) will open over your spreadsheet. With this panel open, you may scroll by way of your spreadsheet. When you click on to pick a spread of cells, their letter-number designations will seem on this panel’s entry field. Click OK and your chosen vary of cells will seem within the entry field to the precise of “List from a range.”
IDG
Select the vary of cells whose information you wish to seem within the dropdown checklist. (Click picture to enlarge it.)
When you’re completed designating a spread of cells, click on Save on the decrease proper of the “Data validation” panel.
Now if you double-click this cell (or click on the down arrow inside it), a listing will open that reveals the present information within the vary of cells that you just chosen. When you choose considered one of these numbers or phrases, it’ll then seem contained in the cell.
IDG
The dropdown reveals a listing of things which are presently within the vary of cells you chose. (Click 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 checklist. If the vary of cells you chose incorporates phrases, these phrases will seem within the dropdown checklist. You may even choose a spread of cells that’s a mixture of numbers, phrases, and formulation. The dropdown will checklist no matter is presently in every cell within the vary of cells that you just chosen.
Edit or take away a dropdown
Select the cell (or cells) that incorporates the dropdown checklist. Then, from the toolbar above your spreadsheet, click on Data > Data validation.
On the “Data validation” panel, you may edit the checklist of numbers or phrases to the precise of “List of items” or change the vary of supply cells subsequent to “List from a range.”
Or, to take away the dropdown checklist from the cell or cells, click on Remove validation on the backside of the panel.
Add colours to a dropdown
You’ve created your dropdown checklist. Now you may assign a coloration to every merchandise on the checklist in order that when you choose one of many objects, the textual content or background of the cell it’s added to turns that coloration. This might help make your spreadsheet extra visually attention-grabbing, signify the significance of an merchandise on the checklist, or emphasize the place the merchandise falls in a spread.
Assign background colours to objects in your dropdown
Click to pick the cell that incorporates the dropdown checklist. Then, on the toolbar above your spreadsheet, click on Format > Conditional formatting. A sidebar (“Conditional format rules”) will open alongside the precise.
IDG
Use the “Conditional format rules” sidebar to assign colours to your dropdown checklist. (Click picture to enlarge it.)
In the sidebar below the “Format rules” header, click on the field with the phrases Is not empty. From the dropdown checklist that opens, choose Text is strictly.
An entry field with the phrases “Value or Formula” seems. Type in all the quantity or phrase out of your dropdown checklist that you just wish to assign a background coloration.
Next, beneath the bar labeled “Default,” click on the Fill coloration icon (a paint can). A coloration choice panel will open. Click to pick a coloration.
IDG
Designate the formatting guidelines, textual content, and fill coloration for an merchandise in your dropdown. (Click picture to enlarge it.)
Finally, click on the Done button.
Now when you choose this merchandise from the dropdown checklist, the background coloration of this cell will change to the one that you just had chosen above. When you choose one other merchandise on this checklist, the background coloration will return to the default coloration.
IDG
If you choose Pending from the dropdown within the instance spreadsheet, its background might be yellow. (Click picture to enlarge it.)
To assign a background coloration for the opposite objects on this cell’s dropdown checklist, click on Add one other rule within the “Conditional format rules” sidebar, then repeat the above steps.
IDG
Now all three dropdown menu decisions have an acceptable background coloration. (Click picture to enlarge it.)
Once 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 checklist calculates them. For instance, if the components for an merchandise in your dropdown checklist calculates the quantity 90, then the cell background coloration may change into inexperienced when this merchandise is chosen from the dropdown checklist. If the quantity turns into 20, then the cell background coloration may change into purple.
To do that: Following the steps above, sort in 90 after which assign the colour inexperienced to it. Then, within the “Conditional format rules” panel, click on Add one other rule. Follow the steps above once more, sort in 20, and assign it the colour purple.
Assign textual content colours to objects in your dropdown
You can assign the objects in your dropdown checklist textual content colours as a substitute of or as well as background colours. Follow the steps above, and within the “Formatting style” space of the “Conditional format rules” sidebar, click on the Text coloration icon (a capital “A”) and select a coloration. Click the Fill coloration icon and select a background coloration or click on None.
Assign a coloration scale to objects in your dropdown
You can assign a spread of background colours to the objects in your dropdown checklist. For instance, you might set it in order that if the consumer selects 100 out of your dropdown checklist, the cell background coloration turns inexperienced. For 60, the cell background turns yellow. For 10, the cell background turns purple. And for any numbers in your dropdown checklist that fall between two of those three, the background will seem as an intermediate shade between the 2 colours.
To illustrate this, let’s create a dropdown checklist that incorporates ten numbers (10, 20, 30, and so forth.) that may be chosen.
Click to pick the cell that incorporates the dropdown checklist. Then on the toolbar above your spreadsheet, click on Format > Conditional formatting to open the “Conditional format rules” sidebar alongside the precise.
On this sidebar, click on the Color scale tab on the higher proper. The sidebar will change to the “Color scale” panel.
Next, below the “Format rules” heading, click on Minpoint to open its dropdown menu. For our instance, choose Number. Type 10 In within the entry field to the precise.
IDG
To arrange a coloration vary, assign a Minpoint, Midpoint, and Maxpoint worth and coloration. (Click picture to enlarge it.)
Click the paint can icon to the precise. From the colour choice panel that opens, click on to pick the colour purple.
Select Number from the dropdown lists for Midpoint and Maxpoint, too, and kind in 60 and 100 respectively. Click 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 coloration vary simply above.
Click the Done button.
Now when the quantity 60 is chosen from the dropdown checklist, the cell’s background coloration turns to yellow. When you choose 70, the background coloration turns to a yellow that has a tinge of inexperienced combined in. When you choose 100, the cell’s background coloration might be totally inexperienced.
IDG
Using a coloration scale supplies a visible indicator for the values of things in a dropdown checklist. (Click picture to enlarge it.)
Manage 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 checklist, click on to pick the cell that incorporates the dropdown checklist, then click on Format > Conditional formatting to open the “Conditional format rules” sidebar. In the sidebar you’ll see a listing of the colour assignments you’ve made for the dropdown checklist, every with its personal coloration swatch.
Remove a coloration: Move the pointer over the colour swatch and click on the garbage can icon that seems to the precise of the swatch.
IDG
Click the garbage can to take away a coloration task.
Change a coloration: Click the colour swatch. If it’s a single coloration, the sidebar will change to the “Single color” panel. If it’s a spread of colours, the sidebar will change to the “Scale color” panel. Click the paint can icons on both panel to alter colours.
Add a brand new coloration: Click Add one other rule. The sidebar will change to the “Single color” panel. If you wish to assign a spread of colours to the objects in your dropdown checklist, click on Scale coloration on the higher proper to modify to this panel.
Read this subsequent: Google Sheets energy ideas: How to make use of filters and slicers