Google Sheets helps you to design spreadsheets with refined 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 mission, wherein 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 selections, whereas the second lists knowledge that at present seems in a spread of cells in your spreadsheet. This information walks you thru the essential steps of making each varieties of dropdowns and including shade to them.Create a dropdown that lists particular numbers or phrasesSelect 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 gadgets you need to seem within the dropdown checklist, separated by commas. (Click picture to enlarge it.)
Now, within the field to the suitable of “List of items,” kind 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 kind in an area between the numbers or phrases. When you create a dropdown checklist, the cell that it’s in will likely be marked with a small down arrow. If you don’t need this arrow to seem 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 out there choices (left); the merchandise you choose will seem within the cell (proper). (Click picture to enlarge it.)
Create a dropdown that lists knowledge out of your spreadsheetSelect 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, kind within the vary of cells that you just need to seem within the dropdown checklist. For instance: If you kind in A1:A10, the information in cells A1 to A10 of your spreadsheet will seem as ten gadgets 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 means 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 suitable of “List from a range.” IDG
Select the vary of cells whose knowledge you need 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 knowledge 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 can be at present 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 at present in every cell within the vary of cells that you just chosen.Edit or take away a dropdownSelect 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 suitable 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 dropdownYou’ve created your dropdown checklist. Now you may assign a shade to every merchandise on the checklist in order that when you choose one of many gadgets, the textual content or background of the cell it’s added to turns that shade. This can assist make your spreadsheet extra visually fascinating, signify the significance of an merchandise on the checklist, or emphasize the place the merchandise falls in a spread.Assign background colours to gadgets in your dropdownClick 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 suitable. IDG
Use the “Conditional format rules” sidebar to assign colours to your dropdown checklist. (Click picture to enlarge it.)
In the sidebar underneath 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 your entire quantity or phrase out of your dropdown checklist that you just need to assign a background shade.Next, beneath the bar labeled “Default,” click on the Fill shade icon (a paint can). A shade choice panel will open. Click to pick a shade. IDG
Designate the formatting guidelines, textual content, and fill shade 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 shade 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 shade will return to the default shade. IDG
If you choose Pending from the dropdown within the instance spreadsheet, its background will likely be yellow. (Click picture to enlarge it.)
To assign a background shade for the opposite gadgets 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 selections have an acceptable background shade. (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 shade may grow to be inexperienced when this merchandise is chosen from the dropdown checklist. If the quantity turns into 20, then the cell background shade may grow to be purple.To do that: Following the steps above, kind 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, kind in 20, and assign it the colour purple.Assign textual content colours to gadgets in your dropdownYou can assign the gadgets 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 shade icon (a capital “A”) and select a shade. Click the Fill shade icon and select a background shade or click on None.Assign a shade scale to gadgets in your dropdownYou can assign a spread of background colours to the gadgets in your dropdown checklist. For instance, you possibly can set it in order that if the consumer selects 100 out of your dropdown checklist, the cell background shade 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 on.) 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 suitable.On this sidebar, click on the Color scale tab on the higher proper. The sidebar will change to the “Color scale” panel.Next, underneath 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 suitable. IDG
To arrange a shade vary, assign a Minpoint, Midpoint, and Maxpoint worth and shade. (Click picture to enlarge it.)
Click the paint can icon to the suitable. 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 shade vary simply above.Click the Done button.Now when the quantity 60 is chosen from the dropdown checklist, the cell’s background shade turns to yellow. When you choose 70, the background shade turns to a yellow that has a tinge of inexperienced blended in. When you choose 100, the cell’s background shade will likely be totally inexperienced. IDG
Using a shade 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 dropdownIf you need to change the textual content or background colours you’ve assigned to the gadgets 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 shade swatch.Remove a shade: Move the pointer over the colour swatch and click on the garbage can icon that seems to the suitable of the swatch. IDG
Click the garbage can to take away a shade task.
Change a shade: Click the colour swatch. If it’s a single shade, 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 vary colours.Add a brand new shade: Click Add one other rule. The sidebar will change to the “Single color” panel. If you need to assign a spread of colours to the gadgets in your dropdown checklist, click on Scale shade on the higher proper to modify to this panel.Read this subsequent: Google Sheets energy suggestions: How to make use of filters and slicers
Copyright © 2022 IDG Communications, Inc.