See all How-To Articles

Add a Driblet Down List with Color Formatting in Excel & Google Sheets

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

DropDown Intro

To create a drib-down list where the background color depends on the text selected, first with Information Validation in Excel, and then use Conditional Formatting to amend the background color.

Create a Drop-Down List With Data Validation

DropDown List Data

To make a driblet-down listing from the information contained in a range of cells, commencement by selecting the prison cell where y'all want the drop down to announced.

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

DropDown DV Ribbon

  1. Select List.

DropDown DV List

  1. Select the range of cells with items as the Source.

DropDown DV Source

  1. Click OK to run across the driblet-down list in the workbook.

DropDown DV Drop Down

Ameliorate 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.

DropDown CF Ribbon

  1. Select New Rule, and and so select Format only cells that contain.

DropDown CF NewRule

  1. Click on the Format… push to set the format. Select the Fill tab and select the color (in this case, cerise).

DropDown CF Format

  1. Click OK to return to the New Rule screen and so OK to show the Rules Manager.

DropDown CF Rule manager

  1. Select the drop-downwardly list and alter the value to Cherry-red to see the issue of the Provisional Formatting.

DropDown CF Red

  1. Create rules for Yellow, Green, and Blue by following the same steps. The Conditional Formatting Rules Director should end up having four rules all applying to the cell containing the drop-downwardly list (e.m., D2).

DropDown CF AllRules

Add a Drop-Down List With Color Formatting in Google Sheets

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

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

Dropdown CF GS Menu

  1. Make sure the Prison cell range is where the drib-downward list should become, so select the Criteria range (e.g., B3:B6).

DropDown CF GS DataValidation

  1. Tick Testify dropdown list in jail cell if it not already ticked and so click Save.

DropDown CF GS Combo

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

DropDown CV GS Rules

  1. Click Add another rule to add together the rest of the Provisional Format rules to the drop-down list.

DropDown LC GS AddRule

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

DropDown CF GS Final