How to autofill a cell with color in Excel

How to Fill Colors in a List in Excel

  • Small Business
  • |
  • Accounting & Bookkeeping
  • |
  • Excel
By Tricia Goss

Related

  • How to Make a Drop Down Box to Allow Multiple Selections in Excel
  • How to Add a Hyphen on Excel
  • How to Insert a Combo Box in Excel 2010
  • How to Add Shading in PowerPoint 2003
  • How to Insert Citations in Microsoft Word

With Microsoft Excel, you can simplify data entry by adding a drop-down list to a cell, allowing users to pick an option from the list instead of entering data. You can further tailor the spreadsheet by using conditional formatting to fill the cell with a specific color depending on which list item the user selects. The colors in the column can help users to quickly identify which list items are entered.

Create the List

1

Type the list entries you want to use in a single column in the order you want them to appear. Make sure there are no blanks between entries. You can create this list on any page in the current workbook.

2

Select the list of entries, and then right-click and choose "Define Name." Type a name for the list in the Name box, omitting any spaces between words, and then click "OK."

3

Select the cell where you want the list. Select the "Data" tab, click "Data Validation" in the Data Tools group, and then choose "Data Validation."

4

Select the "Settings" tab, choose "List" in the Allow box, type "=" followed by the list name in the source box, and then click "OK." The list is available in the selected cell.

Add Fill Colors

1

Select the cell containing the list. Click "Conditional Formatting" in the Styles group of the Home tab.

2

Point to "Highlight Cells Rules," and then choose "Text That Contains."

3

Enter the first list item in the "Format Cells That Contain the Text" box. Choose the fill color from the list or click "Custom Format" at the bottom of the list to select a custom font and fill color. Click "OK."

4

Repeat for each list item and fill color you want to apply. Save the worksheet before closing it.

References

  • Microsoft Office Support: Create a Drop-Down List
  • Microsoft Office Support: Add, Change, Find, or Clear Conditional Formats

Warnings

  • Information in this article applies to Excel 2013. It may vary slightly or significantly with other versions or products.

Writer Bio

Tricia Goss' credits include Fitness Plus, Good News Tucson and Layover Magazine. She is certified in Microsoft application and served as the newsletter editor for OfficeUsers.org. She has also contributed to The Dollar Stretcher, Life Tips and Childcare Magazine.

How to Create a Drop-Down Box With Color

How to Duplicate Words in Excel

How to Show a List of Values in a Cell in Excel

How to Highlight Every Other ID in Excel

How to Set a Low Inventory Alert in Excel

How to Create a Drop-Down List From a Range of Cells on Another Worksheet

How to Identify Duplicate Items in Excel

How to Insert a Caret in Excel

How Do I Create a Column Chart in Excel & Then Move It to a New Page in My Workbook?

How to Make a Drop Down Box to Allow Multiple Selections in Excel

How to Add a Hyphen on Excel

How to Insert a Combo Box in Excel 2010

Most Popular

  • 1 How to Create a Drop-Down Box With Color
  • 2 How to Duplicate Words in Excel
  • 3 How to Show a List of Values in a Cell in Excel
  • 4 How to Highlight Every Other ID in Excel