Home > Format > ConditionalFormatting > Colour

Conditional Formatting - Show List and Colors

Select a colour name from a drop down list in Excel, and a cell fills with the selected colour. There are no macros, just data validation drop downs and conditional formatting rules.

select fill colour name from drop down list

Select Color to Fill Cell

On a Microsoft Excel data entry sheet, select a number of options from a drop down list. That number of options show up in the data entry area. Then, select a color for each item, from another drop down list, and a cell fills with that color. There are 2 sample files:

  1. fill the adjacent cell with the selected colour
  2. fill the same cell with the selected colour

select fill colour name from drop down list

Video: Select Color to Fill Cell

Watch this video to see the steps for creating this worksheet, with conditional formatting cell rules, and data validation. In this example, the adjacent cells fills with the selected color. The timeline, and the written steps, are below the video, and you can download both versions of the color fill example, in the download section.

Video Timeline

  • 0:00 Cell Color Demo
  • 0:27 Start the file set up
  • 1:22 Create drop down lists
  • 2:48 Type list of numbers
  • 3:36 Format the number cells
  • 3:41 Add conditional formatting to number cells
  • 7:09 Add conditional formatting to Sample cells
  • 8:59 Test the worksheet

Set Up the Workbook 

In the workbook, there is an Options sheet, with headings, and this sheet is where the drop downs and formatting will be added.

options worksheet

On the Lists sheet, there are 2 lists -- a list of 8 numbers and a list of three colors.

Note: You can add more colors in your workbook, like green colour, or orange. I kept this list short for the sample file.

numbers and colors lists

Name the Lists

To use these lists in data validation drop down lists, create a named range for each list.

To name the numbers list:

  1. On the Lists sheet, select all the numbers
  2. Click in the Name box, and type a one-word name -- NumList
  3. Press Enter, to complete the name

To name the colors list:

  1. On the Lists sheet, select all the colors
  2. Click in the Name box, and type a one-word name -- ColorList
  3. Press Enter, to complete the name. go to top

naming the lists

Create the Options Drop Down

On the Options sheet, create a drop down list of numbers, so people can select a specific number of Options..

You can use commands on the Excel Ribbon's Home tab to add the formatting, or press Ctrl + 1 keyboard shortcut, to open the Format Cells dialog box.

  1. Select cell C2, where the list will appear.
  2. On the Ribbon's Data tab, click Data Validation
  3. For Allow, select List
  4. Click in the Source box, and press the F3 key, to open the Paste Name window
  5. Select NumList, and click OK, twice.

select NumList name

A drop down list of numbers now appears in the Number of Options cell. go to top

drop down list of numbers

Format the cell

Next, format the cell, to make it clear that the cell is for data entry.

  1. Select cell C2
  2. Format the cell with a light blue fill color
  3. Add an Outside border to the cell.
  4. Center the text in the cell.

format the cell

Create Color Drop Downs

Next, set up the 8 cells with drop down lists of colors.

  1. Select cells C5:C12
  2. On the Ribbon's Data tab, click Data Validation
  3. For Allow, select List
  4. Click in the Source box, and press the F3 key, to open the Paste Name window
  5. Select ColorList, and click OK, twice.

create color drop downs

A drop down list of colors now appears in each of the Color cells.go to top

select from color drop downs

Create List of Option Numbers

Next, create a list of numbers on the Options sheet

  1. In cells B5:B12, type the numbers 1 through 8

Next, the option numbers will be formatted:

  • When the sheet is first opened if nothing is selected for the number of options, no Option numbers should appear.
  • When someone selects a number of options, those numbers should appear in black font, with an outline border.

To hide the numbers if nothing is selected for the number of options:

  1. Select cells B5:B12
  2. Change the font color to white
  3. Change the fill color to white

To show the applicable numbers, when a selection in made in cell C2 (number of options):

  1. Select cells B5:B12
  2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. In the formula box, type the following formula, which refers to the active cell -- B5:
  5. =B5<=$C$2

    In the formula, B5 is a relative reference, so it will adjust for each row in the list of numbers. $C$2 is an absolute reference, because each number cell should compare its value with cell C2-- it should not adjust in each row.

  6. Click the Format button
  7. On the Font tab, select Black as the font color
  8. On the Border tab, select an Outline border
  9. Click OK twice

format number cells

To test the formatting, change the number of options in cell C2.

format number cells

Format Color Drop Downs

Next add conditional formatting on the Color column, so the cell will have a blue fill and outline border if it's option number is visible.

To format the Color cells, when a selection in made in cell C2 (number of options):

  1. Select cells C5:C12
  2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. In the formula box, type the following formula, which refers to the Option Number cell (B5) for the active cell -- C5:

    =B5<=$C$2

    In the formula, B5 is a relative reference, so it will adjust for each row in the list of numbers. $C$2 is an absolute reference, because each number cell should compare its value with cell C2-- it should not adjust in each row.

  5. Click the Format button
  6. On the Fill tab, select light blue as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

    format color cells

To test the formatting, click the drop down arrow in a cell, and change a color selection in column C. The adjacent cell in column should show the current selection color.go to top

formatted color cells

Format the Sample Cells

Next, you'll apply conditional formatting in the sample column, to show specific colors, based on the adjacent cell values. You will set up a separate rule for each color - Red, Blue and Yellow.

Rule for Red

To format the Sample cells, when red is selected in column C:

  1. Select the range of cells, D5:D12
  2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. In the formula box, type the following formula, which refers to the Color cell (C5) for the active cell -- D5, and checks if the value in C5 is equal to the text string, "red":

    =C5="red"

    In the formula, C5 is a relative reference (no dollar sign to lock the row or column), so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select red color as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

    formatted sample cells

Rule for Blue

To format the Sample cells, when blue is selected in column C:

  1. Select cells D5:D12
  2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. In the formula box, type the following formula, which refers to the Color cell (C5) for the active cell -- D5:

    =C5="blue"

    In the formula, C5 is a relative reference, so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select blue as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

Rule for Yellow

To format the Sample cells, when yellow is selected in column C:

  1. Select cells D5:D12
  2. On the Ribbon's Home tab, click Conditional Formatting, New Rule
  3. Click Use a Formula to Determine Which Cells to Format
  4. In the formula box, type the following formula, which refers to the Color cell (C5) for the active cell -- D5:

    =C5="yellow"

    In the formula, C5 is a relative reference, so it will adjust for each row in the list of numbers.

  5. Click the Format button
  6. On the Fill tab, select yellow as the fill color
  7. On the Border tab, select an Outline border
  8. Click OK twice

To test the formatting, change a color selection in column C, and the adjacent cell in column should show the selected color.go to top

change a color selection

Get the Sample Files

  1. Colour Adjacent Cell: Get the sample file from the video, to add fill color in the adjacent cell to the right of the drop down color list. The zipped file is in xlsx format, and does not contain macros.
  2. Colour Same Cell: Get the sample file to add fill color in the same cell as the drop down color list. The zipped file is in xlsx format, and does not contain macros.

More Tutorials

Conditional Formatting Introduction

Conditional Formatting Based on another cell

Conditional Formatting Examples

Conditional Formatting Documentation

Conditional Formatting Data Bars

 

 

Last updated: May 30, 2024 3:11 PM