Color drop-down list in Excel

Contents
Create Drop-Down List
Edit Drop-Down List
Conditional formatting of cells
Color the list
Possible problem
Related Articles

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

preview of the drop-down list www.aredel.com
Preview
www.aredel.com

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

preview of the drop-down list www.aredel.com
Preview
www.aredel.com

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.

selecting a name for the drop-down list www.aredel.com
selecting a name for the drop-down list
www.aredel.com

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 data validation icon www.aredel.com

selecting a name for the drop-down list www.aredel.com
selecting a name for the drop-down list
www.aredel.com

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

specifying the source for the drop-down list www.aredel.com
specifying the source for the drop-down list
www.aredel.com

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

specifying the source for the drop-down list www.aredel.com
specifying the source for the drop-down list
www.aredel.com

7. Click OK

specifying the source for the drop-down list www.aredel.com
specifying the source for the drop-down list
www.aredel.com

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

drop-down list image www.aredel.com
drop-down list image
www.aredel.com

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.

add item drop-down list www.aredel.com
add item drop-down list
www.aredel.com

On the Formulas tab, click Name Manager.

add item drop-down list www.aredel.com
add item drop-down list
www.aredel.com

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

add item drop-down list www.aredel.com
add item drop-down list
www.aredel.com

Changing the range. I replaced 7 with 8.

add item drop-down list www.aredel.com
add item drop-down list
www.aredel.com

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:

drop-down list ranges www.aredel.com
drop-down list ranges
www.aredel.com

The list will look something like this:

drop-down list ranges www.aredel.com
drop-down list ranges
www.aredel.com

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)

drop-down list image www.aredel.com
drop-down list
www.aredel.com

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

formatting the drop-down list item www.aredel.com
formatting the drop-down list item
www.aredel.com

I want the OK option to have a green background.

formatting the drop-down list item www.aredel.com
formatting the drop-down list item
www.aredel.com

Check

green background of the drop-down list www.aredel.com
green background of the drop-down list
www.aredel.com

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

Conditional Formattiong → Manage Rules

add item drop-down list www.aredel.com
add item drop-down list
www.aredel.com
editing a rule for the site image drop-down list www.aredel.com

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

editing a rule www.aredel.com
editing a rule
www.aredel.com

We check the created list of rules.

editing a rule drop-down list www.aredel.com
editing a rule
www.aredel.com

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

editing a rule drop-down list www.aredel.com
editing a rule
www.aredel.com

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.

Related Articles
MS Excel
VBA
Чередование цвета в строках
Режим разработчика
Цветной выпадающий список
Сортировка чисел
Всё пропало
Перенос строки внутри ячейки Excel
Excel не фильтрует столбец

Search on this site

Subscribe to @aofeed channel for updates

Visit Channel

@aofeed

Feedbak and Questions in Telegram

@aofeedchat