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

How to Use 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.

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 applying Data Validation. Here we want to put the condition that only numbers between 40 & 100 can be given as input.

How to Use Data Validation in Excel with Color

  • At first, we will select the Column where we will apply Data Validation. Here I have selected Acquired Number Column.

How to Use Data Validation in Excel with Color

  • Then to apply Data Validation follow the Data tab >> Data Tools >> Data Validation.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

  • Upon selecting Between Type 40 in Minimum box or whatever value we want to be minimum & then Type 100 in Maximum.
  • Then click OK.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

Steps:

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.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

  • Now to Fill the Cells with Color select Fill >> ( Choose desired Color ) >> OK.

How to Use Data Validation in Excel with Color

  • Now the 2nd dialog box will be gone. Click Ok again on the first dialog box.

How to Use Data Validation in Excel with Color

  • Now we will see that all Cells of the Acquired Number column having values between 40 & 59 will be Colored Yellow.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

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

Read More: Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)


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.

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 Dataset Validation.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

  • Now click OK on the Data Validation Dialogue box.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

  • Finally, we will have our Grade column.

How to Use Data Validation in Excel with Color

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.

How to Use Data Validation in Excel with Color

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

How to Use Data Validation in Excel with Color

  • Then we will Click on Format.

How to Use Data Validation in Excel with Color

  • Then select Fill >> ( Choose desired Color ) >> OK.

How to Use Data Validation in Excel with Color

  • Now again press OK in the First dialogue box.

How to Use Data Validation in Excel with Color

  • Now we will have Green Color in Grade Column having A+.

How to Use Data Validation in Excel with Color

  • Following the same for each Grade we will have a complete dataset with different Colors for different Grades.

How to Use Data Validation in Excel with Color

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


Similar Readings:


Method 3. Data Validation Selecting Date with Color

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

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

Read More: Data Validation Drop Down List with VBA in Excel (7 Applications) 


Method 4. 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 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.

Steps:

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)


Practice Worksheet

Here we have provided you with a Practice Worksheet. Try it.


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.


Related Articles

Asif Khan Pranto

Asif Khan Pranto

Hello! I'm Asif here, currently working with Exceldemy as an Excel & VBA Content Developer. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My goal is to work with an organization which will give myself a chance to upgrade besides having a real impact on our surroundings. I'm passionate about travelling new communities & trekking. In my leisure period I usually read books. I've completed graduation in Mechanical Engineering & now I am pursuing Master of Development Studies to experience a new spectrum of knowledge. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo