Data Validation and Error Guards

Dropdown Lists and Input Constraints

Bad data enters spreadsheets through human input. Someone types "West" in one row and "west " in another. Someone enters a date as "01/05/2026" in one cell and "2026-01-05" in the next. Someone types "N/A" into a numeric column. Data validation prevents these errors at the point of entry.

Creating a Dropdown List

A dropdown list constrains a cell to a set of predefined values. Select the cell or range, go to Data > Data Validation, and choose "List" under Allow. Enter the valid values in the Source field, either as a comma-separated list or a range reference.

Source: East,West,North,South

Or reference a range that holds the valid values:

Source: =Lists!$A$1:$A$4

Referencing a range is better than hardcoding the list because you can update the valid values in one place. Add a new region to the Lists sheet and every dropdown that references it immediately includes the new option.

Why Dropdowns Beat Free Text

Free text invites variation. Given a blank cell, one person types "West", another types "W", another types "west", and another types "West Region". A pivot table on this column shows five different values for the same region. A dropdown eliminates this variation by construction — the user can only select from the options you provide.

Dropdowns also prevent typos. A user who needs to type "Pennsylvania" might misspell it. A dropdown removes the typing entirely.

Extending Dropdowns to Tables

If your data is in an Excel Table (Ctrl+T), dropdowns applied to a column automatically extend to new rows added to the table. Without a table, you must manually extend the validation range. This is another reason to use Excel Tables as the foundation for any data entry sheet.

Input Messages and Error Alerts

Data validation has two optional components beyond the rule itself:

Input Message appears when the user selects the cell. Use it to provide instructions: "Select a region from the dropdown. Do not type a custom value."

Error Alert appears when the user enters an invalid value. You choose the style:

• Stop: Prevents the invalid entry entirely. The user must enter a valid value or cancel.
• Warning: Shows a warning but allows the user to proceed.
• Information: Shows a message but allows the user to proceed.

For data quality, use Stop. A warning that can be dismissed is not a constraint — it is a suggestion.

Dependent Dropdowns

A dependent dropdown changes its options based on another cell's value. Suppose you have a Country dropdown and a Region dropdown. When the user selects "USA", the Region dropdown shows US states. When they select "Canada", it shows Canadian provinces.

Implement this with the INDIRECT function. Create named ranges that match each country's options (e.g., a range named USA containing US states, a range named Canada containing provinces). Then set the Region dropdown's source to:

=INDIRECT(A2)

Where A2 is the Country cell. INDIRECT converts the text in A2 into a range reference. When A2 contains "USA", INDIRECT returns the range named USA, and the dropdown shows US states.

Numeric and Date Validation Rules

Not all constraints are lists. Numeric fields need range constraints, date fields need boundary constraints, and text fields need length or format constraints. Data validation handles all of these.

Numeric Validation

Select the cell or range, go to Data > Data Validation, and choose the appropriate type under Allow:

• Whole number: Restricts to integers. Set the operator (between, greater than, less than, etc.) and the boundary values.
• Decimal: Allows fractional numbers. Same operators.
• Custom: Uses a formula to define validity.

For a quantity column that must be a positive integer:

Allow: Whole number
Data: greater than
Minimum: 0

For a discount percentage between 0 and 100:

Allow: Decimal
Data: between
Minimum: 0
Maximum: 100

For a custom rule — say, a price that must be greater than the cost in another column:

Allow: Custom
Formula: =B2>C2

The formula returns TRUE for valid entries and FALSE for invalid ones. The cell reference is relative to the top-left cell of the selected range, so the formula applies correctly to each row.

Date Validation

Dates are numbers in Excel, so date validation uses the same operators as numeric validation. For an order date that must be in 2026:

Allow: Date
Data: between
Start date: 2026-01-01
End date: 2026-12-31

For a date that must be in the future (e.g., a renewal date):

Allow: Date
Data: greater than
Start date: =TODAY()

Using =TODAY() as the boundary makes the rule dynamic — it always compares against the current date, not a hardcoded value.

Text Length and Format

For text fields, you can constrain length:

Allow: Text length
Data: equal to
Length: 10

This is useful for fixed-format fields like phone numbers or ZIP codes.

For format constraints (e.g., an email must contain @), use a custom formula:

Allow: Custom
Formula: =ISNUMBER(FIND("@", A2))

FIND returns the position of @ in the text, or an error if not found. ISNUMBER converts that to TRUE or FALSE.

Validation vs Data Cleaning

Validation prevents bad data at entry. It does not fix bad data that already exists. If you inherit a spreadsheet with 10,000 rows of unvalidated data, adding validation rules will not clean the existing values — it only applies to new entries.

To find existing violations, use conditional formatting (covered in the next chapter) or a helper column with a formula that flags invalid rows. Once identified, clean the data manually or with Power Query (covered in the next topic).

Circular Validation

A validation rule that references the cell it validates creates a circular reference. If cell A1 has a validation formula =A1>0, Excel cannot evaluate the rule because the rule depends on the cell's value, which is what the rule is checking. Avoid this by referencing other cells in validation formulas, not the validated cell itself.

Conditional Formatting for Error Detection

Conditional formatting changes a cell's appearance based on its value or a formula. It is the visual layer of data quality — it does not prevent bad data, but it makes bad data visible.

Highlighting Invalid Entries

Suppose you have a column of email addresses and want to highlight any that do not contain @. Select the range, go to Home > Conditional Formatting > New Rule, and choose "Use a formula to determine which cells to format."

=NOT(ISNUMBER(FIND("@", A2)))

Set the format to a red fill. Every cell that does not contain @ turns red immediately — no manual scanning required.

Highlighting Outliers

For numeric columns, conditional formatting can flag values that fall outside a normal range. Use a formula that compares each cell to the column's mean and standard deviation:

=ABS(A2 - AVERAGE(A$2:A$1000)) > 2 * STDEV(A$2:A$1000)

This highlights values more than two standard deviations from the mean. The $ signs lock the range so the average and standard deviation are computed once for the entire column, not relative to each cell.

Data Bars and Color Scales

Data bars fill a cell with a horizontal bar proportional to its value. Color scales color cells on a gradient from low to high. Both are useful for spotting patterns in large datasets:

• Data bars make it easy to find the largest and smallest values in a column at a glance.
• Color scales reveal gradients — a smooth transition from green to red shows where values cluster and where they diverge.

Apply these via Home > Conditional Formatting > Data Bars or Color Scales. They are formula-free and update automatically when values change.

Highlighting Duplicates

Duplicate values in a column that should be unique (like order IDs) are a common data quality issue. Conditional formatting can flag them:

1. Select the range.
2. Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
3. Choose a format (e.g., light red fill).

All duplicate values are highlighted. The first occurrence and all subsequent occurrences are flagged, so you can identify which rows to investigate.

Icon Sets

Icon sets display an icon next to each value based on its position in the range. A traffic light icon set shows green for high values, yellow for medium, and red for low. This is useful for dashboards where you want a quick visual status without reading the numbers.

Conditional Formatting vs Data Validation

Data validation prevents bad data at entry. Conditional formatting reveals bad data that already exists. They are complementary: validation is the lock on the door; conditional formatting is the security camera. Use both — validation to prevent new errors, conditional formatting to catch existing ones.

Performance Considerations

Conditional formatting is evaluated every time the workbook recalculates. Complex formulas applied to large ranges can slow down the workbook. Keep formatting formulas simple, apply them only to the necessary range, and avoid volatile functions like TODAY() or NOW() in formatting rules applied to thousands of cells.