how to add a drop down list in google sheets

This tutorial will demonstrate how to add a drop down list with color formatting in Excel and Google Sheets.

To create a drop down list where the background color depends on the text selected, first create a drop down list in Excel using Data Validation, then use Conditional Formatting to amend the background color.

Creating a Drop Down List With Data Validation

We want to make a drop down list from the data contained in our list.

First, we select the cell where we want the drop down to appear.

1. In the Ribbon, select Data > Data Tools > Data Validation.

2. Select List.

3. Select the range of cells required as the Source.

4. Click OK to see the drop down list in the workbook.

Amending the Background Color With Conditional Formatting

1. Select the cell that contains the drop down list, and then in the Ribbon, select Home > Styles > Conditional Formatting.

2. Select New Rule, and then select Format only cells that contain.

3. From the Format only cells with, select Specific Text containing and then select the first cell in the data range that we have for our drop down list (e.g., B3).

4. Click on the Format… button to set the format. Select the Fill tab and select the color we require; in this case, we want to set the fill to red.

5. Click OK to return to the New Rule screen and then OK to show the Rules Manager.

6. Select the drop down list and change the value to Red to see the result of the Conditional Formatting.

7. Create rules for Yellow, Green, and Blue by following the same steps. The Conditional Formatting Rules Manager should end up containing four rules all applying to the cell containing the drop down list (e.g., D2).

Add a Drop Down List With Color Formatting in Google Sheets

The process to add a drop down list with color formatting is much the same in Google Sheets as it is in Excel.

1. In the Menu, select Data > Data validation.

2. Make sure the Cell range is where the drop down list needs to be placed, and then select the Criteria range (e.g., B3:B6).

3. Tick Show dropdown list in cell if it not already ticked and then click Save.

4. With the cell that contains the drop down list selected, select Format > Conditional formatting from the Menu.

5. Select the cell to apply the formatting to in Apply to range (e.g., D2). Select Text contains from the Format rules and then refer to the cell containing the Red drop down value (e.g., B3) and then finally, select red as the Formatting style.

6. Click Done to Save the rule.

7. Click Add another rule to add the rest of the Conditional Format rules to the drop down list.

Select a different color from the drop down list to see the result.

how to add a drop down list in google sheets

Source: https://www.automateexcel.com/how-to/drop-down-list-color/

Posted by: noblesounded.blogspot.com

Share this post