How to Use Data Validation in Excel with Color (4 Ways)

In this article, we will learn how to Use Data Validation in Excel with Color. Data Validation is an element in Excel that 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, and Candidate’s Code. We will use different Columns of our dataset to demonstrate different methods of Using Data Validation in Excel with Color.

1. Using Whole Number Criteria to Use Data Validation in Excel with Color

In this method, I’ll show you how to use the Data Validation feature selecting Whole Number criteria with Color. First, I’ll show how to use Data Validation with Whole Numbers then I’ll show how to use Color by applying Conditional Formatting.

Steps:

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 by 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 the 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 D3, 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.

Steps:

Suppose now you want to display Colors in 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 the Home tab >> Conditional Formatting >> New Rule.

Upon clicking New Rule 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 needs.
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 the 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 the Colors.

We can also follow the same procedure for the Decimal option instead of the Whole number option in the dialog box.

2. Creating Drop-Down List with Color Using Excel Data Validation

In this method, I’ll show you how to create a Drop-Down Using Data Validation and then Color it.

Steps:

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 Data 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.

Steps:

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 Sources one by one.
At first, we wanted a Green Color for Grade A+.
So we will select A+ Cell only and then click on the Source button.

Then we will Click on Format.

Then select Fill >> (Choose the 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.

3. Selecting Date with Color with Data Validation in Excel

In this method, I will show you how to Use Color with Data Validation for Date format.

Steps:

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 the 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.

Steps:

Suppose we want to Color the Dob Column with two different categories. Candidates born between 01-01-1997 & 31-12-1999 will be marked Orange and 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.
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.

4. Applying Data Validation Selecting Text Length with Color

Here, I will demonstrate how to Use Color with Data Validation of Text Length.

Steps:

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 the Data Validation Dialog box follow Text Length >> equal to >> 4 (Desired character length) >> OK.

Now if we make any error like mistakenly adding an extra character or removing one, It will show us a warning message.

Steps:

Now we want to Color Cells with Blue having ‘A’ and 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.

Download Practice Workbook

Use Data Validation with Color.xlsx

Conclusion

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.

<< Go Back to Data Validation in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

How to Use Data Validation in Excel with Color (4 Ways)

1. Using Whole Number Criteria to Use Data Validation in Excel with Color

2. Creating Drop-Down List with Color Using Excel Data Validation

3. Selecting Date with Color with Data Validation in Excel

4. Applying Data Validation Selecting Text Length with Color

Conclusion

Related Articles

Leave a reply Cancel reply