(Note: This guide on how to create excel drop down list with color is suitable for all Excel versions including Office 365) Show
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:
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? ExampleThere 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 colorThe 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 optionsThe 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.
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.
In this case, when we select North from the dropdown, the text will be in white and the background will be red. 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 CellsWe 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.
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 QuestionsWhat 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 ThoughtsData 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 CalderChris “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! |