Color drop-down list in Excel

In this article, you will learn how to create a drop-down list in Excell and color it.

In the first part, I talk about creating a drop-down list, but if you are already familiar with this you can immediately go to the second one, which explains how to add a color.

If you need a nice drop-down list, first create a separate sheet (Sheet) and in the column write down all the options that you want to see in this list.

Write in the order you want to see in the list. You don't have to color it yet. The actual color is set at a different stage.

1. Write down the options you need in the drop-down list in the column

2. Select all that you have written and right-click & rarr; Select the command Assign a name (Define Name).

3. In the Name field, enter a name for the records, such as Results (Results), and click OK.

Make sure that the name does not contain spaces. This name will not appear in the list, but you must enter it, to link to a drop-down list.

4. Click the cell on the sheet where you want to place the drop-down list.

Go to the Data section and click Data Validation.

If your version of Excel doesn't have the Data Validation label, look for the icon

5. On the tabParameters (Settings) in the field Allow (Allow) click List (List).

6. In the fieldSource (Source) enter the equal sign
( = ), followed immediately by the name assigned to the list in step 3. for Example: =Results.

7. Click OK

8. A list will appear in the selected cell. So far, black and white.

Editing the drop-down list

Change the drop-down list based on the named range.

Open the sheet that contains the named range for the drop-down list.

My list is already colored, but it doesn't affect the addition of a new item.

To add an item, go to the end of the list and enter a new item.

On the Formulas tab, click Name Manager.

In the Name Manager field, select a named range, which needs to be updated.

Changing the range. I replaced 7 with 8.

Then add the color if necessary.

To view a list of all available ranges, press CTRL & plus; G or from the Home tab, go to in Find & Select and click Go to:

The list will look something like this:

Adding color to drop-down cells

To make the list colored, select a cell. Go to Home → Conditional formatting (Conditional Formatting) & rarr; New rule (New Rule)

Select Format only cells that contain → Change the condition to equal to Enter the first item from our list in this case OK → Format

I want the OK option to have a green background.

Check

To color all the elements in the drop-down list, go to edit the rule.

Conditional Formattiong → Manage Rules

Create a new rule for each item in the list. And so on until you create all the necessary rules. I created seven rules.

We check the created list of rules.

After one cell is ready, you can copy it to any place in the document and use the color list at your pleasure.

Possible problems

When you move a sheet from one file to another, the name that the cell refers to can be to not be available. Check your cells using Data Validation and change the name to the current one, or create a new one.