← 30 Days Excel Series
Day 12 of 30IntermediateData Quality

Day 12: Data Validation

5 questions · Excel Interview Preparation

Q1

What is data validation in Excel and why is it important?

Data validation restricts what a user can enter into a cell — only specific values, numbers in a range, dates, text length, or items from a list. It prevents bad data from entering your workbook at the source. Important because: downstream formulas, Pivot Tables, and VLOOKUP all break or give wrong results when data is inconsistent (e.g. "North ", "north", and "NORTH" in a region column are three different values to Excel).

💡 Interview tip: Data validation is a data quality control tool — set it up before sharing a workbook with non-analysts.
Q2

How do you create a dropdown list using data validation?

Select the cell(s) → Data → Data Validation → Allow: List → Source: either type values separated by commas ("North,South,East,West") or reference a range on another sheet. For maintainable dropdowns, always reference a range — then adding a new item to the list automatically adds it to all dropdowns. Name the range for even easier referencing.

💡 Interview tip: Reference a named range for the dropdown source — never hardcode the list in the validation rule itself.
Q3

How do you create a dependent (cascading) dropdown — where the options in one dropdown depend on a selection in another?

This requires INDIRECT function and named ranges. Step 1: create named ranges for each category's options — name the North options range "North", South options range "South", etc. Step 2: in the first dropdown (Region), select the region. Step 3: in the second dropdown (City), set the Source to =INDIRECT(A2) where A2 contains the region selection. INDIRECT converts the text value in A2 to a range reference matching the named range.

💡 Interview tip: The named range names must match the options in the first dropdown exactly — including case. This is the most common failure point.
Q4

What happens if someone pastes data into a cell with data validation?

Pasting data bypasses data validation — the restriction is not enforced on pasted values. This is a known limitation of Excel data validation. To detect invalid data that has been pasted in: Data → Data Validation → Circle Invalid Data — Excel draws a red circle around any cell that does not meet its validation rule. Users then have to manually correct circled cells.

💡 Interview tip: "Circle Invalid Data" for auditing is an advanced feature most analysts do not know about — mentioning it in an interview stands out.
Q5

How do you use data validation to restrict numeric input to a specific range?

Data → Data Validation → Allow: Whole Number or Decimal → set the condition (between, greater than, less than, etc.) and the Min/Max values. You can also reference cells for Min/Max to make the limits dynamic. Add an Input Message (shown as a tooltip when the cell is selected) explaining what is expected, and an Error Alert that appears when an invalid entry is attempted — choose Stop to prevent the entry, Warning to allow it with a warning, or Information to just show a message.

💡 Interview tip: Always add an Input Message explaining the expected input — it prevents confusion for non-technical users.
← Day 11All DaysDay 13 →

Want 1:1 Excel coaching?

Join EVIKA Academy for live Excel training, real projects, and placement support in Delhi NCR.

Book Free Demo →
Best Data Analytics Course in Noida Delhi NCR | EVIKA ACADEMY