How-to: Validate data based on the value of a different cell in Excel

 Sep 05, 2016

You can get Excel not to accept a cell value if it falls outside a criterion. You specify the criterion in Data Validation located in the Data tab. The criterion, however, can sometimes be dynamic in the way that it might depend on the value of another cell or cells. In these situations, you need to use a Custom Criteria. Here’s an example:

Let’s say we want to allow input in a cell only if the value of another cell is either Option1 or Option3, but not Option2.

So we click on C6 in the picture and apply a Data Validation:

Data tab > Data Validation

And set values as shown in the picture below:

That’s all you need. If the user selects Option2 in C2, then Excel does not allow the user to add any value in C6, because any value there will be invalidated.

You can make the user see that the cell C6 is not available for Option 2 by changing its cell colour to grey using Conditional Formatting like so:

Click on C6 and go, Home > Conditional Formatting > New Rule

Select Use a formula to determine which cells to format and enter this formula;


And then click on Format and set the Fill colour to grey and click on OK and OK.

Now every time C6 is set to Option2, C2 will turn grey indicating that it is not available.

For more information, take a look at New Horizons' Microsoft Excel training courses.

Cyrus Mohseni

How do your Excel skills stack up?   

Test Now  

How do your Excel skills stack up?   

Test Now  

How do
your Excel skills
stack up?

Grade your skills now

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
Back to top