In this article, we will learn how to Use Data Validation in Excel with Color. Data Validation is an element in Excel which is utilized to control what users can input into a cell. It likewise permits users to show an error message while users attempt to enter invalid information. Using Color with Data Validation makes our job easier & more accurate.
Here we have a dataset having Serial No, Candidate’s Name, Acquired Number, DoB & Candidate’s Code. We will use different Columns of our dataset to demonstrate different methods of Using Data Validation in Excel with Color.
Download Practice Workbook
4 Methods to Use Data Validation in Excel with Color
Method 1. Using Whole Number Criteria to Use Data Validation with Color
In this method, I’ll show you how to use the Data Validation feature selecting Whole Number criteria with Color. At first, I’ll show how to Use Data Validation with Whole Numbers then I’ll show how to Use Color with it applying Conditional Formatting.
Suppose we have a dataset of Job recruitment exams having Serial No, Candidate’s Name & Acquired Number Column. The Acquired Number Column is blank which we will now Fill Up applying Data Validation. Here we want to put the condition that only numbers between 40 & 100 can be given as input.
- At first, we will select the Column where we will apply Data Validation. Here I have selected Acquired Number Column.
- Then to apply Data Validation follow the Data tab >> Data Tools >> Data Validation.
- By default Settings Tab will open. Then click the Allow box & select Whole number.
- After that Click, the Data box & you will see a lot of options. You can select any options from here & to satisfy our criteria we will select Between option to validate only data between 40 & 100.
- Upon selecting Between Type 40 in Minimum box or whatever value we want to be minimum & then Type 100 in Maximum.
- Then click OK.
- Now Fill Up the Acquired Number Column.
- Here notice that if you want to enter any number less than 40 or more than 100, Excel will show you an error message.
- In the image below when we tried to insert 34 in Cell 3D, It displayed an error message. Thus barring you from inputting a number that doesn’t fulfill our criteria.
- Let’s Fill up the Acquired Number Column now.
Suppose now you want to display Colors to your Data Validation Column. You want Yellow Color for 40-59 Number range & Green Color for 60-100 Number range. We will use the Conditional Formatting feature to execute this.
- First, select the column where you want to apply Colors. Here we have selected the Acquired Number.
- Then Follow Home tab >> Conditional Formatting >> New Rule.
- Upon clicking New Rules you will find a lot of options to apply & select any which suits you most.
- Here we have selected Format only cells that contain.
- Then go to Edit the rule description part.
- By default the first box will be Cell Value You can change it according to your need.
- Then we have selected between & then type 40 and 59 in the boxes respectively as your limiting values.
- After that click Format.
- Now to Fill the Cells with Color select Fill >> ( Choose desired Color ) >> OK.
- Now the 2nd dialog box will be gone. Click Ok again on the first dialog box.
- Now we will see that all Cells of the Acquired Number column having values between 40 & 59 will be Colored Yellow.
- Following the same procedure for the 60-100 range I have Colored it Green.
- Now we can easily differentiate between numbers range watching at the Colors.
We can also follow the same procedure for the Decimal option instead of the Whole number option in the dialog box.
Method 2. Create Drop-Down List with Color Using Data Validation
In this method, I’ll show you how to create a Drop-Down Using Data Validation then Color it.
Suppose we have a dataset like the previous one with an additional Grade column where we want to input the Candidate’s Grade depending on their Number range. The Grade-Number table or our Source for the Drop-Down is provided beside the dataset.
- At first, we have to select the Grade Column for applying Dataset Validation.
- Now we will follow the previous steps to open the Data Validation Dialog box then Select List from Validation Criteria to Create Drop-Down.
- Then click Source to add source.
- Now we will select our Source with Mouse.
- Here our Source is Grade Column Values from Number-Grade Table beside the dataset.
- Then click on the Source button.
- Now click OK on the Data Validation Dialogue box.
- Now we will see a Drop Down menu in every Cell of the Grade Column.
- Thus We will Select the Drop Down menu & select a Grade for each Cell.
- Finally, we will have our Grade column.
Suppose now we want to have different Colors for different Grades. Using the Conditional Formatting feature we can do this.
- Follow the previous steps to open the Conditional Formatting Dialogue Box.
- Now select Specific Text >> Containing & then click on the Source button.
- Now click on the Source one by one.
- At first, we want Green Color for Grade A+.
- So we will select A+ Cell only then click on the Source button.
- Then we will Click on Format.
- Then select Fill >> ( Choose desired Color ) >> OK.
- Now again press OK in the First dialogue box.
- Now we will have Green Color in Grade Column having A+.
- Following the same for each Grade we will have a complete dataset with different Colors for different Grades.
- Excel Data Validation Alphanumeric Only (Using Custom Formula)
- Excel Data Validation Based on Another Cell Value
- How to Make a Data Validation List from Table in Excel (3 Methods)
- Default Value in Data Validation List with Excel VBA (Macro and UserForm)
Method 3. Data Validation Selecting Date with Color
In this method, I will show you how to Use Color with Data Validation of Date.
Suppose we have a dataset with Serial No, Candidate’s Name & DoB Column. Now we want to sort out candidates depending on Dob & only candidates born between 01-01-1997 & 01-01-2003 are eligible. Here we will apply Data Validation with Date.
- First, select Dob Column where we want to apply Data Validation.
- Then open the Data Validation dialogue box following previous procedures.
- Then follow Date >> between >> Start date >> End date >> OK.
- Now we will have our DoB Column with valid dates.
- Suppose we want to Color Dob Column with two different categories. Candidates born between 01-01-1997 & 31-12-1999 will be marked Orange & Candidates born between 01-01-2000 & 01-01-2003 will be marked Green.
- We have to select the DoB Column.
- Then we will open the New Formatting Rule dialogue box following the previous method(anchor).
- Then follow Cell Value >> between >> 01-01-1997 >> 31-12-1999 >> Format.
- Now I have followed the same procedures mentioned in method 1 to select Color & here is the outcome.
- We can follow the same for all ranges of Date & get out the desired output.
We can also apply the same procedures for Data Validation of Time.
Method 4. Data Validation Selecting Text Length with Color
Here, I will demonstrate how to Use Color with Data Validation of Text Length.
Suppose we have a dataset with Candidate’s Code in Column F. It’s tough to type Code correctly since they don’t have any direct literal meaning & people often mistakes. To minimize this we strict the length of Codes with Data Validation & it will help us to stop typing Extra characters in Code.
- First I have selected the Candidate’s Code.
- Now upon opening Data Validation Dialouge box follow Text Length >> equal to >> 4 ( Desired character length ) >> OK.
- Now if we do any error like mistakenly adding an extra character or removing one, It will show us a warning message.
Now we want to Color Cells with Blue having ‘A’ & Color Green having ‘B’ in the Code.
- To do this first select Candidate’s Code column again.
- Following the procedures shown in Method 3, Choosing Specific Value & then containing A & B respectively we will have the desired output.
Related Content: Excel Data Validation Drop Down List with Filter (2 Examples)
Here we have provided you with a Practice Worksheet. Try it.
In the article above we have learned How to Use Data Validation in Excel with Color. Using this will make our job easier & minimize errors while working. Hope you enjoyed reading this article. If you have any doubt please leave a comment.
- How to Use Data Validation List from Another Sheet (6 Methods)
- Excel VBA to Create Data Validation List from Array
- How to Use Named Range for Data Validation List with VBA in Excel
- Use Custom VLOOKUP Formula in Excel Data Validation
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)