colorize drop down list in excel

In this tutorial, we are going to learn methods that you can use to colorize the drop-down list in excel. We will do two sheets that are - Microsoft Excel Sheet and Google Sheet. We will also learn how you can remove the cells you've created or even change them. Without wasting no time, let us dive in.

Add color to the drop-down list in Excel

1. In this task, we are going to use two methods to solve it. First, create the cells that you want to add color to the drop-down list. When you are done, highlight the cells and head over to the Data tab and then select Data Validation, which is under the Data Tools section.

2. On Microsoft 2016, the Data validation heading might not be written under its icon but, it is represented by an icon click, on the drop-down arrow which is beside it. Which is under the Data Tools section.

data validation

3.  On the resulting page, on the Allow section choose List. Then, on the source box, put the range of cells you've created from the first up to the last one. For example, in my case, I put, =$A$2:$A$4. Then click OK.

4.  After that, please ensure that the cells that you are working on are still highlighted. Now head over to the Home tab and select Conditional Formatting which is under the Styles section.

5.  On the drop-down menu, click on Highlight cell rules, then select More rules.

colorize drop down list

6.  On the new dialog page, click on option two which is, Format only cells that contain and then on the Format only cells with a section on the first box, choose Specific Text. On the third box, fill in the first item on your list then click Format.

colorize drop down list

7.  On the new page, select the Fill tab and choose the color of your choice and specification. In my case, I chose red for 'Fail,' and yellow for 'Pass.' You have to choose the color range to differentiate the list on your cells this makes work easier. Click OK to close the page, also click OK to apply the chosen formatting.

colorize drop down list

8.  Repeat the same steps for the remaining cells until you have now the color formatting that you want. When you choose a color from the drop-down list it will automatically bring out the selected color.

add color

When done, remember to save your work.

Add color to the drop-down list on Google sheet

Google sheets have many useful features that, can be used in data entry and a drop-down list is one of these features. These features are such as coping a drop-down list and pasting it to another. You are also able to delete the drop-down list from the cells just at once.

Here also we are going to use Data Validation to colorize the drop-down list on a google sheet. A drop-down list can be created where the user needs to specify the data such as 'completed' and  'incomplete' task or project. This makes work easier for the user. Let's get started.

Add a drop-down list using a range of cells

1. Open your Google sheet app. Fill in your list of items that you want to work with. Select the range of cells you want to create a drop-down list to. Then, click the Data tab option.

add color

2.  Next, on the drop-down list click the Data  Validation option.

colorize drop down list

3.  In the new Data Validation dialog box, make sure the cell range is correct. Under the Criteria section, fill in List from a range. Next box, fill in the range of cells you want to add a drop-down list.

data validation dialog box

4.  On the 'Show dropdown list in cell' box is checked. Then click Save. Now, you have created a drop-down list in the cells that you selected. You can click the drop-down arrow, next to the items to show the options that you selected.

Add color to cells

1.  To color, your cells simply select the range of cells you need to color code.

2.  Click on the format tab on the menu and select Conditional Formatting. A new dialog page will appear on the right side of your screen. The cells that you have selected will appear on the Apply to section. Then on the Format cell if option allows choosing the condition in which selected cells to adopt a color.

3.  On the Formatting  Style, the section allows choosing the color you require and the save
You can also do the same for the color scale.

add color

Creating the drop-down list manually

Follow these simple steps, to create a dropdown list manually by simply specifying the items.

1. Again select the cell range that you want to add a drop-down list.
2. Select the Data option menu in the tabs. Then, click Data Validation.
3. On the new page, ensure that the correct data range is filled in.

4. On the Criteria section, select the option 'List of items.' Next, enter the items manually by a coma, For example, mine, I entered; 'More, Less.' Make sure the 'Show dropdown list in cell' is checked.

add color to drop down list

5. Click Save. 

Change or remove a drop-down list

If you want to change or remove a drop-down list, you can do so in these steps.
1. Select the cells that you want to remove.
2. Click the Data option.
3. Select the Data Validation option on the drop-down list.
4. On the Data Validation dialog box, ensure that the correct range of cells is selected.
5. Then down on the page click the Remove Validation option.

add color

6. Click Save.


Those were simple steps to follow when you want to colorize, the drop-down list in both Excel sheets and Google sheets. We hope that this tutorial was of much help to you.