Simon Sez IT

Online software training and video tutorials for Microsoft, Adobe & more

  • Course List
    • Adobe
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Flash
        • Flash CS5
      • InDesign
        • InDesign CS6
        • InDesign CS5
      • Photoshop
        • Photoshop CS6
        • Photoshop CS5
        • Adobe Photoshop CS4
      • Photoshop Elements
        • Photoshop Elements 2022
        • Photoshop Elements 2019
        • Photoshop Elements 2018
        • Photoshop Elements 15
        • Photoshop Elements 14
        • Photoshop Elements 13
        • Photoshop Elements 12
        • Photoshop Elements 11
        • Photoshop Elements 10
        • Photoshop Elements 9
        • Photoshop Elements 8
    • Microsoft
      • Access
        • Access 2021 Beginners
        • Access 2019
        • Access 2019 Advanced
        • Access 2016
        • Access 2016 Advanced
        • Access 2013
        • Access 2013 Advanced
        • Access 2010
        • Access 2010 Advanced
        • Access 2007
      • Excel
        • Data Analytics in Excel
        • Excel 2021 Advanced
        • Excel 2021 Intermediate
        • Excel 2021 Beginners
        • PivotTables for Beginners
        • Excel Dashboards
        • Advanced Formulas in Excel
        • Excel for Business Analysts
        • Advanced PivotTables
        • Power Pivot, Power Query and DAX in Excel
        • Excel 2019 Beginners (Mac)
        • Excel 2019 Beginners
        • Excel 2019 Advanced
        • Excel 2016 Beginners
        • Excel 2016 Intermediate
        • Excel 2016 Advanced
        • Excel 2013
        • Excel 2013 Advanced
        • Excel 2010 Beginners
        • Excel 2010 Advanced
        • Excel 2007
      • OneNote
        • OneNote Desktop and Windows 10
        • OneNote 2016
      • Outlook
        • Outlook 2021
        • Outlook 2019
        • Outlook 2016
        • Outlook 2013
        • Outlook 2010
        • Outlook 2007
      • Power Automate
        • Introduction to Power Automate
      • Power BI
        • Power BI
        • Power BI Intermediate
      • PowerPoint
        • PowerPoint 2021
        • PowerPoint 2019
        • PowerPoint 2016
        • PowerPoint 2013
        • PowerPoint 2010
        • PowerPoint 2007
      • Project
        • Project 2021 Beginners
        • Project for the Web
        • Project 2019
        • Project 2019 Advanced
        • Project 2016
        • Project 2016 Advanced
        • Project 2013
        • Project 2013 Advanced
        • Project 2010
        • Project 2010 Advanced
      • Publisher
        • Publisher 2013
      • SharePoint
        • SharePoint Online
        • SharePoint Foundation 2013
        • SharePoint Server 2013
        • SharePoint Foundation 2010
      • Teams
        • Microsoft Teams
      • VBA
        • Macros and VBA for Beginners
        • VBA for Excel
        • VBA Intermediate Training
      • Visio
        • Microsoft Visio 2019
        • Visio 2016
        • Visio 2013
        • Microsoft Visio 2010
      • Windows
        • Windows 11
        • Windows 10 (2020 Update)
        • Windows 10
        • Windows 8
        • Windows 7
        • Windows Vista
      • Word
        • Word 2021
        • Word 2019 Advanced
        • Word 2019
        • Word 2016
        • Word 2013
        • Word 2010
        • Word 2007
    • QuickBooks
      • QuickBooks
        • QuickBooks Desktop Pro 2022
        • QuickBooks Pro 2021
        • QuickBooks Online Advanced
        • QuickBooks Online
        • QuickBooks Canada
        • QuickBooks Pro 2020
        • QuickBooks 2019
        • QuickBooks 2018
        • QuickBooks Pro 2017
        • QuickBooks Pro 2016
        • QuickBooks Pro 2015
        • QuickBooks Pro 2014
        • QuickBooks Pro 2013
        • QuickBooks Pro 2012
        • QuickBooks Pro 2011
        • QuickBooks Pro 2010
        • QuickBooks Pro 2009
    • Web Development
      • AngularJs
        • AngularJS Crash Course
      • Dreamweaver
        • Dreamweaver CC
        • Dreamweaver CS6
        • Dreamweaver CS5
        • Dreamweaver CS4
      • Bootstrap
        • Bootstrap Framework
      • Html/CSS
        • HTML/CSS Crash Course
        • HTML5 Essentials
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
      • Java
        • Java for Beginners
      • JavaScript
        • JavaScript for Beginners
        • jQuery Crash Course
      • MySql
        • MySQL for Beginners
      • PHP
        • PHP for Beginners
        • Advanced PHP Programming
      • XML
        • XML Crash Course
    • Data Analysis
      • Financial Modeling
        • Financial Risk Management
        • Financial Forecasting and Modeling
      • Alteryx
        • Alteryx Advanced
        • Introduction to Alteryx
      • Power BI
        • Power BI Intermediate
        • Power BI
      • Qlik Sense
        • Qlik Sense Advanced
        • Qlik Sense
      • R Programming
        • R Programming
      • Tableau
        • Tableau Desktop Advanced
        • Tableau Desktop
      • Python
        • Python Object-Oriented Programming
        • Pandas for Beginners
        • Introduction to Python
    • Work Productivity
      • Google Sheets
        • Google Sheets for Beginners
      • Confluence
        • Introduction to Confluence
      • Monday
        • Getting Started in Monday.com
      • Asana
        • Asana for Employees and Managers
        • Introduction to Asana
      • Jira
        • Getting Started in Jira
  • For Business
  • About Us
    • Testimonials
    • Contact Us
    • FAQ
    • Membership
    • About Us
  • Pricing
  • Free Resources
  • Sign In
  • Get Started
Home > Microsoft Excel > How to Create Excel Drop Down List With Color?

How to Create Excel Drop Down List With Color?

(Note: This guide on how to create excel drop down list with color is suitable for all Excel versions including Office 365)

Sometimes in Excel, you will have to deal with a lot of data. You will also have to enter, correct, and perform some operations on them. In most cases, the data will mostly be redundant, and typing them over and over might sound like too much work.

In such cases, you can use a dropdown to easily select the predestined values. But additionally, this too becomes redundant when the data is vast. In these cases, adding a different color to the dropdown options helps you with easy differentiation of the data. In addition to minimizing the effort taken to fill the cell, it also provides additional validation and helps the user select the pre-defined values only. 

More importantly, adding a color grading to each data entry will help provide a visual cue to the reader and ascertain things in a better way when there are multiple data.

In this article, I will tell you how to create an Excel drop down list with color along with an example.

You’ll Learn:

  • Example
  • How to Create an Excel Drop Down?
  • How to Add Colors to the Excel Drop Down List?
  • Create an Excel Drop Down List With Colors for All Cells
Download Workbook - Excel Drop Down List With Color
Download Workbook – Excel Drop Down List With Color

Related Reads:

How to Add Page Numbers in Excel? 2 Easy with Options

How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps

How to Split and Combine Data in Excel?

Example

There are several instances where a dropdown for values has proven to be very useful in Excel for academic, corporate, and personal data management.

Consider the example, where you are given a list of students who have recently taken a test in one of the four centers in a city. You have to fill in the details which will help the management know about the students’ performance.

Example for Excel drop down list with color
Example for Excel drop down list with color

The table contains 5 columns – serial number, name, test center, grade, and result. The first two columns are to be manually entered as they will have different data for each entry. But the third, fourth, and fifth columns can only take certain values. 

Drop down options
Drop down options

The test centers are located in four locations in the city and can only be East, West, North, or South. The grading system is only limited to A, B, C, D, E, and F. And, the final result can only be a Pass or a Fail.

How to Create an Excel Drop Down?

Before we learn how to add color to the dropdown list, let us first see how to add a dropdown list in Excel.

  • First, select the cell where you want to add the dropdown list.
Select the cell
Select the cell
  • Navigate to Data and click on Data Validation.
Click on Data Validation
Click on Data Validation
  • This opens up the Data Validation dialog box. From the Allow: dropdown, select List.
Click on drop down and select List
Click on drop down and select List
  • In the Source textbox, you can add the options for the dropdown separated by a comma.
Enter or select the Options
Enter or select the Options
  • Or, you can click on the upward arrow and select the options for the dropdown. It is a good practice to keep your options in a separate sheet and link them to the options. Click OK.
Select the options for the drop down
Select the options for the drop down
  • You can now see the selected cell appearing with the dropdown. Once you have added the dropdown list for one column, you can do the same for other columns.
The drop down is created
The drop down is created

Note: If you want to apply the drop-down to multiple cells either in columns or rows, select the group of cells and then use the Data Validation option to add options in the dropdown.

Also Read:

How to Transpose Excel Data? 2 Easy Methods

Importing and Cleaning Data in Excel

How to Auto Fill Data in Microsoft Excel 2016

How to Add Colors to the Excel Drop Down List?

Now that we have seen how to add a drop-down list in Excel, let us see how to add colors to the drop-down list in Excel.

  • Navigate to Home. Under Styles, click on the dropdown from Conditional Formatting. Again, click on Highlight Cell Rules and select Equal To. This means that when the particular value is selected, Excel selects the particular color assigned.
Select the condition for color
Select the condition for color
  • This opens the Equal To dialog box where you can assign a particular color for a particular text. In the first text box, enter the value to which you want to assign a color. 
  • Assign the color to the entered value using the dropdown on the left. Excel shows some default ideas for color schemes in the drop-down. 
Choose the color formatting
Choose the color formatting
  • If you are not satisfied with the color schemes Excel offers, you can change them manually using the Custom Format option from the dropdown.
  • Selecting Custom Format opens the Format Cells dialog box. 
  • To change the background color, navigate to the Fill section and select the background color. 
  • To change the color of the text, navigate to the Font section of the Format Cells dialog box and choose your desired color. 
  • Click OK.

In this case, when we select North from the dropdown, the text will be in white and the background will be red. 

The Excel drop down list color appears
The Excel drop down list color appears

Note: When you select colors, it is better if you select the cells and then assign the colors using the Conditional Formatting option.

Create an Excel Drop Down List With Colors for All Cells

We now know how to add a drop-down to the cells with colors. But, you cannot do the same process for every cell as it will be more time-consuming and non-constructive. 

  • One way to add a drop-down is to select multiple cells you want to add a drop-down to, and then navigate to Data Validation and choose the options for the dropdown. 
  • Another way to create an Excel drop-down for the upcoming cells is to create a drop-down list for one cell in each column and convert them into a table.
  • To convert the cells into a table and apply the dropdown for all the cells, select the cells which are formatted with dropdown and colors.
Select the formatted cells
Select the formatted cells
  • Now, press Ctrl+T. This converts the selected cells into a table and any entry you make after them will have the same formatting and validations.
  • In the Create Table dialog box, select the data and check the checkbox for My table has headers and click OK.
Convert to Table
Convert to Table
  • Now, navigate to Table Design in the main menu and uncheck the checkbox for Filter to remove filters.
  • Extend the table using the drag handle in the bottom rightmost cell to extend the table.
  • Now, you can enter the data in the table one after the other using the dropdown with colors.
Create all the entries in the Excel drop down list with color
Create all the entries in the Excel drop down list with color

Suggested Reads:

How to Save Excel as PDF? 5 Useful Ways

How to Find and Replace in Excel? A Step-by-Step Guide

How to Round Up in Excel(and Round Down)?

Frequently Asked Questions

What is the purpose of an Excel dropdown list with color?

Adding color coding to an Excel drop-down helps in easy differentiation of data and provides visual cues in helping the reader ascertain the data in a table.

How to easily create a dropdown in cells in Excel?

Select a cell. Navigate to the Data main menu. Click on Data Validation. Select List and then enter the values for the dropdown in the Source text box. Click OK.

How to create an Excel dropdown with symbols and color?

In the same way, enter symbols in the Source text box in Data Validation and using the Conditional Formatting map the colors with the text.

Closing Thoughts

Data Validation in addition to color coding is a powerful tool in Excel. If you want to know more about dropdowns and data validation, read our article on dynamic dropdowns in Excel. 

Please visit our free resources center for more high-quality Excel guides. 

Ready to take the next step and hone your skills in Excel?

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!

Most Popular Posts

  • Kanban vs Scrum: Project Management Methodologies and Their Differences [2022]
  • How to Insert a Checkbox in Excel? 3 EASY Examples
  • How to Autofit Excel Cells? 3 Best Methods
  • XLOOKUP Google Sheets – 4 Best Alternatives!
  • Dashboards in Excel Using Pivot Tables, Pivot Charts and Slicers
  • Free Microsoft Project Training Course
  • Free Microsoft Access Tutorial for Beginners (3.5 Hours Video)
  • How to Use Blending Mode With Layers in Adobe Photoshop Elements 15

Similar Posts

How to Remove Comma in Excel? 5 Easy Ways

Paying Employees in QuickBooks Pro 2013

Using Adjustment Layers in Photoshop Elements 11

HLOOKUP in Excel

Excel Bootcamp for Employees (100+ Best Courses)

Create Access 2013 Forms using the Form Wizard

Course Categories

  • Adobe
  • Data Analysis
  • QuickBooks
  • Microsoft
  • Web Development
  • Work Productivity

About Us

  • About Us
  • Free Resources
  • Affiliates
  • Become an Instructor

Products

  • Pricing and Plans
  • Business Pricing
  • Government Discounts
  • Non-Profit Discounts

Support

  • FAQ’s
  • Contact Us
  • DVD support

Connect

YoutubeFacebookLinkedIn
© 2023 Simon Sez IT, Inc.
  • Terms
  • Privacy Policy
  • Sitemap
888.817.6665 Monday thru Friday 7:30 a.m. - 5:00 p.m. (ET)